Oracle 19 with ODBC

[Victorio]

Hi,
Use somebody ODBC connection in Xbase++ ? I am 3 days working on it , but if finnaly works after several runs cannot connect ODBC driver.
It s unaccountably because I can run several times my test program, and from some monent cannot connect and still error

Code: Select all

= SQL ERROR ===========
Date: 20200510 18:08:52
D:\#POKUSY\#UJD_Zakázka\TESTORACLE\SQLBROW.EXE, Thread: 1
Windows 8 06.02 Build 09200, SQLXpp: 3.3.00, Runtime: 2.00.554
SQLState: IM003,  ErrorCode:160
Specified driver could not be loaded due to system error  1114: A dynamic link library (DLL) initialization routine failed. (Oracle in instantclient_19_6_32bit, C:\#Install\Oracle\instantclient_19_6_32bit\SQORA32.dll).

I installed Oracle Db 19c home1, and ODBC drivers 32bit and also 64 bit, but I use only 32 bit because 64bit not work with Alaska XBase ( i do not know if this is fact or only something wrong).
Also Visual C++ redistributable for 32 bit version and I set system parameters as ORACLE_HOME,TNS_ADMIN, and MRS.

Also in Oracle DB Developer I created database , and also testing table in it.

What can be with this ODBC driver ? when I call it from Xbase++ because when I test user DSB, I can connect to it (Test connection show me Oracle ODBC driver connect window.

Also is this good idea work with Oracle in Xbase++ ?
I have one potential client who need create app only for Oracle db

[unixkd]

Hi

From your error screen, you are using SQLEXPRESS 3.3.00 a product of Boris. This is a great product from all ramifications. I guest your problem is with the driver that you are using and not SQLExoress. I recommend that you download ORACLE driver from http://WWW.DEVART.COM to test your application and if it works for you (which is very likely) then you can purchase their driver at $149.

It is a great idea to work with SQL in Xbase++ using SQLExpress. It simplifies your life. Make sure you study the SQLExpress doc and understanding of SQL language is important if you are to get most out of SQLExpress. There are so many resources out there to jump start you.

Thanks

Joe

[Victorio]

Joe: thanks for advice, I can try devart drivers.

I am using SQL Express++ from Boris several years but with Visual FoxproODBC and SQL Express ODBC, there works. Only with Oracle no . But maybe problem is that I have Oracle DB and also ODBC client on same PC with Windows 10 and i use newest version 19c. (also SQL Express++ I have some time not updated...)
I will try devart driver,

[k-insis]

I use Oracle regulary (sqlexpress too), but there can be either problem with

1) connection string (replace parameters inside with %% with what you have:

DSN=%DSN%;UID=%USER%;PWD=%PASSWORD%;SERVER=%HOST%;

2) Then you should check if by using tnsping you are able to reach instance:

https://docs.oracle.com/cd/E11882_01/ne ... m#NETAG357

3) Check on which port service is running

4) at last if you have service defined you have to specify which one in some cases (like in pyton) like "service=IDB18.oltp"


Oracle is a bit different beast than and can be fussy for connecting to and working with as it has some pecularities but it should not be problem if server and client are on same machine. Just make sure you have Oracle 32bit drivers installed and source configured in 32bit ODBC panel ( C:\windows\syswow64\odbcad32.exe )

If you post here what error you get after connect try it would be helpful to solve problem.

Joe: thanks for advice, I can try devart drivers.

I am using SQL Express++ from Boris several years but with Visual FoxproODBC and SQL Express ODBC, there works. Only with Oracle no . But maybe problem is that I have Oracle DB and also ODBC client on same PC with Windows 10 and i use newest version 19c. (also SQL Express++ I have some time not updated...)
I will try devart driver,

[Victorio]

Hi,
Today I testing it againg.
Actually I have Oracle DB on one PC with Windows 64bit, works all.
On other client PC also W10 64 I have ODBC 32 oracle driver, after include oracle.exe and tnslsnr.exe to firewall on PC with Oracle DB I can create and also test DSN connection, Connection succefully.

To this point is all ok.

With Xbase++ and SQL Express++ I created little program and trying connect to DB, but still error

Code: Select all

= SQL ERROR ===========
Date: 20200525 17:03:54
D:\#POKUSY\#UJD_Zakázka\TESTORACLE\SQLBROW.EXE, Thread: 1
Windows 8 06.02 Build 09200, SQLXpp: 3.3.00, Runtime: 2.00.554
SQLState: IM003,  ErrorCode:160
Specified driver could not be loaded due to system error  1114: A dynamic link library (DLL) initialization routine failed. (Oracle in instantclient_19_6, C:\Oracle\Product\client\instantclient_19_6\SQORA32.dll).

