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

[Boris Mühmer]

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