Re: inconsistent data types

On Thu, 6 Aug 2009, Osei Poku wrote:

> Date: Thu, 6 Aug 2009 11:04:15 -0400
> From: Osei Poku <[email protected]>
> To: [email protected]
> Subject: [CLSQL] inconsistent data types
> 
> I am facing a bug where the data types retrieved by clsql-mysql are not the 
> same as the data types of the actual tables.  In this a specific instance, 
> the table was created using DEF-VIEW-CLASS and the data types of the columns 
> in the tables were specified as :STRING types.  Additionally, the table 
> description in the mysql client confirms the correct data type of 
> "varchar(255)".  However, when querying from the clsql mysql interface, the 
> data types of the second column is reported as a double.  This is happening 
> with other more complex tables as well.  Any clues?
>
> The following is from the mysql client...
>
>
> mysql>  select * FROM CONFIG_TABLE;
> +------+-------+
> |  NAME | VALUE |
> +------+-------+
> |  name | john  |
> |  age  | 13    |
> +------+-------+
> 2 rows in set (0.00 sec)
>
> mysql>  describe CONFIG_TABLE;
> +-------+--------------+------+-----+---------+-------+
> |  Field | Type         | Null | Key | Default | Extra |
> +-------+--------------+------+-----+---------+-------+
> |  NAME  | varchar(255) | NO   | PRI | NULL    |       |
> |  VALUE | varchar(255) | YES  |     | NULL    |       |
> +-------+--------------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
>
> mysql>  \s
> --------------
> /usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.1.32, for unknown-linux-gnu 
> (x86_64) using readline 5.1
>
> Connection id:          41
> Current database:       twdb_c432_test
> Current user:           opoku@localhost
> SSL:                    Not in use
> Current pager:          stdout
> Using outfile:          ''
> Using delimiter:        ;
> Server version:         5.1.32 MySQL Community Server (GPL)
> Protocol version:       10
> Connection:             Localhost via UNIX socket
> Server characterset:    latin1
> Db     characterset:    latin1
> Client characterset:    latin1
> Conn.  characterset:    latin1
> UNIX socket:            /tmp/mysql.sock
> Uptime:                 13 days 23 hours 28 min 40 sec
>
> Threads: 2  Questions: 1090  Slow queries: 0  Opens: 119  Flush tables: 1 
> Open tables: 47  Queries per second avg: 0.0
> --------------
>
> -----end mysql client-----
>
> The following is from the lisp repl...
> note: WITH-SQL-DS is a macro the sets the correct *default-database* from the 
> special variable *s*
>
> REPL>  (with-sql-ds *s* (query "select * from CONFIG_TABLE"))
> ;; 2009-08-06 10:54:32,,0 localhost/twdb_c432_test/opoku => select * from 
> CONFIG_TABLE
> ;; 2009-08-06 10:54:32,,0 localhost/twdb_c432_test/opoku <= ((name 0.0D0) 
> (age 13.0D0))
> (("name" 0.0D0) ("age" 13.0D0))
> ("NAME" NIL)
>
> ;; the same query after tracing a few functions
> 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