From: David Hayes
Newsgroup: microsoft.public.fox.vfp.queries-sql
Date: Thursday, July 15, 1999 5:03 PM
Several participants in this newsgroup have reported their difficulties in writing SQL code that would properly create Memo fields where some, most or all of the records had more than 255 characters in a single Memo field. I have here a solution. These procedures were used by me to create a database with 8893 records. The work was entirely automated, requiring no intervention in FoxPro (except to issue new "Do" commands when one file was finished and I fed in another file of no more than 64K).
The problems that others have had may be gauged by these earlier posts:
Titling his post "SQL truncating memo field" on 1998/07/08, Byron <bcarroll3@erinet.com> wrote, "I have a 5.0 application using a remote view to a SQL Server 6.5 table. When a memo field in the view gets near or over 255 chars, the ODBC driver is giving an error message about incorrect conversion of field types. The corresponding field on SQL is a text type. The table was upsized, originally. My SQL driver says that it is v2.65.0240, dated 12/17/96. Would using a newer version ODBC solve the problem?"
Earlier today, at least two posts contained in the title "Memo fields." (In one case, "Memo fields again.")
In "Visual Fox & ASP & memo field," in February 1999, Pavel Hladky wrote in message <79re1m$865$1@cbu.pvtnet.cz>, "Is there any way how to write a long string ( >255 chars ) to memo field ? I use ASP with Visual FoxPro and I use SQL commands. I want to add a record with memo and it is OK if the string (to memo) is small (<255 chars)." In response, Date:1999/02/15, Dave <DSummZZZ@ix.netcom.com> said: "Try using APPEND MEMO memofield FROM textfile Where textfile has already been created as a big string from an ASP file." This solution, however, spells a nightmare workload of creating individual files where there is a tremendous record count. It doesn't help if some of the other fields being entered are short runs not requiring the use of APPEND.
Another solution offered did not take into account the problem of different procedures being needed depending upon whether there are more or fewer than 255 characters on a given record. (When there are numerous records, some likely will go over this number, others under.)
In "Re: Visual Fox & ASP & memo field," Date:1999/02/28, Michael Drozdov <Drozdov@ics.perm.su> wrote: "In MSDN I have seen, that from ODBC-API in case of length > 255 it is necessary to use function SQLPutData(), I shall try." That didn't help me.
In "sqlexec and memo field," Date:1999/07/01, Mike McCarthy <mike_mccarthy@adelphia.net> asked, "I tried to grab a varchar field from a DB2 backend using SQLEXEC() in VFP 5.0a. It didn't return any of the memo field data from the backend. However, when I created a Remote VFP 5.0a view and checked the "Fetch Memo" option I was able to retrieve the data. Does anyone know how I can get the memo field data using SQLEXEC() ? ”
The first section of FoxPro-like code given below represents how the quote would be executed *IF* FoxPro had not trouble with differing lengths of Memo fields (<255 vs. >255). For those of us who begin with text-based tables, old word-processing files, or databases from other applications, the following can be created quickly from the earlier files, merely having some simple macros insert commas, brackets (or quote marks) and "insert into" codes.
All of the code in this microsoft.public.fox.vfp.queries-sql post assume the database has the following fields:
1. unique record number
2-4. indexing numbers
5. four-digit number unique to each title
6. title of work (a redundant piece of information, because item 5 represents the same
information; eventually, upon confirmation that data is good, this field will be dropped)
7. quotation from literature (it's this last field that goes into a Memo field and thus may exceed
255 characters) "Quotes" is the name of the database. "quote_desc" is the
specific field to contain the long value. (Again, this is 7th in the sequence of fields.)
Again, the following code illustrates the PROBLEM:
* * * * * * * * * * * *
insert into quotes values(101001,2,6,15,1090,[HAMLET],[Alas, poor Yorick! I knew him, Horatio; a fellow of infinite jest, of most excellent fancy. He hath borne me on his back a thousand times. And now, how abhorred in my imagination it is! My gorge rises at it. Here hung those lips that I have kiss'd I know not how oft. Where be your gibes now, your gambols, your songs, your flashes of merriment, that were wont to set the table on a roar? Not one now, to mock your own grinning? Quite chop-fallen? Now get you to my lady's chamber, and tell her, let her paint an inch thick, to this favour she must come. Make her laugh at that.])
insert into quotes values(101002,6,99,23,1089,[MACBETH],[If you can look into the seeds of time, and say which grain will grow and which will not, speak then to me.])
insert into quotes values(101003,7,1,46,1156,[ROMEO AND JULIET],[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.])
insert into quotes values(101004,8,8,12,1378,[SONNET 18],[Shall I compare thee to a summer's day? ¢ Thou art more lovely and more temperate. ¢ Rough winds do shake the darling buds of May, ¢ And summer's lease hath all to short a date. ¢ Sometime too hot the eye of heaven shines, ¢ And often is his gold complexion dimmed, ¢ And every fair from fair sometimes declines, ¢ By chance or nature's changing course untrimmed; ¢ But thy eternal summer shall not fade ¢ Nor lose possession of that fair thou ow'st, ¢ Nor shall death brag thou wander'st in his shade ¢ When in eternal lines to time thou grow'st. ¢ So long as men can breathe or eyes can see, ¢ So long lives this, and this gives life to thee.])
insert into quotes values(101005,9,2,10,1406,[OTHELLO],[One that loved not wisely but too well.])
* * * * * * * * * * * *
(Parenthetical remark: the bullet characters seen in the third and fourth quotes represent where line breaks occur in the original quotations. For databases, I stick in bullets to show the reader where these had been. Now back to the matter at hand:)
My SOLUTION is to use Visual FoxPro's APPEND field to retrieve an individual file for each Memo field desired. APPEND doesn't respond differently to files of different lengths.
Before you can use the APPEND command, you need create individual files for each Memo field. Fortunately, this can be done automatically. By running simple macros on your existing data (in my case, I did it to completed SQL code formatted like that reproduced above), you can create an instruction file for use in Visual Basic (or another Basic program), Visual C++, or an equivalent program.
What now follows are code used in Visual Basic to create one file for each quote. I used this style of coding to create 8893 files, one for each record in my database.
* * * * * * * * * * * *
Open "I:\FoxProWork\Quotes\00\101001.txt" For Output as #1: _ Print #1, "Alas, poor Yorick! I knew him, Horatio; a fellow of infinite jest, of most excellent fancy. He hath borne me on his back a thousand times. And now, how abhorred in my imagination it is! My gorge rises at it. Here hung those lips that I have kiss'd I know not how oft. Where be your gibes now, your gambols, your songs, your flashes of merriment, that were wont to set the table on a roar? Not one now, to mock your own grinning? Quite chop- fallen? Now get you to my lady's chamber, and tell her, let her paint an inch thick, to this favour she must come. Make her laugh at that." Close #1
Open "I:\FoxProWork\Quotes\00\101002.txt" For Output as #1: _ Print #1, "If you can look into the seeds of time, and say which grain will grow and which will not, speak then to me." Close #1
Open "I:\FoxProWork\Quotes\00\101003.txt" For Output as #1: _ Print #1, "O Romeo, Romeo, wherefore art thou Romeo?" ; Chr(149) ; "Deny thy father and refuse they name," ; Chr(149) ; "Or if thou wilt not, be but sworn my love," ; Chr(149) ; "And I'll no longer be a Capulet…" ; Chr(149) ; "'Tis but thy name that is my enemy…" ; Chr(149) ; "What's Montague? It is not hand, nor foot," ; Chr(149) ; "Nor arm, nor face, nor any other part" ; Chr(149) ; "Belonging to a man." ; Chr(149) ; "What's in a name? That which we call a rose" ; Chr(149) ; "By any other word would smell as sweet." ; Chr(149) ; " Close #1
Open "I:\FoxProWork\Quotes\00\101004.txt" For Output as #1: _ Print #1, "Shall I compare thee to a summer's day?" ; Chr(149) ; "Thou art more lovely and more temperate." ; Chr(149) ; "Rough winds do shake the darling buds of May," ; Chr(149) ; "And summer's lease hath all to short a date." ; Chr(149) ; "Sometime too hot the eye of heaven shines," ; Chr(149) ; "And often is his gold complexion dimmed," ; Chr(149) ; "And every fair from fair sometimes declines," ; Chr(149) ; "By chance or nature's changing course untrimmed;" ; Chr(149) ; "But thy eternal summer shall not fade" ; Chr(149) ; "Nor lose possession of that fair thou ow'st," ; Chr(149) ; "Nor shall death brag thou wander'st in his shade" ; Chr(149) ; "When in eternal lines to time thou grow'st." ; Chr(149) ; "So long as men can breathe or eyes can see," ; Chr(149) ; "So long lives this, and this gives life to thee." Close #1
Open "I:\FoxProWork\Quotes\00\101005.txt" For Output as #1: _ Print #1, "One that loved not wisely but too well." Close #1
* * * * * * * * * * * *
(Parenthetical words about the "Chr(149)" commands. Visual Basic can accept the Upper ASCII character set, either by putting in the actual character in the code, or by using ASCII code. Visual Basic uses the same ASCII character set as does HTML, thus character 151 is the long dash, 147 opens a quote, 148 closes it, etc. "149" gives me a bullet. I could have put in "vbCrLf" to get an actual line break; FoxPro will retain the line break if you do this.)
Once you have the files, you will insert the contents into your FoxPro Memo fields at the same time that you create your records and fill them with your other fields and values. The following code shows how this is done:
* * * * * * * * * * * *
insert into quotes values(101001,2,6,15,1090,[HAMLET],[])
APPEND MEMO quote_desc FROM 00/101001.txt
insert into quotes values(101002,6,99,23,1089,[MACBETH],[])
APPEND MEMO quote_desc FROM 00/101002.txt insert into quotes values(101003,7,1,46,1156,[ROMEO AND JULIET],[])
APPEND MEMO quote_desc FROM 00/101003.txt
insert into quotes values(101004,8,8,12,1378,[SONNET 18],[])
APPEND MEMO quote_desc FROM 00/101004.txt
insert into quotes values(101005,9,2,10,1406,[OTHELLO],[])
APPEND MEMO quote_desc FROM 00/101005.txt
* * * * * * * * * * * *
The blank "[]" at the end of each "insert" line leaves blank the Memo field. However, by executing the "APPEND MEMO" field immediately -- BEFORE you go to the next record, the Memo field will receive the values almost as soon as the empty field has been created.
* * * * * * * * * * * *
Some other notes about creating the files: each file is designated by a consecutive number. To create these numbers, you may use a simple program, such as this one I've used often, which I created in C:
if ( (fp = fopen("e:count.txt", "w")) == NULL)
{
fprintf(stderr, "Error opening file.");
exit(1);
}
for (count = 101001; count < 109999; count++)
{
fprintf(fp,"\n%ld", count);
}
fclose(fp);
The macro you use to edit your existing files may then be the receiver of the numbers, one number per record. You may copy the same number several times, once for a unique record number, once for the "APPEND MEMO" line (remember: these lines are identical to each other, EXCEPT that the file number is incremented each time), and once for the instruction accompanying each full quote that will tell Visual Basic, Visual C++ (or whatever) the name you are giving to the particular file that will temporarily house the particular quote.
For those who would prefer to use Visual C++ (or another C compiler) rather than Visual Basic (or another Basic compiler), some sample code is given below. Be FOREWARNED that special non-keyboard characters cannot be created or copied in Visual C++ as simply as possible in Basic. Any Upper ASCII characters put within a quote will be deleted from the resulting file. "CHR" codes are part of the standard repertoire.
(I have left in the bullet characters for this newsgroup post. In actual practice, I would substitute an otherwise-unused keyboard character [perhaps the pipe ("|")] or a combination of characters which I would then change into something else within the individual files via a search-and-replace command.)
* * * * * * * * * * * *
#include <stdio.h>
#include <stdlib.h>
main()
{
FILE *fp; if ( (fp = fopen("i:/FoxProWork/quotedir/101001.txt", "w"))
== NULL)
{
fprintf(stderr, "Error opening file.");
exit(1);
}
fprintf(fp,"Alas, poor Yorick! I knew him, Horatio; a fellow of infinite jest, of most excellent fancy. He hath borne me on his back a thousand times. And now, how abhorred in my imagination it is! My gorge rises at it. Here hung those lips that I have kiss'd I know not how oft. Where be your gibes now, your gambols, your songs, your flashes of merriment, that were wont to set the table on a roar? Not one now, to mock your own grinning? Quite chop-fallen? Now get you to my lady's chamber, and tell her, let her paint an inch thick, to this favour she must come. Make her laugh at that.");
fclose(fp);
if ( (fp = fopen("i:/FoxProWork/quotedir/101002.txt", "w")) ==
NULL)
{
fprintf(stderr, "Error opening file.");
exit(1);
}
fprintf(fp,"If you can look into the seeds of time, and say which grain will grow and which will not, speak then to me.");
fclose(fp);
if ( (fp = fopen("i:/FoxProWork/quotedir/101003.txt", "w")) ==
NULL)
{
fprintf(stderr, "Error opening file.");
exit(1);
}
fprintf(fp,"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.");
fclose(fp);
if ( (fp = fopen("i:/FoxProWork/quotedir/101004.txt", "w")) ==
NULL)
{
fprintf(stderr, "Error opening file.");
exit(1);
}
fprintf(fp,"Shall I compare thee to a summer's day? ¢ Thou art more lovely and more temperate. ¢ Rough winds do shake the darling buds of May, ¢ And summer's lease hath all to short a date. ¢ Sometime too hot the eye of heaven shines, ¢ And often is his gold complexion dimmed, ¢ And every fair from fair sometimes declines, ¢ By chance or nature's changing course untrimmed; ¢ But thy eternal summer shall not fade ¢ Nor lose possession of that fair thou ow'st, ¢ Nor shall death brag thou wander'st in his shade ¢ When in eternal lines to time thou grow'st. ¢ So long as men can breathe or eyes can see, ¢ So long lives this, and this gives life to thee.");
fclose(fp);
if ( (fp = fopen("i:/FoxProWork/quotedir/101005.txt", "w")) ==
NULL)
{
fprintf(stderr, "Error opening file.");
exit(1);
}
fprintf(fp,"One that loved not wisely but too well."); fclose(fp);
}
* * * * * * * * * * * *
As I do not frequent this newsgroup at all times, those who do and who see the "Memo" question asked again at some future date, should feel free to re-post this response should it be convenient for the person who has seen and retained this solution.
--
David Hayes
To respond privately, excise the "nospam" directive. (If your news reader does not allow you to edit an email address within the send fields, my address consists of "davidp" then an underscore ("_") then surname "hayes", followed by: atsign earthlink dot net.)