Insert and Update SQL limitations 2

[dddenney]

We are developing a web application using FoxPro as the backend. We have recently come across a problem, previously undiscovered by us. It seems that when you are using SQL statements to update a memo field in a table, you are not allowed to update it with more than 255 characters. This goes for both "Update" and "Insert" SQL statements. We have tried the same queries from the command line within Foxpro with the same result. The following error message is returned when trying to go over the 255 character limit:
"Command Contains unrecognized phrase/keyword"

We are using Visual FoxPro 6.0 to create the tables.

Does anyone know of a way around this? We need to be able to populate those memo fields through the web app, so we are only able to use SQL statements to update these tables. Any help anyone can provide would be greatly appreciated.

Thanks in advance,

Dan

 

[rgbean]

Dan,
How are you building these SQL statements? While there is a limitation on a string constant of 255 characters (i.e. characters between quotes in a string), you should be able to send up to 16MB to a memo field. e.g.

create table testmemo (mymemo M)
insert into testmemo values (replicate("abc",9999))

Rick

 

[dddenney]

That is the problem. We are using ColdFusion to develop our web apps. ColdFusion will translate the variable into a string literal before the update/insert is sent. Unfortunately, there is no way around this in CF. However, if you know of any other web programming languages (APS, PHP, etc.) that we can work around this problem, we will be will to switch. (At least for those pages where we actually update memo fields)

 

[baltman]

Can you parse your strings into groups of 250 characters and then concatenate them using '&'?

Brian

 

[lachlanK]

If you are using macro substitution when building up the query string then this will be the problem.

e.g.:
myVar = replicate("abc",9999)
INSERT .... VALUES ('&myVar', ....)

This can produce intermittent errors when the length of the macroed string causes the entire length of the line to exceed the 255 (?) char line limit.

Build up your query from string concatenation rather than macro substitution if this is in fact your problem.

- Lachlan

 

[dddenney]

Ok, I created a ColdFusion function that parses the string into 250 character length segments seperated by "+". It seems to work. Thanks to everyone for all the good advice!

Dan