SQL ODBCresultset.execute fails (goes to errorHandler but gives error 500?)

HCL Domino Domino Forum
1

I’m trying to get data from an centura sqlbase server via ODBC. Connection succeeds, but the result.execute fails.

Here’s (most of) my code:

=======================================

… dim variables …

dataSource = “SOURCENAME”

userName = “USER”

password = “PW”

tabelle = “AMES_1”

sqlstr = “SELECT * FROM AMES_1 WHERE KDNR = ‘00001805’”

On Error Goto errorHandler

con.ConnectTo(dataSource, userName, password)

’ – here I test for table names, they return fine – including AMES_1

’ – so the connection is up, and I can “see” the tables

tables = con.ListTables()

msg = dataSource & " contains the following tables:" & Chr(10)

For n% = Lbound(tables) To Ubound(tables)

msg = msg & Chr(10) & tables(n%)

Next

Messagebox msg, "Tables for " & dataSource

’ === now I try my SQL Query: ===

Set qry.Connection = con

qry.sql = sqlstr

Set result.query = qry

result.Execute ’ ===> but from here it jumps to the errorHandler

…

con.Disconnect

Exit Sub

errorHandler:

…

Messagebox result.GetError

’ – and the error message is: result.GetError = “500”

=======================================

When I debug the code and look into the variables, qry.Connection is empty (doesn’t show any value) – but I think this is normal?

And what sort of result.execute error gives an result.geterror = 500? (as far as I understand the docu: “If an operation is successful, the return number is 500” this number shows that there is no error?!)

Is anything wrong in my sql statement?

Or must this be an error in the ODBC connection (and drivers)?

I can query data with excel from this (sqlbase) server, from the AMES_1 table, of course with the same username/pw.

Thanks for any help in advance

Uwe

2

Subject: SQL ODBCresultset.execute fails (goes to errorHandler but gives error 500?)

There is nothing wrong with query or the result set. It has been my experience that you must load the result set into an Array and then parse the results afterwards.

I will look up some old code and paste in next response.

3

Subject: RE: SQL ODBCresultset.execute fails (goes to errorHandler but gives error 500?)

Here is an example. Hope I got all the code…

But you can see how I did it for this old program. Hope

'Create objects and declare variables

Dim session As New NotesSession

Dim doc As NotesDocument

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim retvalue As Variant

Dim result As New ODBCResultSet

Dim key As Variant		

Dim  array14() As String

Dim  array18() As String



Set doc= session.DocumentContext

'Connect to SQL server	

Call MakeODBCConnection(con, GetDataSource(), False)	

If con.IsConnected Then

On Error Goto ErrorHandler

Set qry.connection = con

Set result.query = qry

'Execute query

qry.SQL = "SELECT * FROM table WHERE Key = '" & key & "' ORDER BY Number"

If Not result.execute Then

	Msgbox "Couldn't find Records"

End If

'Initialize array counter

count1 = 0

count2 = 0

count3 = 0

count4 = 0

count5 = 0

Do 

 Redim  Preserve array14(count1) As String

 Redim  Preserve array18(count1) As String

result.NextRow				If result.GetValue("Name") = "Lastname" Then				

array14(count1) = result.GetValue("CustID")

array18(count1) = Propercase(Left(result.GetValue("CustName"),15),False)

End If

Loop Until result.IsEndOfData

result.Close(DB_CLOSE)		

con.disconnect

doc.array14 = array14

doc.array18 = array18

The last 2 lines point to fieldds defined on the form.

Hope this helps

4

Subject: RE: SQL ODBCresultset.execute fails (goes to errorHandler but gives error 500?)

Thanks for your answers + the code example :slight_smile:

our error was caused by wrong table name definition on the sql database side. After correcting this the connection works “quite” fine…

thanks a lot for your “brain” and your research… and I even can learn sth from your code :slight_smile:

Uwe