mORMot Open Source

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Connection Parameters to Oracle...

Hello,

Am trying to change an old application that used a commercial
component to connect to Oracle to just use the greate MorMot framework.

Am calling the following
TSQLDBOracleConnectionProperties.Create('database','user','password');

and still can't connect.

The old component used SID (from the tnsnames.ora file ) as a connection parameter.
If I understood correctly , what is needed now as the first parameter
is the *actual* name of the tnsnames.ora file?

( have Instant client and tnsnames.ora in the same directory along
with my applic)

Much obliged for your help,
Sami

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Connection Parameters to Oracle...

Which version of the library are you using?
The TSQLDBOracleConnectionProperties.Create constructor with 3 parameters is a very old one (before 1.17 revision AFAIR).
Ensure you get the latest 1.18 revision, with the standard signature:

    /// initialize the connection properties
    // - aDatabaseName is not used for Oracle: only aServerName is to be set
    // - this overriden method will force the code page to be zero: you shall
    // better call the CreateWithCodePage constructor instead of this generic method
    constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;

The aServerName parameter is is to be filled as expected by the OCI driver, as stated by http://docs.oracle.com/cd/E11882_01/app … m#autoId49

In particular, the connect_identifier in the OCIServerAttach() call can be specified in the following formats:

- A SQL Connect URL string of the form:
[//]host[:port][/service name]
For example:
//dlsun242:5521/bjava21

- As an Oracle Net connect descriptor. For example:
"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242) (PORT=5521))
(CONNECT_DATA=(SERVICE_NAME=bjava21)))"

- A Connection Name that is resolved through Directory Naming where the site is configured for LDAP server discovery.

In fact, this is clearly stated by the other constructor documentation:

    /// initialize the OCI connection properties
    // - we don't need a database name parameter for Oracle connection
    // - you may specify the TNSName in aServerName, or a connection string
    // like '//host[:port]/[service_name]', e.g. '//sales-server:1523/sales'
    // - since the OCI client will make conversion when returning column data,
    // to avoid any truncate when retrieving VARCHAR2 or CHAR fields into the
    // internal fixed-sized buffer, you may specify here the exact database
    // code page, as existing on the server (e.g. CODEPAGE_US=1252 for default
    // WinAnsi western encoding) - if aCodePage is set to 0, either the global
    // NLS_LANG environnement variable is used, either the thread setting (GetACP)
    constructor CreateWithCodePage(const aServerName, aUserID, aPassWord: RawUTF8; aCodePage: integer); virtual;

With instant client, my favorite is to use the  '//host[:port]/[service_name]', e.g. '//sales-server:1523/sales' syntax, and do not use the tnsname.ora, which may be confusing with existing installations.

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Re: Connection Parameters to Oracle...

Thanks for your help,
and sorry, my mistake, am using a newer version from March 2013,
but left out one parameter in the example,

so am using this:

TSQLDBOracleConnectionProperties.Create
('','tnsnames.ora','USERId', 'mypassword');

Will try the other way you suggested,
hopefully will find all the required parameters in the tnsnames.Ora
file.

Many thanks,
Sami

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Connection Parameters to Oracle...

No, you need to write:

TSQLDBOracleConnectionProperties.Create
('TNSNAMEALIAS','','USERId', 'mypassword');

As clearly stated in the doc, the aDatabaseName (2nd parameter) is ignored, and you must put the connection name in aServerName (1st parameter).

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Re: Connection Parameters to Oracle...

Thanks so much for pointing this out,
somehow got confused in the order of parameters :-).