?
Solved

Output parameter for a stored procedure

Posted on 2003-03-14
17
Medium Priority
?
803 Views
Last Modified: 2008-02-01
Hello

Is it possible to return the data from an Image column as an output parameter?

The DataFile column in table Application can contain an word, pdf or a text document.

Example
CREATE PROCEDURE [dbo].[getApplication]
@DataFile as Image OUTPUT
AS

SELECT @DataFile = DataFile FROM Application WHERE ID = 1
GO

Example Table def
CREATE TABLE [dbo].[Application] (
     [ID] [int] NOT NULL ,
     [DataFile] [image] NOT NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

0
Comment
Question by:johan_asplund
[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
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 4

Expert Comment

by:chaos_hooi
ID: 8134779
Yes... Here is the except from SQL Server Book Online.

OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.
0
 
LVL 1

Author Comment

by:johan_asplund
ID: 8134813
Ok but when i create my stored procedure i get an error saying

"Error 409: The assignment operator operation cannot take a image datatyle as an argument."

I guess that i have to use something else instead of
... @DataFile = DataFile ...

0
 
LVL 1

Author Comment

by:johan_asplund
ID: 8134849
Yes the help says its possible to have image as a parameter but when i try to use it i get an error

"Error 409: The assignment operator operation cannot take a image data type as an argument"

I guess there is some other trick to use instead of

... @DataFile = DataFile ...

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:johan_asplund
ID: 8134857
Sorry for the 2 posts but i did not see that the first one was sent/saved.
I had more than one explorer open.

/Johan
0
 
LVL 4

Expert Comment

by:chaos_hooi
ID: 8134862
I looked it up, you have to use a point to point at the image (TEXTPTR), and then use READTEXT to read the image off the column... here is an example from the MS SQL Book Online....

USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info pr INNER JOIN publishers p
      ON pr.pub_id = p.pub_id
      AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO
0
 
LVL 4

Expert Comment

by:chaos_hooi
ID: 8134866
I mean, use a pointer... not a point... ^_^;;
0
 
LVL 1

Author Comment

by:johan_asplund
ID: 8134969
But that means that i cant have a image parameter in my stored procedure?

i guess that the result of the READTEXT is a recordset?

i tried and that did not work.

CREATE PROCEDURE [dbo].[getApplication]
@DataFile as Image OUTPUT
AS
DECLARE @DataFilePtr varbinary(16)

SELECT @DataFilePtr = TEXTPTR(DataFile) FROM Application WHERE ID = 1

@DataFile = READTEXT db.Application @DataFilePtr 0 0

GO



/Johan
0
 
LVL 4

Accepted Solution

by:
chaos_hooi earned 1500 total points
ID: 8135236
Sorry, I had been doing some reading, after your post, but cannot find anything to help you... if your image size is less than 8 kb, you can using substring of an image, which will return you a varbinary datatype...

"In general, use varbinary for storing binary data, unless the length of the data exceeds 8 KB, in which case you should use image. It is recommended that the defined length of a binary column be no larger than the expected maximum length of the binary data to be stored."

Actually, I read about WRITETEXT reading from a data to the column, so I assume READTEXT should be able to do the opposite... sigh... Really sorry... FYI, actually, you cannot create a temporary image variable...
0
 
LVL 1

Author Comment

by:johan_asplund
ID: 8135626
No problem Thanx for your time.

I guess that Microsoft should put a NOT in the text
"Text, ntext, and image parameters can be used as OUTPUT parameters." in SQL Server Book Online.

So then the answer to this this question must be No it is not possible unless you have a Image column with a small amount of data.


/Johan
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8136363
You can return image, but you must set it by Extended stored procedure first. Write some C++ code ...

Good luck !
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8136378
As a workaround consider using ADO's Stream object instead of an output parameter.

Let me know if you need more details.

Anthony
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8136560
Or ADODB.Recordset.Fields("Field name").GetChunk, but it is more for some controls forum :)
0
 
LVL 1

Expert Comment

by:Computer101
ID: 8362306
A request for deletion or PAQ has been made.  If no response or you feel this is in error, comment.  If no objection, myself or another Moderator will handle this question in three days.

Computer101
E-E Admin
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8376715
Computer101,
  Do not delete it, because of it has some value. Especially chaos_hooi helped johan to find unsolvability of this question. Recover points and move this thread to PAQ. If johan wants to spare points, I award chaos_hooi by myself. But I will wait for johan's reply.

johan,
  it is possible (03/14/2003 06:41AM PST), but it is not so easy. I haven't write an extended stored procedure yet. I scheduled an refresh of my C++ knowledge to the next month :)
  Also acperkins offered a code replacement of the text parameter. It is better to send to your thread before you post to SC.
  But in a simple SQL, you cannot set a BLOB to a variable, as you have found and I tried it before, many times.
  chaos_hooi helped you to find the answer, even it is negative. If you think 500 points is too much, reduce the points.

Best regards, Ivo Spaleny.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8377417
ispaleny,

I agree.

Anthony
0
 
LVL 1

Author Comment

by:johan_asplund
ID: 8378110
Thanx for fast response.

Br
Johan
0
 
LVL 4

Expert Comment

by:chaos_hooi
ID: 8384443
I didn't expect any points here. ^_^;; Thanks all. ^_^
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

765 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