Re: Appending text to a column in SQL

[imp]

I no longer have access to Microsoft SQL server 2000 to test with, but you can append to text columns using UPDATETEXT and TEXTPTR

Something like this:

DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(item) 
   FROM table 
   WHERE whatever criteria

UPDATETEXT table.item @ptrval NULL 0 'new text';

Not exactly elegant, but it works.

[sgifford]

Hi imp,

Any pointers on how to do that with Perl/DBI? I can't seem to find any examples, and haven't been able to figure out the right syntax...

Thanks!

[imp]

I used a stored procedure for encapsulating that behaviour when I had a similar problem in the past, but if you don't have permission to add stored procedures you should be able to run the sequence of commands inline. I don't have access to a server to test with currently though, so your mileage may vary.

Something like this:

my $sql = 'DECLARE @ptrval binary(16);'
        . ' SELECT @ptrval = TEXTPTR(item)'
        . ' FROM table '
        . ' WHERE whatever criteria;'
        . ' UPDATETEXT table.item @ptrval NULL 0 ?';
$dbh->do($sql,{},"new text");

But of course that's horribly ugly.

So, a stored procedure to encapsulate the behaviour:

CREATE PROCEDURE dbo.APPEND_THINGY
@id int,
@new_text varchar(8000)
AS

DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(item) 
   FROM table 
   WHERE id = @id

UPDATETEXT table.item @ptrval NULL 0 @new_text;

GO

And then in your code:

my $sql = q{exec append_thingy @new_text = ?};
$dbh->do($sql,{},"new text");

[sgifford]

Thanks imp! I finally got to my client's site to try that code out, and it worked perfectly!

[Anonymous Monk]

CREATE PROCEDURE dbo.APPEND_THINGY @id int, @new_text varchar(8000) AS Update table SET field = cast(field as varchar(max)) + @new_text where id = @id GO