String is Too Long To Fit

[stanlyn]

Hi,
How do I trim the field value for "description"? The data is coming from a RV using VFPA with MSSQL as backend. The field length of description is 512 chars as nvarchar(512). The actual error is "String is too long to fit"

This code works.

Open Database "EFIR.DBC"
Select Pk, Type, Description ;
	FROM "RV_DOCTYPE" ;
	WHERE Systemid = 'abc';
	INTO Cursor webwizard_query

Select Pk, Type, Description, OK
Select Pk, Type, alltrim(Description), FAILS
Select Pk, Type, trim(Description), FAILS
Select Pk, Type, left(Description, 256) FAILS
Select Pk, Type, left(Description, 254) OK
Select Pk, Type, allt(strconv(Description, 6)) FAILS
Select Pk, Type, alltrim(Description) as Desc1, FAILS

Thanks,
Stanley

 

[foxmuldr3]

Try something like:

CREATE CURSOR webwizard_query ;
( ;
    Pk           .., ;      && Whatever type these are
    Type         .., ;
    Description  m ;        && Use a memo field
)
APPEND FROM RV_DOCTYPE FOR Systemid = 'abc'

--
Rick C. Hodgin

 

[Chris Miller]

The field type of the description field coming from the remote view query should be memo, isn't it? So make that cursor readwrite, which is normal for remote view cursors anyway, then just do

update cursorname set description = alltrim(description)

I actually wonder whether that's even necessary, as the source type is n[highlight #FCE94F]var[/highlight]char it should not have trailing spaces like a normal char or nchar field.

What's really the problem? Also, if the query works, to get a DBF and store it somewhere as a DBF all you need is COPY TO And save the cursor as a DBF table. To keep all field names as they are you need to have the DATABASE clause of COPY TO and end up with a DBF that's part of a DBC.

But I'm not sure whether you want to store the view query result into a DBF. Why? You have the MSSQL DB and the view is getting its data. That's your backend now, no reason to store a cursor into a dbf, just make use of the view and cursor wherever you need it.

Chriss

 

[stanlyn]

Thanks Rick...

I did not try it, but looks like setting description to a memo as you show would have the same issue as I get now.

Maybe, changing the field type in MSSQL to varbinary or memo would cause an implicit conversion. Need more experimenting and was hoping for an explanation as how best to handle long (>255 character) values.

Is there a more direct way to trim it, within the select statement, or change the field type in MSSQL?

The issue came up when playing with the "web publishing" wizard. The wizard does not allow filtering and other basic stuff. It did save the code to a prg file and that was what I was playing with. The web page the wizard generated is works, except for the super long description field that is mostly empty (at this point) and taking up a lot of space in the web page and contained no filtering (where clause).

I added the where clause and that works. Then I tried to trim it and that started this discussion.

I do expect to see the length of all descriptions in the generated web page to be as wide as the longest description.

Thanks,
Stanley

 

[foxmuldr3]

Stanley,

When you do a SELECT you can only populate character fields. You cannot SELECT into a memo field. So, in order to get content into a memo field you have to use an XBASE command or iterate and populate it manually.

By creating a cursor with a memo field in it, you will automatically perform the same operation as a SELECT, but just the old fashioned way before we had fancy SQL in XBASE programming.

--
Rick C. Hodgin

 

[stanlyn]

Hi Chriss,

the remote view query should be memo, isn't it?

No, currently it is nvarchar(512). In my prev post to Rick, I suggested changing the field type on the backend.

What's really the problem?

The generated webpage has a 512 char description shown or a truncated description if I use the allt(left(description, 255)) as anything >255 generates the error. Truncation is not wanted.

And yes, I need to avoid going to and from a dbf, just to get a value from a long field.

If changing the backend can fix this, I'll do it.

Thanks,
Stanley

 

[Chris Miller]

What is the view field type of description?

If you open the view RV_DOCTYPE in the view designer, and then go into the menu Query->View SQL, can you copy all the code from there? It does not only have the sql query but also DBSETPROP calls that define the field type.

That's where you can say that you want a Memo field, that's not done by changing the query, but simpl defininig the view field data types as you want them.

So, I am (and was) talking about the VFP side of it, not the MSSQL side, I am not blind or deaf, I understand MSSQL has nvarchar. So that should be queried into a memo, you should define the view field description as memo. And you do that in the view designer there, on the code level in DBSETPROPS. The view specifies what VFP side data types it wants.

Chriss

 

[stanlyn]

Hi Rick,

I'm confused...

you can only populate character fields. You cannot SELECT into a memo field

