Cannot get values from ODBC connection to SQL database

HCL Domino Domino Forum
1

Hi, I have the followin agent code (an ODBC connection has prevoiusly been set in the PC and it works properly)

Sub Initialize

On Error Goto ErrorHandler

Dim session As New NotesSession

Dim WEBDoc As NotesDocument

Dim dcol As NotesDocumentCollection

Dim con1 As New ODBCConnection

Dim qry1 As New ODBCQuery

Dim result1 As New ODBCResultSet

Dim firstname As String, lastname As String

Dim tmpString As String



tmp_username="Maria de Leon"

refNr="CIP325965"



Call con1.connectTo(CONNECTTO,LOGIN,PASSWORD ) 

Set qry1.Connection = con1

qry1.SQL =  |SELECT * From call_req, ca_contact WHERE call_req.ref_num='|+refNr+|' AND call_req.assignee=ca_contact.contact_uuid|

Set result1.query = qry1

Result1.CacheLimit ="10000"



If result1.Execute Then	

	If result1.IsResultSetAvailable Then

		result1.NextRow

		firstName = result1.GetValue("first_name")

		lastName = result1.GetValue("LAST_NAME")

		Messagebox (msg & Chr(10) & firstName & " " & lastName)

		

	End If

End If	

result1.Close(DB_CLOSE)

con1.Disconnect



Exit Sub

ErrorHandler:

Msgbox "Error. (Initialize) : "+Cstr(Error)+". Line: "+Cstr(Erl())

result1.Close(DB_CLOSE)

con1.Disconnect

End Sub

But Im getting the error message “The result contains no data”

I have an external tool to try the queries in the same database and it works fine, but not in Lotus notes.

When I try with a different table, I alse get the error “Cannot convert data from native to expected or required datatype”

I’ve been searching in this foro but the tips I found didnt work for me.

Could anybody please help me?

Thanks!

2

Subject: Cannot get values from ODBC connection to SQL database

Instead of bringing back every column in the two SQL tables, try limiting your SELECT statement to “first_name” and “LAST_NAME” for now just to see if the query works. It may be that there are columns defined with some data type that the ODBC can’t handle with the query as its written.

You might also add to your error handler, the GetExtendedErrorMessage method to get a more detailed error on what’s happening.

hope that helps.

3

Subject: RE: Cannot get values from ODBC connection to SQL database

I did change my query to bring back only Last_name and first_name but I am still getting the error message: “Cannot convert data from native to expected or requested datatype”. I checked in the SQL table and both fields are VARCHAR(100).

4

Subject: RE: Cannot get values from ODBC connection to SQL database

What relational database are you using? MS SQL? Or DB2? The reason I ask is because I recall there was a setting on the ODBC data source that needed to be set in order to read the data properly from a DB2 back-end.

5

Subject: RE: Cannot get values from ODBC connection to SQL database

Hi, I am using MS SQL. After many hours trying I could fix the problem by casting the fields within the query.My query string now is something like

qry1.SQL= |SELECT CAST(first_name AS varchar(10)), CAST(last_name AS varchar(10))From call_req, ca_contact WHERE call_req.ref_num=‘CIP325965’ AND call_req.assignee=ca_contact.contact_uuid

and it works!!! the same when I have values with datatype BIGINT within SQL, I have to Cast them in the query.

Thanks!!