Reading from an Oracle BLOB column

Home

4D v15

This sample code retrieves data from a BLOB type Oracle column using commands of the 4D for OCI plug-in.

Example

Method: OCI_GET_BLOB

  //connection parameters to modify

 $user:="xxx"

 $password:="xxx"

 $server:="xxx"

  //complex login

 status:=OCIEnvCreate(envhp;OCI_DEFAULT)

 status:=OCIHandleAlloc(envhp;errhp;OCI_HTYPE_ERROR)

 status:=OCIHandleAlloc(envhp;svchp;OCI_HTYPE_SVCCTX)

 status:=OCIHandleAlloc(envhp;authp;OCI_HTYPE_SESSION)

 status:=OCIHandleAlloc(envhp;srvhp;OCI_HTYPE_SERVER)

 status:=OCIServerAttach(srvhp;errhp;$server)

 status:=OCIAttrSetVal(svchp;srvhp;OCI_ATTR_SERVER;errhp)

 status:=OCIAttrSetText(authp;$user;OCI_ATTR_USERNAME;errhp)

 status:=OCIAttrSetText(authp;$password;OCI_ATTR_PASSWORD;errhp)

 status:=OCISessionBegin(svchp;errhp;authp;OCI_CRED_RDBMS;OCI_DEFAULT)

 status:=OCIAttrSetVal(svchp;authp;OCI_ATTR_SESSION;errhp)

 

  //SQL request

 $sql_statement:="SELECT t_blob FROM test_lob WHERE t_id = 1"

 

 C_LONGINT(bloblocator)

 C_LONGINT($define;$position;$sqlt)

 C_POINTER($p_define)

 C_LONGINT(null_ind01;rlen01;rcode01)

 

 bloblocator:=0

 $define:=0

 $position:=1

 $p_define:=->bloblocator

 $sqlt:=SQLT_BLOB

 C_BLOB($blob)

 

  //preparation of request

 status:=OCIHandleAlloc(envhp;stmthp;OCI_HTYPE_STMT)

 status:=OCIDescriptorAlloc(envhp;bloblocator;OCI_DTYPE_LOB)

 status:=OCIStmtPrepare(stmthp;errhp;$sql_statement;OCI_DEFAULT)

 status:=OCIDefineByPos(stmthp;$define;errhp;$position;$p_define;$sqlt;->null_ind01;->rlen01;->rcode01;OCI_DEFAULT)

 

 status:=OCIStmtExecute(svchp;stmthp;errhp;1;0;0;0;OCI_DEFAULT)

 

 status:=OCILobRead(svchp;errhp;bloblocator;1;$blob)

 

  //release

 status:=OCIDescriptorFree(bloblocator)

 status:=OCIHandleFree(stmthp)

 status:=OCISessionEnd(svchp;errhp;authp)

 status:=OCIServerDetach(srvhp;errhp)

 status:=OCIHandleFree(envhp)

 

  //retrieval from Blob into a file

 If(BLOB size($blob)>0)

 

  //if the Oracle BLOB column was supplied by 4D for Oracle,

  //we have to remove the first 8 bytes from the BLOB received

  //DELETE FROM BLOB($blob;0;8)

 

  //we have hard-coded the file name but we can create the name using programming

  //and retrieve the file type in the Blob for the extension

    $DocRef:=Create document("image_1.jpg")

    If(OK=1)

       CLOSE DOCUMENT($DocRef)

       BLOB TO DOCUMENT(Document;$blob)

    End if

 

 Else

    ALERT("No contents in Blob!")

 End if

  //we empty the BLOB

 SET BLOB SIZE($blob;0)

Description of Oracle table used in this example:

CREATE TABLE TEST_LOB (
T_ID NUMBER(5,0), 
T_NAME VARCHAR2(80), 
T_BLOB BLOB
);