oracle odbc.ini values

[Daniel Lee]

Cloverleaf 5.8

Connect 6.0 drivers

AIX 6.1

I’ve connected to other databases (MSSQL, Sybase) with the Connect 6.0 wire protocol drivers without issue but am having problems with this Oracle database.  There are 62 lines in the oracle wire datasource template but I’m not sure which of them I need to mess with.  Below are the lines that I’ve altered, can anyone tell me if I’m missing anything important?

[Datasource Name]

Driver=/cloverleaf/healthvision/cis5.8/integrator/lib/Connect6.0/lib/CVora24.so

Password=xxxxxxx

PortNumber=xxxxx

ServerName=xxx.xxx.xxx.xx

SID=xxxxxxxxxx

The rest of the fields I just left the default values in.

Also, I know I’m probably dreaming here but is there a way to get a more detailed database connection error back instead of “SQL_ERROR”?  Will the vendor’s db logs contain the details on why it refused the connection?

Thanks,

Daniel

Topic

[Tom Rioux]

Daniel,

Is the TNSNamesFile field filled out in your odbc.ini for that server.   It will point to the tnsnames.ora file.  In that file, you will need to set up the information about the SID.

Also, an application typically calls SQLGetDiagRec when a previous call to an ODBC function has returned SQL_ERROR or SQL_SUCCESS_WITH_INFO.   Here is an example we use:

odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 711 TextLength

The variable “MessageText” will contain the error information.   Hope this is what you were looking for.   We also found the command SQLGetInfo to be handy in testing to see if a connection is valid.

Hope this helps….

Tom Rioux

[Daniel Lee]

Thomas,

That’s great information.  I’ve always just dealt with the generic SQL_ERROR messages. It’s nice to know there’s a way to get more detail than that.

A co-worker an I were just talking about the tnsnames file.  It currently is not filled out but I did just get the info that needs to go in that file.  I’ll try it out and let you know how it works.

Thanks again!

[Jim Kosloskey]

Just a point of information – if using the wire protocol you do not need the tsnames file (we don’t use one) if you fill out more connection information specific to the DB.

We use the wire protocol (by the way it is actually faster than the Oracle tsnames method).

We wanted to be aware of when the Oracle DBAs were changing the server we were pointing to because they were and are known to change inappropriately then not be cooperative during troubleshooting. This way they have to let us know. They put a big stink, but in 5 years we have not had to change anything and others using tsnames (not us) have had issues.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 61 years IT – old fart.

[Daniel Lee]

Thanks guys, that did the trick.  I ended up not using a tnsNames.ora file.  Evidently there was a problem with the driver that I was attempting to use.  The SQLgetDiagRec command was the key to figuring out what was wrong.  Once I started getting descriptive error messages back I was able to diagnose the issues.  That command was also helpful when running my fetch statements.

Thanks again guys!

[Tom Rioux]

Jim,

I’m curious,  how do you set it up without the tnsnames.ora file?   That is how we have everything set up that makes a call to a database.   I’m interested in learning any method that is faster.   Does that come into play only with a connection or does it also make the calls to the database work faster?

Thanks…Tom

[Jim Kosloskey]

Tom,

We use the ServiceName Hostname and Portnumber arguments (connect time) we do NOT use the ServerName  or the SID (which requires the tnsnames.ora file).

As for performance – that is something I saw in a white paper produced by Data Direct and another paper I recall seeing on the Oracle Web site related to Data Direct.

So I am not sure whether the differences are only at connect time or not but since my understanding of odbc is the entire protocol is engaged with each interaction and the use of the tnsnames file requires reference to it for every odbc interaction I would suspect the performance differences are throughout.

Again since we have never used the tnsnames method we have nothing with which to compare.

I can tell you all of our Data Direct connectins are lightning fast (but a lot of that credit could go to the DB Servers as well).

If you have not doen so already, I would suggest you read the Data Direct Guides for the ODBC drivers thoroughly (especially the sections related to Oracle).

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 61 years IT – old fart.

[Author]

Replies