in reply to Appending text to a column in SQL

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';

[download]
Not exactly elegant, but it works.

Replies are listed 'Best First'.
Re^2: Appending text to a column in SQL
by sgifford (Prior) on Oct 18, 2006 at 03:17 UTC
    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!


    --
    sgifford's Web page
[reply]
      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");

      [download]
      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

      [download]
      And then in your code: 
      my $sql = q{exec append_thingy @new_text = ?};
$dbh->do($sql,{},"new text");

      [download]
[reply]
[d/l]
[select]
        Thanks imp! I finally got to my client's site to try that code out, and it worked perfectly!

        --
        sgifford's Web page
[reply]
        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
[reply]

In Section Seekers of Perl Wisdom