Notes/Domino 6 and 7 Forum : ODBCResultSet Error

I'm getting the following error in my log and cannot see what I am overlooking. I hard coded my query statement which tested fine with using F.R.O.G. for IBM iSeries DB2 SQL Utility.

01/24/2012 14:20:52 Agent message: Could not execute query :Select FPO# from NWFFCHI.POFILE where FPO# = B123

Any help with ODBC ResultSet is appreciate.

Here is the code which is called from a button

****** Button Script *****************************************************
Sub Click(Source As Button)
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Set uidoc = workspace.CurrentDocument

Dim item As NotesItem
Dim doc As NotesDocument
Dim db As NotesDatabase
Set db = workspace.CurrentDatabase.database
Set doc = db.CreateDocument

Set item = doc.AppendItemValue("PONo",uidoc.document.PONo)

Call doc.save(True,False)
noteid = doc.NoteID

Dim agent As NotesAgent
Set agent = db.GetAgent("POLookup")
Call agent.RunOnServer(noteid)

'Delete in document memory
Delete doc

'Then get the version that the agent just updated
Set doc = db.GetDocumentbyID(noteid)
Set item = doc.GetFirstItem("PODesc")
Call uidoc.FieldSetText( " PODesc ", item.text)

Call uidoc.refresh
Call doc.remove(True)
End Sub

******* POLookup Agent Script *******************************************

Option Public
UseLSX "*LSXODBC"

Dim Con As ODBCConnection 'LSDO Connection Object
Dim Qry As ODBCQuery 'LSDO Query Object
Dim Res As ODBCResultSet 'LSDO Result Set Object

Sub Initialize

Dim s As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim item As NotesItem
Dim agent As NotesAgent

Set db = s.CurrentDatabase
db.delayupdates = False
Set agent = s.CurrentAgent

'Get document used for passing data
Set doc = db.GetDocumentByID(agent.ParameterDocID)

'Create Connection object
Set Con = New ODBCConnection
Dim ds As String, user As String, pw As String

ds = "NWF820"
libr = "NWFFCHI"

'Connect to Datasource
user = "domconn"
pw = "domconn"
retcode% = Con.ConnectTo( ds, user, pw )

'Exit if Connection failed
If ( retcode% = False ) Then
MsgBox "Could not connect to AS/400 datasource " + ds
error% = conn.GetError
extendedMessage$ = conn.GetExtendedErrorMessage(error%)
MsgBox extendedMessage$
Exit Sub
End If

'Create an SQL Query object
Set Qry = New ODBCQuery

'Assign Connection to Query
Set Qry.Connection = Con

Print "**(* This is the PO Number that is found in the POLookup Agent :" + doc.PONo (0)

'Get Name
Qry.SQL = "Select FPO# from NWFFCHI.POFILE where FPO# = " + doc.PONo (0)

Set Res = New ODBCResultSet
Set Res.Query = Qry
retcode% = Res.Execute

'Exit if Execute failed
If ( retcode% = False ) Then
MsgBox "Could not execute query :" + Qry.SQL
error% = data.GetError
extendedMessage$ = data.GetExtendedErrorMessage(error%)
MsgBox extendedMessage$
Exit Sub
End If

'Write back to the temporary document
doc.i5PONum = res.getvalue("PODesc" )

'Update the document
Call doc.Save( True, True )

'Free Result Set
Res.Close(DB_CLOSE)

'Disconnect from datasource
Con.Disconnect
End

Notes/Domino 6 and 7 Forum

Resources

Lotus Support

Wikis

Lotus Forums