convert memo to string within a sql-query

[MrDontKnowNothing]

hi folks,

i use the function below within a query with a call like this:

select IIF(!EMPTY(mytable.thememo), memo2string(mytable.thememo,SPACE(255)) as "MEMO" from mytable

FUNCTION memo2string(memovar)
PRIVATE cErgebnis as String

cErgebnis = ''

FOR i=0 TO MEMLINES(memovar)
cErgebnis = cErgebnis + MLINE(memovar, i)
ENDFOR

RETURN cErgebnis
ENDFUNC

My Problem: it seems to work until i get an error like "to many characters". is there a limit in vfp, how long a string can be. and if so, what is the limit?
by the way: is there a cuter way to solve this kind of problem?

muchas gracias senores e senoritas

alex

 

[rgbean]

Since you can only put 254 characters in a cursor field, that's the limit for a string selected into one.

Select ;
 IIF(!EMPTY(mytable.thememo), left(mytable.thememo,254), ;
  SPACE(255)) as "MEMO" from mytable

Note: It's 255 if you don't require .null. support.

If you put the results into an array, then a string can be much longer.

Select ;
 IIF(!EMPTY(mytable.thememo),left(mytable.thememo, 16777184), ;
  SPACE(255)) as "MEMO" from mytable ;
 INTO ARRAY laMyArray

Rick

 

[justamistere]

To: RGBEAN
I tried the following in Vfp-6.0 and it produced the error message "String too long to fit", until I changed the 1000 to 250. I have 259 records, but only about 10 have some test data in the memo field.

SELECT IIF(!EMPTY(SetupHdr.Notes), LEFT(SetupHdr.Notes, 1000), SPACE(250)) AS "MEMO" FROM SetupHdr INTO ARRAY laNotes

 

[rgbean]

You are right, I guess that internally, VFP is still going through a cursor (and therefore limited to 254/255 characters) even when the output is to an array. <s>

Rick

 

[zhavic]

Rick is right,

but there still can be MEMO field selected into array, but without using any function on it, just

SELECT Notes FROM SetupHdr INTO ARRAY laNotes

But after this you must use for example FOR ... ENFOR and go throught the array - and it is slower.

If there is used some other funcion or operators to modify MEMO field 'Notes' in select list, it uses cursor as 'Rick' says and there is limit 254 chars.

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[webmiser]

If you really want the contents of the memo field in a string just use:
select padr(<memofield>,254) as <fieldname>
your memo2string function is not necessary and padr() doesn't care if the memo field is empty
the down side is that you can only use up to 254 characters since you are creating a character field in the cursor. Character fields in foxpro 6 are limited to 254