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):

TypeDescription
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 includes the following:

Binding Long or Lob Data

Let say 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'.

First, initialize the command object:

SACommand cmd(
    &con, 
    "UPDATE TEST SET FBLOB = :fblob WHERE FKEY = 'KEY'");

For more information see Execute SQL Statement.

Next step is to actually bind the content of a file into input variable. The following code binds parameter :fblob with value of file content read:

SAString sContent = SomeFunctionThatReadFileContent("blob.bin");
cmd.Param("fblob").setAsBLob() = sContent;

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

Lets say 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'.

First, create command object:

SACommand cmd(
    &con,
    "SELECT FBLOB FROM TEST WHERE FKEY = 'KEY'");

For more information see Execute SQL Statement.

Then execute the query:

cmd.Execute();

Finally, fetch the row(s) and access BLob data:

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 this example.

Server specific notes

Oracle

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

UPDATE TEST SET FBLOB = :fblob WHERE FKEY = 'KEY'

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

PostgreSQL

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 details.

Summary of Type Mappings

The table below shows how SQLAPI++ data types correspond to the original database types:

Oracle

SA_dtLongBinary <=> LONG RAW
SA_dtLongChar <=> LONG
SA_dtBLob <=> BLOB, FILE
SA_dtCLob <=> CLOB

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

Need Help?
Send an e-mail to support@sqlapi.com if you want to ask code-level questions, if you’re stuck with a specific error or need advise on the library best practices.