Wrong decimal point position with SQLBase unixODBC 11.7.2

[chaos_prevails]

To set up the unixODBC drivers to connect to a SQLBase database

It works but with figures between (I guess) >0.0 and <0.1 the decimal point is moved one position to the left: so 0.02 becomes 0.2. (look at the DEBET column)

directly on the sqlbase server connected with sqltalk I get (these are the correct values also seen within the bookkeeping program itself):

sqltalk.jpg

via isql I get wrong values: eg 0.02 becomes 0.2 or 0.05 becomes 0.5:

sqlisql.jpg

This error does not occur if the database is connected via the SQLBASEOLEDB provider using a MS SQL Server. In this case the data is correct. So it seems to me the unixODBC drivers are the culprit.

As Database we use SQLBase 8.5 which runs on Windows within Multivers / Unit4, a bookkeeping program. I already asked how it is possible to upgrade the multivers databases to a newer version of SQLBase (which might or might not fix this problem) but this error seems quite serious to me so I would like to fix it straight away.

Because the sqlbase is quite old (8.5) I tried older unixODBC sqlbase driver packs for linux (9.0.1.2, 10.0.0) but I failed getting a connection. As distribution always ubuntu 12.04 or 14.04 is used (for the older drivers I used 32bit, for the 11.7.x I used 64 bit)

Code: Select all

[01000][unixODBC][Driver Manager]Can't open lib '/opt/Unify/SQLBase/libsqlbaseodbc.so' : file not found

With 11.7.3. I - where sql.ini is apparently missing - I get

Code: Select all

[unixODBC][Gupta][ODBC Driver][SQLBase]00401 DBA COD Cannot open database (SQL-08001)

.

So 11.7.2 is the only version which works.

Here are my configuration files

/etc/odbc.ini

Code: Select all

;------ /etc/odbc.ini file begin -----
;
; odbc.ini
;
[ODBC Data Sources]
boekh_sqlbase=Gupta SQLBase ODBC Driver

[boekh_sqlbase]
Driver=/opt/Unify/SQLBase/libsqlbaseodbc.so
Description=Gupta SQLBase DSN
DSN=boekh_sqlbase
Servername=boekh_multivers
#LoginID=SYSADM
#Username=SYSADM
#Password=SYSADM
LoginID=XXXXX
Username=XXXXXX
Password=XXXXX
Database=MVL00300
ini=/opt/Unify/SQLBase/sql.ini
InputMessageSize=2000
IsoLevel=Release Locks
LockTimeOut=300

[Default]
Driver = /opt/Unify/SQLBase/libsqlbaseodbc.so

/etc/odbcinst.ini:

Code: Select all

;------ /etc/odbcinst.ini file begin -----
;
; odbcinst.ini
;
[ODBC Drivers]
Gupta SQLBase = Installed

[Gupta SQLBase]
Driver = /opt/Unify/SQLBase/libsqlbaseodbc.so
Setup = /opt/Unify/SQLBase/libsqlbaseodbcsetup.so

/opt/Unify/SQLBase/sql.ini:

Code: Select all

[linuxclient]
clientname=localhost
clientruntimedir=/opt/Unify/SQLBase

[linuxclient.dll]
comdll=sqltcpip

[linuxclient.tcpip]
serverpath=MVSERV,boekh_multivers,2155/*
[dblxsrv]
servername=MVSERV
dbdir=/opt/Unify/SQLBase
centurydefaultmode=1
ansijoinsyntax=1
cache=65536
DBNAME=MVL00300

[dblxsrv.dll]
comdll=sqltcpip

[dblxsrv.tcpip]
listenport=2155

[chaos_prevails]

a short follow-up:
I upgraded the database from 8.5 to 11.5.4 but this didn't fix the bug: also with 11.5.4. I the decimal position is wrong with numbers between 0.0 and 0.1.

so I converted the number to a string:

Code: Select all

SELECT @STRING(value_column,2) AS value_column

If you use plperl to make the DB connection you would have to put a \ before @ in your code, thus

Code: Select all

\@STRING(....

side remark: I still cannot connect with unixODBC 11.7.3, also when trying to connect to the converted 11.5.4 database. I still get

Code: Select all

[unixODBC][Gupta][ODBC Driver][SQLBase]00401 DBA COD Cannot open database (SQL-08001)