Problems using ADODB

[AdamCounsell]

Hi,

I have recently started using ThinBasic and have been attempting to use the ADODB module to read data from ACCESS and ORACLE database tables. I am able to connect to the databases and run some SQL and using the RecordSet ObjectCount I can see that rows are being returned but as soon as I try and access the data using the Fields(x).Name and Fields(x).Value a pop-up appears saying ‘thinbasic.exe has stopped working’ and if I then select Debug ‘An unhandled WIN32 exception occurred in thinbasic.exe (10040)’. It is running on Windows 7 32 bit.

An example script below – this gets to the line ‘Console_WriteLine(objRecordSet.Fields.Count)’ and then the pop-up appears.

Has anyone else had this issue?

Uses "ADODB"
Uses "ADO"
Uses "Console"

Dim objConnection As ADODB_CONNECTION
Dim objRecordSet As ADODB_RECORDSET
Dim ConnectionDetails, SQL As String
Dim SegmentNames(9999) As String
Dim SegmentCt As Integer
Dim FieldCt As Integer

ConnectionDetails = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = h:\My Documents\Access\Table Space History.accdb"

objConnection = New ADODB_CONNECTION
objRecordSet = New ADODB_RECORDSET

objConnection.Open(ConnectionDetails)

SQL = "Select * from SegmentNames"

objRecordSet.Open SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT

If objRecordSet.RecordCount > 0 Then
Console_WriteLine("Records Extracted = " & objRecordSet.RecordCount)
Console_WriteLine(objRecordSet.Fields.Count)

objRecordSet.MoveFirst

Console_WriteLine(objRecordSet.Fields(1).Value)

SegmentCt = 0
Do Until objRecordSet.EOF
SegmentCt = SegmentCt + 1
Console_WriteLine(SegmentCt)
SegmentNames(SegmentCt) = objRecordSet.Fields(2).Value
objRecordSet.MoveNext
Loop
Else
Console_WriteLine("No records extracted")
End If

objRecordSet.Close
objConnection.Close


Thanks

Adam

[ErosOlmi]

Ciao Adam,

what thinBasic version are you using? I've recently changed a lot ADODB module, so please download and install thinBasic 1.10.4 from here: http://www.thinbasic.com/community/showthread.php?12778-thinBasic-1-10-x

I've tryed to simulate your script and here it seems to work fine with few little changes:

Uses "ADODB"Uses "Console"


Dim objConnection As ADODB_CONNECTION
Dim objRecordSet As ADODB_RECORDSET
Dim ConnectionDetails, SQL As String
Dim SegmentNames(9999) As String
Dim SegmentCt As Integer
Dim FieldCt As Integer


ConnectionDetails = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " & app_sourcepath & "Table Space History.accdb"


objConnection = New ADODB_CONNECTION
objRecordSet = New ADODB_RECORDSET


objConnection.Open(ConnectionDetails)


'---If connection is open then Executes a command and print some data
If objConnection.State = %ADSTATEOPEN Then


SQL = "Select * from SegmentNames"


objRecordSet.Open SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT


If objRecordSet.RecordCount > 0 Then
Console_WriteLine("Records Extracted = " & objRecordSet.RecordCount)
Console_WriteLine(objRecordSet.Fields.Count)


objRecordSet.MoveFirst


Console_WriteLine(objRecordSet.Fields(1).Value)


SegmentCt = 0
while Not objRecordSet.EOF
SegmentCt = SegmentCt + 1
printl SegmentCt
SegmentNames(SegmentCt) = objRecordSet.Fields(2).Value
for FieldCt = 1 to objRecordSet.Fields.Count
printl objRecordSet.Fields(FieldCt).Name , "=", objRecordSet.Fields(FieldCt).Value
Next
objRecordSet.MoveNext
Wend
Else
Console_WriteLine("No records extracted")
End If


objRecordSet.Close
objConnection.Close
Else


