[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Hanfei Shen
qqshfox@REDACTED
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 <qqshfox@REDACTED>:
> 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 <qqshfox@REDACTED>:
>> 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 <ingela@REDACTED>:
>>> 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 <qqshfox@REDACTED>:
>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>> _______________________________________________
>>>> erlang-questions mailing list
>>>> erlang-questions@REDACTED
>>>> 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.htm>
More information about the erlang-questions
mailing list