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