Issue connecting to Oracle when TNS is defined via LDAP (Solved)
lazarus / 16-Databases

----------------------------------------------------------------------
Mike.Cornflake · 2019-05-30 06:28

G'day,

Honestly don't know where this issue is coming from - I'm posting here
in case it's library related...  Reading this though, it's not going
to look like a pascal question, but I'm hoping it is, or that one of
you guys know the magic one line to resolve.

Up until now I've had no problems connecting to Oracle using either
ZEOS, SQLDB or ODBC.  One client has an issue.  Their system is not
configured with the normal TNSNAMES.ORA, instead, their TNS is
resolved via LDAP.  Whenever I try to connect (ZEOS, ODBC), an
exception is raised in "oraldapclnt12!ora_ldap_set_rebind_proc" (as
shown in Assembler window only), and the program immediately
terminates - I've no idea where that call is, and I'm assuming it's
inside OCI.dll?.  Whenever I try to connect to connect via SQLDB an
exception is raised in the middle of nowhere, and the program
immediately terminates.

Outside of freepascal, SQL Developer and sqlplus connect with no
issues.  A colleague who uses python is mildly amused - his code
connects with no change either. 

This issue appeared when the client upgraged from Oracle 11 to Oracle
12.  I don't know the prior state, but I suspect with Oracle 11
connections were handled via TNSNAMES.ORA.

Freepascal 3.0.4, LAZ SVN 59831 32bit
Oracle 12.1.0.2 Server 64bit
Oracle 12.1.0.2 Client 32bit  (it's the client I connect to, this
handles the connection to the server)
Windows 7 64bit

Code is rather simple. 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
FConnection := TZConnection.Create(Application.MainForm); 
FConnection.Protocol := 'oracle';      FConnection.Database :=
FDatabaseName;  // TNS name  FConnection.User := FSchema;  //
Username  FConnection.Password := FPassword;    FConnection.Connected
:= True; // This is where the exception gets raised
Part of this is I've no real idea of what LDAP is.  Does this mean I
handle the connection differently?

Anyone any ideas?

Many thanks

Mike

----------------------------------------------------------------------
Mike.Cornflake · 2019-05-30 06:46

OK, I have been presented a solution (Thanks Ian Smith).  Well, more
like a workaround. Little confused as how I'm going to implement, but
I can connect.

If I change

FConnection.Database := 'THE_TNS_NAME';
to
FConnection.Database := '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=THE
HOST)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=THESID
)))';

(Hand typed here - development is on a locked down PC, my bracket
count above may be wrong ))

Then it works.  I got all that database connection string from the
command line

C:\tnsping THE_TNS_NAME

I never know I could put the connection string in the DATABASE field. 
Sharing this in case others didn't either.

----------------------------------------------------------------------
engkin · 2019-05-30 07:03

Catch the exception and see its details. Might give some hint. Also,
try/finally is a good practice.

----------------------------------------------------------------------
engkin · 2019-05-30 07:29

On Python, is it using ODBC as well? If no, then probably the ODBC
driver needs to be updated?

----------------------------------------------------------------------
Mike.Cornflake · 2019-05-30 23:45

G'day Engkin,

Thanks for the reply.

RE: Python.  cx_Oracle library is used.  Looking at https://cx-
oracle.readthedocs.io/en/latest/installation.html#installing-cx-
oracle-on-windows, it's not clear how the connection is managed with
Oracle Client (which is what we're using), but with Oracle Instant
Client, I note OCI is used.  If normal client also use OCI, then it
would appear we connect the same way.

Having said that I think you may be right about ODBC drivers.  I
couldn't get ODBC to work via Excel either. 

RE: Exception

During normal runtime, no exception is raised, the app just closes.
During debugging a get a minimal exception raised (one I've not seen
before, and I apologise I didn't grab details).  There was no
explanation text, and no exception name.  IDE paused in the middle of
Assembler as described, and if I attempt to continue I get "Oops the
debugger entered an unexpected state" or something similar.

It's all good, I've got a way forward, that means we don't need to try
and get new drivers approved.

Many thanks