SQL ODBCresultset.execute fails

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 …

Set qry.Connection = con

qry.sql = sqlstr

Set result.query = qry

'Verbindung zu ADIS

dataSource = “SOURCENAME”

userName = “USER”

password = “PW”

tabelle = “AMES_1”

sqlstr = " SELECT * FROM AMES_1 WHERE KDNR = ‘00001805’"

If Not con.ConnectTo(dataSource, userName, password) Then

Messagebox "Could not connect to " & dataSource

Exit Sub

End If

’ – 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:

result.Execute

If Not result.IsResultSetAvailable Then

Messagebox “ODBCResultSet.execute eror”

con.Disconnect

Exit Sub

End If

’ – and I always runs into the error message

con.Disconnect

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

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?

So is anything wrong in my sql statement, or must there 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

I think your problem is that you are setting sqlstr after qry.sql = sqlstr

Here’s a sample from one of my agents:

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Dim sql As String

If con.ConnectTo(“ODBCConnectionName”,“UserID”,“Password”) Then

Set qry.Connection = con

If con.IsConnected Then

		Set result.Query = qry	

		qry.sql = |select * from customer_name|

		result.Execute

		If result.IsResultSetAvailable Then				

			Do

				result.NextRow				

				customer_name = result.GetValue(1,customer_name)

				customer_id = result.GetValue(2,customer_id)					

			Loop Until result.IsEndOfData						

		End If

		result.Close(DB_CLOSE)				

	End If

End If

con.Disconnect
3

Subject: upps - sorry!!

in my code it was correct - I rearranged it here for the posting - to make it easier to read…

the error happens also when the sqlstring is set at the correct place in the code - I repost the question, with some more info. Here is the link:

http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedweb/a757f4985b6e4ac385257042002f5033?OpenDocument

thanks for your quick reply - it’s a pity that I didn’t get it friday…

Uwe