Question Re: Convert column error (via ODBC) in Technology Q&A

Convert column error (via ODBC)

Former Member — Wed, 26 Feb 2014 15:31:08 GMT

Hi,

While trying to import data from ASE via a remote server, I’m getting the following error:

SQL Anywhere Error -1000187: Unable to implicitly convert column 'varDate' to datatype (datetime) from datatype (integer).

-- (dflib/df_Heap.cxx 13287) _

Have any one had this issue?

How do you solve it?

Thanks a lot,

Here are the steps to reproduce the error:

create server ohdwetldev01

class 'aseodbc'

using 'driver=/IQ/Server/IQ-16/ODBC/DataAccess64/ODBC/lib/libsybdrvodb.so;dsn=ohdwetldev01'

select * from sysserver

srvid srvname srvclass srvinfo srvreadonly

-------- ------------ ----------- ----------------------------------------------------------------------------------- --------------

1 ohdwetldev01 aseodbc driver=/IQ/Server/IQ-16/ODBC/DataAccess64/ODBC/lib/libsybdrvodb.so;dsn=ohdwetldev01 N

create table DBA.uver_DateTest

(varID integer null,

varDate datetime null)

forward to ohdwetldev01

{select 1, null}

expr_1 expr_2

--------- ---------

1 (null)

1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

[Executed: 26/02/2014 10:16:49 AM] [Execution: 6ms]

insert into DBA.uver_DateTest location 'ohdwetldev01.testStageDb'

{select 1, null}

SQL Anywhere Error -1000187: Unable to implicitly convert column 'varDate' to datatype (datetime) from datatype (integer).

-- (dflib/df_Heap.cxx 13287) _

Re: Convert column error (via ODBC)

markmumy — Wed, 26 Feb 2014 16:03:25 GMT

Try forcing the data type on the remote system. Right now it is an int and null. But the null isn't bound to a data type so we default to an int. Consider using the convert statement to convert null to a datetime data type.

MArk

Re: Convert column error (via ODBC)

Former Member — Wed, 26 Feb 2014 16:05:11 GMT

Hi,

what happens if you not insert a NULL value but a concrete date ?

if this works then you could change your query to :

insert into DBA.uver_DateTest location 'ohdwetldev01.testStageDb'

{select myID, isnull(myDate,'01.01.1900' from mySource}

HTH

Re: Convert column error (via ODBC)

ChrisBaker — Wed, 26 Feb 2014 16:05:27 GMT

There is nothing in the {select 1, null} whether run on IQ or in ASE that indicates the datatype of the 'null'. By default it will be defined as an integer. Even when you forward to ASE, the result that came back was proabably an int, not a datetime.

You will need to cast the null as datetime in your select.

Chris

Re: Convert column error (via ODBC)

Former Member — Wed, 26 Feb 2014 16:16:06 GMT

Thanks all.

Yes, if I convert to datetime at the source it works.

insert into DBA.uver_DateTest location 'ohdwetldev01.testStageDb'

{select 1, cast(null as datetime)}

But, the fist syntax works in SAP IQ 15.2 ESD #2 using JDBC for the remote server, no need to cast or convert.

Is this a change of behavior due to SAP IQ 16.0 SP3 or to the use of ODBC instead of JDBC?

Thanks a lot for your help.

Re: Convert column error (via ODBC)

markmumy — Sun, 02 Mar 2014 20:55:57 GMT

Yes. Likely the issue is what the protocol/driver does to null values that come across from ASE.

Re: Convert column error (via ODBC)

Former Member — Mon, 03 Mar 2014 14:13:09 GMT

As far as i know does JDBC reads the (user)datatypes for the Database ( there has been an Issue some years ago with logging into one db and reading from a table located in another DB with Userdatatypes*, thats why i remember this)

so the jdbc(driver) can then deliver the apropriate type/ cast and its working

* they had to have the same id´s in all databases used in the connection