I am not "SELECTing into a memo field" Currently, no where in my setup (code or DB) is a memo field, as nvarchar is a character field and is what I'm using.

Thanks,
Stanley

 

[stanlyn]

Hi Chriss,

What is the view field type of description?

DBSetProp(ThisView+".description","Field","DataType","M")
DBSetProp(ThisView+".description","Field","UpdateName","dbo.DocType.Description")
DBSetProp(ThisView+".description","Field","KeyField",.F.)
DBSetProp(ThisView+".description","Field","Updatable",.T.)

Looks like it is memo as ODBC must have converted the nvarchar to a memo, probably of its >255 size. This now makes sense of what Rick has been saying. The implicit conversion.

Still confused...

You say I need it as memo. The RV is memo as confirmed above and still errors with the "too long" error when trying to do a simple trim. So, how would copying it into another memo change its behavior? Isn't a memo field a memo field?

Thanks,
Stanley

 

[Chris Miller]

If it already is memo and you have trailing space in the memo fields, that must come from the MSSQL data itself, it has trailing spaces. If you don't want that, you can do as I suggested in mmy first post and after openning the view do the UPDATE or you change the MSSQL data. Not in the data type, just update the description field with the fully trimmed value.

For example in the SSMS do

UPDATE dbo.table SET description = trim(description)

Trim() is the T-SQL (MSSQL) equivalent of VFPs Alltrim() function.

And then the view will also not get trailing spaces.

Chriss

 

[Chris Miller]

So, how would copying it into another memo change its behavior? Isn't a memo field a memo field?

Very true, Stanlyn.

That's why I suggested you do the UPDATE sql on the view itself, not a Select into yet another cursor.

If you query from a memo field with an expression like ALLTRIM(memofield) that's turned into a char field and that has the 254 char limit. That's not because it came from MSSQL, that's also happening if the original dbf had a memo. Expressions only turn into the field type you want using CAST, so CAST(...as memo). But you don't need to go that route. You either just once and for all remove those unwanted trailing spaces from the backend MSSQL table or you always trim the data with an update of the description field after the USE rv_doctype. You could also REPLACE description with ALLTRIM(description) if you like that better.

I'd just once and for all remove those trailing spaces in the original table. There's no reason to define a varchar field (nvarchar, I know) and not use its capability to store values trimmed.

Chriss

 

[stanlyn]

Chris,

The data feeding MSSQL comes from a VFP editbox control. Actually, some of the data was added via SSMS.

So, is it when saving that value in the edit control I need to do a replace or "insert into" clause that contain the alltrim(field), before mssql gets and saves it. The mssql field is a longer that 255 char field.

If so, then one the beauties of remote views is lost as I have to have code that manually converts the data and set assignments to all the fields. Is filling in the editbox and issuing tableupdate() now out the window??? Yes or No?

Thanks,
Stanley

 

[stanlyn]

Chriss,

I just ran the code "UPDATE dbo.DocType SET description = trim(description)" in SSMS and was successful. Still, same original too long error when trimming it again in the select statement. However the generated page's field for description are all as long as the longest trimmed description value and is expected.

So the take away is... the data saved to a long field needs to be trimmed before saving it, therefore eliminating the need to trim it later. Would you agree?

Thanks,
Stanley

 

[Chris Miller]

Still, same original too long error when trimming it again in the select statement.

Yes, of course, but since the data is trimmed, everywhere you used to have alltrim(description) you can now use description alone. It is trimmed and stayss trimmed, unless you'd pad the value before storing.

trimmed before saving it, therefore eliminating the need to trim it later. Would you agree?

Unless you want to store padded values, yes. You have to trim or not enter unwanted spaces in the user interface. The varchar field just stores what comes 1:1, it does neither pad with spaces nor trim automatically.

Where does the padding come from? If you use a web interface I'm not sure what widgets (web controls or also input HTML tags) you use, but there's no automatic padding I'm aware of. If you use a VFP form with a textbox that's set to space(512) or something like that it would explain why you have that padded values.

If you do the interface correctly you also don't need to trim before saving.

Well, and if you're a fan of selecting alltrim(field), then just notice I already told you why that fails with too long values. VFP determines the type of the expression as C, not as memo or varchar or any special char field, just C and then also uses the field type C. Unless your query would be CAST(alltrim(description) as Memo) as description. But that's overcomplicating things, isn't it? When the data is stored trimmed a query or view doesn't pad it and you don't need an alltrim expression at all in the view definition or in secondary queries on the view cursor.


Chriss

 

[stanlyn]

Hi Chriss,

