Hello all,
I’ve currently been using an agent that allows for odbc access to a DB2/400 database. However, calling this agent manually requires me to open up the connection to DB2 and do the processing.
I figured that it would work best if I opened up the connection when the form loads. I put the connection code in the Global declarations area, and then a drop down box would trigger the SQL statements to hit the database. I’m thinking that this would increase performance.
However, when running the sql scripts, I get a generic lotusscript error. Here’s the code. What am I doing wrong? Debugger tells me it’s choking at the “Set qry.Connection = ODBCcon” statement.
On the form:
[Options]
Option Public
Uselsx “*LSXODBC”
[Declarations]
Dim ODBCcon As ODBCConnection
Dim ODBCconnected As Variant ’ set to true when connected to an ODBC data source
Sub Initialize
connectODBC
End Sub
Sub connectODBC
Set ODBCcon = New ODBCConnection
If ODBCcon.ConnectTo("xxx", "xxx" , "xxx") Then
ODBCconnected = True
Else
Print "Replicate Item Documents Is NOT Connected"
Print ODBCcon.GetExtendedErrorMessage,,
ODBCconnected = False
Print odbccon.GetErrorMessage
End If
End Sub
The agent that does the SQL processing:
Sub process
Dim db As NotesDatabase
Dim view As NotesView
Dim workspace As New NotesUIWorkspace
Dim session As New notessession
Dim agent As Notesagent
Dim doc As NotesUIDocument
Dim notesyear As Variant
Dim notesmonth As Variant
Dim datstr As Double
Set session = New NotesSession
Set db = session.CurrentDatabase
Set doc = workspace.CurrentDocument
notesyear = doc.fieldgettext("Year")
notesmonth = doc.fieldgettext("Month")
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Set qry.Connection = ODBCcon
qry.sql = "select * from library.database where PRDYR = '" +notesyear + "' AND PRDMN = '" + notesmonth + "'"
Set result.Query = qry
result.Execute
If Not result.Execute Then
Messagebox result.GetExtendedErrorMessage,, _
result.GetErrorMessage
Exit Sub
End If
datstr = result.GetValue("PRDDYS")
Call doc.fieldsettext("ProratedDays",Cstr(result.GetValue("PRDDYS")))
result.Close(DB_CLOSE)
End Sub