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

[Ingela Anderton Andin]

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
>
>