Convert column error (via ODBC)

[Former Member]

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) _

[markmumy · Accepted solution]

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

[Former Member]

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.

[markmumy]

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

[former_member185199]

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

[c_baker]

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

[former_member185199]

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

dj