[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Ingela Andin
ingela.andin@REDACTED
Tue May 3 16:29:25 CEST 2011
Hi!
Ok problem solved then, from the releasenotes of odbc version 2.10.8:
"1.3 ODBC 2.10.8
Improvements and New Features
*
ODBC now handles the types SQL_WCHAR and SQL_WVARCHAR. Thanks to
Juhani Ränkimies. ODBC also has a new connection option to return all
strings as binaries and also expect strings to be binaries in the
param_query function. These changes provides some unicode support.
Own Id: OTP-7452"
You had version 2.10.7 and latest version (in R14B02) is 2.10.10.
Regards Ingela Erlang/OTP team - Ericsson AB
2011/5/3 Hanfei Shen <qqshfox@REDACTED>:
> Hi,
>
> It returns:
>
> {ok,[{"id",sql_integer},
> {"code",{sql_wvarchar,255}},
> {"compliment_desc",{sql_wvarchar,255}},
> {"status_desc",{sql_wvarchar,255}},
> {"parameter",{sql_wvarchar,255}},
> {"cause",{sql_wvarchar,255}},
> {"disabled",sql_tinyint}]}
>
> I found that the macro SQL_WVARCHAR is defined in sqlucode.h.
>
> 2011/5/3 Ingela Andin <ingela.andin@REDACTED>
>>
>> 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