[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Hanfei Shen
qqshfox@REDACTED
Tue May 3 13:11:36 CEST 2011
Hi,
Well it works on R14B02...
2011/5/3 Hanfei Shen <qqshfox@REDACTED>
> 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/fa8646da/attachment.htm>
More information about the erlang-questions
mailing list