[erlang-questions] Erlang and MySQL: ODBC, Column type not supported
Hanfei Shen
qqshfox@REDACTED
Tue May 3 16:10:37 CEST 2011
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/20110503/43cfc864/attachment.htm>
More information about the erlang-questions
mailing list