I can put SQLORA32.dll and also SQLORAS32.DLL from c:\Oracle\Product\client\instantclient_19_6\ directory to directory test program , but still this error.

My settings in system :

ORACLE_HOME=C:\Oracle\Product\client\instantclient_19_6
ORACLE_SID=orcl
Path=C:\Oracle\Product\client\instantclient_19_6;C:\Program Files (x86)\Com ..etc
TNS_ADMIN=C:\Oracle\Product\client\instantclient_19_6

I am testing several versions of connection string, but not found right.


Here part of my test program :

Code: Select all

oConn := SQLConnection():new()

*
cString := "Driver={Oracle in instantclient_19_6}"
*cString := "Driver={Oracle in instantclient_19_6};DBQ=ORCL;UID=;PWD=;"
*cString := "Driver={Oracle in instantclient_19_6};DBQ=ORCL;UID=;PWD=;DSN=ORCL;"
*cString := "Driver={Oracle in instantclient_19_6};DBQ=ORCL;UID=;PWD=;SERVER=192.175.0.102;"
*cString := "DSN=ORCL;"
*cString := "DSN=%ORCL%;UID=%SYSTEM%;PWD=%Testerone1+%;SERVER=%192.175.0.102%;"

*oConn:DriverConnect( NIL, @cString,SQL_DRIVER_PROMPT)

* Kontrola či bolo pripojenie vytvorené s oznamom o úspešnosti/neúspešnosti pripojenia
if ! oConn:isConnected
  DC_WinAlert("Pripojenie ku databaze neuspesne !!!!")
  ldestroy:=oConn:destroy()        // toto pridávam pre istotu ak mozno pripojí iba zlé heslo a nejaký čas môže trvať kým vráti hodnotu
  KONIEC()
else
  DC_WinAlert("Pripojenie ku databaze uspesne !")
endif
*

ldestroy:=oConn:destroy()

Return

[k-insis]

Try without % as it was my mistake not to omit them from post.

cString := "DSN=ORCL;UID=SYSTEM;PWD=Testerone1+;SERVER=192.175.0.102;"

Also mind that DSN is defined (either user or machine) data source name in 32bit control panel from C:\windows\syswow64\odbcad32.exe

Try and write on resoult

Hi,
Today I testing it againg.
Actually I have Oracle DB on one PC with Windows 64bit, works all.
On other client PC also W10 64 I have ODBC 32 oracle driver, after include oracle.exe and tnslsnr.exe to firewall on PC with Oracle DB I can create and also test DSN connection, Connection succefully.

To this point is all ok.

With Xbase++ and SQL Express++ I created little program and trying connect to DB, but still error

Code: Select all

= SQL ERROR ===========
Date: 20200525 17:03:54
D:\#POKUSY\#UJD_Zakázka\TESTORACLE\SQLBROW.EXE, Thread: 1
Windows 8 06.02 Build 09200, SQLXpp: 3.3.00, Runtime: 2.00.554
SQLState: IM003,  ErrorCode:160
Specified driver could not be loaded due to system error  1114: A dynamic link library (DLL) initialization routine failed. (Oracle in instantclient_19_6, C:\Oracle\Product\client\instantclient_19_6\SQORA32.dll).

I can put SQLORA32.dll and also SQLORAS32.DLL from c:\Oracle\Product\client\instantclient_19_6\ directory to directory test program , but still this error.

My settings in system :

ORACLE_HOME=C:\Oracle\Product\client\instantclient_19_6
ORACLE_SID=orcl
Path=C:\Oracle\Product\client\instantclient_19_6;C:\Program Files (x86)\Com ..etc
TNS_ADMIN=C:\Oracle\Product\client\instantclient_19_6

I am testing several versions of connection string, but not found right.


Here part of my test program :

Code: Select all

oConn := SQLConnection():new()

*
cString := "Driver={Oracle in instantclient_19_6}"
*cString := "Driver={Oracle in instantclient_19_6};DBQ=ORCL;UID=;PWD=;"
*cString := "Driver={Oracle in instantclient_19_6};DBQ=ORCL;UID=;PWD=;DSN=ORCL;"
*cString := "Driver={Oracle in instantclient_19_6};DBQ=ORCL;UID=;PWD=;SERVER=192.175.0.102;"
*cString := "DSN=ORCL;"
*cString := "DSN=%ORCL%;UID=%SYSTEM%;PWD=%Testerone1+%;SERVER=%192.175.0.102%;"

