[erlang-questions] [erlang-bugs] erlang/odbc <-> ms-sql error with 'uniqueidentifiers' (aka GUIDs): ODBC_UNSUPPORTED_TYPE

[Boris Mühmer]

After switching to the "binary" version I did some tests with
parameterized queries and updates.

The following statements do work on my setup:
    odbc:start().
    ConnString = "DSN=erltest;UID=erluser;PWD=erlpass".
    {ok, Ref} = odbc:connect(ConnString, []).
    odbc:describe_table(Ref, "account").
    odbc:sql_query(Ref, "SELECT * FROM account").
    Result = odbc:sql_query(Ref, "SELECT * FROM account WHERE
firstname = 'Boris'").
    {selected, _, [Row]} = Result.
    {Id, _, _, _, _, _, _, _} = Row.
    odbc:param_query(Ref, "SELECT * FROM account WHERE id = ?",
[{sql_guid, [Id]}]).
    odbc:param_query(Ref, "UPDATE account SET lastname = 'Muehmer'
WHERE id = ?", [{sql_guid, [Id]}]).
    odbc:disconnect(Ref).
    odbc:stop().

The current changes are "visible" in my github otp fork "odbc_SQL_GUID_fix":
    https://github.com/bsmr/otp/compare/master...odbc_SQL_GUID_fix

Primary things to do: a complete CRUD (unit-)test.

Current open issues: in C I added preprocessor macros like "#if
(ODBCVER >= 0x0350)". Should I add similar statements to the Erlang
code? Is there a non-manual way to keep C-header files and erlang code
in-sync?


Regards,
Boris

2011/12/12 Boris Mühmer <boris.muehmer@REDACTED>:
> I did a quick test using the "binary" en-/decode routines: actually
> *I* prefer the results to my first attempt! Thank You very much for
> Your suggestions Hynek!
>
> I will do (or add) some more (unit-)tests, before I will submit a
> patch. Hopefully I don't run into too much trouble, now.
>
>
> Regards,
> Boris
>
>
>
> 2011/12/12 Hynek Vychodil <hynek@REDACTED>:
>> Hi,
>>
>> So in other words keep it in binary and split as needed?
>>
>> ei_x_encode_binary(&dynamic_buffer(state), (const void
>> *)column.buffer, sizeof(SQLGUID));
>>
>> and in erlang you can do what you want. I think GUID is mostly used as
>> opaque reference like ref in erlang so I would also prefer let it be
>> as binary. It is simple, fast and least memory consuming.
>>
>> Regards
>> Hynek
>>
>> On Mon, Dec 12, 2011 at 12:18 PM, Ingela Anderton Andin
>> <ingela@REDACTED> wrote:
>>> Hi!
>>>
>>> I probably  am not the best person to comment on this as I do not use
>>> SQL-databases very much and do not know how GUIDs are used.  Maybe someone
>>> else can help me out? Generally if you should convert  the value it would
>>> probably be easier to do the conversion in the erlang-code and always let
>>> the c-code get what it expects.
>>>
>>> Regards Ingela Erlang/OTP-team  -Ericsson AB
>>>
>>>
>>> Boris Mühmer wrote:
>>>>
>>>> Well, maybe I will be able to do such a user-contribution, because
>>>> either I get it running, find another way to access the Microsoft SQL
>>>> server from Erlang, or drop Erlang/OTP (nothing I want to do).
>>>>
>>>> I started to have a closer look at "odbcserver.c". My first success
>>>> was to get a SELECT query with a GUID "working".
>>>>
>>>> My question now is: what would be the "best" Erlang representation for a
>>>> GUID?
>>>>
>>>> The C structure behind a GUID is (found in "sqltypes.h"):
>>>>    typedef struct  tagSQLGUID
>>>>    {
>>>>        DWORD Data1;
>>>>        WORD Data2;
>>>>        WORD Data3;
>>>>        BYTE Data4[ 8 ];
>>>>    } SQLGUID;
>>>>
>>>> For my 1st hack I used the following statements to transform this
>>>> struct to an Erlang tuple:
>>>>        psg = (SQLGUID*)column.buffer;
>>>>        ei_x_encode_tuple_header(&dynamic_buffer(state), 4);
>>>>        ei_x_encode_ulong(&dynamic_buffer(state), psg->Data1);
>>>>        ei_x_encode_ulong(&dynamic_buffer(state), psg->Data2);
>>>>        ei_x_encode_ulong(&dynamic_buffer(state), psg->Data3);
>>>>        ei_x_encode_tuple_header(&dynamic_buffer(state), 8);
>>>>        for(i=0; i<8; i++){
>>>>            ei_x_encode_char(&dynamic_buffer(state), psg->Data4[i]);
>>>>        }
>>>>
>>>> For example, this conversion results in:
>>>>    19BCB12A-E6D1-4E58-A841-30F794C614FA =>
>>>>        {{431796522,59089,20056,{168,65,48,247,148,198,20,250}}
>>>>
>>>>    703F9C47-F1B9-407E-847D-34840AC1E346 =>
>>>>        {1883216967,61881,16510,{132,125,52,132,10,193,227,70}}
>>>>
>>>> Somewhat odd to see how the C struct looks and how Microsoft "displays" a
>>>> GUID.
>>>>
>>>> Should the Erlang representation be changed? Currently, I don't care
>>>> about how it is displayed; of course it would be nice to see something
>>>> more similar to the Microsoft way, on the other hand, it must't be
>>>> converted back in the c-driver. Would be a bit-string better? Or just
>>>> use a 128 Bit value?
>>>>
>>>> I really like to get some input on this.
>>>>
>>>>
>>>>  - boris
>>>>
>>>>
>>>> 2011/12/9 Ingela Anderton Andin <ingela@REDACTED>:
>>>>
>>>>>
>>>>> Hi!
>>>>>
>>>>> It seems to be a missing feature. Support for the data_type SQL_GUID is
>>>>> not
>>>>> implemented.
>>>>> I do not think it would be all that hard to implement but will not be
>>>>> highly
>>>>> prioritized by Ericsson.
>>>>> Maybe somebody would like to make a user-contribution?! You should start
>>>>> looking at the
>>>>> funtion encode_data_type in odbcserver.c
>>>>>
>>>>> Regards Ingela Erlang/OTP team - Ericsson AB
>>>>>
>>>>> Boris Mühmer wrote:
>>>>>
>>>>>>
>>>>>> I try to connect to one of our companies Microsoft SQL databases using
>>>>>> Erlang/OTP R14B04 running on an Ubuntu 10.04 64 Bit installation
>>>>>> (using the FreeTDS drivers from the Ubuntu repositories).
>>>>>>
>>>>>> The problem I have is, that the Erlang ODBC module can't handle
>>>>>> 'uniqueidentifier' (aka GUID) fields in tables. For example a 'select
>>>>>> *' doesn't work, but when I only select the non-uniqueidentifier
>>>>>> fields from the same table it works.
>>>>>>
>>>>>> On the other hand, when I use the "isql" (commandline-)tool with the
>>>>>> same ODBC connection, I don't get any errors.
>>>>>>
>>>>>> So I would say that something within the Erlang ODBC module is broken
>>>>>> (I believe it is in "odbcserver.c").
>>>>>>
>>>>>> Is this a known bug, or did I miss anything?
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>> Boris
>>>>>> _______________________________________________
>>>>>> erlang-bugs mailing list
>>>>>> erlang-bugs@REDACTED
>>>>>> http://erlang.org/mailman/listinfo/erlang-bugs
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> erlang-bugs mailing list
>>>> erlang-bugs@REDACTED
>>>> http://erlang.org/mailman/listinfo/erlang-bugs
>>>>
>>>>
>>>
>>>
>>> _______________________________________________
>>> erlang-bugs mailing list
>>> erlang-bugs@REDACTED
>>> http://erlang.org/mailman/listinfo/erlang-bugs