FreeTDS Development Group

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

Howdy FreeTDS folks,

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.

SQLBindParameter
================
Mixed results here.

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