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