I need to create a button on a form which users will use to pull data from an Oracle database and set fields accordingly. I’ve found some good examples of code in the help for making an ODBCConnection. I’m somewhat comfortable with LotusSript, and this looks fairly simple.
At the server we have the Oracle client installed and the data source defined. But will the button work for all of my users if they do not have the Oracle client or data source defined? I’ve not found a definitive answer to that question, but with all my failed attempts its beginning to look that way. I’ll attach below the code I’ve used in a button to test a connection. This fails on a machine w/o the Oracle client and data source, but works on one that has them. Ideally, we’d like a solution for this button where we do not have to install code at each and every desktop. Can someone please suggest the simplest way to accomplish this?
Example of the test button I’ve created to try to connect:
Uselsx “*LSXODBC”
Sub Click(Source As Button)
Dim con As ODBCConnection
Set con = New ODBCConnection
connectionmade=con.ConnectTo("IFMDEV", "user1234", "pw1234")
If Not connectionmade Then
Messagebox con.getextendederrormessage(con.geterror)
Else
Messagebox "It worked!"
End If
con.Disconnect
Subject: RE: ODBCConnection w/o local data source??
Thanks for the reply Willy. Our action will be to do a select statement and find one record from the table (which will potentially have thousands of records), and bring back data from that row. Does this sound like a simple enough action to use the method you describe for the additional database where new documents are created and an agent RunOnServer populates that document?
If this isn’t the right method, what else can I use?
QryDoc is Created and Saved and QryDocID is a NoteID.
The Run on server agent has this code in it
Dim S As New NotesSession
Dim ThisAgent As NotesAgent
Dim ThisDB As NotesDatabase
Dim QryDoc As NotesDocument
Set ThisDB = S.CurrentDatabase
Set thisAgent = S.CurrentAgent
Print "In Agent "
Set QryDoc = ThisDB.GetDocumentByID(ThisAgent.ParameterDocID)
On the Server Log I get the following
10/17/2008 01:41:07 PM Agent printing: In Agent
10/17/2008 01:41:07 PM Agent ‘ODBCQuery’ error: Invalid note id
I’m at a loss as to why the QryDocID that is captured in the calling agent is not being passed correctly.
If I add this code to the calling agent
Set QryDoc = Nothing
Set QryDoc = ThisDB.GetDocumentByID(QryDocID)
It gets the correct document So QryDocID is correct in the calling Agent, and the Document is saved. Could it be a timing problem that the RunOnServer agent can’t see the document yet?
Subject: RE: ODBCConnection w/o local data source??
Thanks for your help. I have got it ‘working’ but have run into a couple of strange problems. Could we have an off-line dialog on this. email me at bill_fox@wfs.com
Thanks Willy, you’re a genius! I’ve still got coding and “smoothing of rough edges” to do but I’ve already proven that the connection works by pulling in a row from my ORACLE table. This is slick! Thanks for your help.
Do you know for sure if the .RunOnServer requests get queued? Is there a server setting that will allow these call to run asynch similar to the DominoAsynchronousAgents?
Subject: RE: ODBCConnection w/o local data source??
I missed the piece on ODBC and assumed client.
The only other solution I have used and find it to be much better performance is the DECS pulling against a database view or table. The only issue the difficulty trying to trap on errors.
Subject: RE: ODBCConnection w/o local data source??
Thank you for posting a solution and example for one of the most irritating issues with getting external data into the Notes client! I have LEI and it’s still a pain to get the external data.
