Hi,<br><br>It does solve THIS problem, but not all... :(<br><br>Briefly, it does not support the mysql column type TEXT (the unicode one). So I made a patch to solve the unicode TEXT problem.<br><br>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.<br>
<br>Here is my git repository: <br>git://<a href="http://github.com/qqshfox/otp.git" target="_blank">github.com/qqshfox/otp.git</a><br><br>and branch:<br>odbc_sql_wlongvarchar_support<br><br>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. :)<br>
<br><br><div class="gmail_quote">2011/5/3 Ingela Andin <span dir="ltr"><<a href="mailto:ingela.andin@gmail.com" target="_blank">ingela.andin@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi!<br>
<br>
Ok problem solved then, from the releasenotes of odbc version 2.10.8:<br>
<br>
"1.3 ODBC 2.10.8<br>
Improvements and New Features<br>
<br>
*<br>
<br>
ODBC now handles the types SQL_WCHAR and SQL_WVARCHAR. Thanks to<br>
Juhani Ränkimies. ODBC also has a new connection option to return all<br>
strings as binaries and also expect strings to be binaries in the<br>
param_query function. These changes provides some unicode support.<br>
<br>
Own Id: OTP-7452"<br>
<br>
<br>
You had version 2.10.7 and latest version (in R14B02) is 2.10.10.<br>
<div><div></div><div><br>
Regards Ingela Erlang/OTP team - Ericsson AB<br>
<br>
<br>
2011/5/3 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>>:<br>
> Hi,<br>
><br>
> It returns:<br>
><br>
> {ok,[{"id",sql_integer},<br>
> {"code",{sql_wvarchar,255}},<br>
> {"compliment_desc",{sql_wvarchar,255}},<br>
> {"status_desc",{sql_wvarchar,255}},<br>
> {"parameter",{sql_wvarchar,255}},<br>
> {"cause",{sql_wvarchar,255}},<br>
> {"disabled",sql_tinyint}]}<br>
><br>
> I found that the macro SQL_WVARCHAR is defined in sqlucode.h.<br>
><br>
> 2011/5/3 Ingela Andin <<a href="mailto:ingela.andin@gmail.com" target="_blank">ingela.andin@gmail.com</a>><br>
>><br>
>> Hi!<br>
>><br>
>> Interesting what does odbc:describe_table/2 return on<br>
>> R14B02 ?<br>
>><br>
>> Regards Ingela Erlang/OTP team - Ericsson AB<br>
>><br>
>><br>
>> 2011/5/3 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>>:<br>
>> > Hi,<br>
>> ><br>
>> > Well it works on R14B02...<br>
>> ><br>
>> > 2011/5/3 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>><br>
>> >><br>
>> >> Hi,<br>
>> >><br>
>> >> The same on my windows 7 32 bit workstation.<br>
>> >><br>
>> >> Erlang R13B02 (erts-5.7.3)<br>
>> >><br>
>> >> 4> code:which(odbc).<br>
>> >> "d:/PROGRA~1/ERL57~1.3/lib/odbc-2.10.5/ebin/odbc.beam"<br>
>> >><br>
>> >> mysql odbc 5.1.8<br>
>> >><br>
>> >><br>
>> >><br>
>> >> I'm downloading the latest version of erlang (R14B02)...<br>
>> >><br>
>> >><br>
>> >> 2011/5/3 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>><br>
>> >>><br>
>> >>> Hi,<br>
>> >>><br>
>> >>> I had set up a fresh system and reproduce this problem successfully.<br>
>> >>> Here is something about my installations:<br>
>> >>><br>
>> >>> vm:<br>
>> >>> VirtualBox 4.0.6 r71416<br>
>> >>><br>
>> >>> gentoo:<br>
>> >>> livecd: install-amd64-minimal-20110317.iso<br>
>> >>> stage: stage3-amd64-20110428.tar.bz2<br>
>> >>> portage: portage-20110424.tar.bz2<br>
>> >>><br>
>> >>> I only installed:<br>
>> >>><br>
>> >>> dev-db/mysql-5.1.51<br>
>> >>> dev-db/myodbc-5.1.6<br>
>> >>> dev-lang/erlang-13.2.4 USE="kpoll odbc smp"<br>
>> >>><br>
>> >>> table schema:<br>
>> >>><br>
>> >>> CREATE TABLE `action` (<br>
>> >>> `id` int(11) NOT NULL AUTO_INCREMENT,<br>
>> >>> `code` varchar(255) NOT NULL,<br>
>> >>> `compliment_desc` varchar(255) NOT NULL DEFAULT '',<br>
>> >>> `status_desc` varchar(255) NOT NULL DEFAULT '',<br>
>> >>> `parameter` varchar(255) NOT NULL DEFAULT '',<br>
>> >>> `cause` varchar(255) NOT NULL DEFAULT '',<br>
>> >>> `disabled` tinyint(1) NOT NULL DEFAULT '0',<br>
>> >>> PRIMARY KEY (`id`),<br>
>> >>> UNIQUE KEY `index_action_on_cause` (`cause`)<br>
>> >>> ) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8;<br>
>> >>><br>
>> >>><br>
>> >>>> I see you are running on a 64 bit platform, maybe you can try it<br>
>> >>>> also<br>
>> >>>> on a 32 bit platform. Maybe<br>
>> >>>> you could try accessing the database from windows using the windows<br>
>> >>>> my-sql driver.<br>
>> >>><br>
>> >>> I reproduced the problem on a 32bit server just right now...<br>
>> >>><br>
>> >>> I'll try it on a windows machine asap.<br>
>> >>><br>
>> >>> 2011/5/3 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>>:<br>
>> >>> > Hi,<br>
>> >>> ><br>
>> >>> > Hmmmmmm... The unixODBC is already in my system, but I don't know<br>
>> >>> > how<br>
>> >>> > to use it...<br>
>> >>> ><br>
>> >>> > I will setup a vm with fresh system tomorrow to reproduce the<br>
>> >>> > problem...<br>
>> >>> ><br>
>> >>> > PS, could you figure out a name of ANOTHER ODBC-DRIVER which you<br>
>> >>> > mentioned?<br>
>> >>> ><br>
>> >>> > 2011/5/3 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>>:<br>
>> >>> >> Hi,<br>
>> >>> >><br>
>> >>> >> I've tried <a href="https://github.com/dizzyd/erlang-mysql-driver" target="_blank">https://github.com/dizzyd/erlang-mysql-driver</a>, it works.<br>
>> >>> >><br>
>> >>> >> I'm installing unixodbc... Wait a minute. Thnx...<br>
>> >>> >><br>
>> >>> >><br>
>> >>> >> 2011/5/3 Ingela Andin <<a href="mailto:ingela@erlang.org" target="_blank">ingela@erlang.org</a>>:<br>
>> >>> >>> Hi!<br>
>> >>> >>><br>
>> >>> >>> Well the best thing would be if you can provide me with a way to<br>
>> >>> >>> reproduce it.<br>
>> >>> >>> Do you have the possibility to try another odbc-driver with your<br>
>> >>> >>> database?<br>
>> >>> >>><br>
>> >>> >>> Regards Ingela Erlang/OTP team - Ericsson AB<br>
>> >>> >>><br>
>> >>> >>><br>
>> >>> >>> 2011/5/2 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>>:<br>
>> >>> >>>> Hi,<br>
>> >>> >>>><br>
>> >>> >>>> I had changed the type from SQLINTEGER to SQLSMALLINT, but the<br>
>> >>> >>>> problem<br>
>> >>> >>>> is still there...<br>
>> >>> >>>><br>
>> >>> >>>> (gdb) i func encode_data_type<br>
>> >>> >>>> File odbcserver.c:<br>
>> >>> >>>> static void encode_data_type(SQLSMALLINT, SQLINTEGER,<br>
>> >>> >>>> SQLSMALLINT,<br>
>> >>> >>>> db_state *);<br>
>> >>> >>>><br>
>> >>> >>>> Breakpoint 1, encode_data_type (sql_type=-9, size=255,<br>
>> >>> >>>> decimal_digits=0, state=0x7fffa5380210) at odbcserver.c:1396<br>
>> >>> >>>> 1396 switch(sql_type) {<br>
>> >>> >>>><br>
>> >>> >>>> Do you need any details?<br>
>> >>> >>>><br>
>> >>> >>>> 2011/5/2 Ingela Anderton Andin <<a href="mailto:ingela@erix.ericsson.se" target="_blank">ingela@erix.ericsson.se</a>>:<br>
>> >>> >>>>> Hi!<br>
>> >>> >>>>><br>
>> >>> >>>>> Hanfei Shen wrote:<br>
>> >>> >>>>>><br>
>> >>> >>>>>> Hi Ingela,<br>
>> >>> >>>>>><br>
>> >>> >>>>>> Did you mean the value of variable "sql_type"?<br>
>> >>> >>>>>><br>
>> >>> >>>>>><br>
>> >>> >>>>><br>
>> >>> >>>>> Yes.<br>
>> >>> >>>>><br>
>> >>> >>>>>> I tried to inspect it through gdb, setting a break on func<br>
>> >>> >>>>>> encode_data_type. And then I got:<br>
>> >>> >>>>>><br>
>> >>> >>>>>> Breakpoint 1, encode_data_type (sql_type=-9, size=255,<br>
>> >>> >>>>>> decimal_digits=0, state=0x7fffc657ec40) at odbcserver.c:1396<br>
>> >>> >>>>>> 1396 switch(sql_type) {<br>
>> >>> >>>>>><br>
>> >>> >>>>>><br>
>> >>> >>>>><br>
>> >>> >>>>> Well that is defenetly not a good value. Looking at the code<br>
>> >>> >>>>> just<br>
>> >>> >>>>> now<br>
>> >>> >>>>> I noticed that<br>
>> >>> >>>>><br>
>> >>> >>>>> encode_data_type defines sql_type as "SQLINTEGER sql_type" and<br>
>> >>> >>>>> it<br>
>> >>> >>>>> ought to be "SQLSMALLINT sql_type". If you change that does it<br>
>> >>> >>>>> fix<br>
>> >>> >>>>> your<br>
>> >>> >>>>> problem?<br>
>> >>> >>>>><br>
>> >>> >>>>> Regards Ingela Erlang/OTP team - Ericsson AB<br>
>> >>> >>>>><br>
>> >>> >>>>><br>
>> >>> >>>>><br>
>> >>> >>>>>> 2011/5/2 Ingela Andin <<a href="mailto:ingela@erlang.org" target="_blank">ingela@erlang.org</a>>:<br>
>> >>> >>>>>><br>
>> >>> >>>>>>><br>
>> >>> >>>>>>> Hi!<br>
>> >>> >>>>>>><br>
>> >>> >>>>>>> The erlang odbc application supports {sql-varchars, N} where<br>
>> >>> >>>>>>> N =<br>
>> >>> >>>>>>> 255<br>
>> >>> >>>>>>> in your case. The return values<br>
>> >>> >>>>>>> you get suggest that the odbc-driver you have returns an<br>
>> >>> >>>>>>> unexpected<br>
>> >>> >>>>>>> column type.<br>
>> >>> >>>>>>><br>
>> >>> >>>>>>> In the erlang odbc port program there is a switch that handles<br>
>> >>> >>>>>>> type<br>
>> >>> >>>>>>> when describing a coloumn that if we take out<br>
>> >>> >>>>>>> the handling code looks like this.<br>
>> >>> >>>>>>><br>
>> >>> >>>>>>> switch(sql_type) {<br>
>> >>> >>>>>>> case SQL_CHAR:<br>
>> >>> >>>>>>> case SQL_VARCHAR:<br>
>> >>> >>>>>>> case SQL_WCHAR:<br>
>> >>> >>>>>>> case SQL_WVARCHAR:<br>
>> >>> >>>>>>> case SQL_NUMERIC:<br>
>> >>> >>>>>>> case SQL_DECIMAL:<br>
>> >>> >>>>>>> case SQL_INTEGER:<br>
>> >>> >>>>>>> case SQL_TINYINT:<br>
>> >>> >>>>>>> case SQL_SMALLINT:<br>
>> >>> >>>>>>> case SQL_REAL:<br>
>> >>> >>>>>>> case SQL_FLOAT:<br>
>> >>> >>>>>>> case SQL_DOUBLE:<br>
>> >>> >>>>>>> case SQL_BIT:<br>
>> >>> >>>>>>> case SQL_TYPE_DATE:<br>
>> >>> >>>>>>> case SQL_TYPE_TIME:<br>
>> >>> >>>>>>> case SQL_TYPE_TIMESTAMP:<br>
>> >>> >>>>>>> case SQL_BIGINT:<br>
>> >>> >>>>>>> case SQL_BINARY:<br>
>> >>> >>>>>>> case SQL_LONGVARCHAR:<br>
>> >>> >>>>>>> case SQL_VARBINARY:<br>
>> >>> >>>>>>> case SQL_LONGVARBINARY:<br>
>> >>> >>>>>>> case SQL_INTERVAL_MONTH:<br>
>> >>> >>>>>>> case SQL_INTERVAL_YEAR:<br>
>> >>> >>>>>>> case SQL_INTERVAL_DAY:<br>
>> >>> >>>>>>> case SQL_INTERVAL_MINUTE:<br>
>> >>> >>>>>>> case SQL_INTERVAL_HOUR_TO_SECOND:<br>
>> >>> >>>>>>> case SQL_INTERVAL_MINUTE_TO_SECOND:<br>
>> >>> >>>>>>> case SQL_UNKNOWN_TYPE:<br>
>> >>> >>>>>>> default: /* Will probably never happen */<br>
>> >>> >>>>>>> ei_x_encode_atom(&dynamic_buffer(state),<br>
>> >>> >>>>>>> "ODBC_UNSUPPORTED_TYPE");<br>
>> >>> >>>>>>> break;<br>
>> >>> >>>>>>> }<br>
>> >>> >>>>>>><br>
>> >>> >>>>>>><br>
>> >>> >>>>>>> And you end up in the default clause which is very unexpected<br>
>> >>> >>>>>>> and<br>
>> >>> >>>>>>> the<br>
>> >>> >>>>>>> root to your problem. If you can figur out<br>
>> >>> >>>>>>> what your driver returns for the ODBC C-API function<br>
>> >>> >>>>>>> SQLDescribeCol we<br>
>> >>> >>>>>>> could know if the driver is at fault or if the odbc-port<br>
>> >>> >>>>>>> program<br>
>> >>> >>>>>>> is.<br>
>> >>> >>>>>>><br>
>> >>> >>>>>>> Regards Ingela Erlang/OTP team - Ericsson AB<br>
>> >>> >>>>>>><br>
>> >>> >>>>>>> 2011/5/1 Hanfei Shen <<a href="mailto:qqshfox@gmail.com" target="_blank">qqshfox@gmail.com</a>>:<br>
>> >>> >>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> Dear all,<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> I'm a newbie programming in erlang. When I used ODBC to<br>
>> >>> >>>>>>>> connect<br>
>> >>> >>>>>>>> to a<br>
>> >>> >>>>>>>> MySQL database through Connector/ODBC (MyODBC), I got some<br>
>> >>> >>>>>>>> error<br>
>> >>> >>>>>>>> when<br>
>> >>> >>>>>>>> calling odbc:sql_query(Conn, "SELECT * FROM action"):<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> {error,"Column type not supported"}<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> Then I call odbc:describe_table(Conn, "action"), it returned:<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> {ok,[{"id",sql_integer},<br>
>> >>> >>>>>>>> {"code",'ODBC_UNSUPPORTED_TYPE'},<br>
>> >>> >>>>>>>> {"compliment_desc",'ODBC_UNSUPPORTED_TYPE'},<br>
>> >>> >>>>>>>> {"status_desc",'ODBC_UNSUPPORTED_TYPE'},<br>
>> >>> >>>>>>>> {"parameter",'ODBC_UNSUPPORTED_TYPE'},<br>
>> >>> >>>>>>>> {"cause",'ODBC_UNSUPPORTED_TYPE'},<br>
>> >>> >>>>>>>> {"disabled",sql_tinyint}]}<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> And desc the table in mysql client:<br>
>> >>> >>>>>>>> mysql> desc action;<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> +-----------------+--------------+------+-----+---------+----------------+<br>
>> >>> >>>>>>>> | Field | Type | Null | Key | Default |<br>
>> >>> >>>>>>>> Extra<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> +-----------------+--------------+------+-----+---------+----------------+<br>
>> >>> >>>>>>>> | id | int(11) | NO | PRI | NULL |<br>
>> >>> >>>>>>>> auto_increment<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>> | code | varchar(255) | NO | | NULL |<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>> | compliment_desc | varchar(255) | NO | | |<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>> | status_desc | varchar(255) | NO | | |<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>> | parameter | varchar(255) | NO | | |<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>> | cause | varchar(255) | NO | UNI | |<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>> | disabled | tinyint(1) | NO | | 0 |<br>
>> >>> >>>>>>>> |<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> +-----------------+--------------+------+-----+---------+----------------+<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> It seems that Erlang ODBC driver does not support the mysql<br>
>> >>> >>>>>>>> type<br>
>> >>> >>>>>>>> varchar? How can I use the database without touching the db<br>
>> >>> >>>>>>>> schema?<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> Some details about the system env:<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> $ uname -a<br>
>> >>> >>>>>>>> Linux 2.6.36-gentoo-r8 #4 SMP Thu Apr 7 20:13:18 CST 2011<br>
>> >>> >>>>>>>> x86_64<br>
>> >>> >>>>>>>> Intel(R) Core(TM)2 Duo CPU E7500 @ 2.93GHz GenuineIntel<br>
>> >>> >>>>>>>> GNU/Linux<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> $ erl<br>
>> >>> >>>>>>>> Erlang R13B04 (erts-5.7.5) [source] [64-bit] [smp:2:2] [rq:2]<br>
>> >>> >>>>>>>> [async-threads:0] [kernel-poll:false]<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> $ emerge -vp erlang<br>
>> >>> >>>>>>>> dev-lang/erlang-13.2.4 USE="doc kpoll odbc smp ssl -emacs<br>
>> >>> >>>>>>>> -hipe<br>
>> >>> >>>>>>>> -java<br>
>> >>> >>>>>>>> -sctp -tk -wxwidgets"<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> eshell> code:which(odbc).<br>
>> >>> >>>>>>>> "/usr/lib64/erlang/lib/odbc-2.10.7/ebin/odbc.beam"<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> $ mysql -V<br>
>> >>> >>>>>>>> mysql Ver 14.14 Distrib 5.1.51, for pc-linux-gnu (x86_64)<br>
>> >>> >>>>>>>> using<br>
>> >>> >>>>>>>> readline 5.1<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> Server version: 5.1.51-log Gentoo Linux mysql-5.1.51<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> $ emerge -vp myodbc<br>
>> >>> >>>>>>>> dev-db/myodbc-5.1.6 USE="-debug -doc -qt4 -static"<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> Any help will be greatly appreciated. Thanks in advance.<br>
>> >>> >>>>>>>> Sorry for my poor English, I'm not a native...<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> Best,<br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>> --<br>
>> >>> >>>>>>>> Hanfei<br>
>> >>> >>>>>>>> _______________________________________________<br>
>> >>> >>>>>>>> erlang-questions mailing list<br>
>> >>> >>>>>>>> <a href="mailto:erlang-questions@erlang.org" target="_blank">erlang-questions@erlang.org</a><br>
>> >>> >>>>>>>> <a href="http://erlang.org/mailman/listinfo/erlang-questions" target="_blank">http://erlang.org/mailman/listinfo/erlang-questions</a><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>>>><br>
>> >>> >>>>>><br>
>> >>> >>>>>><br>
>> >>> >>>>><br>
>> >>> >>>>><br>
>> >>> >>>> _______________________________________________<br>
>> >>> >>>> erlang-questions mailing list<br>
>> >>> >>>> <a href="mailto:erlang-questions@erlang.org" target="_blank">erlang-questions@erlang.org</a><br>
>> >>> >>>> <a href="http://erlang.org/mailman/listinfo/erlang-questions" target="_blank">http://erlang.org/mailman/listinfo/erlang-questions</a><br>
>> >>> >>>><br>
>> >>> >>><br>
>> >>> >><br>
>> >>> ><br>
>> >>><br>
>> >><br>
>> ><br>
>> ><br>
>> > _______________________________________________<br>
>> > erlang-questions mailing list<br>
>> > <a href="mailto:erlang-questions@erlang.org" target="_blank">erlang-questions@erlang.org</a><br>
>> > <a href="http://erlang.org/mailman/listinfo/erlang-questions" target="_blank">http://erlang.org/mailman/listinfo/erlang-questions</a><br>
>> ><br>
>> ><br>
><br>
><br>
</div></div></blockquote></div><br>