-
-
Notifications
You must be signed in to change notification settings - Fork 17
odbc db2 notes
Use #:quirks (no-c-numeric); no remaining test failures.
Using #:strict-parameter-types? #t seems to work.
Whenever using ODBC driver or calling db2 software:
. /home/db2inst1/sqllib/db2profile
The following links were useful in setting up a DB2 instance for testing:
- https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.qb.server.doc/doc/c0008711.html
- manual configuration: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.qb.server.doc/doc/t0007067.html
- create database: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001941.html
- DB2 uses OS users?: https://www.ibm.com/developerworks/data/library/techarticle/dm-0508wasserman/index.html
- useful commands: https://wiki.base22.com/btg/a-useful-list-of-ibm-db2-commands-81789781.html
- ODBC config:
Always need to run . /home/db2inst1/sqllib/db2profile before doing anything.
This page suggests
that DB2 does not support SQLSetDescField, which means that the set-numeric-descriptors method (which sets
the precision, etc) won't work.
In fact, when I check the result of (SQLSetDescField/Int hdesc i SQL_DESC_TYPE SQL_C_NUMERIC), I get -1
(SQL_ERROR), but no diagnostic.
These docs indicate that the current code is using the wrong convention for calling SQLSetDescField
with an integer value, but after fixing that issue the function still returned -1 without diagnostic.
But then these docs seem to contradict that theory.
I also tried changing the numeric/decimal case to use SQL_C_NUMERIC directly rather than SQL_ARD_TYPE,
but it still failed (but with a different message).
Other references:
- https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0000604.html
- https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-ard-type
- https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/overriding-default-leading-and-seconds-precision-for-interval-data-types
- https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetdescfield-function
- https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0000651.html
- http://fractio.nl/2007/10/26/setting-up-unixodbc-with-a-remote-db2/
Decimals
- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/intro/src/tpc/db2z_numericdatatypes.html
- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_csql.html
- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_odbcdecfloatexample.html
- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_numcsq.html
- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/odbc/src/tpc/db2z_numsqc.html
- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_dtacnv.html
(query-list c "select cast(d as varchar(20)) from foon") '("1.23456" "100.00000")
When retrieved as SQL_C_BINARY, decimals are translated to scaled integers like this:
1.23456 #x 00 00 01 23 45 6c -- ie, one digit per nibble
100.00000 #x 00 01 00 00 00 0c -- final sign nibble: c positive, d negative
Or could just use SQL_C_CHAR, but does it depend on locale? (Doesn't in my tests...)