Long string insert to oracle truncated at 16326 characters

[cstasey]

I've got an old application that was previously running under TD3.1 that inserted a long string to an oracle database. That string is up to 20,000 characters in length. Last year, we upgraded to TD6.0 (currently running SP7) and we are now reviving that old application. However, now, in TD 6.0 that string is getting truncated at 16,326 characters when it is inserted in the oracle table. I need for it to insert the full 20,000 characters. Is there a solution for this? For illustration purposes, I created a short sample .app (also attached) and oracle table (oracle version 10.2.0.4.0):

CREATE TABLE LONG_TEST (
TEXT1 CLOB);

longinsert.zip

code snippet:
On SAM_AppStartup
! **********************
Set SqlUser = 'user'
Set SqlPassword = 'password'
Set SqlDatabase = 'oracledb'
Call SqlConnect(hSql)
!
Set strLong = 'This is a string, not very long'
Set strSql = 'INSERT INTO LONG_TEST VALUES (:strLong)'
Call SqlPrepare( hSql, strSql )
Call SqlExecute( hSql )
Call SqlCommit( hSql )
! The above works
Set strLong = 'This is a string including 16000 ones'
Set nCtr = 0
While nCtr < 16000
Set strLong = strLong || '1'
Set nCtr = nCtr + 1
Call SqlExecute( hSql )
Call SqlCommit( hSql )
! The above works
Set strLong = 'This is a string including 17000 twos'
Set nCtr = 0
While nCtr < 17000
Set strLong = strLong || '2'
Set nCtr = nCtr + 1
Set nLength = SalStrLength(strLong)
! nLength shows to be equal to 17,037
Call SqlExecute( hSql )
Call SqlCommit( hSql )
! the last insert only inserts 16,326 characters into the oracle table, it should insert 17,037
Call SqlDisconnect(hSql)
Call SalQuit()



A simple oracle procedure such as below works fine:
DECLARE

v_sCLOB CLOB;
nCTR NUMBER(5,0);

BEGIN
nCTR := 0;
WHILE nCTR < 17000
LOOP
v_sCLOB := v_sCLOB || '3';
nCTR := nCTR + 1;
END LOOP;
INSERT INTO LONG_TEST VALUES (v_sCLOB);
COMMIT;
END;

longinsert.zip

[Kumuthini Ragavan]

Could it be that you are using a different sql.ini to that of the one you were using with the older version of TD? If so, could it be possible that you have a different longbuffer setting in the new sql.ini to that of the old one? Can you please compare the sql.ini and see if it helps.
Thank you
Kumuthini Ragavan

[cstasey]

Awesome!!!!! That fixed it! There was previously no longbuffer specified in either system in the sql.ini. I assume it must default to something and unicode has something to do with it, maybe using twice as many characters now. I inserted a line for longbuffer in the sql.ini and set it to 50000.

Thanks!
Chris