Unless your query would be CAST(alltrim(description) as Memo) as Description. But that's overcomplicating things, isn't it?

NO, its the simplest, and done in the select statement... Finally, this one line answers my original question and no longer errors. Here is what I entered and it works perfectly...

Select Pk,Systemid,Type,Webtype,CAST(STRCONV(ALLTRIM(description),6) as Memo) as description,

Of-course I will start sending trimmed data to sql.

Thanks for that simple fix that you think is overly complicated. My 1st post to Rick, I was asking for direct way to handle it within the select statement, and you provided it.

if you're a fan of selecting alltrim(field)

Its more of a habit to insure the value is trimmed as sometimes the data may not be trimmed. It does not hurt to trim a trimmed value. Working with not-trimmed values causes me much more problems, as I do not always control the data. Much like allt(upper(field)) = 'CHRIS' being used for exact comparisons without regard to all the set command settings.

Thanks,
Stanley

 

[Chris Miller]

It does not hurt to trim a trimmed value

Yes it does, you've seen that. Just because alltrim(field) is a simple expression with only one field. VFP does not deduct the field type to be the same as the field, it dedcts C as type and tries to fit this into a char field, which fails for anything longer than 254 characters. You're causing that problem with alltrim.

So using alltrim in SQL is really a bad habit, if not an error.

If you have trimmed values you don't alltrim them. It hurts, not because it changes the value, but it changes the type from memo to char. If that's not sunken in by now, I can't help you, but you need to change your habit.

Here's another reason alltrim is really bad, even though it doesn't error in this case:

Create Cursor demo (cText Memo)
Insert into demo values ('hello')
Insert into demo values ('world!')

Select Alltrim(cText) as cText from demo into cursor result
? Len(cText)
Go 2
? cText

To determine the field data type of a result, VFP uses the result of the first record. And if that's a short string, that makes the result short and all strings in further records are trimmed silent without error.

If you don't see it let me point out the exclamation point of 'world!' is lost in the result cursor. The result field is char(5) because 'hello' has length 5.

This is really awful to use. The only thing that saves you in case the original field type is char, is that VFP then uses that field length and doesn't trim, so the same example done with field type char(10) goes without error:

Create Cursor demo (cText char(10))
Insert into demo values ('hello')
Insert into demo values ('world!')

Select Alltrim(cText) as cText from demo into cursor result
? Len(cText)
Go 2
? cText

So that may already have saved you a lot of times. It doesn't, if the original field type is memo, though. Other databases won't determine field data types the way VFP does. But you always risk cutting off data. BEsides, when VFP keeps the field type char(10) in such cases, what effect remains from the alltrim? None, the values are trimmed and put back into char(10) fields, which pads them again, with spaces. So you never win with alltrim, no matter if the original field type is memo or char.

So the only place it makes sense to alltrim is when you transfer the cursor field to the user interface. In VFP controls it makes no sense as using controlsource alltrim(field) will make that control readonly. In case you generate HTML that's fine to do, though.

But don't alltrim in the SQL. Never ever.

In VFP forms you could set the controlsource to a field and in the base class of your textboxes and editboxes you could have This.value = alltrim(this.value) in gotfocus. Something like that would trim values just in time, when needed.

And then, it's still more appropriate to do the REPLACE ALL description with alltrim(description) as that's done within the cursor and doesn't change field types as a query into a second cursor does. Afterward, even if the MSSQL data was "dirty" with trailing spaces, your VFP side data has that trimmed away.

Chriss

 

[Chris Miller]

Is there a more direct way to trim it, within the select statement, or change the field type in MSSQL?

I've seen that question, Stanlyn. It's still a bad way to make queries more complicated than they need to be "just in case". Using CAST(alltrim(field) as Memo) in all future SQL that queries memo fields, have fun. It's really not the way to go. You just cause nothing in case you deal with good data and programming everything else to not get trailing spaces into the table. That is the way to go here, not trimming as "it doesn't hurt". It does. You're just hurting yourself and sticking to it, even now you know it hurts.

You really don't want to complicate all queries having char or memo fields in their field list this way, Stanlyn. You're just mad at me for not giving you the answer to your question. I'm here for teaching best practices, and trimming data in queries is not the way to go. Never. It's not a safety net and oversight you can be proud of, It's just a bad habit if not an error of the overall design. You design the database to store no redundant data and use varchar fields (nvarchar, I know) to store in the length of the string to not need to trim when querying. That's the whole reason for it. Besides saving disk space in storing, which has really become unimportant unless you have extreme cases of exorbitantly large databases. So actually varchars are there for the convenience of not needing to trim in queries. Just trim before saving. And as a safety net, you may trim within the UI, but not in queries. Don't risk it, now you've seen the bad side effects, why are you still resisting? I really don't get it.

