[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Ingela Andin
ingela.andin@REDACTED
Tue May 3 14:52:49 CEST 2011
Hi!
Interesting what does odbc:describe_table/2 return on
R14B02 ?
Regards Ingela Erlang/OTP team - Ericsson AB
2011/5/3 Hanfei Shen <qqshfox@REDACTED>:
> 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
>>> >>>>
>>> >>>
>>> >>
>>> >
>>>
>>
>
>
> _______________________________________________
> erlang-questions mailing list
> erlang-questions@REDACTED
> http://erlang.org/mailman/listinfo/erlang-questions
>
>
More information about the erlang-questions
mailing list