Author Topic: ODBC v 3 - SQLConnectW() - DB2 CLI  (Read 4949 times)

Doug Clark

  • Sr. Member
  • ****
  • Posts: 306
  • Karma: +7/-1
    • View Profile
ODBC v 3 - SQLConnectW() - DB2 CLI
« on: December 17, 2018, 08:00:00 pm »
The DB2 v 7 ODBC driver is at version 3.52, which supports Unicode. The ODBC driver manager shipped with Lotus Smartsuite is version 3 - which was before Unicode was supported.

When I attempt to connect to DB2 using a DB2 v 7 ODBC driver with a version 3 ODBC Driver Manager I crash. It appears to me from tracing that the ODBC driver manager is, for some reason, calling the wide/unicode version of the DB2 CLI functions instead of the ANIS version. For example there is a SQLConnect() which is ANSI and a SQLConnectw() which is unicode; SQLConnectw() is getting called.

I assume my crashing is related to wide version returning data in buffers allocated for ANSI lengths of data. My believe is, in part, based on the tracing, and the fact that if I call SQLConnect() directly by linking to the CLI library rather than the ODBC library the connection succeeds.

It also appears from IBM documentation that the DB2's  defaults to the wide/unicode versions unless, somehow, restricted to ANSI functions. For example there is a db2cli.ini keyword DisableUnicode=1 listed in the version 11 documentation which I cannot find in the version 7 documentation.

Does anyone know how to restrict to ANSI only for DB2 v 7? 

Bogdan

  • Jr. Member
  • **
  • Posts: 93
  • Karma: +1/-0
    • View Profile
Re: ODBC v 3 - SQLConnectW() - DB2 CLI
« Reply #1 on: December 18, 2018, 03:21:23 pm »
The DB2 v 7 ODBC driver is at version 3.52, which supports Unicode. The ODBC driver manager shipped with Lotus Smartsuite is version 3 - which was before Unicode was supported.
Which DB2 Client Runtime is installed on the workstation exactly?

Quote
When I attempt to connect to DB2 using a DB2 v 7 ODBC driver with a version 3 ODBC Driver Manager I crash.
Doesn't DB2 OS/2 CAE come with it's own ODBC driver manager?

Quote
It also appears from IBM documentation that the DB2's  defaults to the wide/unicode versions unless, somehow, restricted to ANSI functions. For example there is a db2cli.ini keyword DisableUnicode=1 listed in the version 11 documentation which I cannot find in the version 7 documentation.

Does anyone know how to restrict to ANSI only for DB2 v 7?
Do you mean the GRAPHIC keyword?

Doug Clark

  • Sr. Member
  • ****
  • Posts: 306
  • Karma: +7/-1
    • View Profile
Re: ODBC v 3 - SQLConnectW() - DB2 CLI
« Reply #2 on: December 18, 2018, 07:48:43 pm »
The DB2 client installed is version 7.2, running on AOS.

The DB2 client does come with an ODBC driver manager, but it is version 2.5. The IBM ODBC driver works correctly with version 2.5 ODBC driver managers,  the one supplied by DB2 and others that are available.

The version 3 ODBC driver manager I am using works with version 3 drivers, e.g. dbase and text, and with the SQL Anywhere ODBC driver which is version 2.5

I think the GRAPHIC keyword simply controls what SQLGetTypeInfo() returns in its result set - which is the list of column types supported by the database.

The keyword I was trying to use is "DisableUnicode". I tried putting that in the db2cli.ini, and passing it as a keyword in SQLDriverConnect(). Neither made any difference. This keyword is described in the readme and does not appear in the GUI for ODBC driver setup.

I am going to try and find the v 7.1 ODBC and try that, based on the following from release.txt, even though it is specific to UNIX.

4.37 Merant Driver Manager and the DB2 UDB Version 7 ODBC Driver on UNIX

Incompatibilities have been encountered with Unicode support when the
Merant Driver Manager accesses DB2's ODBC driver on UNIX. These
incompatibilities result in Unicode being used by the Merant Driver Manager
regardless of whether the application has requested its use. This can lead
to problems with products such as the Data Warehouse Center, Information
Catalog Manager, and MQSI, which require the Merant Driver Manager to
support non-IBM data sources. You can use an alternate DB2 ODBC driver
library without Unicode support enabled until a permanent solution is
available. The affected versions of DB2 UDB include Version 7.1 with FixPak
2 or later, and Version 7.2 at any FixPak level.

An alternative DB2 ODBC driver library without Unicode support enabled was
shipped with DB2 Versions 7.1 and 7.2 for AIX, HP-UX, and Solaris Operating
Environment. To use this alternative library, you must create a copy of it,
giving the copy the original DB2 ODBC driver library's name.


roberto

  • Hero Member
  • *****
  • Posts: 810
  • Karma: +3/-6
    • View Profile
Re: ODBC v 3 - SQLConnectW() - DB2 CLI
« Reply #3 on: December 19, 2018, 09:23:29 am »
I have these notes for the connection:
MODIFY CONFIG.SYS BY ADDING
SET DB2COMM = TCPIP
In the file c: \ mptn \ etc \ tcpip.ini include a line with

