Re: inconsistent data types

On Aug 6, 2009, at 12:51 PM, Carlos Konstanski wrote:
> Any insight into how clsql decides the data type for table fields
> would be welcome.  A few pointers would go a long way in helping us
> users find a fix to the problem.  I simply don't have the time
> resources to study the clsql codebase completely on my own.

I have some insight but only as it relates to my situation.  It may or  
may not apply to your problem at all.  If you look at the traced code  
below, a function called MAKE-TYPE-LIST-FOR-AUTO appears to be  
responsible for determining the data types.  This function is called  
unless somehow (ie i haven't checked) the column field types are  
provided apriori.  In this function, it queries objects using UFFI  
from the mysql client api to determine the type of each field.  In my  
case, this is happening incorrectly (ie the resulting field types are  
not what actually exists on the database).  As I said, however, this  
is from looking at the mysql code directly.  I have no idea about the  
odbc interface.  I can only suspect that figuring out the data types  
of field is backend specific.


>> REPL>  (with-sql-ds *s* (query "select * from CONFIG_TABLE"))
>> 0> Calling (DATABASE-QUERY "SHOW ERRORS LIMIT 1" #<MYSQL-DATABASE  
>> localhost/twdb_c432_test/opoku OPEN #x300041FF74DD> NIL NIL)
>> 1> Calling (CLSQL-MYSQL::DATABASE-MYSQL-PTR #<MYSQL-DATABASE  
>> localhost/twdb_c432_test/opoku OPEN #x300041FF74DD>)
>> <1 CLSQL-MYSQL::DATABASE-MYSQL-PTR returned #<A Foreign Pointer  
>> #x6ADD70>
>> 1> Calling (CLSQL-MYSQL::CANONICALIZE-TYPES NIL 3 #<A Foreign  
>> Pointer #x6ABD60>)
>> <1 CLSQL-MYSQL::CANONICALIZE-TYPES returned NIL
>> <0 DATABASE-QUERY returned 2 values :
>> <0      NIL
>> <0      NIL
>> 0> Calling (QUERY "select * from CONFIG_TABLE")
>> ;; 2009-08-06 10:58:28,,0 localhost/twdb_c432_test/opoku => select  
>> * from CONFIG_TABLE
>> 1> Calling (DATABASE-QUERY "select * from CONFIG_TABLE" #<MYSQL- 
>> DATABASE localhost/twdb_c432_test/opoku OPEN #x300041FF74DD> :AUTO T)
>> 2> Calling (CLSQL-MYSQL::DATABASE-MYSQL-PTR #<MYSQL-DATABASE
>> localhost/twdb_c432_test/opoku OPEN #x300041FF74DD>)
>> <2 CLSQL-MYSQL::DATABASE-MYSQL-PTR returned #<A Foreign Pointer  
>> #x6ADD70>
>> 2> Calling (CLSQL-MYSQL::CANONICALIZE-TYPES :AUTO 2 #<A Foreign  
>> Pointer
>> #x6ABD60>)
>>  3> Calling (CLSQL-MYSQL::MAKE-TYPE-LIST-FOR-AUTO 2 #<A Foreign  
>> Pointer
>>  #x6ABD60>)
>>  <3 CLSQL-MYSQL::MAKE-TYPE-LIST-FOR-AUTO returned (T :DOUBLE)
>> <2 CLSQL-MYSQL::CANONICALIZE-TYPES returned (T :DOUBLE)
>> <1 DATABASE-QUERY returned 2 values :
>> <1      (("name" 0.0D0) ("age" 13.0D0))
>> <1      ("NAME" NIL)
>> ;; 2009-08-06 10:58:28,,0 localhost/twdb_c432_test/opoku <= ((name  
>> 0.0D0) (age 13.0D0))
>> <0 QUERY returned 2 values :
>> <0      (("name" 0.0D0) ("age" 13.0D0))
>> <0      ("NAME" NIL)
>> (("name" 0.0D0) ("age" 13.0D0))
>> ("NAME" NIL)
>> REPL>
>
> I have a similar problem with db-odbc (with Oracle backend)
> (http://lists.b9.com/pipermail/clsql/2009-July/001721.html).  My issue
> is a little different; all my data comes back as strings.  But if I
> write a pipelined table function and query that instead, then I get
> the correct data types.
>
> clsql must be paying some attention to the data typs reported by the
> database, or I would not be able to use pipelined table functions as a
> workaround.  I don't think mysql has this feature, unfortunately.
>
> Any insight into how clsql decides the data type for table fields
> would be welcome.  A few pointers would go a long way in helping us
> users find a fix to the problem.  I simply don't have the time
> resources to study the clsql codebase completely on my own.
>
> For what it's worth, I don't have these data type issues with
> db-postgresql-socket.  That's what I use whenever I am free to choose
> the database engine.  I will check db-odbc with a freetds backend to
> see if the problem exists there.
>
> Carlos