BLOB-Problem

[Reinhard]

Description in GUPTA-Help

bOk = SqlSetLongBindDatatype( nBindVarNumber, nDatatype )

Sets the column data type (Text or Long Binary) that a Long String value is bound to.
By default, Gupta binds Long Strings to the Text column data type. To write or update a Long Binary column instead, bind the Long String data to the Long Binary column data type by calling SqlSetLongBindDatatype and setting nDatatype to 23 (long binary column data). To set the binding back to the Text column data type, call this function and set nDatatype to 22 (Text column data).

If I try this, I get ORA-01461. Does anyone have a solution ?

Best regards

Reinhard

[Dave Rabelink]

What is the exact definition of the column in Oracle?
Is Oracle storing text in ANSI or UNICODE?

[Sunil]

TO Save/Retrieve BLOB data in Oracle table , try this.

!!CB!! 136
Long String: vBinData

//insert into
!!CB!! 70
Call SqlPrepare( hSql, "insert into msg(msgbox) values (:vBinData)" )
Call SqlSetLongBindDatatype( 1,23 )
Call SqlExecute( hSql )

// to retrieve BLOB , try this.
!!CB!! 70
Call SqlPrepare( hSql, "select msgbox from smsg where controlno = :vControlNo into :vBinData" )
Call SqlSetLongBindDatatype( 1, 23 )
Call SqlExecute( hSql )
Call SqlFetchNext( hSql, nError )

in our case, this is working fine for us , write / retrieve , you can try Dave.

Regards.
Sunil

[Sunil]

Oracle Stored in Oracle using Blob datatype in Binary format ( 8 bit ).

Regards.
Sunil

[holger.mueller]

We do it like this (BLOB, Data stored in a long string), first INSERT with EMPTY_BLOB, then UPDATE with the data

For both handles you have to define the buffer
Call SqlSetParameterAll ( ghSqlDML_I_csd_blob, DBP_ORAUSELOB, 1, "1", TRUE )
Select Case SqlSetParameterAll ( ghSqlDML_I_csd_blob, DBP_LONGBUFFER, <inDBP_LONGBUFFER_BLOB>, STRING_Null, TRUE)

Call SqlSetLongBindDatatype ( 1, 23 )
If Not SqlPrepareAndExecute
(
ghSqlDML_I_csd_blob,
"INSERT INTO
csd_blob
(
csd_blob_data,
csd_blob_id,
csd_apl_id,
csd_mandt_id,
csd_blob_alias,
csd_blob_text,
csd_blob_type,
csd_prg_ext_id,
csd_blob_fl_ne,
insertuser,
insertclient,
insertdate,
updateuser,
updateclient,
updatedate,
history
)
VALUES
(
EMPTY_BLOB(),
:inDB_csd_blob_id,
:inDB_csd_apl_id,
:inDB_csd_mandt_id,
:isDB_csd_blob_alias,
:isDB_csd_blob_text,
:isDB_csd_blob_type,
:inDB_csd_prg_ext_id,
:ibDB_csd_blob_fl_ne,
:gObjectDB_csd_usr.isDB_csd_usr_alias,
:gObjectDB_csd_cl.isDB_csd_cl_alias,
SYSDATE,
:gObjectDB_csd_usr.isDB_csd_usr_alias,
:gObjectDB_csd_cl.isDB_csd_cl_alias,
SYSDATE,
0
)"
)

!!CB!! 129
Set nBufferLength = SalStrGetBufferLength ( ilsDB_csd_blob_data )
Select Case SqlSetParameterAll ( ghSqlDML_U_csd_blob, DBP_LONGBUFFER, nBufferLength + 1, STRING_Null, TRUE)
Case 0
Call gObjectSqlMan.cFcSqlDisconnect ( ghSqlDML_U_csd_blob )
Return FALSE
Break
!
Call SqlSetLongBindDatatype ( 1, 23 )
If Not SqlPrepareAndExecute
(
ghSqlDML_U_csd_blob,
"UPDATE
csd_blob
SET
csd_blob_data = :ilsDB_csd_blob_data
WHERE
csd_apl_id = :inDB_csd_apl_id AND
csd_mandt_id = :inDB_csd_mandt_id AND
csd_blob_id = :inDB_csd_blob_id"
)