LPM: Victory over VFP ODBC! (almost)

David Hempy hempy at ket.org
Thu Feb 10 23:37:52 CST 2000


You all have heard me whining recently over my ODBC woes connecting to a
Visual FoxPro data source and writing Memo fields over 255 characters.
I've reached a point where I cannot ignore this limitation any more, so I
sat out to slay the beast.  The good news is...VICTORY!

It turns out there is a limitation (I call it a bug) in VFP's ODBC driver
of 254 characters for string literals.  *Strings* can be longer, but not
string literals.  So you can't say something like:

update sonnet set verse='O Romeo, Romeo, wherefore art thou Romeo?  Deny
thy father and refuse they name, Or if thou wilt not, be but sworn my
love, And I''ll no longer be a Capulet... ''Tis but thy name that is my
enemy... What''s Montague?   It is not hand, nor foot, Nor arm, nor face,
nor any other part Belonging to a man. What''s in a name? That which we
call a rose By any other word would smell as sweet.';

...because the string literal contained within the single quotes is 388
characters long.  (In SQL, doubled single quotes represent an apostrophe
within the string.)

However, you *can* do this:

update sonnet set verse='O Romeo, Romeo, wherefore art thou Romeo?  Deny
thy father and refuse they name, Or if thou wilt not'+
', be but sworn my  love, And I''ll no longer be a Capulet... ''Tis but thy
name that is my enemy... Wh'+
'at''s Montague?   It is not hand, nor foot, Nor arm, nor face, nor any
other part Belonging to a man'+
'. What''s in a name? That which we call a rose By any other word would
smell as sweet.';


I have updated the verse field to be a string made of the concatenation of
several smaller string literals, each of which is 100 characters.  Note it
is not a limitation of the string, but of the string *literal*.  Go figure!


So I've written a subroutine to break it up, and also escape apostrophes in
the string at the same time:

>sub VFPQuoteStr {
>	my $splitstring = shift;
>
>		# SQL-escape all apostrophes:
>	$splitstring =~ s/'/''/g;
>
>		# insert "'+'" every N characters:
>	$splitstring =~ s/(.{250})/$1'+'/gs;
>
>		# if the original string was a multiple of 250 characters,
>		# an error is generated when the string ends in "...blah'+''"
>		# Trim off a potential trailing null string:
>	$splitstring =~s/'\+'$//;
>
>		# encase the final product in single quotes:
>	return "'$splitstring'";
>}


...grr...Now for the bad news...

Okay, so I start to really load it down after writing this much of my
victory memoirs.  All my tests have been 300-400 characters up until now.
I start throwing several-KB values at it, and start getting runtime
exceptions again!  A little playing around, and the closest I can narrow it
down is "somewhere around 500 bytes".  Depending on how I split it, it
starts crashing between around 450 to 510 characters.  I'm pretty sure it's
not a 512 byte limit, not of the resulting string nor of the "concatenating
strings".

Ugh.  Just when I had popped the cork.

Okay...I concede for the evening...I'm going home.  Anyone have any ideas?


-dave  |-(


ps.  If anyone has any cool improvements to my VFPQuoteStr() sub, I'd love
to see them.


--
David Hempy
Internet Database Administrator
Kentucky Educational Television




More information about the Lexington-pm mailing list