Text and image data handling

The Adaptive Server Enterprise database driver for Perl supports image and a text type for LONG/BLOB data. Each type can as much as 2GB of binary data.

The default size limit for text/image data is 32KB. Use the LongReadLen attribute to change this limit, which is set by a call to the fetch() API.

You cannot use bind parameters to insert text or image data.

When using regular SQL, image data is normally converted to a hex string, but you can use the syb_binary_images handle attribute to change this behavior. As an alternative, you can use a Perl function similar to $binary = pack("H*", $hex_string); to perform the conversion.

As the DBI has no API support for handling BLOB style (text/image) types, the SybaseASE.pm file includes a set of functions you can install, and use in application-level Perl code to call the Open Client ct_get_data() style calls. The syb_ct_get_data() and syb_ct_send_data() calls are wrappers to the Open Client functions that transfer text and image data to and from Adaptive Server.

$sth->syb_ct_get_data($col, $dataref, $numbytes);

You can use the syb_ct_get_data() call to fetch the image/text data in raw format, either in one piece or in chunks. To enable this call, set the dbh->{syb_no_bind_blob} statement handle to 1.

The syb_ct_get_data() call takes these arguments: the column number (starting at 1) of the query, a scalar reference, and a byte count. A byte count of 0 reads as many bytes as possible. The image/text column must be last in the select list for this call to work.

The call sequence is:

$sth = $dbh->prepare("select id, img from a_table where id = 1");

	$sth->{syb_no_bind_blob} = 1;

	$sth->execute;

	while($d = $sth->fetchrow_arrayref) {

		# The data is in the second column

		$len = $sth->syb_ct_get_data(2, \$img, 0);

}

syb_ct_get_data() returns the number of bytes that were fetched, if you are fetching chunks of data, you can use:

while(1) {

$len = $sth->syb_ct_get_data(2, $imgchunk, 1024);

... do something with the $imgchunk ...

 last if $len != 1024;

}

The syb_ct_data_info() API fetches or updates the CS_IODESC structure for the image/text data item you want to update, as shown in this example:

$stat = syb_ct_data_info($action, $column, $attr)

• $action – CS_SET or CS_GET.

• $column – the column number of the active select statement (ignored for a CS_SET operation).

• $attr – a hash reference that sets the values in the structure.

You must fist call syb_ct_data_info() with CS_GET to fetch the CS_IODESC structure for the image/text data item you want to update. Then update the value of the total_txtlen structure element to the length (in bytes) of the image/text data you are going to insert. Set the log_on_update to true to enable full logging of the operation.

Calling syb_ct_data_info() with a CS_GET fails if the image/text data for which the CS_IODESC is being fetched is NULL. Use standard SQL to update the NULL value to non-NULL value (for example, an empty string) before you retrieve the CS_IODESC entry.

In this example, consider updating the data in the image column where the id column is 1:

1. Find the CS_IODESC data for the data:

   $sth = $dbh->prepare("select img from imgtable where id = 1"); 
   		$sth->execute; 
   	while($sth->fetch) {    # don't care about the data! 
   		$sth->syb_ct_data_info('CS_GET', 1); 
   	}
   

2. Update with the CS_IODESC values:

   $sth->syb_ct_prepare_send();
   

3. Set the size of the new data item to be inserted and make the operation unlogged:

   $sth->syb_ct_data_info('CS_SET', 1, {total_txtlen 
   => length($image), log_on_update => 0});
   

4. To transfer the data in a single chunk:

   $sth->syb_ct_send_data($image, length($image));
   

5. To commit the operation:

   $sth->syb_ct_finish_send();