PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: How to handle image data type
Date: Mar 8 1999 9:56PM

Patrick S. Leung writes:
 > Hi,
 > 
 > I have search a few of the mailing list but found little on how to
 > handle image data thru sybperl to linux sybase ase.  I looked at
 > sybooks.sybase.com too but with little help.  What I need to do is:
 > 
 > 1.  Import the data to Sybase ASE, either thru bcp or sybperl.

There's sample code in eg/dbtext.pl on how to use dbwritetext(). Also, 
the sybperl man page explains how to use ct_send_data():

Using ct_get_data() and ct_send_data() to do raw TEXT processing
     As of release 2.09_06 of sybperl Sybase::CTlib includes the
     ability to process TEXT and IMAGE datatypes using perl
     versions of ct_get_data() and ct_send_data(). Using these
     functions is a little tricky, however.

     NOTE: This discussion applies equally to TEXT and IMAGE
     datatypes, even if only one or the other is mentioned in the
     text.

     Retrieving TEXT columns using ct_get_data()

     First lets see how ct_get_data() is implemented to retrieve
     TEXT or IMAGE data types in raw format, and (possibly) in
     retrieve large data items in smaller, more manageable
     pieces.

     First, it is essential that the TEXT columns appear last in
     the select statement (there can be several TEXT columns in
     the statement, but they must appear after any regular
     columns.
     For example:

             select userID, userName, msgText
             from   messageTable

     (where msgText is a TEXT column) would work fine.

     You issue the query in the normal way:

             $dbh->ct_execute("select userID, userName, msgText
                             from messageTable where userID = 5");

     You call ct_results() in the normal way, with the exception
     that you pass the $textBind param as FALSE to prevent
     ct_fetch() from returning the TEXT column.

     If there are fetchable results, you call ct_fetch() to
     retrieve the normal data columns, and for each row you then
     call ct_get_data() to retrieve the TEXT column(s).

     For example:

             $dbh->ct_execute("select userID, userName, msgText
                             from messageTable where userID = 5");
             while($dbh->ct_results($restype, 0) == CS_SUCCEED) {
                 next unless $dbh->ct_fetchable($restype);
                 while(@row = $dbh->ct_fetch) {
                     ($ret, $msg) = $dbh->ct_get_data(3);
                 }


     Updating TEXT columns using ct_send_data()

     This operation is a little more complicated. Essentially,
     you must first select the column that you wish to update to
     obtain a valid text pointer (via a call to
     ct_data_info(CS_GET), then you initiate a CS_SEND_DATA_CMD
     command using ct_command(), you set the new total length of
     the column via ct_data_info(CS_SET), send the data to the
     server via ct_send_data(), commit the operation with
     ct_send(), and then process the results in the normal way
     with ct_results() and ct_fetch().

     For example, assuming the following table:

             create table blobtext(id numeric(5,0) identity,
                                   data image)

     We would update the data column of a particular row like
     this:

             $dbh->ct_execute("select id, data from testdb..blobtest where id = 5");
             my $restype;
             while($dbh->ct_results($restype) == CS_SUCCEED) {
                 next unless($dbh->ct_fetchable($restype));
                 my @dat;
                 while(@dat = $dbh->ct_fetch) {
                     $dbh->ct_data_info(CS_GET, 2);
                 }
             }

             my $data = "This is a the new content that we want to place in
                         the 'data' column for the row";
             my @dat;
             $dbh->ct_command(CS_SEND_DATA_CMD, '', CS_UNUSED, CS_COLUMN_DATA);
             $dbh->ct_data_info(CS_SET, 2, {total_txtlen => length($data)});
             $dbh->ct_send_data($data, length($data));
             $dbh->ct_send;
             while($dbh->ct_results($restype) == CS_SUCCEED) {
                 next unless $dbh->ct_fetchable($restype);

                 while(@dat = $dbh->ct_fetch) {
                     print "@dat\n";
                 }
             }

     The last ct_fetch() will return one column - the new text
     pointer. At the moment there is no way to make use of this
     text pointer directly.

     You can also update TEXT fields on a set of rows by using a
     second connection and performing the ct_send_data() in a
     nested loop:

             $dbh->ct_execute("select id, data from testdb..blobtest");
             my $restype;
             while($dbh->ct_results($restype) == CS_SUCCEED) {
                 next unless($dbh->ct_fetchable($restype));
                 my @dat;
                 while(@dat = $dbh->ct_fetch) {
                     $dbh->ct_data_info(CS_GET, 2);

                     # get the data to be updated, based on the 'id' column
                     # presumably the get_data() function knows what to do :-)
                     my $data = get_data($dat[0]);
                     $dbh2->ct_command(CS_SEND_DATA_CMD, '', CS_UNUSED, CS_COLUMN_DATA);
                     # copy the CS_IODESC struct from $dbh to $dbh2, and
                     # set 'total_txtlen' to the correct value.
                     $dbh2->ct_data_info(CS_SET, 2, {total_txtlen => length($data)}, $dbh);
                     $dbh2->ct_send_data($data, length($data));
                     $dbh2->ct_send;
                     while($dbh2->ct_results($restype) == CS_SUCCEED) {
                         next unless $dbh2->ct_fetchable($restype);
                         while(@dat = $dbh2->ct_fetch) {
                             print "@dat\n";
                         }
                     }
                 }
             }



 > 2.  In a cgi-script, use sybperl to extract the image data and form a
 > html.

Is the image stored as an IMAGE object?

If so (assuming jpg):

#!/usr/local/bin/perl -w
use strict;
use Sybase::CTlib;
use CGI;

my $query = new CGI;
my $id = $query->param('id');
print $query->header(-type=>'image/jpg');
my $dbh = new Sybase::CTlib 'user', 'pwd', 'server';
my $d = $dbh->ct_sql("select image_data from image_table where id =
$id");
# $d is a reference to an array of rows.
print pack("H*", $d->[0]->[0]);   # column 0 of row 0.
exit(0);

__END__

Assuming this script is named show.cgi, you code your html like so:



and that will show you the image stored with id 1234.

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@mbay.net       -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com