Output Image (binary) data from Stored Procedure 3

[TheInsider]

Hi,

I'm writing a small application in Java that accesses a SQL Server 2000 database via JDBC. One of my tables stores binary data (files) in a column of type IMAGE. These files may be anywhere from 30kB to 2MB in size. (Please don't reply telling me about the inefficiency of doing this. I've taken the courses, and I'm aware of the pitfalls.) Anyway, what I'm trying to do is something like the following:

CREATE PROCEDURE test
 @my_id INT,
 @fileData IMAGE OUTPUT
AS
 SELECT @fileData = fileData
 FROM my_table
 WHERE my_id = @my_id
GO

Unfortunately, the latter doesn't work because you can't assign IMAGE data to a variable. I've read up on TEXTPTR() and READTEXT, but really don't understand where the information from READTEXT actually goes.

I also can't do something like because it's also illegal:

DECLARE @size
SET @size = 123456
SET TEXTSIZE @size

I know I could use a straight SELECT statement and a Recordset / ResultSet, but I need the stored procedure to validate user credentials and do some other processing before I return the file data.

I've scoured the web looking for examples, but the only ones I've found are for inserting binary data, which is trivial, and I don't need a tutorial for that. And, because of Microsoft's unfortunate choice in naming the data type "IMAGE", most Google results return tutorials about pictures.

Is there any way of outputting the IMAGE data, other than by doing the following and using a Recordset / ResultSet to capture the data?

CREATE PROCEDURE test
 @my_id INT
AS
 SELECT fileData
 FROM my_table
 WHERE my_id = @my_id
GO

TIA

 

[AlexCuse]

Here seems a good place to start:

http://www.dbazine.com/sql/sql-articles/charran5

But what is stopping you from doing the user validation and what not, then returning the same select (if the user is validated successfully)?



[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[TheInsider]

Thanks for replying. I'll have a look at that tutorial.

But what is stopping you from doing the user validation and what not, then returning the same select (if the user is validated successfully)?

This is what I'm currently doing... or am in the process of doing. I've written the stored procedures and will be writing the Java code to interface with them sometime today. Since I'm fairly new to JDBC, this will be the first time that I've tried to mix a ResultSet with a CallableStatement. I'm just trying to determine if I've got the stored procedure method right before I waste a lot of time trying to get the Java code to interface with something that doesn't work in the first place.

 

[Crowley16]

instead of trying to return the data from an output variable, just get the stored procedure to directly return a resultset, and then bind your dataset to the execute of the sproc...

at least that's how it works in .NET...

--------------------
Procrastinate Now!

 

[ESquared]

The only way to return blob data is in a recordset. You can't return blob data (text, image) in an OUTPUT variable.

If you need to manipulate the blog before returning it to the client, you can use TEXTPTR and other blob manipulation functions to get the job done. It is hard work since you can't do anything more than 8000 characters at a time. You have to have an extra text pointer column. Updates have to be done in two parts: one pass for the regular values in the row and one looping or even nested looping pass to update each text row.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[TheInsider]

instead of trying to return the data from an output variable, just get the stored procedure to directly return a resultset, and then bind your dataset to the execute of the sproc...

Yes, this is what I'm currently doing; my stored procedure checks for the user's credentials and then returns the recordset/cursor containing the file data (i.e. SELECT fileData FROM...). My original hope was to return it as an output variable because that would be cleaner to code in my client application, but I can see now that it isn't possible. Thanks.

If you need to manipulate the blog before returning it to the client, you can use TEXTPTR and other blob manipulation functions to get the job done. It is hard work since you can't do anything more than 8000 characters at a time. You have to have an extra text pointer column. Updates have to be done in two parts: one pass for the regular values in the row and one looping or even nested looping pass to update each text row.

Wow, then it's a good thing that I don't need to manipulate the data before returning it!

Thanks, you have all clarified my suspicions that what I'm currently doing is the correct method.

 

[ESquared]

blog blob