Manipulating Variable-length Types in PHP

You can work with values of variable-length types using the PHP module in two ways: read them from an open cursor directly, or use the LOB resource handle.

Variable-length database types are CLOB, NCLOB and BLOB.

The first mode to work with LOBs is called WB_LOBMODE_DIRECT, the other is WB_LOBMODE_RESOURCE. You can set the WB_LOBMODE_DIRECT separately for each opened cursor using the wb_set_lob_mode functions. The WB_LOBMODE_DIRECT is set for an open cursor by default.

Working with LOBs in the WB_LOBMODE_DIRECT Mode

This mode is used to get the LOB value directly. LOB reading is affected by the wb_longreadlen and wb_binmode parameter settings. These parameters may be set for each cursor separately. The default value is taken from the global settings in the PHP.INI when the cursor is created.

If you read a variable-length text in UNICODE (SQL NCLOB type), the PHP module will automatically convert the UCS-2 coding (which is used for storing text in 602SQL) into UTF-8 coding, that is easier to use in PHP. This conversion is done even when reading text in the wb_binmode=WB_PASSTHRU mode (the input stream will be text in UTF-8 coding).

The wb_binmode parameter may have one of these three values: WB_PASSTHRU, WB_RETURN, and WB_CONVERT. The first value means that the values of the variable-length attributes will be sent directly into the PHP output stream instead of storing them into a variable (an empty value will be stored in the variable). This output method is not suitable in some cases (e.g. in the wb_result_all function) and the value of this type is essentially ignored. The WB_RETURN value means that the value will be stored in a string variable as it is, while the WB_CONVERT value converts this value into hexadecimal form. Both of these values are equivalent for non-binary types (TEXT).

If the wb_binmode parameter is either WB_RETURN or WB_CONVERT, only the beginning of the variable-length attribute will be stored in the variable. The length of the stored part (in bytes) is set by the wb_longreadlen parameter.

The default value of the wb_longreadlen parameter is 4096, and the default value of the wb_binmode parameter is WB_CONVERT. These values will be used if they are not specified in the PHP.INI file.

Example:

Use the WB_PASSTHRU parameter in the WB_LOBMODE_DIRECT mode. Let's have an open cursor, with a .GIF picture in the first column. We want to send this picture to the browser.

/* Output directly to the PHP stream */ 
wb_binmode($res, WB_PASSTHRU);
/* Try if the first line exists */
if(!wb_fetch_row($res)) die("Error in fetch_row");
/* Set the HTTP header with the GIF type. */
header("content-type: image/gif");
/* Send the data. Since PASSTHRU is specified the cursor data is sent directly to output. */ 
wb_result($res, 1);

Functions for Working with LOBs in the WB_LOBMODE_RESOURCE Mode

LOBs in this mode are accessed using handles. Functions in the previous mode return the LOB value (e.g. wb_result), now these functions will only open the LOB and return the LOB resource handle (identifier). This handle is is used to refer to the LOB when calling functions for reading and writing to the LOB. LOBs opened in this mode are not affected by the wb_binmode and wb_longreadlen parameter values.

Example: Read the first 50 characters from the CLOB type value in the first row of a query:

$cursor=wb_exec($connection, "SELECT clob_column FROM clob_table");
wb_fetch_row($cursor);
wb_set_lob_mode($cursor,WB_LOBMODE_RESOURCE);
$clob=wb_result($cursor,"clob_column");
$initial50=wb_lob_read($clob,0,50);
...
wb_close($clob);
wb_close($cursor);

This mode allows you to read the LOB value in parts. You could only read from the beginning of the LOB value using the first mode. You can also rewrite a part of the LOB value using the wb_lob_write function, but only for LOBs that are opened from an editable cursor.

A LOB is opened and a new LOB resource handle is created in this mode each time you call the wb_result function or the wb_fetch_into function for reading the next record into the array type PHP variable. The individual LOB (value of variable-length in the specified column and in the specified record of the open cursor) can be opened as many times as you wish and all the LOB resource handles created for manipulating the LOB will have access to the actual LOB value (e.g. when you rewrite some part of the LOB using one handle, the second handle will read the modified LOB). LOB operations executed through LOB resouce handles can be rolled back (e.g. if the operations are a part of a database transaction, these operations will be recalled when rolling back this transaction). The opened LOB resource handle can be closed using the wb_close function. LOBs that are opened when a process request has ended will be closed automatically (you do not need to call the wb_close function). A cursor that was used as a source to open some LOBs, may not be explicitly closed if there is a LOB still opened.

You can find more information about this mode in the description of functions that work with LOBs. You can get the actual mode of processing LOBs for the specified cursor using the wb_get_lob_mode function. You can set the mode for processing LOBs for the specified cursor using the wb_set_lob_mode function, the wb_lob_read function reads a part of the LOB, the wb_lob_write function writes/rewrites a part of the LOB, the wb_lob_length function returns the current length of the LOB and the wb_lob_truncate function modifies (shortens) the LOB length.

List of topics:

Information about Cursors

PHP Interface

'Prepared' SQL Functions