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