TCPPORT = 50000


I do not know if it will work for you, but from what I see the functions with W at the end are for unicode, and without the W they are for ANSI

Doug Clark

  • Sr. Member
  • ****
  • Posts: 306
  • Karma: +7/-1
    • View Profile
Re: ODBC v 3 - SQLConnectW() - DB2 CLI
« Reply #4 on: December 19, 2018, 06:31:49 pm »
Thanks Roberto for the suggestion.  I have the DB2 v 7.2 client setup to use either Netbios or TCP/IP. I get the crash either way.

Based on the info from IBM on the issues of going to version 7.2 on Unix I installed the DB2 client version 7.1 on another machine (eCS 2.0)  along with a version 3 ODBC driver manager. That works. The DB2 ODBC driver reports itself as ODBC version 3. It appears that one of the differences between DB2 v 7.1 and 7.2 is the ODBC driver was upgraded to  version 3.52.

The conclusions at this point are:
1) if you have an ODBC driver manager at version 3 you can use DB2 client version 7.1 but not version 7.2.
2)  If you have an ODBC driver manager at version 2.5 you can use either version of the DB2 client.
3) If you bypass ODBC and call into the CLI library directly you can call the ANSI version of the functions and it appears to work OK.


I am pasting some info from the DB2 site about ODBC and unicode.  Based on the name, I believe the version 3 OS/2 driver manager is DataDirect. Earlier versions of driver managers can be DataDirect or Visigenic

stuff from DB2 site
--------------------------------------
DB2 10.5 for Linux, UNIX, and Windows
CLI applications that are ODBC-compliant can access a DB2® database through the CLI/ODBC driver in one of two ways:
linking to the CLI/ODBC driver library or linking to the ODBC driver manager library.

CLI applications can access a database by:

    Direct access - An application links to the CLI/ODBC driver library and makes calls to exported CLI/ODBC functions.
    Unicode applications accessing the CLI/ODBC driver directly should access and perform transactions against the
    database using the CLI Unicode functions, and use SQLWCHAR buffers with the understanding that all Unicode data
    is UCS-2. To identify itself as a Unicode application, the application must connect to the database using either
    SQLConnectW() or SQLDriverConnectW().
   
    Indirect access - An application links to an ODBC driver manager library and makes calls to standard ODBC functions.
    The ODBC driver manager then loads the CLI/ODBC driver and calls exported ODBC functions on behalf of the application.
    The data passed to the CLI/ODBC driver from the application might be converted by the ODBC driver manager. An
    application identifies itself to an ODBC driver manager as a Unicode application by calling SQLConnectW() or SQLDriverConnectW().

When connecting to a data source, the ODBC driver manager checks to see if the requested driver exports the SQLConnectW()
function. If the function is supported, the ODBC driver is considered a Unicode driver, and all subsequent calls in the
application to ODBC functions are routed to the functions' Unicode equivalents (identified by the 'W' suffix; for example,
SQLConnectW()) by the ODBC driver manager. If the application calls Unicode functions, no string conversion is necessary,
and the ODBC driver manager calls the Unicode functions directly. If the application calls ANSI functions, the ODBC driver manager
converts all ANSI strings to Unicode strings before calling the equivalent Unicode function.

If an application calls Unicode functions, but the driver does not export SQLConnectW(), then the ODBC driver manager routes
any Unicode function calls to their ANSI equivalents. All Unicode strings are converted by the ODBC driver manager to ANSI
strings in the application's code page before calling the equivalent ANSI function. This might result in data loss if the
application uses Unicode characters which cannot be converted to the application's code page.

Various ODBC driver managers use different encoding schemes for Unicode strings, depending on the operating system:
Table 1. Unicode string encoding schemes by operating systemDriver manager
Operating system                        Microsoft Windows    Linux and UNIX
Microsoft ODBC Driver Manager            UTF-16*            not applicable
unixODBC Driver Manager           UCS-2                   UCS-2
DataDirect Connect for ODBC Driver Manager    UTF-16*    UTF-8

* UTF-16 is a superset of UCS-2 and therefore is compatible

The DataDirect ODBC driver manager for DB2® can be used for connections to the DB2 database.
Restrictions

Complications arise when using the CLI/ODBC driver with the DataDirect Connect for ODBC Driver Manager
in the UNIX environment because of the use of UTF-8 character encoding by the driver manager. UTF-8 is a variable length
character encoding scheme using anywhere from 1 to 6 bytes to store characters. UTF-8 and UCS-2 are not inherently compatible,
and passing UTF-8 data to the CLI/ODBC driver (which expects UCS-2) might result in application errors, data corruption, or application exceptions.

To avoid this problem, the DataDirect Connect for ODBC Driver Manager 4.2 Service Pack 2 recognizes a CLI/ODBC driver and not
use the Unicode functions, effectively treating the CLI/ODBC driver as an ANSI-only driver. Before release 4.2 Service Pack 2,
the DataDirect Connect for ODBC Driver Manager had to be linked with the _36 version of the CLI/ODBC driver which does not export
the SQLConnectW function.