Oracle and adodb syntax?

HCL Domino Domino Forum
1

I have what I think is probably a simple syntax ?

I’ve never connected to Oracle from Notes before (usually sql using dsns, yuk).

I want to connect to an oracle db without using a dsn or installing anything on my notes server. Possible, yes? From what I’ve read I should look into adodb. Does anyone have a good documentation resource for this you can point me to?

This is the info I have from my oracle developer:

Server : stef.domain.com

Service name /SID : MDFDW2

database/login ID : USERID

password : PASSWORD

table_name : TABLENAME

Is there any other info I need from him?

Here’s the start of the code :

Sub Initialize

Dim adoConn As Variant

Dim adoCmd As Variant

Dim rs As Variant

Dim param As Variant



Dim session As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument 



Dim strTxt As String, strConn As String, strSQL As String

Dim x As Integer



Set db = session.CurrentDatabase

Set adoConn = CreateObject("ADODB.Connection")

Set adoCmd = CreateObject("ADODB.Command")

Set rs = CreateObject("ADODB.Recordset")



adoConn.ConnectionTimeout = 30

adoConn.Provider =??

adoConn.Properties("Data Source") =??



adoConn.Properties("User ID") = "USERID"

adoConn.Properties("Password") = "PASSWORD"

’ adoConn.ConnectionString = strConn

adoConn.Open

=====================================

I get the error msg:

“ADODB.Connection: Provider cannot be found. It may not be properly installed”

2

Subject: oracle and adodb syntax ?

Stefanie, give this a try. I haven’t tested it and don’t have Oracle, but this is based on sample code I got from support.microsoft.com.

adoConn.Provider = “MSDAORA”

adoConn.Properties(“Data Source”) = “stef.domain.com”

3

Subject: RE: oracle and adodb syntax ?

Thanks for your response – I’ll check it out.

support.microsoft.com should be my source of documentation for this I guess.

From what I’ve read online, this appears to be the fastest method.

Have you worked with importing data from oracle before?

I still have to install the oracle client on my server, correct?

thanks again.

4

Subject: RE: oracle and adodb syntax ?

I’m really really slow. I just realized that by Data Connection you meant under Shared Resources/Data Connections.

Still can’t get this to work but my eyes are open a little more.

Thank you soooooo much. I apologize for my ignorance.

5

Subject: RE: oracle and adodb syntax ?

I don’t know about adodb. I would use the LC LSX for this. Long chapter in this Redbook.

6

Subject: RE: oracle and adodb syntax ?

but if I’m going to use lsc, that requires a dsn, correct?

thx

stef

7

Subject: RE: oracle and adodb syntax ?

No – if you have the oracle access software installed you can use the oracle or oracle8 Lotus Connector to avoid using ODBC. I don’t know anything that doesn’t require you to install something on the server.

What’s the big problem you set up a DSN in any case?

8

Subject: RE: oracle and adodb syntax ?

ok. just ran a script that lists the usable connectors:

came up with:

The usable Connectors are file, notes, odbc2, oledb

Part of a lot of my issues, yes?

9

Subject: RE: oracle and adodb syntax ?

sorry for all the stupid user ?s

So I need to have the oracle8 Lotus Connector installed. It comes with Notes? Works with oracle 10g? How does my admin make it available for devleoper use?

Thanks for your time…and patience.

10

Subject: RE: oracle and adodb syntax ?

The connector should be installed automatically along with those others when you install Domino, or the Notes client with DECS option.

In addition to the Oracle8 Lotus connector, you need an Oracle client. The Lotus connector uses the Oracle API to access Oracle, and so the API has to be available locally.

I haven’t heard whether LC supports Oracle 10g. I know it supports 9i.

11

Subject: RE: oracle and adodb syntax ?

no big deal. I use dsns all the time. Was just trying to avoid it if I could… do something new.

For oracle though I still have to install the oracle client on my server, correct?

Thanks.