Working with Long or Lob(CLob, BLob) data

SQLAPI++ supports four types for working with this kind of data (see Server specific notes later on this page for details about how SQLAPI++ maps this types on a different SQL platforms):

Name	C enum constant	Description
LongBinary	SA_dtLongBinary	Generally this type is mapped to an appropriate SQL type that holds long binary data of variable length but does NOT supports "handle" semantics.
LongChar	SA_dtLongChar	Generally this type is mapped to an appropriate SQL type that holds long character data of variable length but does NOT supports "handle" semantics.
BLob (Binary Large object)	SA_dtBLob	Generally this type is mapped to an appropriate SQL type that holds large binary data of variable length and does supports "handle" semantics.
CLob (Character Large object)	SA_dtCLob	Generally this type is mapped to an appropriate SQL type that holds large character data of variable length and does supports "handle" semantics.

Working with Long or Lob data assumes the following:

• Binding Long or Lob data into input variables (SQL statements) or input parameters (stored procedures/functions).
• Reading Long or Lob data (from fields in result set or from output parameters of stored procedure)

Binding Long or Lob data

Suppose, we want to update BLob field named FBLOB from table named TEST where some other field named FKEY is equal to 'KEY' (Update TEST set FBLOB = :fblob where FKEY = 'KEY' ). Field should be updated with the content of a file named 'blob.bin'.

As usual, the first thing to do is to create the objects.
SACommand cmd(&Connection, "Update TEST set FBLOB = :fblob where FKEY = 'KEY' ");
For more information see Connecting to databases.

Next step is used to actually bind the content of a file into input variable:
SAString sContent = SomeFunctionThatReadFileContent("blob.bin");
The above line reads the whole content of a file.
cmd.Param("fblob").setAsBLob() = sContent;
The above line binds parameter :fblob with value of file content previously read.

All that we need now is to execute a query:
cmd.Execute();

For using piecewise capabilities of SQLAPI++ for binding Long or Lob types see example.

Reading Long or Lob data

Suppose, we want to retrieve BLob field named FBLOB from table named TEST where some other field named FKEY (primary key) is equal to 'KEY' (Select FBLOB from TEST where FKEY = 'KEY'). Field should be read into a file named 'blob.bin'.

As usual, the first thing to do is to create the objects:
SACommand cmd(&Connection, "Select FBLOB from TEST where FKEY = 'KEY' ");
For more information see Connecting to databases

Next thing to do is to execute a query:
cmd.Execute();

Next step is used to actually fetch the row and access BLob data:
if(cmd.FetchNext())  // or while(cmd.FetchNext())
{
    SAString sBLob = cmd.Field("FBLOB").asBLob();
    SomeFunctionToSaveBLobToFile("blob.bin", sBLob);
}

For using piecewise capabilities of SQLAPI++ for reading Long or Lob types see example.

Server specific notes

1. Binding Lob(CLob, BLob) data when working with Oracle server has some differences from others. It's necessary for a name of bind variable to be the same as the column name it associated with.

Ex.:     Update TEST set FBLOB = :fblob where FKEY = 'KEY'

Therefore, it's impossible to bind Lob(CLob, BLob) data to Oracle database by position.

2. Working with PostgreSQL Large Objects data (Oid field type) in PostgreSQL server has some particular features. Generally when you fetch Oid field data (which can point to any object, not necessary PostgreSQL Large Object) SQLAPI++ returns its value as a number (SQLAPI++ returns an object identifier). If you want to retrieve Oid field as PostgreSQL Large Object you should set command-related option OidTypeInterpretation to "LargeObject"value before the command execution. See SACommand::setOption for more detailes.

3. The table below shows how SQLAPI++ data types correspond with servers original data types.

Oracle	When using OCI8: SA_dtLongBinary <= > LONG RAW SA_dtLongChar <= > LONG SA_dtBLob <= > BLOB, FILE SA_dtCLob <= > CLOB When using OCI7: SA_dtLongBinary <= > LONG RAW SA_dtLongChar <= > LONG SA_dtBLob = > LONG RAW SA_dtCLob = > LONG
SQL Server	SA_dtLongBinary <= > IMAGE SA_dtLongChar <= > TEXT SA_dtBLob = > IMAGE SA_dtCLob => TEXT
Sybase	SA_dtLongBinary <= > IMAGE SA_dtLongChar <= > TEXT SA_dtBLob = > IMAGE SA_dtCLob => TEXT
DB2	SA_dtLongBinary <= > LONG VARGRAPHIC SA_dtLongChar <= > LONG VARCHAR SA_dtBLob <= > BLOB SA_dtCLob <=> CLOB, DBCLOB
Informix	SA_dtLongBinary <= > BYTE SA_dtLongChar <= > TEXT SA_dtBLob = > BYTE SA_dtCLob => TEXT
InterBase	SA_dtLongBinary = > BLOB, subtype 0 SA_dtLongChar = >BLOB, subtype 1 SA_dtBLob <=> BLOB, subtype 0 SA_dtCLob <=> BLOB, subtype 1
SQLBase	SA_dtLongBinary <= > LONG VARCHAR SA_dtLongChar <= > LONG VARCHAR SA_dtBLob => LONG VARCHAR SA_dtCLob => LONG VARCHAR
MySQL	SA_dtLongBinary <= > BLOB SA_dtLongChar <= > TEXT SA_dtBLob = > BLOB SA_dtCLob = > TEXT
PostgreSQL	SA_dtLongBinary <= > BYTEA SA_dtLongChar <= > TEXT SA_dtBLob <= > Large Object SA_dtCLob < = > Large Object
ODBC	SQLAPI++ maps data types to ODBC constants. Actual mapping from constant to SQL type is ODBC driver specific. SA_dtLongBinary <=> SQL_LONGVARBINARY SA_dtLongChar <= > SQL_LONGVARCHAR SA_dtBLob = > SQL_LONGVARBINARY SA_dtCLob => SQL_LONGVARCHAR

Problems and Questions

If you haven't found the answer to your questions or have some problems on using the Library, please, send e-mail to howto@sqlapi.com.