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

Hanfei Shen <>
Tue May 3 12:09:36 CEST 2011


Hi,

The same on my windows 7 32 bit workstation.

Erlang R13B02 (erts-5.7.3)

4> code:which(odbc).
"d:/PROGRA~1/ERL57~1.3/lib/odbc-2.10.5/ebin/odbc.beam"

mysql odbc 5.1.8



I'm downloading the latest version of erlang (R14B02)...


2011/5/3 Hanfei Shen <>

> 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/e71ac314/attachment.html>


More information about the erlang-questions mailing list