Solved

retrieve blob field's textual data from tables in Sybase

Posted on 2006-11-07
3
2,420 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

7 Experts available now in Live!

Get 1:1 Help Now