ADO module function list help

[Reinking]

Eros,

I thought I'd experiment with the ADO module. I couldn't find much help for internal function calls. I wanted to traverse the recordset of the 'Authors' table in BIBLIO.MDB. Can you show me how to get field data out of the recordset object?

Thanks a lot,

John Reinking

example script below...

USES "ADO"
USES "Console"

dim lpConnection as long
dim lpRecordSet  as Long
dim sdbName      as String Value "c:\ThinBasic\SampleScripts\ODBC\Biblio.mdb"

'---Connection string
dim stConnection  as String Value "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbName 
dim sQuery as String Value "SELECT * FROM [Authors];"

lpConnection=DB_Open(stConnection)
lpRecordSet =RS_Open(lpConnection, sQuery)

RS_Traverse(lpRecordSet)

RS_Close(lpRecordSet)
DB_Close(lpConnection)

console_Waitkey(5)

stop


Function RS_Open(lpConnection as Long, sQuery as String) as Long
    Dim lpRecordSet as Long
    
    lpRecordSet=ADO_CreateObject("ADODB.RecordSet")
    ADO_RecordSet_Open(lpRecordset, sQuery, lpConnection, %adOpenKeyset, %adLockOptimistic, %adCmdText)
    Function=lpRecordSet 
End Function

Function RS_Close(lpRecordSet as Long) as Long
    '---Close and release the RecordSet 
    If lpRecordSet Then 
       ADO_RecordSet_Close(lpRecordSet) 
       ADO_Release(lpRecordSet) 
    End If

End Function
  
Function DB_Open(strConn as String) as Long
    dim lpConn as Long
    
    '---Creates an ADO connection object 
    lpConn = ADO_CreateObject("ADODB.Connection") 

    If ISFALSE(lpConn) Then
       Console_WriteLine("Connection: " & lpConnection & "Ado result: " & ADO_Result & "")
       console_Waitkey
       Stop 
    End If

    
    
    '---Opens database 
    ADO_Connection_Open(lpConn, strConn) 

    If ADO_Result Then
       Console_WriteLine( "Connection open error: " & ADO_Result & "")
       console_waitkey
       STOP
    End If    
    
    Function=lpConn 
End Function


Function DB_Close(lpConnection as Long) As Long
 
    '---Close and release the connection 
    If lpConnection Then 
       ADO_Connection_Close(lpConnection) 
       ADO_Release(lpConnection) 
    End If

End Function

Function RS_Traverse(lpRecordSet as Long) as Long
    dim p1 as long value 0

    '----- Fetch the first row
    ADO_RecordSet_MoveFirst(lpRecordset)
  
    WHILE isFalse(ADO_RecordSet_EOF(lpRecordset))
    
      '----- Get the field info here??????
       
      '----- Fetch the next row
      Ado_RecordSet_MoveNext(lpRecordset)
      
      incr p1 
    WEND
    Console_Write("Found:" & str$(p1) & " records.") 
End Function

[Reinking]

Okay,

I poked through the thinBasic_ADO.dll file with a text editor and found two function literals that look promising....ADO_RecordSet_GetCollectS and ADO_RecordSet_GetCollectN. I'll try them out...

Regards

[Reinking]

Yup! That works...below is the revised ADO sample script:

USES "ADO"
USES "Console"

dim lpConnection as Long
dim lpRecordSet  as Long
dim sdbName      as String = APP_Path & "SampleScripts\ODBC\Biblio.mdb"

'----- Connection string
dim stConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbName

'----- Data queries 
dim sQuery1      as String = "CREATE VIEW [myView] AS SELECT * FROM [Authors] WHERE [Year Born] BETWEEN 1940 AND 1960;"
dim sQuery2      as String = "SELECT * FROM [myView]"
dim sQuery3      as String = "DROP VIEW [myView]"

'----- Connect
lpConnection=DB_Open(stConnection)

'----- Create a simple VIEW
ADO_Connection_Execute(lpConnection, sQuery1)

'----- Create a recordset based on the VIEW
lpRecordSet =RS_Open(lpConnection, sQuery2)

RS_Traverse(lpRecordSet)

'----- Drop the VIEW
ADO_Connection_Execute(lpConnection, sQuery3)

'----- Clean Up
RS_Close(lpRecordSet)
DB_Close(lpConnection)

Console_Waitkey(10)

stop


Function RS_Open(lpConnection as Long, sQuery as String) as Long
    Dim lpRecordSet as Long
    
    lpRecordSet=ADO_CreateObject("ADODB.RecordSet")
    ADO_RecordSet_Open(lpRecordset, sQuery, lpConnection, %adOpenKeyset, %adLockOptimistic, %adCmdText)
    
    If ADO_Result then
       Console_WriteLine("Connection: " & lpConnection & "Ado result: " & ADO_Result & "")
       Console_Waitkey
       Stop 
    End If

    Function=lpRecordSet 
End Function

Function RS_Close(lpRecordSet as Long) as Long
    '---Close and release the RecordSet 
    If lpRecordSet Then 
       ADO_RecordSet_Close(lpRecordSet) 
       ADO_Release(lpRecordSet) 
    End If

End Function
  
Function DB_Open(strConn as String) as Long
    dim lpConn as Long
    
    '---Creates an ADO connection object 
    lpConn = ADO_CreateObject("ADODB.Connection") 

    If IsFalse(ADO_Result) Then
       Console_WriteLine("Connection error: " & lpConn & "Ado result: " & ADO_Result & "")
       Console_Waitkey
       Stop 
    End If
    
    '---Opens database 
    ADO_Connection_Open(lpConn, strConn) 

    If ADO_Result Then
       Console_WriteLine( "Connection string error:  " & strConn & "ADO_Result: " & ADO_Result & "")
       Console_Waitkey
       STOP
    End If    
    
    Function=lpConn 
End Function


Function DB_Close(lpConnection as Long) As Long
 
    '---Close and release the connection 
    If lpConnection Then 
       ADO_Connection_Close(lpConnection) 
       ADO_Release(lpConnection) 
    End If

End Function

Function RS_Traverse(lpRecordSet as Long) as Long
    dim p1 as long value 0
    dim sLn as String

    '----- Fetch the first row
    ADO_RecordSet_MoveFirst(lpRecordset)
  
    WHILE isFalse(ADO_RecordSet_EOF(lpRecordset))
      
      sLn=String$(60," ")
      Mid$(sLn,1)=str$(Ado_RecordSet_GetCollectN(lpRecordset, "Au_ID"))
      Mid$(sLn, 10)= Ado_RecordSet_GetCollectS(lpRecordset, "Author")
      Mid$(SLn, 40)= str$(Ado_RecordSet_GetCollectN(lpRecordset, "Year Born"))
      Console_WriteLine(sLn )
       
      '----- Fetch the next row
      Ado_RecordSet_MoveNext(lpRecordset)
      
      incr p1 
    WEND

    Console_Write("Found:" & str$(p1) & " records.") 
End Function

[ErosOlmi]

Hi John,

I'm sorry you have to find out by yourself function usage. I did had few time to document ADO module also because I stopped it for a while due to VARIANT usage inside ADO interface. I had some problems incorporanting them into thinBasic but I think to have a possible solution to test.

I will try to document already developed ADo functions during this weekend and try to fix VARIANT usage also adding some more functionalities.

Thanks for using thinBasic
Eros