[erlang-questions] [erlang-bugs] erlang/odbc <-> ms-sql error with 'uniqueidentifiers' (aka GUIDs): ODBC_UNSUPPORTED_TYPE
Ingela Andin
ingela.andin@REDACTED
Tue Dec 13 11:47:10 CET 2011
I think that you should only need the #if-def in decode_params that
should return FALSE if
(ODBCVER >= 0x0350) is not fullfilled and erlang will get a
"param_badarg"-error.
Regards Ingela Erlang/OTP - team Ericsson AB
2011/12/12, Boris Mühmer <boris.muehmer@REDACTED>:
> 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
> _______________________________________________
> erlang-questions mailing list
> erlang-questions@REDACTED
> http://erlang.org/mailman/listinfo/erlang-questions
>
More information about the erlang-questions
mailing list