Can someone guide through the steps to connect Oracle database from the Clarion 11 application? I went through the DatabaseDrivers.pdf which is available under reference docs but no luck so far. I see the error message ‘Oracle could not be found(1)’ in all the combination available in help docs.
Hope you have Oracle installed
Obviously. I have oracle express 11 installed.
Have you installed the 32 bit ODBC driver?
I installed Oracle 11 express 64 bit (and have 64 bit ODBC driver). Do you mean I need 32 bit version of it? I’m under impression that Clarion 11 works with 64 bit.
As Clarion generates 32 bit applications then yes, you will need the 32 bit driver.
Do I need oracle 32 bit server if I want to connect using Oracle driver instead of ODBC? I believe yes.
You can use a 64bit OS just like you can with the clarion ide and any clarion generated applications.
So the only thing to think about is that because your applications are 32bit, then they require 32bit ODBC driver to access the database.
32bit ODBC drivers can access a 64bit SQL Server, be that Oracle, MSSQL etc.
So to summarise:
Clarion is a programming language that produces 32-bit applications. However, it’s possible for Clarion-generated applications to access and interact with 64-bit SQL Server databases on a 64-bit Windows system. This is made possible through a compatibility layer called WOW64, which allows 32-bit applications to run on a 64-bit Windows environment.
To connect to a 64-bit SQL Server database, Clarion applications can use a 32-bit ODBC (Open Database Connectivity) driver. This driver, even though it’s 32-bit, can communicate with the 64-bit SQL Server through the WOW64 layer, ensuring that Clarion applications can access the database seamlessly.
In summary, Clarion’s 32-bit applications can work with 64-bit SQL Server databases on a 64-bit Windows system thanks to the compatibility provided by WOW64 and the use of a 32-bit ODBC driver for database connections.
Yes, I got it.
I want to make oracle connection through clarion oracle driver instead of ODBC. Like when I try to import table, I see ODBC as well as Oracle under “Select Server” dropdown.
My information is incorrect, see response by @JonW further down
I think I am correct in the following:
I think that SoftVelocity used the term “Driver” in a way that is confusing here. In this context, when they refer to “Driver,” they do not mean a standalone driver as we commonly understand it. Instead, it they are offering an enhancement or optimization layer (the accelerator drivers) on top of their ODBC (Open Database Connectivity) driver.
To avoid confusion, it’s essential to recognize that, while they use the term “Driver,” what they are providing is not be a complete, independent driver. Rather, it’s an additional layer designed to improve the performance and functionality of their ODBC driver. So, in practical terms, you’ll still need the appropriate ODBC driver for your specific data server, like Oracle, along with the accelerator drivers if you choose to use them.
With Oracle there is an ODBC accelerator, and then there’s the ‘Oracle’ driver which uses OCI, the Oracle Call Interface which requires you to install OCI.DLL (and other stuff) on client PCs
There’s a section in Clarion’s main help on it, but like most of the Clarion Help it’s hopelessly out of date.
Your best bet would be to subscribe to the Clarion newsgroups and place a post in the Softvelocity.clarion.databasedrivers group and get advice from other Oracle uses like Jon Waterhouse
I’m having trouble connecting through ODBC. How the ‘DriverOptions’ and ‘OwnerName’ looks like for this connection?
From one of Jon Waterhouse’s posts on the DatabaseDriver newsgroup
Just because you will probably need it: I generally use an owner string for accessing oracle tables, and have a little window that displays the user_name (normally pre-filled), the user enters the password, and the TakeCloseEvent method has:
GLO:connectstring = clip(user_name) & ‘/’ & clip(password_entered) & ‘@10.35.4.1:1521/research’
where 10.35.4.1 is the IP address of the server, 1521 is the port (that’s the default port for oracle), and research is the SID of the database.
This page might be useful for you Oracle connection strings - ConnectionStrings.com
When I was connecting to a government oracle db, it took every shred of patience that I had. If they have a sysadmin you could ask, it might save you a lot of time. You may or may not need tsnames.ora and other config files.
NB: the above is when using the ‘Oracle’ driver.
I have XE (Express edition) on my home computer, my owner name for an oracle connection looks like:
That uses the instant client, and after copying the files you just need to make sure that the directory you copied to is in the Path environment variable
the same connection with the ODBC driver looks like:
where ojtest (the bit before the comma above), is a 32-bit ODBC User DSN set up using the 32bit Oracle instant client and 32bit ODBC driver. The driver shows as “Oracle in ora32”, which is the name of the 32-bit ODBC driver from Oracle that I installed.
The ODBC version will prompt for a password, the OCI version will not.
Not correct on this. The ODBC driver from Clarion connects to the ODBC (or other) driver that actually talks to your database. The Clarion version translates Clarion like SET(view) into SQL queries that it passes to the other side’s ODBC driver with instructions on where to put the information it returns. The MSSQL driver is a tweaked ODBC driver that sends a some commands via ODBC that no other backend would understand, with specific calls to things like sp_serverprepare etc.
The Oracle driver is not an ODBC driver at all. It talks directly to the OCI (Oracle Call Interface) on the client machine, which then talks directly to the server.
So there are only ever two layers to worry about: the Clarion “driver”, which translates Clarion data manipulation (SET/NEXT etc) into something that is understood by (or passed on by) the database’s driver. However, that driver on the other side might be two layers. For example, for Oracle if you are using ODBC, Clarion will talk to the Oracle ODBC layer, and the Oracle ODBC layer will translate stuff and send it to OCI which then talks to the database. If all you have on your client is the Oracle odbc, but no client (OCI .DLL and related files), you won’t get anywhere – you’ll get an “Oracle not found” error.
@JonW, thank you for your response.
I have marked my response as incorrect.
Yes. You can use the 64bit server, but need a 32bit client for 32bit Clarion to communicate with!
To add: i have always used the ODAC client, never the ODBC driver.
I just did a quick browse through this thread. I gather that you want to use the Oracle driver rather than the ODBC (wise choice). You need the 32-bit instant client installed on your computer. It doesn’t matter if you are using a 32 or 64 bit OS. You need to create an environmental system variable called TNS_ADMIN with a pass of the full path to the instant client. Mine is ‘C:\oracle\product\10.2.0\db_1\network\ADMIN’. There are three files of interest in that directory: listerner.ora, sqlnet.ora, and tnsnames.ora. (You can find information about how to configure these on the Oracle website.)