Inserting binary documents in a (oracle) blob field

Forum > Integration & Database > Inserting binary documents in a (oracle) blob field

Ingo Guthmann

postedon October 15, 2015at 5:05 PM

Version 6.2.7

Hello

I have to store a file (pdf or txt or …) in a cblob field of an oracle db.
How i have to build the sql-statement?

First try:
Code:
SqlQueryStatement $sqlQueryStatement := new SqlQueryStatement;
Indexed Any $values := new Indexed Any;
String $data := ReadBinaryData('work\\tmp\\uploads' & $file);
$sqlQueryStatement .sqlStatement := 'INSERT INTO DOCUMENTS(id, doc) values(12345, utl_raw.cast_to_raw(?))';
$values.addElement($data);
$sqlQueryStatement.paramsForStatement := $values;
SqlQuery(GetDefaultDatasource(true), $sqlQueryStatement.sqlStatement.toString(), $sqlQueryStatement.paramsForStatement);
Result:
can bind a LONG value only for insert into a LONG column

Second try :
If if use READTEXTFILE() instead ReadBinaryData() and a txt-file, it works correct (only) for txt.

Can someone please give me a hint ?

Ingo

Ingo Guthmann

repliedon October 19, 2015at 8:58 AM

Hi Andrea,
thanks for your help.

Unfortunately it doesn't work.
Now i get this error:
"com.nm.sdk.NmRuntimeException: java.sql.SQLException: Invalid column type"

Code:
SqlQueryStatement $sqlQueryStatement := new SqlQueryStatement;
Indexed Any $values := new Indexed Any;
java.io.File $file := NEW(java.io.File, 'PATH_TO_FILE');
$sqlQueryStatement .sqlStatement := 'INSERT INTO DOCUMENTS(id, doc) values(12345, ?)';
java.sql.Blob $blob := NEW(javax.sql.rowset.serial.SerialBlob, org.apache.commons.io.FileUtils.readFileToByteArray($file));
$values.addElement($blob);
$sqlQueryStatement.paramsForStatement := $values;
SqlQuery(GetDefaultDatasource(true), $sqlQueryStatement.sqlStatement.toString(), $sqlQueryStatement.paramsForStatement);

Regards, Ingo

Please sign in to add a reply