ORACLE and RIO10.3.3 Problem with SELECT-Statement

[wiwie]

Hi,
Just compiling and installing the ZEOSLIB 7.2.6 with RIO 10.3.3-Compiler without error messages; only ORACLE access is activated.

LOGIN into the DB is OK, but any SELECT-Statement craches.

It seems that "GetPlainDriver.ClientVersion" in function "TZOracleConnection.GetClientVersion" is the first point that throws the exception.
I am using XE5 with ZEOS-Components on the same machine without problems.

My Environment:
RIO 10.3.3
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production (DB-Login OK)
Oracle Client 9.2 32bit (Login OK)

Thanks for any help
/chris

[Fr0sT]

Never ever post error message, developers should train their Crystal balls

[wiwie]

Hallo FrOsT, ist there any activity, regarding the ticket? Unfortunately I am not allowed to write PNs.

[marsupilami]

Hello Chris,

nothing is going on there because we don't have enough information. What fr0st tried to say: It helps to have a kinda test application. This allows us to debug the problem. Without this we have to do a lot of guesswork on what could cause your problems - which is very time consuming.

Can you supply a table definition, some test data and a small program that demonstrates the problem? Which error message do you get?

Best regards,

Jan

[wiwie]

Hallo ZEOS-Team,
I prepared a small testcase.

In the Dropbox, I put an ORACLE-DMP with one table TKP_ERROR, it contains a single row. ("IDGRUPPEPROFIL NUMBER(10)")

https://www.dropbox.com/sh/yvat84u5tn9z ... lR_Ya?dl=0

I installed the latest Snapshot of the ZEOS-Source.
As I said before, the ORACLE-Version Check fails, so I provided a Mock to continue. Both Versions 11 and 9 fail.

function TZOracleConnection.GetClientVersion: Integer;
var
major_version, minor_version, update_num,
patch_num, port_update_num: sword;
begin
//Wiwie
(*
FPlainDriver.OCIClientVersion(@major_version, @minor_version, @update_num,
@patch_num, @port_update_num);
*)

major_version := 11;
minor_version := 2;
update_num := 0;

(*
major_version := 9;
minor_version := 2;
update_num := 0;
*)

Result := EncodeSQLVersioning(major_version,minor_version,update_num);
end;

The a simple SELECT:

Query.SQL.Add(
'select IDGRUPPEPROFIL FROM TKP_ERROR');
Query.Open;

Fails with ORA 01455 withit the "Open".

Feel free to contact me by mail. I am very interested in solving the issue, because my projects has 950.000 LOC.
wiwie@wiwie.de

Best regards
Chris

[EgonHugeist]

Hello Chris,
looking to your details, i would start from the premisse the LibraryLoader did found a 'oci.dll' on your system which as same bitness but is not a oracle-client OCI.DLL. Track my suggestion by using the LibraryLocation manuallly(add full path and lib-name) if the error went away, check which dll's are located on your system with same name. You may debug it if you set a breakpoint in procedure TZOraclePlainDriver.LoadApi (don't expect line numbers we do not use the deprecated package any more -> use lastest version from SVN if you didn't already)

To be clear: there is NO OCI.dll which does not export the OCIClientVersion function. If it's address is nil then a wrong lib was found and loaded.

[aehimself]

if the error went away, check which dll's are located on your system with same name

I think it would be a nice idea to have a read-only property of ZConnection which would reflect the (full?) path of the library loaded. As far as I can see TZNativeLibraryLoader exports this information as CurrentLocation...

I'll see if I can get that further exported...

[aehimself]

So, in TZAbstractConnection we could do something like...

Property LoadedLibrary: String Read GetLoadedLibrary;

Code: Select all

Function TZAbstractConnection.LoadedLibrary: String;
Begin
 If Not Assigned(FConnection) Or
    Not Assigned(TZAbstractDbcConnection(FConnection)) Or
    Not Assigned(TZAbstractPlainDriver(TZAbstractDbcConnection(FConnection).PlainDriver).Loader) Then Result := ''
   Else Result := GetModuleName(TZAbstractPlainDriver(TZAbstractDbcConnection(FConnection).PlainDriver).Loader.Handle);
End;

