SQL Programming
Transferring large text data from VFP memo fields to SQL
Gravatar is a globally recognized avatar based on your email address. Transferring large text data from VFP memo fields to SQL
  Bob
  All
  Apr 23, 2020 @ 10:09am

I am trying to convert a VFP table containing memo fields that store data from a form containing 4 text imput fields. The data stored in the memo fields is RTF text and can range from 0 to 5,500 characters per memo field. The only thing I can see in SQL to store that much data is Varchar(max). The issues I am trying to figure out are:

  1. Would it be better to put 3 varchar(max) fields in one table or create a 2nd table that contains 1 varchar(max) field with 3 records for each record in the 1st data table?
  2. What would be the best way to get the data from the VFP memo field to the sql table? I'm pretty sure the data would be too large to be handled as strings.
    Thanks in advance for any suggestions that you can provide.
Gravatar is a globally recognized avatar based on your email address. re: Transferring large text data from VFP memo fields to SQL
  Rick Strahl
  Bob
  Apr 23, 2020 @ 11:56am

You can use varchar(max) (or nvarchar(max) if you plan to use this in other places and then set FoxPro to auto-convert to and from Unicode).

Just tried this out to be sure it actually works since there can be ODBC driver issues, but this works with a field called SpotifyUrl marked as nvarchar(max):

CLEAR
DO wwSql

LOCAL loSql as wwSql
loSql = CREATEOBJECT("wwSql")
? loSql.Connect("database=AlbumViewer")

PRIVATE pcValue
pcValue = REPLICATE("1234567890",1000000)  && 10 million chars
? loSql.Executenonquery("update Albums set SpotifyUrl = ?pcValue where Id = 3")  && .T.

? loSql.Execute("select SpotifyUrl from Albums where id = 3","TAlbums")

*** For some reason the data has a number of CHR(0)'s in the content (nchar conversion I'm guessing)
? LEN(Trim(TAlbums.SpotifyUrl,CHR(0)))

This works just fine.

Notice the issue with the CHR(0) values. I suspect this is related to the Unicode conversion back to string as these values are not showing up in the Unicode data if I query from say .NET. It doesn't actually hurt the FoxPro string behavior because CHR(0) is ignored. Odd... and I suspect a driver issue.

+++ Rick ---

© 1996-2020