jdsiam
asked on
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(Assignmen t), @blobsize=DATALENGTH(Assig nment),
@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+@c hunksize,
@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
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(Assignmen
@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+@c
@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.