Over the years Microsoft have provided a number of programming models for accessing SQL databases, from the API-based Open DataBase Connectivity library, to the Data Access Objects, Remote Data Objects, and ActiveX Data Objects object models. Of these, ODBC is the oldest, and ADO is the most recent and most powerful, and in its latest guise, ADO.NET, the only fully native database connectivity method supported by Microsoft Visual Studio .NET.

ADO provides a hierarchical object model, with, simplistically, a Connection object at the top, Command and Recordset objects in the middle, and Field objects at the base. The following Visual Basic code shows how a single field from a table could be accessed using ADO:

Dim oConnection As ADODB.Connection
Dim rs As ADODB.RecordSet
Dim sSQL As String
’
… Code to obtain database connection here
’
sSQL = ”SELECT name FROM customers”
Set rs = New ADODB.RecordSet
rs.Open sSQL, oConnection, adOpenStatic, adLockReadOnly
If Not rs.EOF Then
    Print rs.Fields(”name”)
End If
rs.Close

What the ADO.SBR provides to AlphaBASIC is direct access to the ADO programming model. The key problems are that AlphaBASIC does not have a syntax conducive to object-oriented programming, and that all operations must be funnelled through an XCALL to the ADO.SBR routine.

In order to best understand how this Visual Basic code translates into AlphaBASIC, it is easiest to first to revise the Visual Basic code, removing the code which relies on concepts not supported by AlphaBASIC:

• Objects are normally terminated or destroyed when they go out of scope (i.e. the function is exited). The concept of scope does not exist under AlphaBASIC, so all objects must be explicitly destroyed.

• Because all ADO properties and function return values must be accessed with an XCALL, it is not possible to use any ADO properties or functions within an expression. Instead the value of a property or function must first be assigned to a variable.

• Default properties cannot be supported. In this example the clause rs.Fields(“name”) relies on two defaults – firstly that the default property of the Fields collection is Item, and secondly that the default property of the Field object is Value.

• Because AlphaBASIC cannot support an object hierarchy syntax, such as a.b.c.d, or in this example rs.Fields(“name”).Item.Value, the hierarchy must be navigated manually, obtaining object references at each level.

When so modified, the Visual Basic now reads:

Dim oConnection As ADODB.Connection

Dim oFields As ADODB.Fields
Dim oField As ADODB.Field
Dim rs As ADODB.RecordSet
Dim sSQL As String
Dim bIsEOF As Boolean
Dim sName As String
’
… Code to obtain database connection here
’
sSQL = ”SELECT name FROM customers”
Set rs = New ADODB.RecordSet
rs.Open sSQL, oConnection, adOpenStatic, adLockReadOnly
Set bIsEOF = rs.EOF

 

If Not bIsEOF Then

Set oFields = rs.Fields
Set oField = oFields(”name”).Item
sName = oField.Value
Print sName
Set oField = Nothing
Set oFields = Nothing

End If
rs.Close
’
Set rs = Nothing
Set oConnection = Nothing

Once this level of Visual Basic has been reached, then it is almost a line-for-line syntax substitution to convert the same code into AlphaBASIC.

map1 HCONNECTION,b,2
map1 HFIELDS,b,2
map1 HFIELD,b,2
map1 HRS,b,2
map1 SQL,s,100
map1 ISEOF,b,1
map1 NAME,s,10
!
… Code to obtain database connection here
!
SQL = ”SELECT name FROM customers”
xcall ADO,ADO'RECORDSET'NEW,HRS
xcall ADO,ADO'RECORDSET'OPEN,HRS,SQL,HCONNECTION, &
      adOpenStatic,adLockReadOnly
xcall ADO,ADO'RECORDSET'EOF'GET,HRS,ISEOF
if ISEOF <> 0 then &

xcall ADO,ADO'RECORDSET'FIELDS'GET,HRS,HFIELDS : &
xcall ADO,ADO'FIELDS'ITEM'GET,HFIELDS,HFIELD,"name" : &
xcall ADO,ADO'FIELD'VALUE'GET,HFIELD,NAME : &
xcall ADO,ADO'FIELD'DESTROY,HFIELD : &
xcall ADO,ADO'FIELDS'DESTROY,HFIELDS : &
print NAME

xcall ADO,ADO'RECORDSET'CLOSE,HRS

!

xcall ADO,ADO’RECORDSET’DESTROY,HRS

xcall ADO,ADO'CONNECTION'DESTROY,HCONNECTION

The key points to remember when converting Visual Basic ADO code to AlphaBASIC code may be summarised in the following list:

• An object reference is mapped to an object ‘handle’, implemented as a 2-byte binary value.

• Object calls of the form Object.Method(Param1,Param2) map to ADO.SBR calls of the form XCALL ADO,ADO’Object’Method,Param1,Param2.

• The constructor Set oObject = New Object maps to an ADO.SBR call of the form XCALL ADO,ADO’Object’NEW,oObject.

• The destructor Set oObject = Nothing maps to an ADO.SBR call of the form XCALL ADO,ADO’Object’DESTROY,oObject.

• Property accesses of the form Object.Property map to ADO.SBR calls of the form XCALL ADO,ADO’Object’Property’Get,Value and
XCALL ADO,ADO’Property’Put,Value for read and write operations.

ADO.SBR contains two operations which are not strict ADO functions, but may be considered useful ‘helper’ functions for AlphaBASIC. These are ADOX'GET'FIELDS and ADOX’SET’FIELDS which can read and write multiple fields from and to a Recordset in a single XCALL. Example use of these occurs in the ADOTST test program.