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