How to connect to Oracle without TnsNames.ora?

[aehimself]

We all know the regular way to connect to an Oracle instance: Have your TnsNames.ora file, set your TNS_ADMIN environmental variable, put the connection descriptor in the TnsNames.Ora file and set ZConnection.Database to the descriptor name. But, can we connect to an Oracle database leaving all the TnsNames.ora bullshit behind?

As it turns out, yes, we can.

An average descriptor in TnsNames.ora will look something like this:

Code: Select all

DescriptorName = (
  DESCRIPTION =
  (
    ADDRESS = (PROTOCOL = TCP) (HOST = server.host.lan) (PORT = 1521)
  )
  (
    CONNECT_DATA =
      (SERVICE_NAME = DBNAME)
  )
)

The format is quite strict but removing (specific) padding and line breaks are allowed. So the above is - in the eye of oci.dll - equal to:

Code: Select all

DescriptorName=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.host.lan)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME=DBNAME)))

As we don't really need a descriptor name (it is only needed to identify each entry in the file anyway) feel free to leave it out:

Code: Select all

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.host.lan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBNAME)))

Now, we have a oneliner what we can insert into ZConnection.Database property. Set everything else right and you should be able to establish the connection!
I'm only writing this "guide" because I remembered reading something similar but never got any confirmation.

I wrote a TnsNames.ora parser for... reasons, which handles each descriptor as a separate class and can be serialized to a TnsNames.ora accepted string format. This class can be used to build your connection string on the fly, without care of the actual format itself.

I'll publish it once the final refinements are completed.

Now you know. The curse of TnsNames.ora nightmare can finally be lifted.
Happy hardcoding!

[aehimself]

ZConnection setup:

NoTnsNamesOra #1.png

TestApp code:

NoTnsNamesOra #2.png

Result:

NoTnsNamesOra #3.png

[MJFShark]

That's been possible since way back. Alternately you can use the EZConnect syntax: //server/sid (it can be more complex than that.)

https://docs.oracle.com/en/database/ora ... F9F79A7504

-Mark

[aehimself]

Nice!

I knew it is possible somehoe, I just had no idea Zeos is capable of doing that too until now.

[MJFShark]

I actually don't mind tnsnames.ora. I think I need to use it currently since I use the Oracle Wallet thing. However I find that a lot of issues occur if you don't use TNS_ADMIN to force its location. You can do that with an environment var set before the client load or you can set it in the registry.

btw. I've been looking into that 1406 error... some interesting stuff and some head scratching stuff lol. Did you know you can diagnose 1406 using the indicator variables? I just learned that and am hoping it adds some insight. The thing is that the individual define sizes are correct (actually way too big for utf-16, and so that doesn't seem to be the actual problem.) I've written my own "clean room" version of the statement handler and that has no problems with the same table and UTF16. More to come...

-Mark

[aehimself]

My huge issue in the UTF16 error (and the AL32UTF8 64-bit AV too) is my current debugging and this deep Zeos knowledge doesn't seem to be sufficient. I did play around in both and however I did achieve an effect - I did not find the root cause and therefore could not fix them myself.

I was hoping for an easy buffer overrun or type mismatch

Keep in mind that there was a fix in UTF16 for TZCachedResultSet as there were some encoding malformations a while ago, look for commit

584814045dcd7d80c165ef2e7d618285a9d839e0
By AEHimself: Let's see what Jenkins will say about this. Fix for TZCachedResultSet to function using UTF16.
marsupilami79 on 2/13/2022, 9:19:56 AM

Maybe they are related somehow.

[MJFShark]

My huge issue in the UTF16 error (and the AL32UTF8 64-bit AV too) is my current debugging and this deep Zeos knowledge doesn't seem to be sufficient. I did play around in both and however I did achieve an effect - I did not find the root cause and therefore could not fix them myself.

I'm looking at the UTF16 and ORA-01406 error. I'm not sure I know about the AL32UTF8 error you mention.

I was hoping for an easy buffer overrun or type mismatch

Agreed 100%. I feel like I checked all the obvious possibilities. Right now, I'm tracking down stuff related to ConSettings (which gets oddly messed up when UTF16 is used, just look at GetRawCharacterSet which changes it based on the Oracle NLS_CHARACTERSET setting and then partially changes the clientcodeset settings but the ResetCurrentClientCodePage procedure does some odd things too like retaining the codepage name while changing all the other values.) Also the CharWidth part of clientcodepage is changed in TZOracleConnection.Open, but without changing anything else. It's rather dizzying.

