4K char limit in SQL ???

[ciberperson]

I am inserting new records into my oracle db using an asp form. One field is intended to exceed 4,000 characters so the field was defined as a 'long' in Oracle. Problem is that my SQL bombs and we have determined that anything over 4K does not update the database. Using SQL+, we get the same error. The DBA was able to manually insert the text using PL-SQL but that doesn't seem available to my asp script. I have found nothing on the web about the problem but we have 2 developers here who are stuck. Anyone else have this issue and how did you solve it??

 

[Guest_imported]

Hi,

When you try to submit the page does the page just do nothing, as if the button is disabled? If yes this is a known restriction with the textarea capabilites of certain operating systems/browser software check out the link below for more information.

http://www.cs.tut.fi/~jkorpela/forms/textarea.html#browserlimits

HTH

Andrew

 

[ciberperson]

Thanks for the reply. I only wish our problem was entering 32,000+ characters... we are being stopped at 4,000 characters so it doesn't seem to be the browser issue mentioned in the article. We have another developer here who just hit the same problem in a completely different area of our dept. Even if I break up the fields in the form, I still have to combine them to insert in the DB and that's when it bombs. The first 3999 characters make it to the DB and the rest are left out.... I can't believe no one else has encountered this problem. There is nothing on the net which mentions this???

 

[glendsj]

I had the same problem. Try a CLOB field(this is supposed to hold 2 gigs of data). If that does not work for you then you need VARCHAR 4000 fields. I wound up using 4 VARCHAR 4000 columns for my data. I have a funtion that parses through the string and stores it sequentially in the fields.
To view it, i have to query and string it back together. It's a hassle, but the only way I have found to work around this Oracle problem. I had trouble with Oracle truncating the data going into the CLOB.

 

[ciberperson]

I finally found the answer in the PHP and Perl message boards. Common problem with SQL. The solution is to use the AppendChunk method of a recordset. Create a loop that updates the DB with a chunk of data each loop and appends to the data. the code looks something like this:

set rs=server.createobject("adodb.recordset&quot
rs.cursorlocation=3
rs.locktype=3
rs.open "select longfield from table where...", conn
'## append in chunks to the long field
rs.fields("longfield&quot.appendchunk(value)
rs.update
rs.close


hope this helps someone out there. Do a search on Oracle error ORA-01704 to find more info.