long nError
If objConnection.Errors.Count Then
PrintL "--> Error happened:" In %CCOLOR_FLIGHTRED
For nError = 1 to objConnection.Errors.Count
PrintL "Error number..:", objConnection.Errors(nError).Number
PrintL "Description...:", objConnection.Errors(nError).Description
PrintL "Source........:", objConnection.Errors(nError).Source
PrintL "SQLState......:", objConnection.Errors(nError).SQLState
PrintL "NativeError...:", objConnection.Errors(nError).NativeError
Next
'---Clear all errors so new errors will restart to fill Errors collection
objConnection.Errors.Clear
End If


end If


printl "---Press a key to end---"
WaitKey




Attached the script and an access DB I've tried to replicate to simulate your script

Let me know.
Eros

[AdamCounsell]

Hi Eros,

Thanks for that - yes it does look like I had downloaded an old(ish) version of ThinBasic (1.9.16.17).

I've now downloaded 1.10.4.0 and the ADODB File(x).Name and File(x).Value now work.

I do have one other query now though:

The value of <recordset>.EOF is set to 0 whilst there are records to be read but returns a value of 4294967295 at end of file so when I try and use 'Do Until objRecordSet.EOF = %TRUE' this is never triggered. Is this correct?


Thanks

Adam

[ErosOlmi]

Try using


While Not rs.EOF
...
Wend


Like in my Example and see if it works.

[AdamCounsell]

Yup that has done the trick.

Thanks for your help.


Adam

[ErosOlmi]

Good.

If you want to use DO/LOOP use DO While ...


Do While Not objRecordSet.EOF
Loop

I will check why Do Until objRecordSet.EOF is not working.
I suppose objRecordSet.EOF is not returning real TRUE/FALSE

[AdamCounsell]

Hello Again.

I have been attempting to write some code to access an Access database using ThinBasic but have been receiving a strange error.

The program was attempting to open a table (recordset) run some SQL and retrieve the results and then using a different selection run the same query but this second attempt at running the SQL causes the error 'Assignment type not supported. Something is wrong with variable assignment.' to be shown (below).

9848

This is the script I ended up with to test the connection - just a simple loop but the second time 'objRecordSet.Open' is issued the error is shown.

Uses "UI"
Uses "ADODB"

Dim objConnection As ADODB_CONNECTION
Dim objRecordSet As ADODB_RECORDSET
Dim a As Integer
Dim ConnectionDetails, SQL As String
Dim segct As Integer
Dim segmentname(9999) As String
Dim segmentid(9999) As Integer

ConnectionDetails = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = h:\My Documents\Access\ATCORE Table Space History.accdb"

objConnection = New ADODB_CONNECTION
objRecordSet = New ADODB_RECORDSET

objConnection.Open(ConnectionDetails)

For a = 1 To 3

SQL = "Select * from segmentnames"

objRecordSet.Open SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT

If objRecordSet.RecordCount > 0 Then
SegCt = 0
objRecordSet.MoveFirst
While Not objRecordSet.EOF
Incr SegCt

SegmentName(SegCt) = objRecordSet.Fields(2).Value
SegmentID(SegCt) = objRecordSet.Fields(1).Value

objRecordSet.MoveNext
Wend
MsgBox "Loop " & a
End If

objRecordSet.Close

Next

Is there something I should be doing?

This on Version 1.10.4.0 (Windows 7 64 bit).


Thanks

Adam Counsell

[ErosOlmi]

Checking ...

[ErosOlmi]

Ciao Adam,

to solve, please add open and close parentheses to objRecordSet.Open in this way:


objRecordSet.Open(SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT)


I still need to understand why first time loop works and from second time on it gives error.
I suppose it is an optimization step I do the first time I parse the source code that is not correct.

Let me know if it works for you too.
Eros

[AdamCounsell]

Hi Eros,

Yes that is now working.

Thanks for your help.


Adam

[ErosOlmi]

Great, I will try to fix for the next release.