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

[Ingela Anderton Andin]

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