Connecting to ORACLE database

HCL Domino Domino Forum
1

Dear All,

I am trying to access a ORACLE database from a domino web application. can somebody post a sample code.

My Database Name : “prd”

User Name : “Admin”

Password : “123htgs”

Table Name : “CUSTMAST”

Thanks

2

Subject: Connecting to ORACLE database

hey thomson,

try this…

Dim src As LCConnection

Set src =New LCConnection (“oledb”)

src.provider = “sqloledb”

src.Server = “ORACLE SERVER NAME”

src.Database = “DATA BASE”

src.Userid = “Admin”

src.Password = “123htgs”

src.connect

3

Subject: RE: Connecting to ORACLE database

I’m trying to connect through ODBC.

Do I have to create a system DSN if I use this method?

4

Subject: RE: Connecting to ORACLE database

Hi,

I used this code:

Sub Initialize

On Error Goto trap	

Dim ses As New Lcsession	

Dim src As LCConnection

Set src =New LCConnection ("oledb") 

src.provider = "oracle"

src.Server = "10.242.9.10"

src.Database = "prd"

src.Userid = "eagleisf"

src.Password = "isfscho8583"

src.connect

trap:

Dim stat$, errcode As Long, msg$

If ses.Status <> LCSUCCESS Then

	ses.Getstatus stat, errcode, msg

	If (ses.Status = LCFAIL_EXTERNAL)Then

		Messagebox "ODBC message: " & msg & "  code #" & Cstr(errcode), 0, _

		"error number " & Err & " line " & Erl

	Else

		Messagebox "Connector message: " & text, 0, "error number " & _ 

		Err & " line " & Erl

	End If

Else

	Messagebox Error, 0, "error number " & Err & " line " & Erl

End If

End Sub

When I run this from a view action, it gives the error:

“ODBC message: code #0”

If I debug the code, it gives the error:

“Connector message: 1280”

Please help

5

Subject: RE: Connecting to ORACLE database

I believe you will have to also create an Data odbc source under Administrative tools in windows, for each pc, unless the Agent is run on the server then you’ll only need it on the server

Cheers

6

Subject: RE: Connecting to ORACLE database

Buwa,

To run on server =

Create an ODBC connection on server. Use the following code in an agent :

To run on each PC =

Create ODBC connections on each pc and use the following code in the actions:

Uselsx “*LSXODBC”

Dim session As New NotesSession

Dim strActCode As String

Dim strActDesc As String

Set con = New ODBCConnection

Set qry = New ODBCQuery

Set result = New ODBCResultSet

Set qry.Connection = con

Set result.Query = qry

If con.ConnectTo(“ODCB_connection_name”, “user_name”, “password”) Then

qry.SQL = "SELECT * FROM table or view"

If Not result.Execute Then

	Messagebox result.GetExtendedErrorMessage,, result.GetErrorMessage

	con.Disconnect

	Exit Sub

End If

Do

	result.NextRow

	--- your logic

	--- your logic

	--- your logic

	--- your logic

Loop Until result.IsEndOfData		



result.Close(DB_CLOSE)

End If

con.Disconnect

7

Subject: RE: Connecting to ORACLE database

I tried this even. But It doesn’t connect. It doesnt go in to If con.ConnectTo…

I am executing this code on the server. Do I need to install a specific Oracle Client on the Domino Server? I have installed Oracle Client version 6i. Oracle Server version is 9i. Does it matter?

8

Subject: RE: Connecting to ORACLE database

No need to install oracle client software.

Try the following code without if condition as shown below. With this you can identify the error bcos it will generate the error and come out of the code.

My idea is the user you are trying with has a problem access rights to the oracle database or the table you are trying.

Try this code and let me know the error code:

con.ConnectTo(“ODCB_connection_name”, “user_name”, “password”)

9

Subject: RE: Connecting to ORACLE database

Yeah… I think so. I have installed Oracle 6 client. Now I’m trying to get down Oracle 9 client and test it.

10

Subject: RE: Connecting to ORACLE database

Yes, it could be. Try by installing Oracle 9i client software. Other thing would be, there are no proper rights for the user. As I suggested earlier, you can identify this situation thru error code that notes throw.

11

Subject: RE: Connecting to ORACLE database

Check these codes from Domino help examples :

example 1: using Error class

On Error Goto errorHandler

con.ConnectTo(“ATDB”)

qry.SQL = “SELECT * FROM STUDENTS”

result.Execute

…

errorHandler:

If con.GetError <> DBstsSUCCESS Then

Messagebox con.GetExtendedErrorMessage,, _

con.GetError & " " & con.GetErrorMessage

End If

If qry.GetError <> DBstsSUCCESS Then

Messagebox qry.GetExtendedErrorMessage,, _

qry.GetError & " " & qry.GetErrorMessage

End If

If result.GetError <> DBstsSUCCESS Then

Messagebox result.GetExtendedErrorMessage,, _

result.GetError & " " & result.GetErrorMessage

End If

Exit Sub

Example 2: Error code on a message box

con.ConnectTo(“ATDB”)

If con.GetError <> DBstsSUCCESS Then

Messagebox con.GetExtendedErrorMessage, _

con.GetError & " " & con.GetErrorMessage

Exit Sub

End If

qry.SQL = “SELECT * FROM STUDENTS”

If qry.GetError <> DBstsSUCCESS Then

Messagebox qry.GetExtendedErrorMessage, _

qry.GetError & " " & qry.GetErrorMessage

Exit Sub

End If

result.Execute

If result.GetError <> DBstsSUCCESS Then

Messagebox result.GetExtendedErrorMessage, _

result.GetError & " " & result.GetErrorMessage

Exit Sub

End If

12

Subject: RE: Connecting to ORACLE database

I do have created a System DSN on the server.