[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Hanfei Shen
qqshfox@REDACTED
Tue May 3 19:07:46 CEST 2011
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
> >> >
> >> >
> >
> >
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://erlang.org/pipermail/erlang-questions/attachments/20110504/70078268/attachment.htm>
More information about the erlang-questions
mailing list