LPM: Victory over VFP ODBC! (almost)

[Wesley]

David Hempy wrote:
> 
> 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:

[snip]
 
> 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:
 
[snip]
 
> ...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  |-(
 
Ok, so update statements don't seem to work.  Does VFP have any kind of
bulk import facility?  Would it be possible to write the new records to
either a text file or to a different database's temp table or somewhere,
then bulk import them into VFP?  This is a kludgy solution at best and
has several possible pitfalls.  The import might balk at overwriting
records with the same key; if so, you might have to delete the old
record(s) before inserting the new one(s).  If you do that, make sure
you have some sort of transaction in place so you can roll back the
deletion if the import fails for some reason.  A bigger stretch would be
to see if VFP has a blob or binary datatype, and if so, see if storing
stuff that way would still let you manipulate the data you need to.  It
would probably get around the string literal limitation (I hope), but
might introduce other problems.

HTH.  Let us know when you have a final solution; I'd like to learn what
winds up working.

-- 
Wes Sheldahl
wsheldahl at qx.net