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

[Carl McDade]

Thanks!

That got me far enough to frighten me to death . It appears that only
integer fields are supported:

4> Results = odbc:describe_table(Conn, "wp_posts").
{ok,[{"ID",'SQL_BIGINT'},
     {"post_author",'SQL_BIGINT'},
     {"post_date",'SQL_TYPE_TIMESTAMP'},
     {"post_date_gmt",'SQL_TYPE_TIMESTAMP'},
     {"post_content",'ODBC_UNSUPPORTED_TYPE'},
     {"post_title",'ODBC_UNSUPPORTED_TYPE'},
     {"post_category",sql_integer},
     {"post_excerpt",'ODBC_UNSUPPORTED_TYPE'},
     {"post_status",'ODBC_UNSUPPORTED_TYPE'},
     {"comment_status",'ODBC_UNSUPPORTED_TYPE'},
     {"ping_status",'ODBC_UNSUPPORTED_TYPE'},
     {"post_password",'ODBC_UNSUPPORTED_TYPE'},
     {"post_name",'ODBC_UNSUPPORTED_TYPE'},
     {"to_ping",'ODBC_UNSUPPORTED_TYPE'},
     {"pinged",'ODBC_UNSUPPORTED_TYPE'},
     {"post_modified",'SQL_TYPE_TIMESTAMP'},
     {"post_modified_gmt",'SQL_TYPE_TIMESTAMP'},
     {"post_content_filtered",'ODBC_UNSUPPORTED_TYPE'},
     {"post_parent",'SQL_BIGINT'},
     {"guid",'ODBC_UNSUPPORTED_TYPE'},
     {"menu_order",sql_integer},
     {"post_type",'ODBC_UNSUPPORTED_TYPE'},
     {"post_mime_type",'ODBC_UNSUPPORTED_TYPE'},
     {"comment_count",'SQL_BIGINT'}]}

Out of this list there are 8 varchar fields of varying lengths that
should be supported as strings according to the ODBC to Erlang map in
the docs.

http://erlang.org/doc/apps/odbc/databases.html#type

Looks like something is seriously broken or the docs need some
rewrites. Or is the implementation explicitly Microsoft SQL Server?





On Mon, May 11, 2009 at 3:01 PM, Robert Raschke <rtrlists@REDACTED> wrote:
>
> 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
>
>
>
>



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