*oConn:DriverConnect( NIL, @cString,SQL_DRIVER_PROMPT)

* Kontrola či bolo pripojenie vytvorené s oznamom o úspešnosti/neúspešnosti pripojenia
if ! oConn:isConnected
  DC_WinAlert("Pripojenie ku databaze neuspesne !!!!")
  ldestroy:=oConn:destroy()        // toto pridávam pre istotu ak mozno pripojí iba zlé heslo a nejaký čas môže trvať kým vráti hodnotu
  KONIEC()
else
  DC_WinAlert("Pripojenie ku databaze uspesne !")
endif
*

ldestroy:=oConn:destroy()

Return

[Victorio]

Hi , thanks i will try it. Please can you tell me can i use direct driver and no dsn?
Because i need minimize user interaction when installing?

[k-insis]

You will probably not get though without installing driver software package onto client workstation as it

But you can do software registry add of "user data source" either by importing/writing set of registry keys ( .reg file) or by issuing Winapi calls to registry to create one you need. Mind you have to create 32bit ODBC data source.

That can be done with setup package, on app first run or at time when shortcut to .exe is created for user if runtime etc is contained on file server.

Refer to https://www.oracle.com/database/technol ... bc-ic.html for odbc install

Hi , thanks i will try it. Please can you tell me can i use direct driver and no dsn?
Because i need minimize user interaction when installing?

[Victorio]

Hi,
I still get this error :

Code: Select all

= SQL ERROR ===========
Date: 20200527 11:42:41
D:\#POKUSY\#UJD_Zakázka\TESTORACLE\SQLBROW.EXE, Thread: 1
Windows 8 06.02 Build 09200, SQLXpp: 3.3.00, Runtime: 2.00.554
SQLState: IM003,  ErrorCode:160
Specified driver could not be loaded due to system error  1114: A dynamic link library (DLL) initialization routine failed. (Oracle in instantclient_19_6, C:\Oracle\Product\client\instantclient_19_6\SQORA32.dll).

Thread ID 1
Called from SQLCONNECTION:DRIVERCONNECT(672)
Called from MAIN(88)

However this files are on its place also in registry I looked , and also I can test connection from Oracle ODBC driver configuration.
and from Xbase++ / SQLExpress++ not work.
Also I have VC++redistributable 32 version installed.

Also I do not understand, why not accept DSN=ORCL in connection string, I get always user interaction to select data source however DNS is in connection string. ?

cString := "DSN=ORCL;UID=SYSTEM;PWD=Testerora1+;SERVER=192.175.0.102;"
oConn:DriverConnect( NIL, @cString,SQL_DRIVER_PROMPT)

[k-insis]

Did you install "VC++ 2010" x86 (32b) ?

It seems that there is a snafu for some users between 32b driver - windows 10 - VC++ runtime and PATH

https://community.oracle.com/thread/3938283

Try to compile link and install on 'stock' Windows 10 machine or VM,

Hi,
I still get this error :

Code: Select all

= SQL ERROR ===========
Date: 20200527 11:42:41
D:\#POKUSY\#UJD_Zakázka\TESTORACLE\SQLBROW.EXE, Thread: 1
Windows 8 06.02 Build 09200, SQLXpp: 3.3.00, Runtime: 2.00.554
SQLState: IM003,  ErrorCode:160
Specified driver could not be loaded due to system error  1114: A dynamic link library (DLL) initialization routine failed. (Oracle in instantclient_19_6, C:\Oracle\Product\client\instantclient_19_6\SQORA32.dll).

Thread ID 1
Called from SQLCONNECTION:DRIVERCONNECT(672)
Called from MAIN(88)

However this files are on its place also in registry I looked , and also I can test connection from Oracle ODBC driver configuration.
and from Xbase++ / SQLExpress++ not work.
Also I have VC++redistributable 32 version installed.

Also I do not understand, why not accept DSN=ORCL in connection string, I get always user interaction to select data source however DNS is in connection string. ?

cString := "DSN=ORCL;UID=SYSTEM;PWD=Testerora1+;SERVER=192.175.0.102;"
oConn:DriverConnect( NIL, @cString,SQL_DRIVER_PROMPT)