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

Hanfei Shen <>
Tue May 3 11:03:15 CEST 2011


Hi,

I had set up a fresh system and reproduce this problem successfully.
Here is something about my installations:

vm:
VirtualBox 4.0.6 r71416

gentoo:
livecd: install-amd64-minimal-20110317.iso
stage: stage3-amd64-20110428.tar.bz2
portage: portage-20110424.tar.bz2

I only installed:

dev-db/mysql-5.1.51
dev-db/myodbc-5.1.6
dev-lang/erlang-13.2.4 USE="kpoll odbc smp"

table schema:

CREATE TABLE `action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `compliment_desc` varchar(255) NOT NULL DEFAULT '',
  `status_desc` varchar(255) NOT NULL DEFAULT '',
  `parameter` varchar(255) NOT NULL DEFAULT '',
  `cause` varchar(255) NOT NULL DEFAULT '',
  `disabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_action_on_cause` (`cause`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8;


I see you are running on a 64 bit  platform, maybe you can try  it also on a
> 32 bit platform.  Maybe
> you could try accessing the database from windows using the windows my-sql
> driver.
>

I reproduced the problem on a 32bit server just right now...

I'll try it on a windows machine asap.

2011/5/3 Hanfei Shen <>:
> Hi,
>
> Hmmmmmm... The unixODBC is already in my system, but I don't know how
> to use it...
>
> I will setup a vm with fresh system tomorrow to reproduce the problem...
>
> PS, could you figure out a name of ANOTHER ODBC-DRIVER which you
mentioned?
>
> 2011/5/3 Hanfei Shen <>:
>> Hi,
>>
>> I've tried https://github.com/dizzyd/erlang-mysql-driver, it works.
>>
>> I'm installing unixodbc... Wait a minute. Thnx...
>>
>>
>> 2011/5/3 Ingela Andin <>:
>>> Hi!
>>>
>>> Well the best thing would be if you can provide me with a way to
reproduce it.
>>> Do you have the possibility to try another odbc-driver with your
database?
>>>
>>> Regards Ingela Erlang/OTP team - Ericsson AB
>>>
>>>
>>> 2011/5/2 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 <>:
>>>>> 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 <>:
>>>>>>
>>>>>>>
>>>>>>> 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 <>:
>>>>>>>
>>>>>>>>
>>>>>>>> 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
>>>>>>>> 
>>>>>>>> http://erlang.org/mailman/listinfo/erlang-questions
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>> _______________________________________________
>>>> erlang-questions mailing list
>>>> 
>>>> http://erlang.org/mailman/listinfo/erlang-questions
>>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://erlang.org/pipermail/erlang-questions/attachments/20110503/96990279/attachment.html>


More information about the erlang-questions mailing list