Home How To Online Documentation Support Download Order

 

 

Connecting to databases
Transactions
Executing an SQL command
Executing stored procedures
Binding input parameters
Fetching result set
Fetching multiple result sets
Optimizing Database Performance
Returning output parameters
Working with Long or Lob(CLob, BLob) Data
Working with date/time values
Error handling
Using native API
Query examples


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

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.