[erlang-questions] MySQL and Erlang: ODBC , column type error

[Carl McDade]

I am beginning to think this is a bug because the underscore does not
have to be in the query. Its appearance in the result causes things to
crash.

5> Results = odbc:sql_query(Conn, "SELECT * FROM employee").
** exception error: no match of right hand side value
                    {selected,["NR","FIRSTNAME","LASTNAME","GENDER","town",
                               "tel_no"],
                              [{1,"carl","mcdade","m","hayward","23562366"},
                               {2,"john","mason","m","san pablo","55214569"}]}
6> Results = odbc:sql_query(Conn, "SELECT * FROM employee").
** exception error: no match of right hand side value {error,
                                                       connection_closed}
7> Results = odbc:sql_query(Conn, "SELECT * FROM employee").
** exception error: no match of right hand side value {error,
                                                       connection_closed}
8>

The above causes werl.exe to crash and have to be restarted to get any
response. You can only get a query response once before the underscore
kills things.

After removing the underscore from the column name the query runs as
it should and as many times as it is called.

Eshell V5.7.1  (abort with ^G)
1> application:start(odbc).
ok
2> ConnString = "Driver={MySQL ODBC 5.1
Driver};Server=localhost;Database=wordpress;User=root;Password=;Option=3;".
"Driver={MySQL ODBC 5.1
Driver};Server=localhost;Database=wordpress;User=root;Password=;Option=3;"
3> {ok, Conn} = odbc:connect(ConnString, []).
{ok,<0.40.0>}
4> Results = odbc:sql_query(Conn, "SELECT * FROM employee").
{selected,["NR","FIRSTNAME","LASTNAME","GENDER","town",
           "telno"],
          [{1,"carl","mcdade","m","hayward","23562366"},
           {2,"john","mason","m","san pablo","55214569"}]}
5> Results = odbc:sql_query(Conn, "SELECT * FROM employee").
{selected,["NR","FIRSTNAME","LASTNAME","GENDER","town",
           "telno"],
          [{1,"carl","mcdade","m","hayward","23562366"},
           {2,"john","mason","m","san pablo","55214569"}]}
6> Results = odbc:sql_query(Conn, "SELECT * FROM employee").
{selected,["NR","FIRSTNAME","LASTNAME","GENDER","town",
           "telno"],
          [{1,"carl","mcdade","m","hayward","23562366"},
           {2,"john","mason","m","san pablo","55214569"}]}
7>

On Mon, May 11, 2009 at 5:39 PM, Carl McDade <carlmcdade@REDACTED> wrote:
> Hmm,
>
> It appears that the underscore character in the database scheme names
> are the problem. Easy enough to fix but Erlang does not seem to have
> any explicit syntax for escaping these names.
>
> /Carl
>
> On Mon, May 11, 2009 at 5:19 PM, Carl McDade <carlmcdade@REDACTED> wrote:
>> Well, after having werl.exe crash a hundred times (overly sensitive I think)
>>
>> I got good response by creating a  table and then accessing them. But
>> Erlang odbc will not access any resource created outside of the
>> environment or in another command line utility. Strange and strange
>> yet.
>>
>> Eshell V5.7.1  (abort with ^G)
>> 1> application:start(odbc).
>> ok
>> 2> ConnString = "Driver={MySQL ODBC 5.1
>> Driver};Server=localhost;Database=wordpress;User=root;Password=;Option=3;"
>> 2>
>> 2> .
>> "Driver={MySQL ODBC 5.1
>> Driver};Server=localhost;Database=wordpress;User=root;Password=;Option=3;"
>> 3> {ok, Conn} = odbc:connect(ConnString, []).
>> {ok,<0.40.0>}
>> 4> odbc:sql_query(Conn, "CREATE TABLE EMPLOYEE (NR integer,FIRSTNAME
>> char varying(20), LASTNAME  char varying(20), GENDER char(1),PRIMARY
>> KEY(NR))").
>> {updated,0}
>> 5> Results = odbc:sql_query(Conn, "SELECT * FROM employee").
>> {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],
>>          [{1,"carl","mcdade","m"},{2,"john","mason","m"}]}
>> 6>
>>
>> On Mon, May 11, 2009 at 4:42 PM, Robert Raschke <rtrlists@REDACTED> wrote:
>>>
>>> On Mon, May 11, 2009 at 3:20 PM, Carl McDade <carlmcdade@REDACTED> wrote:
>>>>
>>>> On Mon, May 11, 2009 at 4:19 PM, Carl McDade <carlmcdade@REDACTED> wrote:
>>>> Well, MySQL only allows CONVERT to certain types. The relavent one
>>>> here being CHAR. But Erlang does not accept this either. VARCHAR is a
>>>> basic in MySQL but Erlang refuses this data type even when I forced
>>>> the database into ANSI only mode. But it works fine when using DECIMAL
>>>> or CONVERT to DECIMAL.
>>>>
>>>> Weird.
>>>
>>> Weird indeed. Maybe MySQL doesn't do ODBC conversions properly ... ah, and a
>>> bit of searching found this: http://bugs.mysql.com/33808
>>>
>>> So, it looks like MySQL and ODBC don't really play together very nicely.
>>> Sigh. Especially since MySQL appears to have quite a weird approach to
>>> string columns (at least when coming from big name databases).
>>>
>>> Not sure what else to recommend now. ODBC looks like a non-starter for
>>> MySQL. :-(
>>>
>>> Maybe someone has made a specific MySQL to Erlang binding? Or with a bit of
>>> blood, sweat and tears you could go via Java and JDBC, yuck.
>>>
>>> Robby
>>>
>>> PS Makes me wonder if it's a semi-religious thing, ODBC coming from
>>> Microsoft, MySQL being OSS not wanting to support the evil empire, yada,
>>> yada, yada ...
>>>
>>>
>>
>>
>>
>> --
>> Carl McDade
>> Content Management Systems Consultant
>> www.hiveminds.co.uk
>> ________________________
>>
>
>
>
> --
> Carl McDade
> Content Management Systems Consultant
> www.hiveminds.co.uk
> ________________________
>



-- 
Carl McDade
Content Management Systems Consultant
www.hiveminds.co.uk
________________________