Clovertech

oracle odbc.ini values

› Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › oracle odbc.ini values

  • Creator
    Topic
  • December 15, 2011 at 10:07 pm #52850
    Daniel Lee
    Participant

      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

    • Creator
      Topic
    Viewing 5 reply threads
    • Author
      Replies
      • December 15, 2011 at 10:31 pm #75663
        Tom Rioux
        Participant

          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

        • December 15, 2011 at 10:38 pm #75664
          Daniel Lee
          Participant

            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!

          • December 16, 2011 at 2:33 am #75665
            Jim Kosloskey
            Participant

              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.

            • December 16, 2011 at 3:15 pm #75666
              Daniel Lee
              Participant

                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!

              • December 16, 2011 at 6:19 pm #75667
                Tom Rioux
                Participant

                  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

                • December 16, 2011 at 7:31 pm #75668
                  Jim Kosloskey
                  Participant

                    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
                Viewing 5 reply threads
                • The forum ‘Cloverleaf’ is closed to new topics and replies.