• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3250
  • Last Modified:

retrieve blob field's textual data from tables in Sybase

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
jdsiam
Asked:
jdsiam
1 Solution
 
bretCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now