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 (<br>
 ItemChunkId INT NOT NULL PRIMARY KEY, <br>
 ItemChunk image NOT NULL<br>
) <br>
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)<br>
AS<br>
BEGIN<br>
    DECLARE @data_offsets TABLE(item INT NOT NULL, offset INT NOT NULL, length INT NOT<br>NULL) <br>
    INSERT INTO @data_offsets (item, offset, length) values(1, 1, 200) <br>
    INSERT INTO @data_offsets (item, offset, length) values(2, 201, 10000) <br>
    INSERT INTO @data_offsets (item, offset, length) values(3, 10201, 30) <br>
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) <br>
DECLARE @chunk2_1 VARCHAR(8000) <br>
DECLARE @chunk2_2 VARCHAR(8000) <br>
DECLARE @chunk2_3 VARCHAR(4000) <br>
DECLARE @chunk3 VARCHAR(60) <br>
SET @chunk1 = replicate('af', 200) <br>
SET @chunk2_1 = replicate('be', 4000) <br>
SET @chunk2_2 = replicate('be', 4000) <br>
SET @chunk2_3 = replicate('cd', 2000) <br>
SET @chunk3 = replicate('dc', 30) <br><br>
EXEC('EXEC InsertItemChunks 0x' + @chunk1 + @chunk2_1 + @chunk2_2 + @chunk2_3 + <br>@chunk3) <br><br>
SELECT *, datalength(ItemChunk) <br>
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) <br>
    SELECT d.item, SUBSTRING(@data, d.offset, CASE<br>
    WHEN d.length > 8000 THEN 8000 ELSE d.length END) <br>
    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<br>
DROP PROC InsertItemChunks<br>
GO<br><br>
CREATE PROCEDURE InsertItemChunks (@data image) <br>
AS<br>
BEGIN<br>
    DECLARE @data_offsets table(item INT NOT NULL, offset INT NOT NULL, <br>
   length INT NOT NULL) <br>
    INSERT INTO @data_offsets (item, offset, length) values(1, 1, 200) <br>
    INSERT INTO @data_offsets (item, offset, length) values(2, 201, 10000) <br>
    INSERT INTO @data_offsets (item, offset, length) values(3, 10201, 30) <br><br>
    -- Insert rows with all offsets first. <br>
    -- Data with length > 8000 will automatically get truncated to 8000 and inserted because<br>
    -- SUBSTRING can return only a maximum of 8000 bytes. <br>
    INSERT INTO ItemChunks (ItemChunkId, ItemChunk) <br>
    SELECT d.item, SUBSTRING(@data, d.offset, CASE WHEN d.length > 8000<br>
   THEN 8000 ELSE d.length END) <br>
    FROM @data_offsets AS d<br><br>
    DECLARE @large_vals CURSOR<br>
    DECLARE @offset INT<br>
    DECLARE @length INT<br>
    DECLARE @textptr binary(16) <br>
    DECLARE @start INT<br>
    DECLARE @overflow varbinary(8000) <br><br>
    -- Get the items with length > 8000 and their textptr values: <br>
    SET @large_vals = CURSOR FOR<br>
                      SELECT textptr(i.ItemChunk), d.offset, d.length - 8000<br>
                        FROM @data_offsets AS d<br>
                        JOIN ItemChunks AS i<br>
                          ON d.item = i.ItemChunkId<br>
                       WHERE d.length > 8000<br>
    OPEN @large_vals<br>
    WHILE(1=1) <br>
    BEGIN<br>
        fetch @large_vals INTO @textptr, @offset, @length<br>
        IF @@fetch_status
<br>
        -- Use UPDATETEXT to append the remaining data to existing value: <br>
        SET @start = @offset + 8000<br>
        while(@length > 0) <br>
        BEGIN<br>
            SET @overflow = SUBSTRING(@data, @start, CASE WHEN @length > 8000 <br>
   THEN 8000 ELSE @length END) <br>
            UPDATETEXT ItemChunks.ItemChunk @textptr NULL 0 @overflow<br>
            SET @length = @length - CASE WHEN @length > 8000 <br>
   THEN 8000 ELSE @length END<br>
            SET @start = @start + @length<br>
        END<br>
    end<br>
    DEALLOCATE @large_vals<br>
END<br>
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 challenge@sqlmag.com 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<br>
GO<br>
CREATE TABLE dbo.Sequences ( SeqName varchar(30) NOT NULL PRIMARY KEY, <br>NextNum bigint NOT NULL DEFAULT 0) <br>
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #1', <br> DEFAULT ) <br>
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #2', <br> DEFAULT ) <br>
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #3', <br> DEFAULT ) <br>
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<br>
GO<br>
CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName varchar(30), @NextNum<br> bigint = NULL OUTPUT) <br>
AS<br>
BEGIN<br>
    BEGIN TRANSACTION<br>
    SET @NextNum = (SELECT NextNum FROM dbo.Sequences WHERE SeqName =<br> @SeqName) <br>
    UPDATE dbo.Sequences<br>
    SET NextNum = NextNum + 1<br>
    WHERE SeqName = @SeqName<br>
    COMMIT TRANSACTION<br>
END<br>
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.