Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

retrieve blob field's textual data from tables in Sybase

Posted on 2006-11-07
3
Medium Priority
?
3,036 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Read on to get a few ideas on how to promote your next corporate event.
In this article, WatchGuard's Director of Security Strategy and Research Teri Radichel, takes a look at insider threats, the risk they can pose to your organization, and the best ways to defend against them.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

722 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