1. Install UnixODBC package
As root:
# pkgadd -d unixodbcpkg
The following packages are available:
1 unixODBC unixODBC
(sparc) 2.1.1
Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]: 1
Processing package instance <unixODBC> from </export/bulk/unixodbcpkg>
unixODBC
(sparc) 2.1.1
http://www.unixodbc.org
Using </usr/local> as the package base directory.
## Processing package information.
## Processing system information.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
The following files are already installed on the system and are being
used by another package:
/usr/local/bin <attribute change only>
/usr/local/include <attribute change only>
/usr/local/lib <attribute change only>
Do you want to install these conflicting files [y,n,?,q] y
## Checking for setuid/setgid programs.
Installing unixODBC as <unixODBC>
## Installing part 1 of 1.
/usr/local/bin/dltest
/usr/local/bin/isql
/usr/local/bin/odbcinst
/usr/local/etc/odbc.ini
/usr/local/etc/odbcinst.ini
/usr/local/include/autotest.h
/usr/local/include/odbcinst.h
/usr/local/include/odbcinstext.h
/usr/local/include/sql.h
/usr/local/include/sqlext.h
/usr/local/include/sqltypes.h
/usr/local/include/sqlucode.h
/usr/local/include/uodbc_stats.h
/usr/local/lib/libboundparam.la
/usr/local/lib/libboundparam.so <symbolic link>
/usr/local/lib/libboundparam.so.1 <symbolic link>
/usr/local/lib/libboundparam.so.1.0.0
/usr/local/lib/libesoobS.la
/usr/local/lib/libesoobS.so <symbolic link>
/usr/local/lib/libesoobS.so.1 <symbolic link>
/usr/local/lib/libesoobS.so.1.0.0
/usr/local/lib/libgtrtst.la
/usr/local/lib/libgtrtst.so <symbolic link>
/usr/local/lib/libgtrtst.so.1 <symbolic link>
/usr/local/lib/libgtrtst.so.1.0.0
/usr/local/lib/libnn.la
/usr/local/lib/libnn.so <symbolic link>
/usr/local/lib/libnn.so.1 <symbolic link>
/usr/local/lib/libnn.so.1.0.0
/usr/local/lib/libodbc.la
/usr/local/lib/libodbc.so <symbolic link>
/usr/local/lib/libodbc.so.1 <symbolic link>
/usr/local/lib/libodbc.so.1.0.0
/usr/local/lib/libodbccr.la
/usr/local/lib/libodbccr.so <symbolic link>
/usr/local/lib/libodbccr.so.1 <symbolic link>
/usr/local/lib/libodbccr.so.1.0.0
/usr/local/lib/libodbcdrvcfg1S.la
/usr/local/lib/libodbcdrvcfg1S.so <symbolic link>
/usr/local/lib/libodbcdrvcfg1S.so.1 <symbolic link>
/usr/local/lib/libodbcdrvcfg1S.so.1.0.0
/usr/local/lib/libodbcdrvcfg2S.la
/usr/local/lib/libodbcdrvcfg2S.so <symbolic link>
/usr/local/lib/libodbcdrvcfg2S.so.1 <symbolic link>
/usr/local/lib/libodbcdrvcfg2S.so.1.0.0
/usr/local/lib/libodbcextras.la
/usr/local/lib/libodbcextras.so <symbolic link>
/usr/local/lib/libodbcextras.so.1 <symbolic link>
/usr/local/lib/libodbcextras.so.1.0.0
/usr/local/lib/libodbcinst.la
/usr/local/lib/libodbcinst.so <symbolic link>
/usr/local/lib/libodbcinst.so.1 <symbolic link>
/usr/local/lib/libodbcinst.so.1.0.0
/usr/local/lib/libodbcminiS.la
/usr/local/lib/libodbcminiS.so <symbolic link>
/usr/local/lib/libodbcminiS.so.1 <symbolic link>
/usr/local/lib/libodbcminiS.so.1.0.0
/usr/local/lib/libodbcmyS.la
/usr/local/lib/libodbcmyS.so <symbolic link>
/usr/local/lib/libodbcmyS.so.1 <symbolic link>
/usr/local/lib/libodbcmyS.so.1.0.0
/usr/local/lib/libodbcnnS.la
/usr/local/lib/libodbcnnS.so <symbolic link>
/usr/local/lib/libodbcnnS.so.1 <symbolic link>
/usr/local/lib/libodbcnnS.so.1.0.0
/usr/local/lib/libodbcpsql.la
/usr/local/lib/libodbcpsql.so <symbolic link>
/usr/local/lib/libodbcpsql.so.1 <symbolic link>
/usr/local/lib/libodbcpsql.so.1.0.0
/usr/local/lib/libodbcpsql.so.2 <symbolic link>
/usr/local/lib/libodbcpsql.so.2.0.0
/usr/local/lib/libodbcpsqlS.la
/usr/local/lib/libodbcpsqlS.so <symbolic link>
/usr/local/lib/libodbcpsqlS.so.1 <symbolic link>
/usr/local/lib/libodbcpsqlS.so.1.0.0
/usr/local/lib/libodbctxt.la
/usr/local/lib/libodbctxt.so <symbolic link>
/usr/local/lib/libodbctxt.so.1 <symbolic link>
/usr/local/lib/libodbctxt.so.1.0.0
/usr/local/lib/libodbctxtS.la
/usr/local/lib/libodbctxtS.so <symbolic link>
/usr/local/lib/libodbctxtS.so.1 <symbolic link>
/usr/local/lib/libodbctxtS.so.1.0.0
/usr/local/lib/liboplodbcS.la
/usr/local/lib/liboplodbcS.so <symbolic link>
/usr/local/lib/liboplodbcS.so.1 <symbolic link>
/usr/local/lib/liboplodbcS.so.1.0.0
/usr/local/lib/liboraodbcS.la
/usr/local/lib/liboraodbcS.so <symbolic link>
/usr/local/lib/liboraodbcS.so.1 <symbolic link>
/usr/local/lib/liboraodbcS.so.1.0.0
/usr/local/lib/libsapdbS.la
/usr/local/lib/libsapdbS.so <symbolic link>
/usr/local/lib/libsapdbS.so.1 <symbolic link>
/usr/local/lib/libsapdbS.so.1.0.0
/usr/local/lib/libtdsS.la
/usr/local/lib/libtdsS.so <symbolic link>
/usr/local/lib/libtdsS.so.1 <symbolic link>
/usr/local/lib/libtdsS.so.1.0.0
/usr/local/lib/libtemplate.la
/usr/local/lib/libtemplate.so <symbolic link>
/usr/local/lib/libtemplate.so.1 <symbolic link>
/usr/local/lib/libtemplate.so.1.0.0
[ verifying class <none> ]
Installation of <unixODBC> was successful.
2. Start PosgreSQL database.
# su - postgres
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
$ ./start
postmaster starting
(I assume you may have your own script to start PostgreSQL).
3. Search for defined databases using psql utility
$ psql -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
balls | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)
4. Use psql program to check the "balls" database.
$ psql balls
balls=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------
public | coffees | table | carlos
public | table1 | table | carlos
(2 rows)
# \dt command: displays tables
5. List "coffees" table records.
balls=> select * from coffees;
cof_name | sup_id | price | sales | total
--------------------+--------+-------+--------+-------
Espresso | 150 | 9.99 | 0 | 0
Colombian_Decaf | 101 | 8.99 | 0 | 0
French_Roast_Decaf | 49 | 9.99 | 0 | 0
(3 rows)
\q
# \q command: exit from psql
6. Install OTL in the same directory where unixODBC headers are located.
# mv otlv4.h /usr/local/include
# cd /usr/local/include
# ls -la
total 2872
drwxr-xr-x 3 bin bin 512 Jan 30 07:08 .
drwxr-xr-x 14 root other 512 Jan 28 19:27 ..
-r--r--r-- 1 root bin 3173 May 20 2004 align.h
-rw-r--r-- 1 root bin 10540 Dec 28 2003 ansidecl.h
-rw-r--r-- 1 bin bin 5511 Jan 17 2002 autotest.h
-r--r--r-- 1 root bin 802 May 20 2004 avoffset.h
-rw-r--r-- 1 root bin 149866 Dec 28 2003 bfd.h
-rw-r--r-- 1 root bin 26046 Dec 28 2003 bfdlink.h
drwxr-xr-x 3 root bin 512 May 22 2005 c++
-rw-r--r-- 1 root bin 15226 Dec 28 2003 dis-asm.h
-rw-r--r-- 1 root bin 4294 Jan 13 2005 iconv.h
-rw-r--r-- 1 root bin 1385 Jan 13 2005 libcharset.h
-rw-r--r-- 1 bin bin 8595 Jan 17 2002 odbcinst.h
-rw-r--r-- 1 bin bin 8097 Jan 17 2002 odbcinstext.h
-rw-r--r-- 1 root bin 634838 Jan 28 18:59 otlv4.h
-rw-r--r-- 1 bin bin 28431 Jan 17 2002 sql.h
-rw-r--r-- 1 bin bin 77915 Jan 17 2002 sqlext.h
-rw-r--r-- 1 bin bin 10154 Jan 17 2002 sqltypes.h
-rw-r--r-- 1 bin bin 21660 Jan 17 2002 sqlucode.h
-rw-r--r-- 1 root bin 1897 Dec 28 2003 symcat.h
-rw-r--r-- 1 root bin 83481 Mar 27 2005 tcl.h
-rw-r--r-- 1 root bin 158656 Mar 27 2005 tclDecls.h
-rw-r--r-- 1 root bin 6266 Mar 27 2005 tclPlatDecls.h
-rw-r--r-- 1 root bin 57195 Mar 28 2005 tk.h
-rw-r--r-- 1 root bin 90179 Mar 28 2005 tkDecls.h
-rw-r--r-- 1 root bin 10202 Mar 28 2005 tkPlatDecls.h
-rw-r--r-- 1 bin bin 2591 Jan 17 2002 uodbc_stats.h
7. Here is the ODBC data source configuration for user "carlos"
# su - carlos
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
.profile loaded
$ cat .odbc.ini
[balls-dsn]
Description = Test to Postgres - balls database
Driver = PostgreSQL
Trace = Yes
TraceFile = /tmp/otl2sql.log
Database = balls
Servername = localhost
UserName =
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = Yes
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
Note the DSN is named "balls-dsn" since it will be used as a binding in the test program.
The value in the Driver attribute corresponds to the PostgreSQL section in /usr/local/odbcinst.ini
8. Here is the (system wide) PostgreSQL driver configuration.
$ su -
Password:
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
# cd /usr/local/etc
# cat odbcinst.ini
[PostgreSQL]
Description = PostgreSQL driver for Unix
Driver = /usr/local/lib/libodbcpsql.so
Setup = /usr/local/lib/libodbcpsqlS.so
Threading = 2
FileUsage = 1
9. Check C++ availabliity and version.
$ g++ --version
g++ (GCC) 3.4.2
$ pwd
/export/users/carlos/otl/article
10. The sample number 300 from the OTL Home Page samples list is compiled, then linked in a second step.
$ g++ -c ex300_odbc.cpp -o test.o -I/usr/local/include
$ g++ -o test test.o -L/usr/local/lib -lodbc
$ ls -l
total 854
-rw-r--r-- 1 carlos root 2351 Jan 30 08:40 ex300_odbc.cpp
-rwxr-xr-x 1 carlos root 193276 Jan 30 08:42 test
-rw-r--r-- 1 carlos root 214284 Jan 30 08:42 test.o
Note: use libodbc.la to link against unixODBC.
11. Verify shared libraries dependencies.
$ ldd test
libodbc.so.1 => /usr/local/lib/libodbc.so.1
libstdc++.so.6 => /usr/local/lib/libstdc++.so.6
libm.so.1 => /usr/lib/libm.so.1
libgcc_s.so.1 => /usr/local/lib/libgcc_s.so.1
libc.so.1 => /usr/lib/libc.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libthread.so.1 => /usr/lib/libthread.so.1
librt.so.1 => /usr/lib/librt.so.1
libaio.so.1 => /usr/lib/libaio.so.1
libmd5.so.1 => /usr/lib/libmd5.so.1
12. Verify LD_LIBARY_PATH contains /usr/lib and /usr/local/lib
$ echo $LD_LIBRARY_PATH
/usr/local/lib:/usr/lib:/usr/sfw/lib
13. Run test program.
$ ./test
f1=8, f2=Name8
f1=9, f2=Name9
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=10, f2=Name changed
f1=11, f2=NULL
14. Stop PostgreSQL database.
$ su -
Password:
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
# su - postgres
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
$ ./stop
waiting for postmaster to shut down.... done
postmaster stopped
15. Run test with no database.
$ ./test
[unixODBC]Could not connect to the server;
Could not connect to remote socket.
Test program sample code (note my changes made in boldface)
$ cat ex300_odbc.cpp
#include <iostream>
using namespace std;
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#define OTL_ODBC_POSTGRESQL
#define OTL_ODBC_UNIX
#include <otlv4.h> // include the OTL 4.0 header file
otl_connect db; // connect object
void insert()
// insert rows into table
{
// open a stream with no implicit committing
otl_stream
o(1, // stream buffer size should be set to 1
"insert into test_tab values(:f1<int>,:f2<char[31]>)",
// SQL statement
db // connect object
);
char tmp[32];
for(int i=1;i<=100;++i){
sprintf(tmp,"Name%d",i);
o<<i<<tmp;
}
}
void update(const int af1)
// insert rows into table
{
otl_stream
o(1, // buffer size
"UPDATE test_tab "
" SET f2=:f2<char[31]> "
" WHERE f1=:f1<int>",
// UPDATE statement
db // connect object
);
o<<"Name changed"<<af1;
o<<otl_null()<<af1+1; // set f2 to NULL
}
void select(const int af1)
{
otl_stream i(50, // buffer size may be > 1
"select * from test_tab "
"where f1>=:f11<int> "
" and f1<=:f12<int>*2",
// SELECT statement
db // connect object
);
// create select stream
int f1;
char f2[31];
i<<af1<<af1; // Writing input values into the stream
while(!i.eof()){ // while not end-of-data
i>>f1;
cout<<"f1="<<f1<<", f2=";
i>>f2;
if(i.is_null())
cout<<"NULL";
else
cout<<f2;
cout<<endl;
}
}
int main()
{
otl_connect::otl_initialize(); // initialize ODBC environment
try{
db.rlogon("carlos/carlos@balls-dsn"); // use Unix credentials
otl_cursor::direct_exec
(
db,
"drop table test_tab",
otl_exception::disabled // disable OTL exceptions
); // drop table
otl_cursor::direct_exec
(
db,
"create table test_tab(f1 int, f2 varchar(30))"
); // create table
insert(); // insert records into the table
update(10); // update records in the table
select(8); // select records from the table
}
catch(otl_exception& p){ // intercept OTL exceptions
cerr<<p.msg<<endl; // print out error message
cerr<<p.stm_text<<endl; // print out SQL that caused the error
cerr<<p.sqlstate<<endl; // print out SQLSTATE message
cerr<<p.var_info<<endl; // print out the variable that caused the error
}
db.logoff(); // disconnect from the database
return 0;
}
About Author:
Special thanks to
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
and Palle Haastrup for the packaged unixODBC.
Please send me your opinions and suggestions to the address below:
Copyright 2006 Carlos Crosetti (
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
';
document.write( '' );
document.write( addy_text54601 );
document.write( '<\/a>' );
//-->\n This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
)
http://otl.sourceforge.net/
