We help IT Professionals succeed at work.

ODBC Connection

tuancd
tuancd asked
on
Hi experts,

I design a database running on Lotus Notes and connect to SQL database through ODBC. It running OK if I have SQL ODBC driver to be installed on my PC. The issue here I don't want to install the ODBC drive on all of PC in my company to read the SQL database, so I installed the SQL ODBC driver in Lotus Notes server only, but all users were not successfully to connect to SQL database. Could you please tell me how to resolve this problem ?(Notice that I don't want to install ODBC driver on all PCs)

Thanks & Regards,
Tuancd.
Comment
Watch Question

Create an agent which connects to odbc dsn.

Run this using Notesagent.RunOnServer method, so that it would perform all odbc lookups at server level.

supporting info: http://www.support.lotus.com/sims2.nsf/00c6bd75e325c34585256acd005b47ae/7f83f571f4526383852565550074b2f1?OpenDocument

~Hemanth

Author

Commented:
Hi Hemanth,

Could you tell me more detail,please, what script should I put in the agent and how can I display the information after I connected to database, because as my understand, it is impossible to diplay the messagebox in the agent.

Thanks & Regards,
Tuancd.


Commented:
If u run agent on server, use "Print" to display the information, and the printed line will appear in the server log (log.nsf).
You can't display any msgbox or ui objects like dialog or form when agent is runonserver.

The method I have suggested is used to trigger the agent as if it is run by server on a schedule.

In the agent make odbc connection and fetch the data then udpdate or create a document with the values that you would like to present to the user and open that document into read mode.

Hope it is clear

~Hemanth

Author

Commented:
Hi Hemanth,
I followed your intruction and created 2 Agents as below:
Agent 1 Called "CallRep"
     Dim session As New NotesSession
     Dim db As NotesDatabase
     Dim agent As NotesAgent
     Set db = session.CurrentDatabase
     Set agent = db.GetAgent("ConnectODBC")
     If agent.RunOnServer = 0 Then
          Messagebox "Agent successfully ran",, "Success"
     Else
          Messagebox "Agent did not run",, "Failure"
     End If
End Sub
Agent 2 Called "ConnectODBC"
     Dim con As New ODBCConnection
     Dim qry As New ODBCQuery
     Dim result As New ODBCResultSet
     Dim StrSql  As String
     If Not con.ConnectTo("CCBV","HOANVU","180") Then
          Print "Could not connect to Data"
          Exit Sub
     End If
     
     StrSql = "Select * from NhanVien"
     
     Set qry.Connection = con
     Set result.Query = qry
     qry.SQL = StrSql
     result.Execute
     i = 1
     If result.IsResultSetAvailable Then
          Do
               result.NextRow
               TenEng = result.GetValue("Ten_Eng")    
               STT = result.GetValue("STTNV")
               LeaveDate = result.GetValue("Ngay_VM")
          Loop Until result.IsEndOfData
          result.Close(DB_CLOSE)
     Else
          Print "No data"
          Exit Sub
     End If    
     
     Print STT
     Print TenEng
End Sub

But when I launch the "CallRep" agent I get an error message as below

"Notes Error :Unknown LotusScript Errors"

Could you please explain and tell me what wrong with my agent ?

Thanks & Regards,
Tuancd.
Can't really spot the problem !

Try to debug by printing information between the code, also check if the odbc connection is made by printing out odbcConnection. DataSourceName

Unknown LS error is caused by ui functions in the scheduled agents, this msg is also generic so you can't really bank on it.

CERTIFIED EXPERT

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept HemanthaKumar's comment as answer.

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

CRAK
EE Cleanup Volunteer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.