#7 VDK SQL Abstraction layer

[Jason Ely]

VBScript SQL programming is handled via the ADODB library. The VDK SQL abstraction layer of classes will be built on top of this microsoft library to provide abstraction.

Features in the new abstraction layer will include:

1. Prepared statements
2. POVO's (Plain old vbscript objects) creation and injection

The idea behind the abstraction layer is to provide a quick and intuitive way to actually get at a database, get the data, and get out and then do your data processing on the VBScript side.

A sample of the new functionality will look something like this:

Assume you have a database with a table named person. Person table has columns: name, surname, age

set objSql = new clsVdkSql
set objConnection = CreateObject("ADODB.CONNECTION")
objConnection.Open  "your_connection_string_with_username_and_password"
strSql = "select * from person where name = ?"

set objResult = objSql.withConnection (objConnection) _
    .prepareSql(strSql, array("jason")) _
    .listResults()

The variable objResult will be an arraylist object where each item in the class will be a Person object that wraps every column in table Person in the form of getters and setters. So, you can then do this in VBScript

 for int i = 0 to objResult.getCount() - 1
     strName = objResult.getItemAt(i).getName
     strSurname = objResult.getItemAt(i).getSurname
     intAge = objResult.getItemAt(i).getAge
 next

Alternatively, you can specify an object to inject into example:

 Class Person
private strThisName
    public function getName()
        getName = strThisName
     end function

     public sub setName(strName)
        strThisName = strName
     end function
 End class

Save the class along the VDK path. For this example assume its saved in path io.sourceforge.sql.person. We can now inject results into your class

 set objSql = new clsVdkSql
set objConnection = CreateObject("ADODB.CONNECTION")
objConnection.Open  "your_connection_string_with_username_and_password"
strSql = "select * from person where name = ?"

set objResult = objSql.withConnection (objConnection) _
    .prepareSql(strSql, array("jason")) _
    .listResultsToClass("io.sourceforge.sql.person")

    For each column in the result set, we will inject into the supplied object. 
 for int i = 0 to objResult.getCount() - 1
     strObjjectTypeName = typename(objResult.getItemAt(i)) 'will return Person
     strName = objResult.getItemAt(i).getName 'can only get name because person class only has getter for name column

 next

With the SQL library you will also be able to create your own POVO objects on the fly like this:

 set objSql = new clsVdkSql
set objConnection = CreateObject("ADODB.CONNECTION")
objConnection.Open  "your_connection_string_with_username_and_password"
strSql = "select * from person where name = ?"

set objResult = objSql.withConnection (objConnection) _
    .prepareSql(strSql, array("jason")) _
    .SavePovoToPath("io.sourceforge.sql.clsPerson")

 next