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

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

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

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
  • 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 is associated with:

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

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

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

  • The table below shows how SQLAPI++ data types correspond with servers original data 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

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

Last modified: 19 October 2019
webmaster@sqlapi.com