Originally posted by: paul.brett

Check out the enclosed testcase.

Test1_Oracle.mmc uses the Oracle adapter, the rule is:

=DBLOOKUP("select seq1.nextval from dual","Test.mdq","Test_ORAC:-T")

...and is successfully able to retrieve the next numerical value from the sequence defined in the Oracle database. 

Test2_ODBC.mmc uses the ODBC adapter and the rule is:

=DBLOOKUP("select seq1.nextval from dual","Test.mdq","Test_ODBC:-T")

...with no other changes.  The data returned is something like:

00000001: 38 7C 00 00 00 00 00 00 45 40                   | 8|      E@

What this represents is the raw binary returned from the WTX ODBC adapter which treats the column as a FLOAT (in my local database) because the Oracle driver returns that specific type, as per the SQLDescribeCol entry in the ODBC trace log.

In the final map, Test3_ODBCcast.mmc uses a slight change to the SQL query to 'cast' the value into an integer.  The rule is:

=DBLOOKUP("select cast(seq1.nextval as int) from dual","Test.mdq","Test_ODBC:-T")

...and the data is returned as an integer (to WTX as a string) as expected.

There are other ways to handle this situation, using mapping to deal with the raw output from the ODBC adapter, but this method is more elegant.

Thank-you.

Paul

Follow me on Twitter