[erlang-questions] Erlang and MySQL: ODBC, Column type not supported

[Hanfei Shen]

Hi,

I had changed the type from SQLINTEGER to SQLSMALLINT, but the problem
is still there...

(gdb) i func encode_data_type
File odbcserver.c:
static void encode_data_type(SQLSMALLINT, SQLINTEGER, SQLSMALLINT, db_state *);

Breakpoint 1, encode_data_type (sql_type=-9, size=255,
decimal_digits=0, state=0x7fffa5380210) at odbcserver.c:1396
1396        switch(sql_type) {

Do you need any details?

2011/5/2 Ingela Anderton Andin <ingela@REDACTED>:
> Hi!
>
> Hanfei Shen wrote:
>>
>> Hi Ingela,
>>
>> Did you mean the value of variable "sql_type"?
>>
>>
>
> Yes.
>
>> I tried to inspect it through gdb, setting a break on func
>> encode_data_type. And then I got:
>>
>> Breakpoint 1, encode_data_type (sql_type=-9, size=255,
>> decimal_digits=0, state=0x7fffc657ec40) at odbcserver.c:1396
>> 1396        switch(sql_type) {
>>
>>
>
> Well that is defenetly not a good value. Looking at the code just now
> I noticed that
>
> encode_data_type defines sql_type as "SQLINTEGER sql_type" and it
> ought to be "SQLSMALLINT sql_type". If you change that does it fix your
> problem?
>
> Regards Ingela Erlang/OTP team - Ericsson AB
>
>
>
>> 2011/5/2 Ingela Andin <ingela@REDACTED>:
>>
>>>
>>> Hi!
>>>
>>> The erlang odbc application supports {sql-varchars,  N} where N = 255
>>> in your case. The return values
>>> you get suggest that the odbc-driver you have returns an unexpected
>>> column type.
>>>
>>> In the erlang odbc port program there is a switch that handles type
>>> when describing a coloumn that if we take out
>>> the handling code looks like this.
>>>
>>> switch(sql_type) {
>>>   case SQL_CHAR:
>>>   case SQL_VARCHAR:
>>>   case SQL_WCHAR:
>>>   case SQL_WVARCHAR:
>>>   case SQL_NUMERIC:
>>>   case SQL_DECIMAL:
>>>   case SQL_INTEGER:
>>>   case SQL_TINYINT:
>>>   case SQL_SMALLINT:
>>>   case SQL_REAL:
>>>   case SQL_FLOAT:
>>>   case SQL_DOUBLE:
>>>   case SQL_BIT:
>>>   case SQL_TYPE_DATE:
>>>   case SQL_TYPE_TIME:
>>>   case SQL_TYPE_TIMESTAMP:
>>>   case SQL_BIGINT:
>>>   case SQL_BINARY:
>>>   case SQL_LONGVARCHAR:
>>>   case SQL_VARBINARY:
>>>   case SQL_LONGVARBINARY:
>>>   case SQL_INTERVAL_MONTH:
>>>   case SQL_INTERVAL_YEAR:
>>>   case SQL_INTERVAL_DAY:
>>>   case SQL_INTERVAL_MINUTE:
>>>   case SQL_INTERVAL_HOUR_TO_SECOND:
>>>   case SQL_INTERVAL_MINUTE_TO_SECOND:
>>>   case SQL_UNKNOWN_TYPE:
>>>   default: /* Will probably never happen */
>>>       ei_x_encode_atom(&dynamic_buffer(state), "ODBC_UNSUPPORTED_TYPE");
>>>       break;
>>>   }
>>>
>>>
>>> And you end up in the default clause which is very unexpected and the
>>> root to your problem.  If  you can figur out
>>> what your driver returns for the ODBC C-API function SQLDescribeCol we
>>> could know if the driver is at fault or if the odbc-port program is.
>>>
>>> Regards  Ingela Erlang/OTP team - Ericsson AB
>>>
>>> 2011/5/1 Hanfei Shen <qqshfox@REDACTED>:
>>>
>>>>
>>>> Dear all,
>>>>
>>>> I'm a newbie programming in erlang. When I used ODBC to connect to a
>>>> MySQL database through Connector/ODBC (MyODBC), I got some error when
>>>> calling odbc:sql_query(Conn, "SELECT * FROM action"):
>>>>
>>>> {error,"Column type not supported"}
>>>>
>>>> Then I call odbc:describe_table(Conn, "action"), it returned:
>>>>
>>>> {ok,[{"id",sql_integer},
>>>>     {"code",'ODBC_UNSUPPORTED_TYPE'},
>>>>     {"compliment_desc",'ODBC_UNSUPPORTED_TYPE'},
>>>>     {"status_desc",'ODBC_UNSUPPORTED_TYPE'},
>>>>     {"parameter",'ODBC_UNSUPPORTED_TYPE'},
>>>>     {"cause",'ODBC_UNSUPPORTED_TYPE'},
>>>>     {"disabled",sql_tinyint}]}
>>>>
>>>> And desc the table in mysql client:
>>>> mysql> desc action;
>>>>
>>>> +-----------------+--------------+------+-----+---------+----------------+
>>>> | Field           | Type         | Null | Key | Default | Extra
>>>>  |
>>>>
>>>> +-----------------+--------------+------+-----+---------+----------------+
>>>> | id              | int(11)      | NO   | PRI | NULL    | auto_increment
>>>> |
>>>> | code            | varchar(255) | NO   |     | NULL    |
>>>>  |
>>>> | compliment_desc | varchar(255) | NO   |     |         |
>>>>  |
>>>> | status_desc     | varchar(255) | NO   |     |         |
>>>>  |
>>>> | parameter       | varchar(255) | NO   |     |         |
>>>>  |
>>>> | cause           | varchar(255) | NO   | UNI |         |
>>>>  |
>>>> | disabled        | tinyint(1)   | NO   |     | 0       |
>>>>  |
>>>>
>>>> +-----------------+--------------+------+-----+---------+----------------+
>>>>
>>>> It seems that Erlang ODBC driver does not support the mysql type
>>>> varchar? How can I use the database without touching the db schema?
>>>>
>>>>
>>>> Some details about the system env:
>>>>
>>>> $ uname -a
>>>> Linux 2.6.36-gentoo-r8 #4 SMP Thu Apr 7 20:13:18 CST 2011 x86_64
>>>> Intel(R) Core(TM)2 Duo CPU E7500 @ 2.93GHz GenuineIntel GNU/Linux
>>>>
>>>> $ erl
>>>> Erlang R13B04 (erts-5.7.5) [source] [64-bit] [smp:2:2] [rq:2]
>>>> [async-threads:0] [kernel-poll:false]
>>>>
>>>> $ emerge -vp erlang
>>>> dev-lang/erlang-13.2.4  USE="doc kpoll odbc smp ssl -emacs -hipe -java
>>>> -sctp -tk -wxwidgets"
>>>>
>>>> eshell> code:which(odbc).
>>>> "/usr/lib64/erlang/lib/odbc-2.10.7/ebin/odbc.beam"
>>>>
>>>> $ mysql -V
>>>> mysql  Ver 14.14 Distrib 5.1.51, for pc-linux-gnu (x86_64) using
>>>> readline 5.1
>>>>
>>>> Server version: 5.1.51-log Gentoo Linux mysql-5.1.51
>>>>
>>>> $ emerge -vp myodbc
>>>> dev-db/myodbc-5.1.6  USE="-debug -doc -qt4 -static"
>>>>
>>>>
>>>> Any help will be greatly appreciated.  Thanks in advance.
>>>> Sorry for my poor English, I'm not a native...
>>>>
>>>> Best,
>>>>
>>>> --
>>>> Hanfei
>>>> _______________________________________________
>>>> erlang-questions mailing list
>>>> erlang-questions@REDACTED
>>>> http://erlang.org/mailman/listinfo/erlang-questions
>>>>
>>>>
>>
>>
>
>