[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Hanfei Shen
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-q...@erlang.org
http://erlang.org/mailman/listinfo/erlang-questions
Parnell Springmeyer
Hash: SHA1
I would recommend using dizzyd's MySQL module:
https://github.com/dizzyd/erlang-mysql-driver
It works very well for me!
Hanfei Shen <qqs...@gmail.com> writes:
- --
Parnell "ixmatus" Springmeyer (http://ixmat.us)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
iQEcBAEBAgAGBQJNvZ9BAAoJEPvtlbpI1POLn3gH/3WPPtfTUSdtEdGCI/kg0Gj9
BOB4vLBId+s5BpFPcm90ZBCkUZccKqT6LTCxMLBZ99w8hlIGXPKafCVO5AMDCZeC
/2ZMPW2xHHfseixtYjATj5XG7/6SR1cTsrVhjxvdOPnFNxSXDcW4eZTbJYTWf8Es
jSZE6hDTg4DoHLOvOc3UeSw8QiBmLD1nFB0EQM/dcTHuGifygDXztr/0TBBr5VHq
yjd2V1BMW0WeSFrHT+mAO8Oh+gtleh0N06R1a5EMCtBDIGmrmy56DciQQirdAjbT
NXlpYjHXCbjka1KM0/ov9UWIgw6fhs9EsrKgXpD8pBw0cnYq2Hs7oy5v0M+A0BQ=
=Pm6l
-----END PGP SIGNATURE-----
Ingela Andin
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 <qqs...@gmail.com>:
Hanfei Shen
The module which you recommended does work correctly, but I wonder
why erlang odbc/MyODBC does not support such a common column type...
Thank you very much indeed.
2011/5/2 Parnell Springmeyer <ixm...@gmail.com>:
Hanfei Shen
Did you mean the value of variable "sql_type"?
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) {
2011/5/2 Ingela Andin <ing...@erlang.org>:
Ingela Anderton Andin
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?
Hanfei Shen
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 <ing...@erix.ericsson.se>:
Ingela Andin
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 <qqs...@gmail.com>:
Hanfei Shen
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 <ing...@erlang.org>:
Hanfei Shen
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 <qqs...@gmail.com>:
Hanfei Shen
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.
Hanfei Shen
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)...
Hanfei Shen
Well it works on R14B02...
Ingela Andin
Interesting what does odbc:describe_table/2 return on
R14B02 ?
Regards Ingela Erlang/OTP team - Ericsson AB
Hanfei Shen
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.
Ingela Andin
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.
Hanfei Shen
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. :)
Ingela Andin
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