There were no winners for the November Reader Challenge, "Parse and Insert Data from Uploaded Image Files." Here’s a recap of the problem and the solution to the November Reader Challenge.
Problem:
Denny is a database developer who needs to develop a Web portal for intranet use. This Web portal will use a SQL Server 2000 database as the data store. The portal has functionality that lets users upload binary data, such as image files and Microsoft Office Outlook messages, to the database. The uploaded files are stored in a table called ItemChunks with the following schema:
CREATE TABLE ItemChunks (
ItemChunkId INT NOT NULL PRIMARY KEY,
ItemChunk image NOT NULL
)
GO
The Web portal page that lets users upload binary data has the following characteristics:
1. The page sends the binary data as a single BLOB to SQL Server.
2. One or more binary data files can be uploaded from the page in one transaction.
3. The file sizes can range from 1K to 20K.
Denny is designing a stored procedure that can insert the data from the BLOB into the ItemChunks table. The skeleton stored procedure looks like the following code. For the sake of simplicity, assume that the data offsets and length of each binary data file contained in the parameter value are known.
CREATE PROCEDURE InsertItemChunks (@data image)
AS
BEGIN
DECLARE @data_offsets TABLE(item INT NOT NULL, offset INT NOT NULL, length INT NOT
NULL)
INSERT INTO @data_offsets (item, offset, length) values(1, 1, 200)
INSERT INTO @data_offsets (item, offset, length) values(2, 201, 10000)
INSERT INTO @data_offsets (item, offset, length) values(3, 10201, 30)
END
Help Denny write the stored procedure logic to parse and insert the data from the image value into the ItemChunks table. Denny can use the item column in the @data_offsets table as the primary key value for the ItemChunks table.
Denny can test the stored procedure by using the following code. (The batch generates a binary data value of 10,230 bytes with three chunks each containing 200 bytes, 10,000 bytes, and 30 bytes of data respectively.)
DECLARE @chunk1 VARCHAR(400)
DECLARE @chunk2_1 VARCHAR(8000)
DECLARE @chunk2_2 VARCHAR(8000)
DECLARE @chunk2_3 VARCHAR(4000)
DECLARE @chunk3 VARCHAR(60)
SET @chunk1 = replicate('af', 200)
SET @chunk2_1 = replicate('be', 4000)
SET @chunk2_2 = replicate('be', 4000)
SET @chunk2_3 = replicate('cd', 2000)
SET @chunk3 = replicate('dc', 30)
EXEC('EXEC InsertItemChunks 0x' + @chunk1 + @chunk2_1 + @chunk2_2 + @chunk2_3 +
@chunk3)
SELECT *, datalength(ItemChunk)
FROM ItemChunks
Solution:
Denny can use the SUBSTRING function in SQL Server to parse and retrieve the individual values from the image data passed to the stored procedure. However, the SUBSTRING function can retrieve only a maximum of 8000 bytes. To insert only values that are less than or equal to 8000 bytes, Denny must use the following code:
INSERT INTO ItemChunks (ItemChunkId, ItemChunk)
SELECT d.item, SUBSTRING(@data, d.offset, CASE
WHEN d.length > 8000 THEN 8000 ELSE d.length END)
FROM @data_offsets AS d
For the values that exceed 8000 bytes, Denny has to the UPDATETEXT statement in his logic. UPDATETEXT can append data to an existing text, ntext, or image value by using the text pointer of an existing value. So, for values that are larger than 8000 bytes, Denny can parse the image data multiple times and use UPDATETEXT to append the remaining value from @data.
The modified stored procedure that performs these operations is shown in the following code:
IF OBJECT_ID('InsertItemChunks') IS NOT NULL
DROP PROC InsertItemChunks
GO
CREATE PROCEDURE InsertItemChunks (@data image)
AS
BEGIN
DECLARE @data_offsets table(item INT NOT NULL, offset INT NOT NULL,
length INT NOT NULL)
INSERT INTO @data_offsets (item, offset, length) values(1, 1, 200)
INSERT INTO @data_offsets (item, offset, length) values(2, 201, 10000)
INSERT INTO @data_offsets (item, offset, length) values(3, 10201, 30)
-- Insert rows with all offsets first.
-- Data with length > 8000 will automatically get truncated to 8000 and inserted because
-- SUBSTRING can return only a maximum of 8000 bytes.
INSERT INTO ItemChunks (ItemChunkId, ItemChunk)
SELECT d.item, SUBSTRING(@data, d.offset, CASE WHEN d.length > 8000
THEN 8000 ELSE d.length END)
FROM @data_offsets AS d
DECLARE @large_vals CURSOR
DECLARE @offset INT
DECLARE @length INT
DECLARE @textptr binary(16)
DECLARE @start INT
DECLARE @overflow varbinary(8000)
-- Get the items with length > 8000 and their textptr values:
SET @large_vals = CURSOR FOR
SELECT textptr(i.ItemChunk), d.offset, d.length - 8000
FROM @data_offsets AS d
JOIN ItemChunks AS i
ON d.item = i.ItemChunkId
WHERE d.length > 8000
OPEN @large_vals
WHILE(1=1)
BEGIN
fetch @large_vals INTO @textptr, @offset, @length
IF @@fetch_status
-- Use UPDATETEXT to append the remaining data to existing value:
SET @start = @offset + 8000
while(@length > 0)
BEGIN
SET @overflow = SUBSTRING(@data, @start, CASE WHEN @length > 8000
THEN 8000 ELSE @length END)
UPDATETEXT ItemChunks.ItemChunk @textptr NULL 0 @overflow
SET @length = @length - CASE WHEN @length > 8000
THEN 8000 ELSE @length END
SET @start = @start + @length
END
end
DEALLOCATE @large_vals
END
GO
DECEMBER READER CHALLENGE:
Now, test your SQL Server savvy in the December Reader Challenge, "Improve T-SQL to Prevent Deadlocks" (below). Submit your solution in an email message to [email protected] by November 22. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Problem:
Manoj is a database developer for a company that develops business applications that use SQL Server 2000 as a database server. In Manoj's environment, the database that the applications use contains a table that stores sequential numbers used by various features. The table stores the name of the sequence and the next number. The schema of the table is shown in the following code:
USE tempdb
GO
CREATE TABLE dbo.Sequences ( SeqName varchar(30) NOT NULL PRIMARY KEY,
NextNum bigint NOT NULL DEFAULT 0)
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #1',
DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #2',
DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #3',
DEFAULT )
GO
The database also contains a GetNextSeqNum stored procedure, which the applications use to get the next number in a particular sequence. The stored procedure is shown in the following code:
USE tempdb
GO
CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName varchar(30), @NextNum
bigint = NULL OUTPUT)
AS
BEGIN
BEGIN TRANSACTION
SET @NextNum = (SELECT NextNum FROM dbo.Sequences WHERE SeqName =
@SeqName)
UPDATE dbo.Sequences
SET NextNum = NextNum + 1
WHERE SeqName = @SeqName
COMMIT TRANSACTION
END
GO
When Manoj tests the stored procedure with concurrent SQL Server connections, he notices that some of the calls encounter deadlocks and fail. Modify the stored procedure logic to help Manoj resolve the deadlock problem.
