[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Ingela Andin
ingela@REDACTED
Wed May 4 10:29:26 CEST 2011
Hi!
It looks like a good start :-) Your patch does not address
parameterized queries, it would be nice if it did, even if
it is interesting even without that support. You also need to
contribute test cases and updated documentation.
Please follow the instructions from:
https://github.com/erlang/otp/wiki/submitting-patches
Regards Ingela Erlang-OTP team - Ericsson AB
2011/5/3 Hanfei Shen <qqshfox@REDACTED>:
> Hi,
>
> It does solve THIS problem, but not all... :(
>
> Briefly, it does not support the mysql column type TEXT (the unicode one).
> So I made a patch to solve the unicode TEXT problem.
>
> Thanks to Juhani Ränkimies, most of hard work has been done by him, I just
> add some lines to the file odbcserver.c and it works well for me.
>
> Here is my git repository:
> git://github.com/qqshfox/otp.git
>
> and branch:
> odbc_sql_wlongvarchar_support
>
> This is the first time I submit a patch to a open source project, I'd
> greatly appreciate if you could kindly review my code. :)
>
>
> 2011/5/3 Ingela Andin <ingela.andin@REDACTED>
>>
>> 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