x

Cannot Update table when using a Cursor

Hi, I am using a Cursor to parse filepaths. The parsing is working fine but the values aren't being written back to the table. There are no error messages. Can anyone suggest why?

USE [Music ETL] 
go

DECLARE @SlashPos1 AS INT
DECLARE @SlashPos2 AS INT
DECLARE @SlashPos3 AS INT
DECLARE @SlashPos4 AS INT
-- Define the Cursor and what it covers ie the five columns from Transform-Albums ---

DECLARE AlbumCursor CURSOR FOR 
SELECT DirLineNo, AlbumPath, Genre, AlbumArtist, Album
FROM dbo.[Transform-Albums]
FOR UPDATE 

-- Start the Cursor running through the rows-----------------
OPEN AlbumCursor 

-- Declare temporary variables within the Cursor Loop------------
DECLARE @TempDirLineNo AS INT
DECLARE @TempPath varchar(255)
DECLARE @TempGenre varchar(25)
DECLARE @TempAlbumArtist varchar(50)
DECLARE @TempAlbum varchar(255)

-- Perform the first fetch--------------------------------
FETCH NEXT FROM AlbumCursor into @TempDirLineNo, @TempPath, @TempGenre,    @TempAlbumArtist, @TempAlbum

-- Check @@FETCH_STATUS to see if there are any more rows to fetch--------------
WHILE @@FETCH_STATUS = 0 BEGIN 

-- Zeroise the temporary delimiter variable---------------------------- 
SET @SlashPos1 = 0
SET @SlashPos2 = 0
SET @SlashPos3 = 0
SET @SlashPos4 = 0

-- -----Locate delimiters------------------------------------
SET @SlashPos1 = CHARINDEX('\', @TempPath, 1)

IF (@SlashPos1 <> 0)
BEGIN
SET @SlashPos2 = CHARINDEX('\', @TempPath, @SlashPos1 + 1)
END 

IF (@SlashPos2 <> 0)
BEGIN
SET @SlashPos3 = CHARINDEX('\', @TempPath, @SlashPos2 + 1)
END 

IF (@SlashPos3 <> 0)
BEGIN
SET @SlashPos4 = CHARINDEX('\', @TempPath, @SlashPos3 + 1)
END 

-- -----Parse the row ONLY where there an Album Name-----------------------------
IF (@SlashPos4 <> 0 )
BEGIN
SET @TempGenre = SUBSTRING(@TempPath, @SlashPos2 + 1, @SlashPos3 - @SlashPos2 - 1)
SET @TempAlbumArtist = SUBSTRING(@TempPath, @SlashPos3 + 1, @SlashPos4 - @SlashPos3 -    1)
SET @TempAlbum = SUBSTRING(@TempPath, @SlashPos4 + 1, LEN(@TempPath) - @SlashPos4)

-- OUTPUT inserted.genre AS newgenre
PRINT @TempDirLineNo
PRINT @TempGenre
PRINT @TempAlbumArtist
PRINT @TempAlbum
END

-- -----Fetch the next row------------------------------------
FETCH NEXT FROM AlbumCursor into @TempDirLineNo, @TempPath, @TempGenre,    @TempAlbumArtist, @TempAlbum

END 

-- Stop the Cursor running through the rows----------------------
CLOSE AlbumCursor

DEALLOCATE AlbumCursor
GO 
more ▼

asked Jul 25, 2012 at 06:52 AM in Default

Karl gravatar image

Karl
20 1 1 1

can you give us the details about the transform-albums table and a sample of its data please?
Jul 25, 2012 at 07:22 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

First mistake: a CURSOR to update the table which can be a single UPDATE statement

Second mistake: Used CURSOR with FOR UPDATE option but forgot to update the table. Use UPDATE TABLE with WHERE CURRENT OF option

UPDATE dbo.[Transform-Albums]
SET COLUMNNAMES = @VALUES
WHERE CURRENT OF AlbumCursor
more ▼

answered Jul 25, 2012 at 07:38 AM

robbin gravatar image

robbin
1.6k 1 3 5

Robbin,

Thanks for this. It worked. I took the code from a respected book and have triple-checked I followed the form. It doesn't have the UPDATE that you suggested so maybe the code wasn't intended to update the table. I thought it odd at the time but as a newbie I assumed the book was correct and would do an update.

I really appreciate your help.

The reason for the use of a cursor is that the code is trying to analyse strings that are not consistent. The path could just point to a genre folder, others to the sub-folders for artists and album names. It looks for '' chars. Some have two, others have three or four. I didn't feel the data was consistent enough for set-based parsing so each string is analysed in turn. Here are three examples;

Directory of L:\Music\ANDINO
Directory of L:\Music\ANDINO\Caliche
Directory of L:\Music\ANDINO\Caliche\Music of the Andes

The number and position of the slashes varies.

If you can think of a way to do this set-based I would be delighted to hear about it as a newbie trying to learn the pro techniques.

I really would like to thank you once again for your tremendous help.

Cheers,

Jul 25, 2012 at 07:25 PM Karl
(comments are locked)
10|1200 characters needed characters left

As @robbin says, it is very likely that a cursor is not going to be the most efficient way of making the updates that you want to. An UPDATE statement that applies to a set of rows in the table will be much quicker and cause less drain on server resources (CPU etc). If you want a hand working on that then feel free to start a new question but there are a lot of good resources on how to work effectively with SETS of data, which is how SQL Server is built to run.

The construct of your code does seem to be missing a statement to actually update a table so adding that will certain make a difference. Currently the variables you are using get the values accurately, you just need to do something with them other than show them on screen with the PRINT command.

As a side matter, I would seriously consider changing the table name. Using a hyphen (-) character in a table name is not a good idea. If you cant use TransformAlbum then how about Transform_Album?
more ▼

answered Jul 25, 2012 at 07:57 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x131
x58

asked: Jul 25, 2012 at 06:52 AM

Seen: 1437 times

Last Updated: Jul 25, 2012 at 07:29 PM