Data type difference in OpenESQL between ADO and ODBC

Hi Peter,

I am not seeing the same results. 

If I have a table defined with a uniqueidentifier column with the default of NEWID()

and I insert a row and then select the row back I retrieve the converted 36 byte character string if my host variable is pic x(36) for both ODBC and ADO.

I tested with V11.0 PU6.

Can you provide more detail on where you are seeing this difference?

Thanks

Hi Chris,

Thanks for getting back to me on this. We are using a beta copy of COBOL with patches on top of 11.0 PU1 as part of our modernisation project.

The issue itself is not that it brings the value back to the host variable it is when you use the SQLTYPE and SQLLEN in SQLDA.CPY to identify what the column type is. The SQLTYPE being returned for a uniqueidentifier column is 444 (binary) and SQLLEN is being returned as 16. 

The equivalent check via ODBC sets the SQLTYPE to char (452) and SQLLEN to 36. 

Neil has just informed me that he did raise this on one of our case numbers (01163878) and Kim has asked for a sample program to show the issue. I will create the sample and attach it to the case.

Thanks

Hi Peter,

Yes, I was just able to recreate this using dynamic SQL.

uniqueidentifier columns are stored as 16 byte binary numbers but are converted to the 36 character hexadecimal string equivalent on return.

It seems in ADO it returns the type of the stored column and not of the returned type.

I will let Kim handle this through the case.

Thanks.

Thanks Chris, 

You couldn’t perhaps supply me with the code you used to reproduce this? If not then I will create a sample for Kim

Here you go… Only the connect statement has to change between native and .NET.

       identification division.
       program-id. Program1.
       data division.
       working-storage section.
       EXEC SQL INCLUDE SQLCA END-EXEC.
       exec sql include sqlda end-exec
       01  DCLGUIDTABLE.
           03 GUIDTABLE-MYGUID                  PIC X(36).
           03 GUIDTABLE-MYDATA                  PIC X(10).
       01 sql-buffer            pic x(500)          value spaces.

       01 connect-str pic x(1000) value "server=localhost;"
           & "Trusted_Connection=yes;Database=NORTHWIND;Integrated"
           & " Security=True;MultipleActiveResultSets=True;"
           & "factory=System.Data.SqlClient;".
       *>01 connect-str pic x(100) value "SQLODBC64WA".
       procedure division.
            exec sql whenever sqlerror
              perform openesql-error-routine
           end-exec

           EXEC SQL
               CONNECT using :connect-str 
           END-EXEC 

           exec sql drop table GUIDTABLE end-exec
           exec sql 
               CREATE TABLE GUIDTABLE(
	             MYGUID uniqueidentifier NOT NULL,
	             MYDATA char(10) NULL
               )
           end-exec
           exec sql alter table GUIDTABLE
              add constraint DF_GUIDTABLE_MYGUID
              DEFAULT (newid()) for MYGUID
           end-exec

           move "test" to GUIDTABLE-MYDATA

           EXEC SQL 
              INSERT INTO GUIDTABLE  
                (MYDATA
               ) VALUES (
                :GUIDTABLE-MYDATA
               )
           END-EXEC 
           exec sql commit work end-exec
           exec sql select myguid into :guidtable-myguid from
              GUIDTABLE where mydata = 'test'
           end-exec
           display GUIDTABLE-MYGUID

           move "select * from guidtable" to sql-buffer    
        *> set the maximum number of variables to be returned
           move 2 to sqln

        *> calculate size of sqlda area

           compute sqldabc = 16 + 56 * sqln
        *> for 64-bit use 16 + 56 * sqln   

           exec sql prepare mystatement from :sql-buffer end-exec
           exec sql describe mystatement into :sqlda end-exec
           display "name = " sqlnamec(1)(1:sqlnamel(1))
           display "type = " sqltype(1)
           display "len  = " sqllen(1)

           exec sql disconnect all end-exec

           goback.
       openesql-error-routine.

           display "Error occurred: "
           display "sqlcode= ", sqlcode
           display sqlerrmc.

Thanks Chris really appreciate it