SQLAPI Library Working with Long or Lob(CLob, BLob) data

Working with Long or Lob(CLob, BLob) data

SQLAPI++ supports four types for working with this kind ofdata (see Server specific noteslater on this page for details about how SQLAPI++ maps this types on adifferent SQL platforms):

Name	C enum constant	Description
LongBinary	SA_dtLongBinary	Generally this type is mapped to an appropriate SQLtype that holds long binary data of variable length but does NOTsupports "handle" semantics.
LongChar	SA_dtLongChar	Generally this type is mapped to an appropriate SQLtype that holds long character data of variable length but does NOTsupports "handle" semantics.
BLob (Binary Large object)	SA_dtBLob	Generally this type is mapped to an appropriate SQLtype 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 SQLtype that holds large character data of variable length and doessupports "handle" semantics.

Working with Long or Lob data assumes the following:

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

Binding Long or Lob data

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

As usual, the first thing to do is to create the objects.
SACommandcmd(&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 intoinput 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 withvalue 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 Longor Lob types see example.

Reading Long or Lob data

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

As usual, the first thing to do is to create the objects:
SACommandcmd(&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 BLobdata:
if(cmd.FetchNext())  // or while(cmd.FetchNext())
{
    SAString sBLob = cmd.Field("FBLOB").asBLob();
    SomeFunctionToSaveBLobToFile("blob.bin", sBLob);
}

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

Server specific notes

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

Ex.:     Update TESTset FBLOB = :fblob where FKEY ='KEY'

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

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

3. The table below shows how SQLAPI++ datatypes 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. Actualmapping 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