Bit of casting, but it works. The only issue is that it does not really handle SysWOW64 well - for me it returns C:\Windows\System32\libmysql.dll for a 32-bit app on a 64-bit system, however the real path is C:\Windows\SysWOW64\libmysql.dll...
I'll have to check this one out further.

[aehimself]

No need for component modification, it actually can be accessed from outside too!

Code: Select all

Uses ZAbstractConnection, System.SysUtils;

Function GetLoadedLibrary(inConnection: TZAbstractConnection): String;
Begin
 If Assigned(inConnection.DbcConnection) And
    Assigned(inConnection.DbcConnection.GetIZPlainDriver) And
    Assigned(inConnection.DbcConnection.GetIZPlainDriver.GetInstance.Loader) Then Result := 'Loaded library: ' + GetModuleName(inConnection.DbcConnection.GetIZPlainDriver.GetInstance.Loader.Handle)
   Else Result := 'There are no loaded libraries at the moment';
End;

You can create a variable for each element (DBCConnection, PlainDriver, Loader) if you wish to make the code easier to read and call the getter methods only once.

[marsupilami]

Hello Chris,

unfortunately it is not the data that we need but source code that demonstrates the problem.

I compiled our test suite with Delphi XE 10.3 Update 3 and Oracle Express 11g. It works and doesn't show any access violations. So I assume that EgonHugeist is correct. Please recheck your environment. Maybe install Oracle and your app on a fresh install of Windows.

Best regards,

Jan

[aehimself]

So, I did some research if OP did not.

Fails with ORA 01455 withit the "Open".

This site says that the full error message is: ORA-01455 converting column overflows integer datatype.

("IDGRUPPEPROFIL NUMBER(10)")

In Oracle, NUMBER(10) means that there can be 10 digits BEFORE the decimal. So it can store numbers from
-9 999 999 999 to 9 999 999 999
Integer's range is close, but not the same:
-2 147 483 648 to 2 147 483 647

So in theory there can be a chance that this error pops up, but it's hard to reproduce without a valid test case. At work we use Oracle 12.1.0 and I installed the Oracle client 19.6.0; latest build of my application is using Zeos 7.3.0-c806f0c9. Everything is working as it should and trust me - we use all kind of data types.

This link says:

The error does not always occur and seems to be random.

Unfortunately Oracle now requires an account to see it's support documents, but here I found an older client-server compatibility matrix, stating that for Oracle 11.2.0 (which OP is using) 11.1.0 and up is currently supported, 9.2.0 and up already requires paid subscription to report bugs (means it might work, but it does have bugs), everything else MIGHT work, but noone cares anymore.

Oracle tends to write really buggy drivers, so my first attempt would be to update the OCI client first to the highest version possible. Especially since test cases do not raise the error OP mentions.

@wiwie - do you think you might be able to do so?

[wiwie]

SOLVED:

Hallo ZEOS-Team,
thank you for your effort. And indeed it was not ZEOS, it was the ORACLE-Installation that caused the problem.

I "only" had to
* >>ADD<< the appropriate CLIENT 11.2.0.1 Installation, (Lesson for me: OCI.dll in the Client- and Server-Installation are different)
* and set the "ZConnection.LibraryLocation"-Property to route to the appropriate OCI.dll
and it works fine.

Again: Thank you for your help!
Chris

[CharlyTango]

Bit of casting, but it works. The only issue is that it does not really handle SysWOW64 well - for me it returns C:\Windows\System32\libmysql.dll for a 32-bit app on a 64-bit system, however the real path is C:\Windows\SysWOW64\libmysql.dll...

I think I can help there and translate
https://www.lazarusforum.de/viewtopic.p ... 95#p130995

The 64bit dll belongs in "Windows\System32" and the 32bit dll in "Windows\SysWOW64".
On a 32Bit-Windows there are 32Bit-dlls in System32 and on a 64Bit-Windows there are 64Bit-dlls in System32. The directory was not renamed when switching to 64Bit, probably for compatibility reasons.
For it the SysWOW64 directory was invented, which then contains the libraries for architecture foreign (speak 32Bit) programs . The "WOW64" stands for "Windows(32Bit program) On Windows64(Bit)".