none
Error Working with SQL Queries in Visual Studio when Data Source is UDB DB2 on an iSeries Computer

    Question

  • This error relates to the query designer incorrectly building WHERE Clauses where the filter value is a string literal and the data source is an ODBC data source using the iSeries Access ODBC driver. This behaviour has been experienced in VB6.0 ,Visual Studio 2005 and also in Crystal Reports 10.

    IN all cases the query designer incorrectly constructs the WHERE clause where the filter value is a string literal.

    IN VB6.0 and Visual Studio 2005 let's say you have a table named F4111 in library A73AUJDTA and fieldName ILDCT

    You wish to build a simple query SELECT * FROM A73AUJDTA.F1111 WHERE ILDCT = 'OV' .

    To do this you right mouse click on the data connection in Server Explorer and select New Query

    You add table F4111 .

    You then key 'OV' as the fillter value  against the column name ILDCT. You will get an error "Data Type Error in Expression"

    If you key in 99 (or any digits)  you will not get an error and the WHERE clause will be constructed as  : WHERE     (ILDCT = 99) which is incorrect as ILDCT is an aplhanumberic field. (Need Quotes)

    You can edit the SQL Query directly (bypass the Query Designer) and achieve the desired outcome. Once this is done the Query designer does correctly update the Criteria Pane to reflect the directly edited SQL Query.

    As mentioned above I believe similiar behaviour has been experienced using Cyrstal Reports 10. I have been trying to produce a Crystal Report on a  data source using the iSeries Access ODBC driver. When I have entered a record selection formula such as fieldname equals a  string literal such as  "AUT" the Crystal Reports produces a SQL Query with a WHERE CLAUSE which reads WHERE fieldname = 0xAUT  instead of  WHERE fieldname = 'AUT' as it should. 

    The iSeries ODBC Driver  is Version 9.00.00.0

     

     

     

    Sunday, October 15, 2006 4:49 AM

Answers

  • I'd contact the vendor of the iSeries driver (datadirect.com) and see if they've seen this.  It sounds as though your driver isn't getting the datatype from your source correctly.

    -Tim <MSFT>

    Thursday, October 19, 2006 5:17 PM

All replies

  • I'd contact the vendor of the iSeries driver (datadirect.com) and see if they've seen this.  It sounds as though your driver isn't getting the datatype from your source correctly.

    -Tim <MSFT>

    Thursday, October 19, 2006 5:17 PM
  • The problem I think relates to how visual studio interacts with the driver when the database field is of type adBinary (ADO datatype 128). UDB DB2 uses this field to store  alphanumeric data in hexadecimal.

     

    The driver has a translation setting CCSID(Convert Binary Data to Text) 65535. If this setting is not ticked alpha data is downloaded as hexadecimal. If it is text the fields comes down properly converted.

     

    PLease note that I have this probelm wether this setting is or is not ticked

     

     

    Friday, October 20, 2006 7:08 AM