Reading and Writing BLOB data to SQL Anywhere ??

[David Pylatuk]

Does anyone have a simple code example they could provide
that demonstrates the reading and writing of Blob data, lets
say a BMP file to a column in an SQL Anywhere 5.5.04 database ?

I am attempting to use the SELECTBLOB and UPDATEBLOB statements
with no luck.

Thanks in advance

[Jason Hinsperger]

What kind of code sample?
ESQL, ODBC, Powerbuilder, VB, Delphi, etc...?

Jason Hinsperger
Product Quality
Adaptive Server Anywhere

[David Pylatuk]

Powerbuilder 6.5.1
Thanks

[Breck Carter]

The PowerBuilder 6.5 Help has code examples for both statements.

What problems are you having?

SELECTBLOB and UPDATEBLOB are embedded SQL commands, and as such you
have to check SQLCA.SQLCode afterwards. Is it non-zero? If so, what
does SQLCA.SQLErrText contain?

Have you defined the SQL Anywhere column as LONG BINARY?

Breck

[David Pylatuk]

We can sucessfully write to a database column of type LONG BINARY and
perform a commit without errors. The SELECTBLOB statement in fact does
not place any data in SQLCA.SQLCODE or in the ERRTEXT attributes. (At
least this is what the PB help says) What it is supposed to do is place
data in the SQLnRows attribute to indicate success or failure.

My 'blob' field is defined as a LONG BINARY type.

The code that gets placed in the SQLNROWS (zero) in my case indicates
that
no data was sucessfully read. The code I am using is as follows:

//
----------------------------------------------------------------------
blob lb_file, lb_out
int li_filenum, li_filelength, i
string ls_filename, ls_savefile, ls_temp

ls_filename = "D:\\test_in.bmp"
ls_savefile = "D:\\test_out"

li_filelength = FileLength(ls_filename)

// Clear table
DELETE FROM hole_doodle;
COMMIT USING SQLCA;

// Read it into the blob
li_filenum = FileOpen(ls_filename, StreamMode!, Read!, LockRead!)
FileRead(li_filenum, lb_file)
FileClose(li_filenum)

// Save it to the database
INSERT INTO "hole_doodle" ( "hole_number", "doodle") VALUES ( '10',
:lb_file );
IF SQLCA.SQLCode = 0 THEN
COMMIT USING SQLCA;
ELSE
MessageBox("DEBUG", "Error inserting blob")
RETURN
END IF

SQLCA.Autocommit = TRUE
UPDATEBLOB hole_doodle SET doodle = :lb_file WHERE hole_number = '10'
USING SQLCA;

IF SQLCA.SQLNRows > 0 THEN
COMMIT USING SQLCA;
ELSE
MessageBox("DEBUG", "Error writing blob to database")
RETURN
END IF

// Load it from the database
SELECTBLOB doodle INTO :lb_out FROM hole_doodle WHERE hole_number = '10'
USING SQLCA ;

IF SQLCA.SQLNRows > 0 THEN
// Write it to a file
li_filenum = FileOpen(ls_savefile + ".bmp", StreamMode!, Write!,
LockReadWrite!, Replace!)
FileWrite(li_filenum, lb_out)
FileClose(li_filenum)
ELSE
MessageBox("DEBUG", "Error reading blob from database")
END IF

END IF

//
----------------------------------------------------------------------

[Breck Carter]

All PB embedded SQL commands set SQLCA.SQLCode. Having it set to zero
is a necessary but not sufficient condition for correct operation. If
it comes back as -1 then the command did not work.

The PowerBuilder SELECTBLOB Help sentence to which you refer reads
like this: "To make sure the update affected at least one row, check
the SQLNRows property of SQLCA or the transaction object. The SQLCode
or SQLDBCode property will not indicate the success or failure of the
SELECTBLOB statement." Note the incorrect use of the word "update",
which indicates the lazy Help author simply copied the sentence from
UPDATEBLOB and did not make the correct changes. This kind of thing
happens throughout PB Help's discussion of arcane SQL topics (the
best/worst example is AutoCommit :)

[end rant]

The sentence for UPDATEBLOB reads thusly: "To make sure the update
affected at least one row, check the SQLNRows property of SQLCA or the
transaction object. The SQLCode or SQLDBCode property will not
indicate the success or failure of the UPDATEBLOB statement."

This does NOT say SQLCode is not set. It says checking SQLCode is not
sufficient to determine if the UPDATEBLOB worked. For example, if the
WHERE clause matches on zero rows, the statement works perfectly and
updates no rows.

The bottom line is this. Every single PowerBuilder command that uses
SQLCA MUST (IMHO) immediately be followed by a check of SQLCode to see
if it worked at all. After all, database servers can crash, etc, and
you want to know as early as possibly that something has gone wrong.

=====

Having said all that, except for the INSERT (which refers to the
uninitialized lb_file) the code looks OK... if the UPDATEBLOB works
then so should the SELECTBLOB. But please check SQLCode... :)

Does the row show up in ISQL?

Breck

On Tue, 07 Nov 2000 13:48:12 -0500, David Pylatuk