FreeTDS Development Group

["David Fraser" <david AT abelon.com>]

Hi all,

I posted this on Friday and have gotten no response as yet. My fault
for posting it on a Friday afternoon perhaps! :-)

I was hoping someone would say - yes that's a bug/omission,
yes the patch will work, no it won't, etc.

Take two, then - re-formatted slightly since there was some
horrible linewrapping...


I've been taking a look at GUIDs and how to use them with the ODBC
interface of FreeTDS - specifically in combination with
SQLBindParameter.

Unfortunately, MS documentation of this and how the many parameters to
SQLBindParameter should be set isn't terribly clear. The main complication
being the choice of formats for a GUID. (i.e. 36 char string or 16 byte
SQLGUID struct).

To figure this out I wrote some test code which I tried against both
FreeTDS (on Linux) and against the MS ODBC DLL on Windows (as a
reference implementation).

So, here's what works and what doesn't on both.

(I also checked out SQLGetData() and it worked perfectly on both platforms
 returning the SQLGUID struct or the 36 char string when SQL_GUID or
 SQL_CHAR was specified respectively).

Perhaps some of the readers could tell me if any of the approaches
below is actually preferred or despised in practice.

1) This works on both platforms...
    SQLINTEGER lenOrInd = SQL_NTS;
    SQLCHAR *guidstr = (SQLCHAR*) "12345678-1234-1234-1234-123456789012";
    SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 36,
                      0, guidstr, 0, &lenOrInd );

2) Meanwhile, the following works on Microsoft, but not on FreeTDS.
    SQLINTEGER lenOrInd = SQL_NTS;
    SQLCHAR *guidstr = (SQLCHAR*) "12345678-1234-1234-1234-123456789012";
    SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 0,
                      0, guidstr, 0, &lenOrInd );

    - HOWEVER:  FreeTDS can be made to work for this case, with a one
                line patch, if this syntax usage for GUIDs makes sense.
                The patch is to add "case SQL_GUID:" into the switch
                statement in prepare_query.c's _need_comma().

                Anyone want to verify the patch and stick it in CVS if it
                is merited?

3) The following works on only the Microsoft platform - converting the C
   structure form of GUID into a bound parameter... although I managed to
   hack FreeTDS grossly to get it working (more below):
    SQLINTEGER lenOrInd = 0;
    SQLGUID guid; /* ... initialised to some value. */
    SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_GUID, SQL_GUID, 16,
                      0, &guid, 0, lenOrInd );

    - QUESTION/ANALYSIS:
        Is this a known omission, on someone's todo list, or is it for some
        reason, considered bad practice to invoke SQLBindParameter for GUIDs
        in this way?
        Or am I supplying the wrong parameter somewhere?

        In the above case, it bombs out after discovering that lenOrInd is
        zero.

        I also tried with lenOrInd set to 16 and with the second last
        parameter also 16. No joy. This time, it gets as far as
        _get_sql_textsize() whose switch statement doesn't list SQL_GUID
        so it bombs out with -1.
        I then tried hacking in a new case for SQL_GUID into
       _get_sql_textsize():
                    case SQL_GUID:
                        sql_len = 21;
                        break;

        This actually works perfectly allowing me to insert GUIDs in rows,
        that can then be successfully retrieved.
        But it is a hack, because I have no real idea what sql_len should
        be, or if this behaviour is safe. 21 was found by trial and error.
        Any less than 21 and the malloc/heap gets horribly broken.
        I note that the code ends up down in tds_convert_unique() which
        sounds about right.

        Perhaps one of the FreeTDS pros can check this out and clarify
        whether this hack is safe, what len should really be and/or what
        parameters should be provided to SQLBindParameter for GUIDs.

Opinions, views, etc. welcomed.

Cheers,

David Fraser

--
Consultant, Abelon Systems Ltd, UK
http://www.abelon.com