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

[Robert Raschke]

On Mon, May 11, 2009 at 12:47 PM, Carl McDade <carlmcdade@REDACTED> wrote:

> Hello,
>
> Is this a problem with the driver (usually it is) or Erlangs use of the
> driver?
>
> 5> 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;"
> 6> {ok, Conn} = odbc:connect(ConnString, []).
> {ok,<0.43.0>}
> 7> Results = odbc:sql_query(Conn, "SELECT post_title FROM wp_posts LIMIT
> 5").
> {error,"Column type not supported"}
> 8> Results = odbc:sql_query(Conn, "SELECT post_name FROM wp_posts LIMIT
> 5").
> {error,"Column type not supported"}
> 9> Results = odbc:sql_query(Conn, "SELECT * FROM wp_posts LIMIT 5").
> {error,"Column type not supported"}
> 10> Results = odbc:sql_query(Conn, "SELECT option_name FROM wp_options").
> {error,"Column type not supported"}
> 11> Results = odbc:sql_query(Conn, "SELECT OPTION_NAME FROM wp_options").
> {error,"Column type not supported"}
>
>
> It seems to me that there is a flaw in what the ODBC driver is sending
> and what Erlang can understand. But in might also be a bug since I
> doubt many use MySQL in conjunction with Erlang. So maybe no one has
> tested this?
>
>
> Can someone toss me a bone?
>

The Erlang ODBC driver doesn't do BLOB or national character data types (I
think dates aren't in there either, but can't remember). When you issue SQL
queries, write them so that they return regular (char or varchar) strings or
numbers.

Assuming option_name is an nvarchar, you could try:
odbc:sql_query(Conn, "SELECT {fn CONVERT(option_name, SQL_VARCHAR)} FROM
wp_options").

One of the odbc functions (odbc:describe_table/2) gives you the types of the
columns in a table. I think that pointed out if something wasn't going to be
supported.

Hope this gets you a bit further,
Robby
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://erlang.org/pipermail/erlang-questions/attachments/20090511/c739d3d2/attachment.htm>