Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

retrieve blob field's textual data from tables in Sybase

Posted on 2006-11-07
3
Medium Priority
?
3,143 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 1000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

916 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