Keep in mind that there was a fix in UTF16 for TZCachedResultSet as there were some encoding malformations a while ago

I'm not sure it's related but I do have that change.

[MJFShark]

Nothing I did seemed to help until I replaced the current FRowsBuffer with my own buffers that I'm storing in each TZSQLVar. I have it working perfectly (so far lol) with UTF16. I'm not quite sure what this means, but whatever is causing the issue appears to happen in procedure TZOracleResultSet.Open and appears to relate to how it stores the data all in one big tbytes array with pointers into it.

-Mark

[marsupilami]

I am sorry to resurrect this thread but I wonder why we don't add a possibility for Zeos to internally create a correct connection string? Something like:

Code: Select all

  if HostName <> '' then begin
    ConnStr := '//' + HostName;
    if Port <> 0 then
      ConnStr := ConnStr + ':' + IntToStr(Port);
    if Database <> '' then
      ConnStr := ConnStr + '/' + Database;
  end else
    ConnStr := Database;

This would make the handling of Oracle more like we handle other databases too?

I think about this beacause I think about using Oracle Instant Client to be able to automatically test different client versions or to be able to have different client version on one system side by side, so I can test different server versions with their respective clients.

Opinions?

[MJFShark]

Looks good to me!

-Mark

[aehimself]

I am sorry to resurrect this thread but I wonder why we don't add a possibility for Zeos to internally create a correct connection string? Something like:

Code: Select all

  if HostName <> '' then begin
    ConnStr := '//' + HostName;
    if Port <> 0 then
      ConnStr := ConnStr + ':' + IntToStr(Port);
    if Database <> '' then
      ConnStr := ConnStr + '/' + Database;
  end else
    ConnStr := Database;

This would make the handling of Oracle more like we handle other databases too?

I think about this beacause I think about using Oracle Instant Client to be able to automatically test different client versions or to be able to have different client version on one system side by side, so I can test different server versions with their respective clients.

Opinions?

Sorry for resurrecting this thread lol... this would make a LOT of sense, but... since we are storing the TnsNames identifier in the Database property it would be hard to impossible to determine whether we need the EasyConnect syntax or we just let oci to try to resolve it.
Yes, we can say if the host name is filled we PROBABLY want the easyconnect... but at the moment I'm filling all properties of the TZConnection in my app so error logging shows everything properly and I might not be alone with this.
Add an option to TZConnection maybe?

[marsupilami]

Sorry for resurrecting this thread lol... this would make a LOT of sense, but... since we are storing the TnsNames identifier in the Database property it would be hard to impossible to determine whether we need the EasyConnect syntax or we just let oci to try to resolve it.
Yes, we can say if the host name is filled we PROBABLY want the easyconnect... but at the moment I'm filling all properties of the TZConnection in my app so error logging shows everything properly and I might not be alone with this.
Add an option to TZConnection maybe?

Hmm - it feels right to add a aparameter like "DisableEasyConnect" for your use case, because I feel it is counterintuitive for users to explicitly enable Zeos to use easy connect syntax?

And then I start to wonder wether this would be a breaking change: I want to try to use semantic versioning with Zeos. If we introduce something breaking we will have to up the major version number with this versioning scheme.
But is it a breaking change to introduce this? From a Zeos perspective the behavior is undefined with a filled in server name? But then it worked in the past which still makes it an at least unexpected change in behavior for Zeos users...

[aehimself]

because I feel it is counterintuitive for users to explicitly enable Zeos to use easy connect syntax?

You are absolutely right. If we find a way to reliably make it automatic it makes sense to be on by default.

But is it a breaking change to introduce this? From a Zeos perspective the behavior is undefined with a filled in server name?

Afaik server name did not / does not matter at the moment for Zeos if Oracle protocol is selected as it relied on oci.dll to resolve the endpoint with TnsNames.ora. I can make some tests to put some garbage in and try to connect.

But then it worked in the past which still makes it an at least unexpected change in behavior for Zeos users...

If it did not affect functionality the usage was purely cosmetic we can almost certainly presume. I only used it to make the status / error messages uniform in my program. We can implement an option for TZConnection to disable this parsing as you suggested but that indeed COULD be a breaking change.

I'd suggest a conditional parsing: we ONLY assemble and use EasyConnect strings internally IF server name is specified and database does not seem to be one already (database contains at least 3 forward slashes and an optional colon. As Tns entries can not have special characters in their names, in theory we simply could check if the starting character is a forward slash or not...)
This way previous codes would continue to function while new ones could already take advantage of the new feature.
In Zeos 9 we can disable the parsing altogether and simply say if server is specified, always assemble EasyConnect...