Keith Joubert logo - home.

Home
Contact

Binary Search
Sort Multiple Columns
Filing Functions
Extracting Excel data
Read and Write .csv files
Show Progress Messages
Send Automatic Email
Running under .hta
Setting Folder Attributes
Long-script warnings
Desktop Shortcut
ADODB & SQL Queries
  Connection String
  Connecting to SQL Database
  Retieve Data From SQL
  Get SQL image data (BLOBS)

 
ADODB & SQL Queries

How to extract SQL data using ADODB and Javascript. If you wish to access a Microsoft SQL database, using Javascript, this can be done using ActiveX and ADODB.  The code below has been used with SQL2000 & 2005.  You will of course require the SQL code you wish to run as a query.  This will depend on the structure of the database and the attributes you wish to retrieve.

CONNECTION STRING

To access the database, a connection string is required.  This can take many forms, best explained by examples.  The punctuation is important:

connStr="Provider=SQLOLEDB;Data Source='(local)';Initial Catalog='DatabaseName';Trusted_Connection='yes';";

OR

connStr="Provider=SQLOLEDB;Data Source='LAPTOP';Initial Catalog='DatabaseName';User ID='sa';Password='mypassword';";

OR

connStr="Provider=SQLOLEDB;Data Source='xxx.xxx.xx.x';Initial Catalog='DatabaseName'';UserID='sa';Password='*&^%$#@!';";

i.e., of the form:

Provider=SQLOLEDB;
Data Source=server_name_or_address;
Initial Catalog=database_name;
User ID=username;
Password=password;

// A new instance name must begin with a letter, an ampersand (&), or an underscore (_), and can contain numbers, letters, or other characters. 

TopTop


CONNECTING TO SQL DATABASE

function SQLconnect(){
  var adModeRead=1;
  conn = new ActiveXObject("ADODB.Connection");
  conn.mode=adModeRead;
  try{conn.open(connStr, "", "");}
  catch(err01){
     Some catch code....
  }
}

TopTop


RETRIEVE DATA FROM SQL DATABASE

Example simple query:   query="SELECT x,y FROM blah-blah";

Run function if connection successful (see SQLconnect(), above).

 
function GetDatabaseData(){ //saves all rows of desired DataBase tables in sqlArr
  var adOpenDynamic=2;
  var adLockOptimistic=3;
  sqlArr=new Array();
  try{
    var rs = new ActiveXObject("ADODB.Recordset");
    rs.open(query, conn, adOpenDynamic, adLockOptimistic);
    var j=0;var tArr=[];
    if(!rs.bof){rs.MoveFirst();}
    for(var i=0;i<rs.fields.count;++i){tArr[i]=rs.fields(i).name;}
    sqlArr[j]=tArr;j++;
    if(!rs.eof){rs.MoveNext();}
    while(!rs.eof){
      tArr=[];
      for(var i=0;i<rs.fields.count;++i){
        tArr[i]=rs.fields(i).value;
        if(tArr[i]!=tArr[i]+''){tArr[i]='<object>';}
        if(tArr[i]=='null'){tArr[i]='-';}
      }
      sqlArr[j]=tArr;j++;
      rs.MoveNext();
    }
    rs.close();//record set
  }
  catch(err01){
    conn.close();
    conn=null;// leave DB
    Some error handling...
  }
}

TopTop


GET SQL IMAGE DATA & STORE IN FOLDER

SQL stores images and other big binary data files in "BLOBS".  To retrieve these and save in a non-database folder, use the following function, which includes a typical query for image data (construct yours to suit your database, of course):

 
 function GetBlob(node,nom){ //Gets blob image data from SQL and stores using nom as name, in accessible folder
    var rs=new ActiveXObject("ADODB.Recordset");
    var query='Select THE_IMAGES.PIC From THE_IMAGES Where THE_IMAGES.NODE_ID = '+node;
    gpath=RootPath+nom+'.jpg';//RootPath is, eg, "C:\\DbImages\\"
    rs.open(query, conn, adOpenDynamic, adLockOptimistic);
    var bin=new ActiveXObject("ADODB.Stream");
    bin.Type=1;//adTypeBinary;
    bin.Open();
    bin.write(rs.fields(0).value);
    bin.SaveToFile(gpath,1);
    bin.close();
    rs.close();
  }

You can now access images by reading from RootPath, eg, "C:\\DbImages\\JohnsPic.jpg"

TopTop