Bookstore

Recent

Author Topic: Issue connecting to Oracle when TNS is defined via LDAP (Solved)  (Read 522 times)

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
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]
  1.   FConnection := TZConnection.Create(Application.MainForm);
  2.   FConnection.Protocol := 'oracle';  
  3.  
  4.   FConnection.Database := FDatabaseName;  // TNS name
  5.   FConnection.User := FSchema;  // Username
  6.   FConnection.Password := FPassword;
  7.  
  8.   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
« Last Edit: May 30, 2019, 08:49:26 am by Mike.Cornflake »
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: Issue connecting to Oracle when TNS is defined via LDAP
« Reply #1 on: May 30, 2019, 08:46:56 am »
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=THEHOST)(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.
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

engkin

  • Hero Member
  • *****
  • Posts: 2513
Re: Issue connecting to Oracle when TNS is defined via LDAP (Solved)
« Reply #2 on: May 30, 2019, 09:03:35 am »
Catch the exception and see its details. Might give some hint. Also, try/finally is a good practice.

engkin

  • Hero Member
  • *****
  • Posts: 2513
Re: Issue connecting to Oracle when TNS is defined via LDAP (Solved)
« Reply #3 on: May 30, 2019, 09:29:20 am »
On Python, is it using ODBC as well? If no, then probably the ODBC driver needs to be updated?

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: Issue connecting to Oracle when TNS is defined via LDAP (Solved)
« Reply #4 on: May 31, 2019, 01:45:34 am »
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
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines