From my OpenEdge 11.5.1 WebSpeed application I'm trying to use the ODBC Bridge to connect to a ORACLE database (via Oracle's ODBC Drivers) and I'm not having much luck. The point as which the code does a SQLConnect returns -1.
I've had some success under Windows 7 as proof-of-concept but I needed it working under Linux..
I can confirm that Oracle's SQLPlus and unixODBC's isql command are configured & working.
I've followed the instructions as per the PDF documentation but I just not sure what is going on.
I know the ODBC Bridge was intended to work with DataDirect Cloud and Progress Easly, but I see no reason why it can't work with other ODBC drivers i.e Oracle.
Reference:
Details:
Which version of this are you using? There is a comment from Peter Judge at the bottom of the page that states:
This sample works with OE 11.3, but not OE 11.4+. An updated version that works with 11.4 is at community.progress.com/.../2331.abl-odbc-sample.aspx
I obtained the latest code from github.com/.../abl_odbc_api
I made one amendment and that was to change the path for the shared object (libodbc.so) for Linux 64bit and recompiled.
Are you running 64 bit OpenEdge?
Yes. OpenEdge/Webspeed 11.5.1 64bit.
Quick Update:
I've updated to the latest version of unixODBC 2.3.2 and that installed with no issues.
Under my WebSpeed Broker configuration I have set the following environment variables (ubroker.properties):
TNS_ADMIN=/etc/oracle
ORACLE_HOME=/usr/lib/oracle/12.1/client64
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
ODBCSYSINI=/etc
ODBCINI=/etc/odbc.ini
Rebooted the server. Still having issue. Google is not returning any thing useful.
Quick Update:
I've updated to the latest version of unixODBC 2.3.2 and that installed with no issues.
Under my WebSpeed Broker configuration I have set the following environment variables (ubroker.properties):
TNS_ADMIN=/etc/oracle
ORACLE_HOME=/usr/lib/oracle/12.1/client64
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
ODBCSYSINI=/etc
ODBCINI=/etc/odbc.ini
Rebooted the server. Still having issue. Google is not returning any thing useful.
If you check those environment variables from within webspeed are they set correctly? What about the PATH?
If you set the environment values in a shell and kick off a CHUI screen does the odbc work?
Where art thou Peter Judge? :-)
From my very, very basic proof-of-concept WebSpeed HTML page I can see the environment variables have been set and can be retrieved using the OS-GETENV function.
Surprisingly is does work when using the TTY OpenEdge Procedure Editor (sort of). I'm running the same procedure as did the WebSpeed agents and with the same environment variables.
The code can only be executed once from the Procedure Editor because the way the editor does some sort of garbage collection*. So the code can't be executed again until you exit the procedure editor and go back in again.
It also works without errors when you run the code from the command using the -p startup parameter and batch mode (bpro).
The procedure I'm using currently only does the SQLConnect process and yet to do any actual SQL execute statement.
So the question seams to be why does it not work from WebSpeed?
Also when I do run the code in batch mode (or not ) the QUIT statement does not return back to the OS shell. I'm left with just a blank screen and CTRL+C is not doing anything. It's like _progres is in limbo. I then have to issue a kill -HUP command the stop the progress process. Humm.
Is this possibly a permission issue? Is it the same user running the Webspeed broker as when you run the procedure editor? _progres is the same exe as the Webspeed agent, so this suggests it is the environment in which it is running.
User Permission might be the cause.... The WebSpeed Broker runs under 'progress' user and my testing from the command line was under root. If I test the code by 'su progress' first then I try to execute the code I get the -1 error. So what needs to have it's permission changed? Is it unixODBC, Oracle ODBC ?
I don't know enough about ODBC on unix. It might be an issue with loading libraries. IIRC, running a setuid exe owned by root (which is typically how OE is installed) ignores LD_LIBRARY_PATH when loading libraries, which confuses people... so the problem you seem to be seeing is the inverse of what I normally deal with. Truss/strace might give you some insight here, indicating whether the problem is a failure to load a library, or loading an incorrect library.
okay, I did an strace between the root user and the progress user and the results are different. The root user is obviously working correctly and 'progress' user is failing and is using different paths for the library. Now would it help if I loaded the two strace files?
You could look at the strace files in the context of the LD_LIBRARY_PATH. I would guess you would see attempts to load the required .so from various places in the LD_LIBRARY_PATH, and none of these would be the places where it actually is. Or maybe there is a permission issue loading the .so. You could attach the logs for people to look at, in case it rings a bell for someone.
To get dedicated Progress developer time on it, the correct process would be to log a call with Tech Support. I could spend a few minutes reviewing them for the thrill of it, but I have other priorities I need to address (e.g. calls from Tech Support).