Solved

retrieve blob field's textual data from tables in Sybase

Posted on 2006-11-07
3
2,489 Views
Last Modified: 2008-01-09
I am interacting with a Sybase database, which is being used at a customers site for running PowerSchool. I don’t think the OS of the target system is involved with this issue.
I cannot determine how to retrieve blob fields’ textual data from certain tables in Sybase. The actual field datatype in question is “IMAGE” which is merely being used to store (potentially) large amounts of textual data.

For example, here is a table definition I am encountering (written in SQL):
Query:
sp_columns sectionscores
Edited Response:

column_name                    data_type   type_name        precision   length      

-----------                    ----------- ---------        ----------- -----------

DCID                                    4 int                       10           4

ID                                        4 int                       10           4

SectionID                              4 int                       10           4

STID                                     4 int                       10           4

Assignment                          -4 image             2147483647          16

Percent                                 -4 image             2147483647          16

Score                                   -4 image             2147483647          16

GRD                                     -4 image             2147483647          16

comment_value                           -4 image             2147483647          16

Unused1                                  4 int                       10           4
Previous Query Attempts:

I have already tried SQL of the following form (this query is for testing purposes) and other variations of this:

DECLARE @textptr binary(16), @blobsize int, @chunkindex int, @chunksize int
SET TEXTSIZE 64

BEGIN TRAN
SELECT @textptr=TEXTPTR(Assignment), @blobsize=DATALENGTH(Assignment),

      @chunkindex=0, @chunksize=CASE WHEN @@TEXTSIZE < @blobsize THEN

      @@TEXTSIZE ELSE @blobsize END

FROM sectionscores WHERE DCID=318612


IF(@textptr IS NOT NULL) AND (@chunksize > 0)

WHILE (@chunkindex < @blobsize) AND (@@ERROR = 0) BEGIN

      READTEXT sectionscores.Assignment @textptr @chunkindex @chunksize

      SELECT @chunkindex=@chunkindex+@chunksize,

            @chunksize=CASE WHEN (@chunkindex+@chunksize) > @blobsize THEN @blobsize-@chunkindex ELSE @chunksize END

END

COMMIT TRAN

SET TEXTSIZE 0

All I get back from the usage of READTEXT is merely the same Data Pointer value as if I had just issued the following query:

SELECT Assignment FROM sectionscores WHERE DCID=318612

THanks
0
Comment
Question by:jdsiam
3 Comments
 
LVL 10

Accepted Solution

by:
bret earned 250 total points
ID: 17899809
I think I would expect the two queries to generate the same value - the
simple "SELECT Assignment ..." just does it all at once, the other is
doing so a chunk at a time, but the end result will be the binary blob
value that was stored in the column.

ASE doesn't know that that value is supposed to be readable text,
it is just arbitrary binary data.  The client side would be expected
to know what to do with the blob (i.e. display it as text, as a GIF
image, as a jpg image, play it as a WAVE sound file, etc.)

ASE also doesn't allow explicit conversion from IMAGE to CHAR
or VARCHAR.  But it does allow it to BINARY, and BINARY can
be converted to CHAR or VARCHAR, so there is a
fairly easy way to convert the blob to text on the
server side, providing the value isn't over 16384 bytes (the
maximum size of a varchar in memory):

select convert(varchar(16384), convert(binary(16384), Assignment)
from sectionscores WHERE DCID=318612

Anything longer than that is harder to deal with (not impossible,
there is hack possible using proxy tables - see my newsgroup posting
http://tinyurl.com/yzx5wo for details )
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now