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:
- 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?
- 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.
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 ---