Skip to Content.
Sympa Menu

freetds - [freetds] ODBC driver and GUIDs: Analysis, possible buglet, patch and hack.

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "David Fraser" <david AT abelon.com>
  • To: "FreeTDS" <freetds AT lists.ibiblio.org>
  • Subject: [freetds] ODBC driver and GUIDs: Analysis, possible buglet, patch and hack.
  • Date: Fri, 3 Oct 2003 16:05:17 +0100

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




  • [freetds] ODBC driver and GUIDs: Analysis, possible buglet, patch and hack., David Fraser, 10/03/2003

Archive powered by MHonArc 2.6.24.

Top of Page