Finding out what a procedure is doing when you can't see the output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding out what a procedure is doing when you can't see the output

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-15 : 09:11:27

Adam writes "The following procedure is being called in ASP and seems to go through fine but the image doesnt get stored and the link is never made. I'm fixing someone elses mess so can't really stop storing images in the database and can't seperate the steps since ASPupload which is how I am getting the files into the database doesnt return anything from it's query (helpfully). I suppose the question could be better worded (and more useful for others) "How can I track a procedure when I can't directly see the output" Please take pity on a poor soul who has had half a system dumped in his lap.


CREATE PROCEDURE [dbo].[insertImage] 


@coid uniqueidentifier,
@keywords nvarchar(100),
@uid smallint,
@sid smallint,
@name nvarchar(255),
@width int,
@height int,
@size int,
@type nvarchar(10),
@stream bit,
@catid smallint,
@image image

AS

IF @coid = null 
	INSERT INTO [Image] (im_Data, im_Keywords, im_Author, im_Site, im_title, im_width, im_height, im_size, im_type, im_stream, im_category)
	VALUES (@image, @keywords, @uid, @sid, @name, @width, @height, @size, @type, @stream, @catid)

ELSE
	DECLARE @guid uniqueidentifier
	SET @guid = NEWID()
	PRINT CONVERT(varchar(255), @guid)

/*commented out as not necessary*/
	/*IF @sid = null AND @keywords = null SELECT @sid = co_site, @keywords = co_Keywords FROM Content WHERE co_ID = @coid*/

	INSERT INTO [Image] (im_ID, im_Data, im_Keywords, im_Author, im_Site, im_title, im_width, im_height, im_size, im_type, im_stream, im_category)
	VALUES (@guid, @image, @keywords, @uid, @sid, @name, @width, @height, @size, @type, @stream, @catid)

	INSERT INTO [ImageLink] (il_Content, il_Image)
	VALUES (@coid, @guid)
GO

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-08-15 : 14:13:44

To insert/modify IMAGE and TEXT data, you cannot use INSERT. You must use WRITETEXT or UPDATETEXT. Look at this code ( a sp of mine that uses a TEXT column)
--Retrieves and writes notes from the word processor into the Note column, which is of type TEXT
CREATE Procedure WriteWordProc @PatID INT,@VoucherNo INT = 0,@LineNum TINYINT = 0,@NewText TEXT, @Exists BIT
As
IF @Exists = 0
BEGIN
DECLARE @Ptr VARBINARY(16)
INSERT INTO WordProcessor(Patid,VoucherNo,LineNum,Note) VALUES (@PatID,@VoucherNo,@LineNum,'')
SELECT @Ptr = TEXTPTR(Note) FROM WordProcessor WHERE Patid = @PatID AND VoucherNo = @VoucherNo AND LineNum = @LineNum
WRITETEXT WordProcessor.Note @Ptr @NewText --alternatively use UPDATETEXT WordProcessor.Note @Ptr 0 0 @NewText
END
ELSE
SELECT @Ptr = TEXTPTR(Note) FROM WordProcessor WHERE Patid = @PatID AND VoucherNo = @VoucherNo AND LineNum = @LineNum
WRITETEXT WordProcessor.Note @Ptr @NewText --alternatively use UPDATETEXT WordProcessor.Note @Ptr 0 0 @NewText
RETURN

I do an existence check to see if the row I want exists, if not, must INSERT data first, with an empty string for the TEXT field. Then get the TEXTPTR (textpointer) to that row, and use WRITETEXT or UPDATETEXT to fill in the BLOB data. If a row already exists, get the pointer, and update the field.

BOL documents this topic fairly well.

HTH, and LOL!

Sarah Berger MCSD

Edited: Another issue I just thought of: Are you using ADO? Are you using the AppendChunk method to get the image data to the stored procedure? Make sure there's no error there by checking the value of the command's image parameter before you call execute.

Edited by - simondeutsch on 08/15/2002 14:16:17

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources