IBM Support

IC81206: CLI0100E SQLSTATE 07001 RETURNED FROM VISUAL STUDIO QUERY BUILDER USING DB2.NET PROVIDER

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SQL query built using Query Builder in Microsoft Visual Studio
    when executed against a DB2 database using IBM DB2.NET provider
    can result in a CLI0100E error when the query has certain
    keywords, such as "CASE", "WHEN", "THEN", "ELSE", and/or "END".
    
    AdoDotNetDataSourceInformation (not an IBM method) calls
    GetSchema (one of IBM's methods) to get a ReservedKeyword list.
    The GetSchema call does indeed generate the list but the
    keywords "CASE", "WHEN", "THEN", "ELSE", "END" are not in the
    list, so the designer does not treat those as reserved keywords.
    
    
    Queries work fine from DB2 command line, but only fails from
    Query builder and Microsoft Visual Studio using DB2 .NET
    provider.
    
    For example:
    SELECT CODE, CASE COUNTRY WHEN 'XX' THEN 'XXXX' WHEN 'YY' THEN
    'YYYY' ELSE 'ZZZZ' END AS "SOURCE"
    FROM DB2ADMIN.COUNTRY WHERE (CODE = ?)
    
    SQL Execution Error.
    
    Executed SQL statement:SELECT CODE, CASE COUNTRY WHEN 'XX' THEN
    'XXXX' WHEN 'YY' THEN 'YYYY' ELSE 'ZZZZ' END AS "SOURCE"
    FROM DB2ADMIN.COUNTRY WHERE (CODE = ?)
    
    Error Source: IBM.Data.DB2
    Error message; Error [07001][IBM] CLI0100E Wrong number of
    parameters. SQLSTATE=07001
    
    
    This APAR fixes the problem in the DB2 .NET provider.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 V9.7 users on Windows OS                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * SQL query built using Query Builder in Microsoft Visual      *
    * Studio when executed against a DB2 database using IBM        *
    * DB2.NET provider can result in a CLI0100E error when the     *
    * query has certain keywords, such as "CASE", "WHEN", "THEN",  *
    * "ELSE", and/or "END".                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7 FP 6                                     *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 V9.7 FP 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC81206

  • Reported component name

    DB2 CONNECT

  • Reported component ID

    DB2CONNCT

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-02-03

  • Closed date

    2012-06-05

  • Last modified date

    2012-06-05

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC84350

Fix information

  • Fixed component name

    DB2 CONNECT

  • Fixed component ID

    DB2CONNCT

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC81206

Modified date: 05 June 2012