Writing long raw

[pflucker]

Hi, all.

Writing a long raw to Oracle does not work in 5.2 (same code works in 4.0).

This is the code:

Code: Select all

..
Long String: lsFileBlob
..

If NOT SalFileOpen( hFile, sFileName, OF_Binary | OF_Read )
	Return ....error

Set nFileLength = VisFileGetSize( sFileName )
Set nLengthRead = SalFileRead( hFile, lsFileBlob, nFileLength )
If nLengthRead != nFileLength
	Return ...error
Call SalFileClose( hFile )

Set sSqlCommand = "
		INSERT INTO F_EMAIL_MESSAGE_ATTACHMENT 
		( MESSAGE_NO, MESSAGE_ATTACHMENT, ... ) 
		VALUES 
		( :nMessageNo, :lsFileBlob, ... )"
If NOT SqlPrepare( hSqlMain, sSqlCommand )
	Return FALSE
Call SqlSetLongBindDatatype( 2, 23 )
If NOT SqlExecute( hSqlMain )
....

the column is a long raw, oracle 10g.

The file length is about 120K, and it reads the correct length. However, the file written to the database is only 64k. (exactly 65,536 bytes). If I try larger files it always writes the same 64K.

I have
longbuffer=10000000
in my sql.ini.

Thanks for any assistance.

[Jeff Luther]

I would suggest you provide us a small test app, and include a sample file and the CREATE TABLE statement for Oracle.

However, that said, the code of yours looks OK. You did write:

I have
longbuffer=10000000
in my sql.ini.

Where, which section? In [oragtwy] ?

After the SalFileRead() call somewhere, use Expressions window and try this: SalGetBufferLength( lsFileBlob )
Is it the value you expect? 120K = 122,880 bytes. What utility are you using to get the size of the column value in Oracle?

P.S. I deleted your duplicate of this msg.

[mkonir]

We recently ran into similiar issue when writing long into the database -- the data gets truncated to 32k.
The best thing you can probably do is to change the data type of your long / long raw column to CLOB / BLOB as long columns are not supported by Oracle for a long time.

[pflucker]

Thanks for your help. The test app worked fine, and now the real one does too, but I am still not sure why:

Tracing my steps down, I found that if I use another Sql Handle (not hSqlMain, which is used throughout the program), it works OK. I am not sure how can I see if there is any difference between the two Sql handles.

Code: Select all

Function:  fEmail
...
Local variables
Sql Handle: hSql1
...

Call SqlConnect( hSql1 )

Set sSqlCommand = "
INSERT INTO F_EMAIL_MESSAGE_ATTACHMENT ...

If NOT SqlPrepare( hSql1 , sSqlCommand )
Return FALSE
Call SqlSetLongBindDatatype( 2, 23 )
If NOT SqlExecute( hSql1 )
...

[Jeff Luther]

if there is any difference between the two Sql handles

Well, there should be no difference. However, one could have a compiled command on it, the other not, etc.

And in terms of your funct. code, the only thing I'd be sure to add is: Call SqlDisconnect( hSql1 )
before the function returned.