I'm not great with Linux but I'm able to muddle through. My final goal is to get RODBC working in a shiny app to connect to an oracle 11.1g data base. My first step was to set up the oracle client on the Ubuntu VM.
Ubuntu version details:
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 14.04.4 LTS
Release: 14.04
Codename: trusty
I followed the instructions at:
https://help.ubuntu.com/community/Oracle%20Instant%20Client
To install the client. I also included the odbc package as well. I was able to get sqlplus to connect to the database fairly easily but the RODBC in shiny still wasn't working. When I attempted to use isql in a terminal window it failed. To over come this I installed freeTDS.
sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc
I have configured the following files as so.
--- /etc/odbcinst.ini ---
[Oracle]
Description = Oracle ODBC Connection
Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
--- /etc/odbc.ini ---
[STODS1]
Driver = Oracle
ServerName = STODS1
Port = 1521
Database = STODS1
TDS_Version = 7.1
--- /etc/freetds/freetds.conf ---
[global]
tds version = 7.1
[STODS1]
host = stods1.xxxx.xxxxxxx.com
port = 1521
tds version = 7.1
with this configuration I get the following response from different commands
isql STODS1 USER PASSWORD -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
As can be seen isql works great
However osql fails.
sudo osql -S "STODS1" -U USER -P PASSWORD
checking shared odbc libraries linked to isql for default directories...
strings: '': No such file
trying /tmp/sql ... no
trying /tmp/sql ... no
trying /etc ... OK
checking odbc.ini files
reading /home/oper/.odbc.ini
[STODS1] not found in /home/oper/.odbc.ini
reading /etc/odbc.ini
[STODS1] found in /etc/odbc.ini
found this section:
[STODS1]
Driver = Oracle
ServerName = STODS1
Port = 1521
Database = STODS1
TDS_Version = 7.1
looking for driver for DSN [STODS1] in /etc/odbc.ini
found driver line: " Driver = Oracle"
driver "Oracle" found for [STODS1] in odbc.ini
found driver named "Oracle"
"Oracle" is not an executable file
looking for entry named [Oracle] in /etc/odbcinst.ini
found driver line: " Driver = /usr/lib/oracle/12.1/client64
/lib/libsqora.so.12.1"
found driver /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1 for
[Oracle] in odbcinst.ini
/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1 is an executable file
Using ODBC-Combined strategy
DSN [STODS1] has servername "STODS1" (from /etc/odbc.ini)
cannot read "/home/oper/.freetds.conf"
/etc/freetds/freetds.conf is a readable file
looking for [STODS1] in /etc/freetds/freetds.conf
found this section:
[STODS1]
host = stods1.xxxx.xxxxxx.com
port = 1521
tds version = 7.1
Configuration looks OK. Connection details:
DSN: STODS1
odbc.ini: /etc/odbc.ini
Driver: /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
Server hostname: stods1.xxxx.xxxxxx.com
Address: 10.17.16.44
Attempting connection as ESG_REPORT ...
+ isql STODS1 ESG_REPORT ESG_REPORT1 -v
[08004][unixODBC][Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the
connect identifier specified
[ISQL]ERROR: Could not SQLConnect
sed: can't read /tmp/osql.dump.25185: No such file or directory
I don't understand why osql fails and isql works as it looks like osql uses isql in the last couple of lines.
tsql also fails and from looking through the net and manuals it looks like it's due to the TDS version but i've attempted all the TDS versions I know both in the config files and by forcing it using TDSVER.
tsql -S STODS1 -U USER -P PASWORD
locale is "en_GB.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20017 (severity 9):
Unexpected EOF from the server
OS error 115, "Operation now in progress"
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
I think I've included most of the information but if there is anything else you need then please just let me know. I've been working at this for 5 days now so getting a bit fed up. Any help would be great.
Thanks