Using Smart Large Objects (IDS)

The SELECT, UPDATE, and INSERT statements do not manipulate the values of smart large objects directly. Instead, they use a handle value, which is a type of pointer, to access the BLOB or CLOB value, as follows:

• The SELECT statement returns a handle value to the BLOB or CLOB value that the projection list specifies. SELECT does not return the actual data for the BLOB or CLOB column that the projection list specifies. Instead, it returns a handle value to the column data.
• The INSERT and UPDATE statements do not send the actual data for the BLOB or CLOB column to the database server. Instead, they accept a handle value to this data as the value to be inserted or updated.

To access the data of a smart-large-object column, you must use one of the following application programming interfaces (APIs):

• From within an IBM Informix ESQL/C program, use the ESQL/C library functions that access smart large objects. For more information, see the IBM Informix ESQL/C Programmer's Manual.
• From within a C program such as a DataBlade module, use the Client and Server API. For more information, see your IBM Informix DataBlade Developer's Kit User's Guide.

You cannot use the name of a smart-large-object column in expressions that involve arithmetic operators. For example, operations such as addition or subtraction on the smart-large-object handle value have no meaning.

When you select a smart-large-object column, you can assign the handle value to any number of columns: all columns with the same handle value share the CLOB or BLOB value. This storage arrangement reduces the amount of disk space that the CLOB or BLOB value, but when several columns share the same smart-large-object value, the following conditions result:

• The chance of lock contention on a CLOB or BLOB column increases. If two columns share the same smart-large-object value, the data might be locked by either column that needs to access it.
• The CLOB or BLOB value can be updated from a number of points.

To remove these constraints, you can create separate copies of the BLOB or CLOB data for each column that needs to access it. You can use the LOCOPY function to create a copy of an existing smart large object.

You can also use the built-in functions LOTOFILE, FILETOCLOB, and FILETOBLOB to access smart-large-object values, as described in Smart-Large-Object Functions (IDS). For more information on the BLOB and CLOB data types, see the IBM Informix Guide to SQL: Reference.

Conditional Expressions

Conditional Expressions:
 
                          (1)
|--+-| CASE Expressions |------+--------------------------------|
   |                  (2)      |
   +-| NVL Function |----------+
   |                     (3)   |
   '-| DECODE Function |-------'