Chriss

 

[Chris Miller]

I got one more finishing thought about this:

If you would do the CAST() within the RV_DOCTYPE view, you would have a hard time making that an updatable view with that expression mapped to the dbo.DocType.Description column in MSSQL.

So actually you have the view at its best and then you just need to save trimmed values.

I can even imagine how a query with ALLTRIM caused padding. When you take the view cursor and query from it - for whatever reason I also don't understand - using SELECT ...,ALLTRIM(description) as description, .... FROM rv_doctype INTO CURSOR xyz then this xyz cursor has a char field, not Memo, I showed that.

So if the first record in rv_doctype has a longer description you at least get a longer char field, maybe char(200), whatever, but aside from the problem thaat all longer descriptions get cut off, all shorter descriptions got padded to that length and if you later save that back to dbo.DocType.Description you introduce those spaces there by your faulty technique.

It's ironic, isn't it? But if you'd not do this you wouldn't have that problem.

Chriss

Just to showcase that problem of causing both padding and cut-off:

Clear
Create Cursor demo (mDescription Memo)

Insert into demo values ('Lorem ipsum dolor sit amet, consetetur sadipscing')
Insert into demo values ('Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam')
Insert into demo values ('Lorem ipsum dolor sit amet')

? 'original data'
Scan 
? '-->'+mDescription+'<--'
endscan 
?

? 'query result structure:'
Select Alltrim(mDescription) from demo into cursor result

AFields(laFields)
? 'result field name:'+laFields(1,1)
? 'result field type:'+laFields(1,2)
? 'result field length:',laFields(1,3)
?

? 'first query result:'
Scan 
? '-->'+exp_1+'<--'
endscan 

? 'second query result:'
Select mDescription from demo into cursor result2
Scan 
? '-->'+result2.mDescription+'<--'
endscan

You'll see in the first result the second record is cut off to the same length as the first, the third record is padded to the length of the first. Both things happen.

So the best thing you can query is the memo as is, just mDescription, as in the second query. That just gives you what's in the table itself. Anything else is just causing the problem you want to avoid. I understand you think the best moment to remove trailing spaces that made it into the table is alltrim in a query, but what would best resolve that issue is not having padded spaces saved. Then you also don't get padding.

 

[stanlyn]

Thanks Chriss...

I really mean it, as a lot of what I learn, I learn it here on Tek-Tips and you have been very helpful in breaking it down. I also appreciate your persistence against my persistence. That is how we/I learn. I usually don't take the first advice I get unless it make immediate sense. Like the varchar type that auto trims itself. I knew that, but did not factor that in, mainly because I had forgotten that detail, too far removed to the original question and, not sure if nvarchar was reliable. Therefore, I always safe-garded against padding issues with alltrim() and has worked for years in xbase without issue. I'm a newbie with the view stuff and learning my way and thanks to you for your many explanations.

I apologize for leaving an important thread with you hanging without getting a solution several months back. Its been almost a year now, July 28th, 2022 that changed everything for me/us. We are still recovering and explains why I'm MIA for months. I put together a tiny window for friends and family here at: TroublesomeCreekKy dotcom into what our world has been all about since then, totally upside down. Some of our Tek-Tips community was aware of the event and its impact.

Since this discussion, I have refactored this app with what has been learned here from you plus some other related changes. For example all the subclasses for edit and textboxes have been modified with alltrim logic, all nvarchar types has been changed to varchar, which gets rid of the char(0) issues, removed all transformations from select statement, except for a couple of read only results with NO postback. Here is one example...

	Select WebType, xTotalFee, ', plus $ ' + Str(RecOverageQtyFee, 6, 2) + ' per page if over ' + ;
		Str(RecStdPageQty, 4, 0) + ' pages' As OverageFee, SystemID, pk ;
		FROM 'rv_DocType' ;
		WHERE 1=2 And Uploadable = .T. And (Not Isnull(WebType) And Not Isnull(xTotalFee)) ;
		INTO Cursor 'curType' ;
		order By WebType

Have you ever thought about putting together a bootcamp? Sign me up. Not sure how you got so knowledgeable in these languages. I have asked before and have never received a reply. Seriously, formal training, books, school, work, 80 hours a week on the job???

Anyway, thanks again,
Stanley

 

[Tore Bleken]

No Boot Camp, but there will be a VFP conference in Phoenix this October.

https://swfox.net/