Introduction
The purpose of this article is a library composition of JavaScript functions that I have been able to collect that are equivalents to Visual Basic functions.
A couple of months ago, I started learning JQuery Mobile and this exposed me to a lot of JavaScript and PHP coding. From a Visual Basic background, I wanted an easy transition to JavaScript and I must admit, learning JQuery and JavaScript were not as hard as I expected. As long as I knew what I wanted to achieve, Googling around the internet seemed to provide most of the answers. Some worked and some did not. I decided that I will then compile this into a single library of all functions that I have learned and or found useful, others I conceptualized.
The library files here have been split into two files, one called support.js and the other websql.js for all web SQL related functions.
Quote:
Javascript Vb Equivalent Functions
Background
The basic idea behind this is for anyone using the JavaScript function to use a similar code syntax like what they used in Visual Basic when writing programs and the functions with whatever they return will perform the same functionality. As an example, Visual Basic has a Len
function, which returns the length of a string
as an integer. There is no Len
function in JavaScript but a length
function that you can pass to a string
to return its length
. These scripts then provide a gateway for ease of implementation to anyone between the two. These scripts are for use in developing JavaScript applications and not VB ones. In explaining the source code, I will give a description of each function and then the applicable JavaScript function that does the same functionality. I intend to add to these as I find more functionality and functions.
Some silly assumptions: You are familiar with Visual Basic & JavaScript programming.
This article has been written with CodeProject.Show offline.
Using the Code
FileNameValid(str)
- Returns a string
with all special characters removed.
function FileNameValid(str) {
var s = str + '';
s = s.replace(/\s+/gi, '-');
return s.replace(/[^a-zA-Z0-9\-]/gi, '');
}
ValidID(str)
- Returns a string
with all spaces turned into hash -
.
function ValidID(str) {
var s = str + '';
s = s.replace(/\s+/gi, '-');
return s;
}
NumericOnly(svalue)
- Returns all numbers in a string
. (This is a long version.)
function NumericOnly(svalue) {
var sout,
lenvalue,
i,
spart;
sout = "";
lenvalue = Len(svalue);
for (i = 1; i <= lenvalue; i++) {
spart = Mid(svalue, i, 1);
switch (true) {
case ((spart) == "1"):
sout = sout + spart;
break;
case ((spart) == "2"):
sout = sout + spart;
break;
case ((spart) == "3"):
sout = sout + spart;
break;
case ((spart) == "4"):
sout = sout + spart;
break;
case ((spart) == "5"):
sout = sout + spart;
break;
case ((spart) == "6"):
sout = sout + spart;
break;
case ((spart) == "7"):
sout = sout + spart;
break;
case ((spart) == "8"):
sout = sout + spart;
break;
case ((spart) == "9"):
sout = sout + spart;
break;
case ((spart) == "0"):
sout = sout + spart;
break;
case ((spart) == "-"):
sout = sout + spart;
}
}
return sout;
}
NewId()
- Returns a unique id based on system date.
function NewId() {
var dc = new Date();
var id = new String(dc.getTime());
id += new String(getRandomInt(0, 100));
return id;
}
Replace(str,fnd,rpl)
- Returns a string
in which a specified substring has been replaced with another substring a specified number of times.
function Replace(str,fnd,rpl) {
var regex;
var cnt = null;
var st = null;
var cmp;
if(st===null||st===""||st<0){st=1;}
st-=1;
if(cnt===null||cnt===""||cnt<0){cnt=0;}
if(st>=0){str=str.substr(st,str.length);}
fnd=fnd.replace(/([\$\^\[\(\)\|\*\+\?\.\\])/g,'\\$1');
var opt='';
cmp=cmp+"";
if(cmp==='1'){opt='i';}
if(cnt>0) {
regex=new RegExp(fnd,opt);
for(var i=0;i<cnt;i++) {
str=str.replace(regex,rpl);
}
}else{
opt+='g';
regex=new RegExp(fnd,opt);
str=str.replace(regex,rpl);
}
return str;
}
IsNumeric(sValue)
- Returns a boolean indicating whether an object is numeric / not.
function IsNumeric(sValue) {
return!isNaN(sValue);
}
FixParent(sField, sValue)
- Returns a string
in sField-sValue
format if sValue
is numeric, else returns sValue
.
function FixParent(sField, sValue) {
var sout;
if (IsNumeric(sValue) == true) {
sout = sField + "-" + sValue;
return sout;
} else {
return sValue;
}
}
getRandomInt(min, max)
- Returns a random integer between the two specified values.
function getRandomInt(min, max) {
return Math.floor(Math.random() * (max - min + 1)) + min;
}
NewDate()
- Returns the current system date time.
function NewDate() {
return new Date();
}
Right(str, n)
- Returns a string
containing a specified number of characters from the right side of a string
.
function Right(str, n){
if (n <= 0)
return "";
else if (n > String(str).length)
return str;
else {
var iLen = String(str).length;
return String(str).substring(iLen, iLen - n);
}
}
Left(str, n)
- Returns a string
containing a specified number of characters from the left side of a string
.
function Left(str, n) {
var s = str + '';
var iLen = s.length;
if (n <= 0) {
return "";
} else if (n >= iLen) {
return str;
} else {
return s.substr(0, n);
}
}
Mid(strMid, intBeg, intEnd)
- Returns a string
containing a specified number of characters from a string
.
function Mid(strMid, intBeg, intEnd) {
if (strMid === null || strMid === '' || intBeg < 0) {
return '';
}
intBeg -= 1;
if (intEnd === null || intEnd === '') {
return strMid.substr(intBeg);
} else {
return strMid.substr(intBeg, intEnd);
}
}
CStr(str)
- Returns a string
from the passed object.
function CStr(str) {
var s = str;
s = s.toString();
return s;
}
LCase(str)
- Returns a lower case string
of the passed value.
function LCase(str) {
return str.toLowerCase();
}
InStr(searchStr, searchFor)
- Returns the position of a string
within another.
function InStr(searchStr, searchFor) {
if (Len(searchStr) = 0) {
return 0;
}
var s = searchStr;
var s1 = searchFor;
s = s.toString();
s1 = s1.toString();
s = LCase(s);
s1 = LCase(s1);
var loc = s.indexOf(s1) + 1;
return loc;
}
InStrRev(srchStr, fndStr, start, cmp)
- Returns the last position of a string
within another.
function InStrRev(srchStr, fndStr, start, cmp) {
if (!fndStr || fndStr === null) {
fndStr = "";
}
if (!cmp) {
cmp = 0;
}
srchStr.toString();
if (cmp == 1) {
srchStr = srchStr.toLowerCase();
fndStr = fndStr.toLowerCase();
}
if (!start || !IsNumeric(start)) {
start = -1;
}
if (start > -1) {
srchStr = srchStr.substr(0, start);
}
var loc;
if (fndStr === "") {
loc = srchStr.length;
} else {
loc = srchStr.lastIndexOf(fndStr) + 1;
}
return loc;
}
Len(str)
- Returns an integer that contains the number of characters in a string
.
function Len(str) {
str += '';
return str.length;
}
Chr(num)
- Returns the character associated with the specified character code.
function Chr(num) {
var res = String.fromCharCode(num);
return res;
}
The opposite of this is Asc
.
function Asc(str) {
return str.charCodeAt(0);
}
FM()
- Returns character 254 string
.
function FM() {
return Chr(254);
}
VM()
- Returns character 253 string
.
function VM() {
return Chr(253);
}
Quote()
- Returns character 34 string
.
function Quote() {
return Chr(34);
}
InQuotes(value)
- Returns the passed string
enclosed in double quotes.
function InQuotes(value) {
var res = Quote + value + Quote
return res;
}
MakeMoney(nStr)
- Returns the passed string
as Currency
.
function MakeMoney(nStr) {
nStr = ProperAmount(nStr);
nStr += '';
x = nStr.split('.');
x1 = x[0];
x2 = x.length > 1 ? '.' + x[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, '$1' + ',' + '$2');
}
return x1 + x2;
}
isValidDate(dateStr)
- Returns a boolean of whether a date is valid or not, valid date formats are MM/DD/YY
, MM/DD/YYYY
, MM-DD-YY
, MM-DD-YYYY
.
function isValidDate(dateStr) {
if (Len(dateStr) = 0) {
return "0"
}
var msg = "";
var datePat = /^(\d{1,2})(\/|-)(\d{1,2})\2(\d{4})$/;
var matchArray = dateStr.match(datePat);
if (matchArray == null) {
return "0";
}
month = matchArray[1];
day = matchArray[3];
year = matchArray[4];
if (month < 1 || month > 12) {
msg = "Month must be between 1 and 12.";
return "0";
}
if (day < 1 || day > 31) {
msg = "Day must be between 1 and 31.";
return "0";
}
if ((month == 4 || month == 6 || month == 9 || month == 11) && day == 31) {
msg = "Month " + month + " doesn't have 31 days!";
return "0";
}
if (month == 2) {
var isleap = (year % 4 == 0 && (year % 100 != 0 || year % 400 == 0));
if (day > 29 || (day == 29 && !isleap)) {
msg = "February " + year + " doesn't have " + day + " days!";
return "0";
}
}
if (day.charAt(0) == '0')
day = day.charAt(1);
return msg;
}
MvRest(svalue, iposition, delimiter)
- Returns the rest of the string
from a particular position of a delimited string
. Index is 1 based.
function MvRest(svalue, iposition, delimiter) {
var mvalues,
tvalues,
xvalue,
x,
y,
resultx;
var lendelim;
lendelim = Len(delimiter);
mvalues = Split(svalue, delimiter);
tvalues = mvalues.length - 1;
iposition = iposition - 1;
if (iposition <= -1) {
xvalue = mvalues[tvalues];
return xvalue;
}
if (iposition > tvalues) {
return "";
}
resultx = "";
x = iposition + 1;
for (y = x; y <= tvalues; y++) {
xvalue = mvalues[y];
resultx = resultx + xvalue + delimiter;
}
resultx = Left(resultx, Len(resultx) - lendelim);
return resultx;
}
Age(birthDate)
- Returns an integer age based on a passed birth date, valid date format is YYYY-MM-DD
. This uses the system date.
function Age(birthDate) {
birthDate = Replace(birthDate, ".", "-");
var yyyy = MvField(birthDate,1, "-");
var mmmm = MvField(birthDate,2, "-");
var dddd = MvField(birthDate,3, "-");
var agex = getAge(mmmm,dddd,yyyy);
agex = MvField(agex,1, ".");
return agex;
}
getAge(birthMonth, birthDay, birthYear)
- Returns an integer age based on month
, day
and year
passed.
function getAge(birthMonth, birthDay, birthYear) {
todayDate = new Date();
todayYear = todayDate.getFullYear();
todayMonth = todayDate.getMonth();
todayDay = todayDate.getDate();
age = todayYear - birthYear;
if (todayMonth < birthMonth - 1) {
age--;
}
if (birthMonth - 1 == todayMonth && todayDay < birthDay) {
age--;
}
return age;
}
MvSum(strvalues, delim)
- Returns a value of the sum of all the delimited values, e.g., MvSum("123,5,6",",")
will return 134
.
function MvSum(strvalues, delim) {
var dtot,
i;
dtot = 0;
var spvalues;
spvalues = Split(strvalues, delim);
var dlen;
dlen = spvalues.length - 1;
for (i = 0; i <= dlen; i++) {
dtot = parseFloat(dtot) + parseFloat(spvalues[i]);
}
dtot = Round(dtot, 2);
return dtot;
}
InSingleQuote(strV)
- Returns a string
of the passed value in single quotes.
function InSingleQuote(strV) {
return "'" + strV + "'";
}
MonthNumber(strM)
- Returns the month number from a passed month name.
function MonthNumber(strM) {
strM = Left(strM,3);
strM = LCase(strM);
switch (true) {
case ((strM) == "jan"):
return 1;
break;
case ((strM) == "feb"):
return 2;
break;
case ((strM) == "mar"):
return 3;
break;
case ((strM) == "apr"):
return 4;
break;
case ((strM) == "may"):
return 5;
break;
case ((strM) == "jun"):
return 6;
break;
case ((strM) == "jul"):
return 7;
break;
case ((strM) == "aug"):
return 8;
break;
case ((strM) == "sep"):
return 9;
break;
case ((strM) == "oct"):
return 10;
break;
case ((strM) == "nov"):
return 11;
break;
case ((strM) == "dec"):
return 12;
}
}
Split(svalue, sdelimiter)
- Returns a zero-based, one-dimensional array containing a specified number of substrings.
function Split(svalue, sdelimiter) {
svalue += '';
sdelimiter += '';
return svalue.split(sdelimiter);
}
RemoveDelim(strmv, delim)
- Returns a string
without a delimiter at the end, e.g., RemoveDelim("Mash;Mbanga;")
will return Mash;Mbanga
.
function RemoveDelim(strmv, delim) {
var lendelim,
rightpart,
strout;
lendelim = Len(delim);
rightpart = Right(strmv, lendelim);
strout = strmv;
if (rightpart == delim) {
strout = Left(strmv, Len(strmv) - lendelim);
}
return strout;
}
SaveSetting(key, value)
- This is the same as localStorage.setItem(key, value)
.
function SaveSetting(key, value) {
key = CStr(key);
key = Trim(key);
value = CStr(value);
value = Trim(value);
localStorage.setItem(key, value);
}
GetSetting(key)
- This is the same as localStorage.getItem(key)
.
function GetSetting(key) {
key = CStr(key);
key = Trim(key);
var svalue;
svalue = localStorage.getItem(key);
if (isNull(svalue) == true) {
svalue = "";
}
svalue = CStr(svalue);
svalue = Trim(svalue);
return svalue;
}
MvRemoteItem(strmv, sremove, delim)
- Returns a delimited string
without the specified sub-item.
function MvRemoteItem(strmv, sremove, delim) {
sremove = LCase(sremove);
var sout,
lendelim;
sout = "";
var spv = Split(strmv, delim);
var stot,
scnt,
sitem;
lendelim = Len(delim);
stot = spv.length - 1;
for (scnt = 0; scnt <= stot; scnt++) {
sitem = LCase(spv(scnt));
if (sitem != sremove) {
sout = sout + spv(scnt) + delim;
}
}
sout = Left(sout, Len(sout) - lendelim);
return sout;
}
MvSearch(searchvalues, strsearch, delim)
- Returns the position of a sub-item within a delimited string
.
function MvSearch(searchvalues, strsearch, delim) {
if (Len(searchvalues) == 0) {
return -1;
}
var spvalues,
i,
itot,
ivalue;
spvalues = Split(searchvalues, delim);
strsearch = LCase(strsearch);
itot = spvalues.length - 1;
for (i = 0; i <= itot; i++) {
ivalue = spvalues[i];
ivalue = LCase(ivalue);
if (ivalue == strsearch) {
return i;
}
}
return -1;
}
UCase(str)
- Returns a string
or character containing the specified string
converted to uppercase.
function UCase(str) {
return str.toUpperCase();
}
Trim(str)
- Returns a string
containing a copy of a specified string
with no leading or trailing spaces.
function Trim(str) {
return str.trim();
}
ProperAmount(svalue)
- Returns a string
for amount formatting, rounded, e.g. 1,234,456
will return 123456.00
.
function ProperAmount(svalue) {
svalue = Replace(svalue, ",", "");
svalue = svalue.toFixed(2);
return svalue;
}
MvField(svalue, iposition, delimiter)
- Returns the sub-item at the particular position within a delimited string
. This is 1 based.
function MvField(svalue, iposition, delimiter) {
var mvalues,
tvalues,
xvalue;
mvalues = Split(svalue, delimiter);
tvalues = mvalues.length - 1;
iposition = iposition - 1;
if (iposition <= -1) {
xvalue = mvalues[tvalues];
return xvalue;
}
if (iposition > tvalues) {
return "";
}
xvalue = mvalues[iposition];
return xvalue;
}
email(t, subject, body)
- Send an email.
function email(t, subject, body) {
location = "mailto:" + encodeURI(t) + "?subject=" + encodeURI(subject) + "&body=" + encodeURI(body);
}
phone(tel)
- Make a phone call.
function phone(tel) {
location = "tel:" + tel;
}
skype(tel)
- Make a skype call.
function skype(tel) {
location = "skype:" + tel;
}
sms(tel, body)
- Send a text message.
function sms(tel, body) {
location = "sms:" + tel + "?body=" + encodeURL(body);
}
Alphabets(svalue)
- Return a string
of alphabets from an alphanumeric string
.
function Alphabets(svalue) {
var sout,
slen,
i,
schar,
isnum;
sout = "";
slen = Len(svalue);
schar = Mid(svalue, slen, 1);
isnum = IsNumeric(schar);
do {
if (isnum == false)
break;
svalue = Left(svalue, slen - 1);
slen = Len(svalue);
schar = Mid(svalue, slen, 1);
isnum = IsNumeric(schar);
} while (0 < 1);
return svalue;
}
MonthName(svalue)
-Returns the name of the month name from a given number. This is 1 based.
function MonthName(svalue) {
var month = new Array();
month[1] = "January";
month[2] = "February";
month[3] = "March";
month[4] = "April";
month[5] = "May";
month[6] = "June";
month[7] = "July";
month[8] = "August";
month[9] = "September";
month[10] = "October";
month[11] = "November";
month[12] = "December";
return month(svalue);
}
CharAt(svalue, pos)
- Returns a string
at a particular position of a string
. This is 1 based.
function CharAt(svalue, pos) {
return svalue.charAt(pos - 1)
}
CharCodeAt(svalue, pos)
- Returns a unicode of a character in the specified location of a string
. This is 1 based.
function CharCodeAt(svalue, pos) {
return svalue.charCodeAt(pos - 1)
}
DateIconv()
- Returns the current system date as a number.
function DateIconv() {
var x = new Date();
x = Number(x)
return x;
}
ThisYear()
- Returns the current system year.
function ThisYear() {
var x = new Date();
x = x.getFullYear();
return x;
}
ThisWeekDay()
- Returns the current system week day. This is 1 based and assumes Sunday
as the beginning of the week.
function ThisWeekDay() {
var x = new Date();
x = x.getDay() + 1;
return x;
}
ThisWeekDayName()
- Returns the current system week day name and assumes Sunday
as the beginning of the week.
function ThisWeekDayName() {
var x = new Date();
var days = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"];
x = days[x.getDay()];
return x;
}
ThisMonth()
- Returns the current system month. This is 1 based.
function ThisMonth() {
var x = new Date();
x = x.getMonth() + 1;
return x;
}
ThisMonthName()
- Returns the current system month name. This is 1 based.
function ThisMonthName() {
var x = new Date();
x = x.getMonth() + 1
x = MonthName(x);
return x;
}
Concat(s1, s2)
- Returns a concatenated string
of two string
s.
function Concat(s1, s2) {
var s = s1.concat(s2);
return s;
}
IsArray(myArray)
- Returns a boolean indicating whether the passed object is an array.
function IsArray(myArray) {
return myArray.constructor.toString().indexOf("Array") > -1;
}
IsDate(myDate)
- Returns a boolean indicating whether the passed object is a date
.
function IsDate(myDate) {
return myDate.constructor.toString().indexOf("Date") > -1;
}
CInt(value)
- Returns an integer based on the passed value.
function CInt(value) {
var x = Number(value);
return x;
}
ThisTime()
- Returns the current system time in milliseconds.
function ThisTime() {
var x = new Date();
x = x.getTime();
return x;
}
Asc(str)
- Returns the ASCII code of a given ASCII character string
.
function Asc(str) {
return str.charCodeAt(0);
}
format(str, col)
- Function to format a string
, e.g., x = format('Tel {0}', 'mine');
returns 'Tel mine
'.
var format = function (str, col) {
col = typeof col === 'object' ? col : Array.prototype.slice.call(arguments, 1);
return str.replace(/\{\{|\}\}|\{(\w+)\}/g, function (m, n) {
if (m == "{{") { return "{"; }
if (m == "}}") { return "}"; }
return col[n];
});
};
String.prototype.startsWith(x)
- Returns a boolean whether a string
starts with particular values.
String.prototype.startsWith = function(prefix) {
return (this.substr(0, prefix.length) === prefix);
}
String.prototype.endsWith(x)
- Returns a boolean whether a string
ends with particular values.
String.prototype.endsWith = function (suffix) {
return (this.substr(this.length - suffix.length) === suffix);
}
String.prototype.format(x)
- Returns a formatted string
, e.g., 'Your balance is {0} USD'.format(77.7)
.
String.prototype.format = String.prototype.f = function() {
var s = this,
i = arguments.length;
while (i--) {
s = s.replace(new RegExp('\\{' + i + '\\}', 'gm'), arguments[i]);
}
return s;
};
Date.prototype.adjust(part, amount)
- Returns an adjusted date, e.g., var d = new Date(); d.adjust('hours', -10);
Date.prototype.adjust = function(part, amount){
part = part.toLowerCase();
var map = {
years: 'FullYear', months: 'Month', weeks: 'Hours', days: 'Hours', hours: 'Hours',
minutes: 'Minutes', seconds: 'Seconds', milliseconds: 'Milliseconds',
utcyears: 'UTCFullYear', utcmonths: 'UTCMonth', weeks: 'UTCHours', utcdays: 'UTCHours',
utchours: 'UTCHours', utcminutes: 'UTCMinutes',
utcseconds: 'UTCSeconds', utcmilliseconds: 'UTCMilliseconds'
},
mapPart = map[part];
if(part == 'weeks' || part == 'utcweeks')
amount *= 168;
if(part == 'days' || part == 'utcdays')
amount *= 24;
this['set'+ mapPart]( this['get'+ mapPart]() + amount );
return this;
}
Date.prototype.diff (date2, parts)
- Returns a date diff
, e.g., d1.diff(d2,'weeks');
Date.prototype.diff = function(date2, parts){
var d1 = new Date(this.getTime()),
d2 = new Date(date2.getTime()),
pm = d1 <= d2? 1 : -1,
result = { },
factors = { weeks: (1000*60*60*24*7), days: (1000*60*60*24),
hours: (1000*60*60), minutes: (1000*60), seconds: 1000, milliseconds: 1 };
if(parts === undefined)
parts = ['years', 'months', 'weeks', 'days', 'hours', 'minutes', 'seconds', 'milliseconds'];
else if(typeof(parts) == "string")
parts = [parts];
for(var i=0, l=parts.length; i<l; i++){
var k = parts[i];
result[k] = 0;
if(factors[k] === undefined){
inaWhile: while( true ){
d2.adjust(k, -1*pm);
if( (pm === 1 && d1 > d2) || (pm === -1 && d1 < d2)){
d2.adjust(k, 1*pm);
break inaWhile;
}
result[k]++;
}
}
else{
var tmpDiff = Math.abs(d2.getTime() - d1.getTime());
result[k] = Math.floor(tmpDiff / factors[k]);
d2.adjust(k, result[k]*-1*pm);
}
result[k] *= pm;
}
if(parts.length == 1)
return result[parts[0]];
return result;
}
DateAdd(ItemType, DateToWorkOn, ValueToBeAdded)
- Returns a date based on added interval.
function DateAdd(ItemType, DateToWorkOn, ValueToBeAdded)
{
switch (ItemType)
{
case 'd':
DateToWorkOn.setDate(DateToWorkOn.getDate() + ValueToBeAdded)
break;
case 'm':
DateToWorkOn.setMonth(DateToWorkOn.getMonth() + ValueToBeAdded)
break;
case 'y':
DateToWorkOn.setYear(DateToWorkOn.getFullYear() + ValueToBeAdded)
break;
case 'h':
DateToWorkOn.setHours(DateToWorkOn.getHours() + ValueToBeAdded)
break;
case 'n':
DateToWorkOn.setMinutes(DateToWorkOn.getMinutes() + ValueToBeAdded)
break;
case 's':
DateToWorkOn.setSeconds(DateToWorkOn.getSeconds() + ValueToBeAdded)
break;
}
return DateToWorkOn;
}
Today()
- Returns a string
of YYYY-MM-DD
from the current system date.
function Today() {
var currentTime = new Date();
var currentMonth = currentTime.getMonth();
var currentDate = currentTime.getDate();
var currentYear = currentTime.getFullYear();
var tday = currentYear + "-" + currentmonth + "-" & currentDate;
return tday;
}
Round(n,d)
- Returns a rounded number from the passed.
function Round(n,d) {
if (!d||d===null||d===""){
d=0;
}
d = Math.floor(d);
d = d<1?0:d;
d=Math.pow(10,d);
var result=Math.round(n*d)/d;
return result;
}
WebSQL Javascript Functions
Constants to Use for the WebSQL
var DB_REAL = "REAL";
var DB_INTEGER = "INTEGER";
var DB_BLOB = "BLOB";
var DB_TEXT = "TEXT";
var DB_FLOAT = "FLOAT";
var DB_NUMERIC = "NUMERIC";
SqlOpenDb(shortName, version, displayName, maxSize)
- Open a WebSQL database, returns the database as an object.
Example: var db = SqlOpenDb("School");
function SqlOpenDb(shortName, version, displayName, maxSize) {
var db, dbsize = 1;
try {
if (!window.openDatabase) {
return 0;
} else {
if (typeof (shortName) === 'undefined') {
return 0;
}
if (typeof (version) === 'undefined') version = "";
if (typeof (displayName) === 'undefined') displayName = shortName;
if (typeof (maxSize) === 'undefined') maxSize = dbsize * (1024 * 1024);
db = openDatabase(shortName, version, displayName, maxSize);
}
} catch (e) {
return 0;
}
return db;
}
SqlExecute(db, sqlList)
- Executes commands against the websql
database. sqlList
can be a string
/ an array of commands. See examples below:
function SqlExecute(db, sqlList) {
db.transaction(function (transaction) {
for (var i = 0; i < sqlList.length; i++) {
(function (tx, sql) {
if (typeof (sql) === 'string') sql = [sql];
if (typeof (sql[1]) === 'string') sql[1] = [sql[1]];
var args = (typeof (sql[1]) === 'object') ? sql.splice(1, 1)[0] : [];
var sql_return = sql[1] || function () {};
var sql_error = sql[2] || function () {};
tx.executeSql(sql[0], args, sql_return, sql_error);
}(transaction, sqlList[i]));
}
});
}
SqlCreateTable(db, TableName, FieldsAndTypes, PrimaryKey, AutoIncrement)
- Creates a table in an existing database.
var tblStructure = {};
tblStructure.FullName = DB_TEXT;
Example: SqlCreateTable(db, "Kids", tblStructure, "FullName", "");
function SqlCreateTable(db, TableName, FieldsAndTypes, PrimaryKey, AutoIncrement) {
var sb = "(";
for (item in FieldsAndTypes) {
sb += "[" + item + "] " + FieldsAndTypes[item];
if (item == PrimaryKey) {
sb += " NOT NULL PRIMARY KEY";
}
if (item == AutoIncrement) {
sb += " AUTOINCREMENT";
}
sb += ", ";
}
sb = Left(sb, (Len(sb) - 2));
sb += ")";
sb = "CREATE TABLE IF NOT EXISTS [" + TableName + "] " + sb + ";";
return Execute(db, sb);
}
SqlAddColumns(db, TableName, FieldAndTypes)
- Updates the table structure of an existing table.
function SqlAddColumns(db, TableName, FieldsAndTypes) {
var sqlColumn = [];
var strCol = '';
for (item in FieldsAndTypes) {
strCol = "ALTER TABLE [" + TableName + "] _
ADD COLUMN [" + item + "] " + FieldsAndTypes[item] + ';';
sqlColumn.push(strCol);
}
SqlExecute(db, sqlColumn);
}
SqlInsertRecord(db, tblName, tblRecord)
- Adds a new record to an existing table.
var tblKid = {};
tblKid.FullName = 'Usibabale Mbanga';
Example: SqlInsertRecord(db, "Kids", tblKid);
function SqlInsertRecord(db, tblName, tblRecord) {
var qry, flds = "", vals = "", avals = [];
for (var key in tblRecord) {
flds += "[" + key + "],";
vals += "?,";
avals.push(tblRecord[key]);
}
flds = Left(flds, Len(flds) - 1);
vals = Left(vals, Len(vals) - 1);
qry = "INSERT INTO [" + tblName + "] (" + flds + ") VALUES (" + vals + ");";
return Execute(db, qry, avals);
}
SqlCreateIndexes(db, TableName, Indexes)
- Create indexes in an existing table.
function SqlCreateIndexes(db, TableName, Indexes) {
var sb, idef, sqlCreateIdx = [], idxname, spidx, idxtot, idxcnt, idx;
spidx = Split(Indexes, ",");
idxtot = spidx.length - 1;
for (idxcnt = 0; idxcnt <= idxtot; idxcnt++) {
idx = spidx(idxcnt);
idxname = TableName + "_" + idx;
idef = "CREATE INDEX IF NOT EXISTS [" + idxname + "] _
ON [" + TableName + "] ([" + idx + "]);";
sqlCreateIdx[idxcnt] = idef;
}
SqlExecute(db, sqlCreateIdx);
}
SqlUpdateRecordWhere(db, tblName, tblRecord, tblWhere)
- Update an existing record in a table. This sets age
to be 3
for Kid
whose name is Usibabale Mbanga
.
var KidRec = {};
KidRec.Age = 3;
var KidUpdate = {};
KidUpdate.FullName = 'Usibabale Mbanga';
Example: SqlUpdateRecordWhere(db, "Kids", KidRec, KidUpdate)
function SqlUpdateRecordWhere(db, tblName, tblRecord, tblWhere) {
var qry = "", vals = "", wvals = "", avals = [];
for (item in tblRecord) {
vals += "[" + item + "] = ?,";
avals.push(tblRecord[item]);
}
for (item in tblWhere) {
wvals += "[" + item + "] = ? AND ";
avals.push(tblWhere[item]);
}
vals = Left(vals, Len(vals) - 1);
wvals = Left(wvals, Len(wvals) - 5);
qry = "UPDATE [" + tblName + "] SET " + vals + " WHERE " + wvals + ";";
return Execute(db, qry, avals);
}
SqlGetRecordWhere(db, tblName, tblWhere)
- Gets records with select
condition, same as above example.
function SqlGetRecordWhere(db, tblName, tblWhere) {
var qry = "", vals = "", avals = [];
for (item in tblWhere) {
vals += "[" + item + "] = ? AND ";
avals.push(tblWhere[item]);
}
vals = Left(vals, Len(vals) - 5);
qry = "SELECT * FROM [" + tblName + "] WHERE " + vals + ";";
return Execute(db, qry, avals);
}
Execute(db, qry, args)
- Executes statements against the database, see above examples.
function Execute(db, qry, args){
if (typeof (args) === 'undefined') args = [];
return $.Deferred(function (d) {
db.transaction(function (tx) {
tx.executeSql(qry, args, successWrapper(d), failureWrapper(d));
});
});
};
ResultSetToJSON(results, PrimaryKey)
- Returns a resultset
from a table as a json object referenced by PrimaryKey
specified.
function ResultSetToJSON(results, PrimaryKey) {
var Records = {};
var len = results.rows.length - 1, priKey, i, row;
for (i = 0; i <= len; i++) {
row = results.rows.item(i);
priKey = row[PrimaryKey];
priKey = priKey.split(' ').join('-');
Records[priKey] = row;
}
return Records;
}
SqlDeleteRecordWhere(db, tblName, tblWhere)
- Delete an existing record from a table. This works the same as the SqlUpdate
function above.
function SqlDeleteRecordWhere(db, tblName, tblWhere) {
var qry, wvals = "", avals = [];
for (item in tblWhere) {
wvals += "[" + item + "] = ? AND ";
avals.push(tblWhere[item]);
}
wvals = Left(wvals, Len(wvals) - 5);
qry = "DELETE FROM [" + tblName + "] WHERE " + wvals + ";";
return Execute(db, qry, avals);
};
Points of Interest
Whilst JavaScript is a fairly simple to understand programming language, at times, people want simplification and easier methods of doing things, especially the same functionality over and over again. I have found that consolidating my scripts into one single base, it's easier for me to use the js file across my projects without problems. As soon as something gets enhanced, that function gets changed in one location. You can also refer to my articles posted here in terms of how I have used some of these functions.