Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

Appending text to a column in SQL

by sgifford (Prior)
on Oct 17, 2006 at 23:02 UTC ( #578924=perlquestion: print w/replies, xml ) Need Help??
sgifford has asked for the wisdom of the Perl Monks concerning the following question:

Hello fellow monks,

I'm trying to append some text to a column in a database table, in a way that's straightforward and atomic. Basically I want to say:

UPDATE table SET item = append(item,'new text\r\n')
The database is currently Microsoft SQL Server, and the data type of this column is Text (basically a BLOB of text, as compared to a string). Ideally I'd like something portable, but I'll take what I can get.

We're writing into a database used by a proprietary application, so I can't make any changes to the table structure.

Currently I'm reading the column, appending the text, then writing it back out. This works, but is ugly, and prone to race conditions.

Any suggestions?

Replies are listed 'Best First'.
Re: Appending text to a column in SQL
by grep (Monsignor) on Oct 17, 2006 at 23:25 UTC
    In SQL Server the concat operator is '+' (unlike most other SQL implementations where it is '||').

    UPDATE tablename SET foo = foo + 'bar'

    This is atomic and should not cause a race condition.

    One dead unjugged rabbit fish later
        Sorry about that - PostgreSQL does let you concatenate ('||') text fields. It's been awhile since I had to deal with SQLServer weirdness.

        Well then you're either left with running a transaction around your read/append/write or CAST the text field to varchar then concat (assuming the data can be cast).

        I would go for the transaction approach, you'll be sure it'll work with any kind of data.

        As for the ugliness it should be easy to make a method to hide the $dbh->{AutoCommit} = 0 (or $dbh->begin_work), read, append, update, $dbh->commit. Don't forget to turn AutoCommit off if it's on. In fact I would just add that in there to be safe.

        local $dbh->{AutoCommit} = 0; local $dbh->{RaiseError} = 1; # You can RaiseError and eval the transaction # or you can check $sth->err each time and rollback eval { ##Do your read and write here $dbh->commit; }; if ($@) { warn "append failed $@"; $dbh->rollback; }
        UPDATE: something similar to this
        sub append_to_text { my $dbh = shift; my $table = shift; my $id = shift; my $col = shift; my $data = shift; local $dbh->{RaiseError} = 1; eval { $dbh->begin_work(); my $read = qq| SELECT $col FROM $table WHERE id = ? |; my $update = qq| UPDATE $table SET $col = ? WHERE id = ? |; my $sth_read = $dbh->prepare($read); $sth_read->execute($id); my $row = $sth_read->fetchrow_hashref(); $sth_read->finish(); my $current = $row->{$col}; $current .= $data; my $rtn = $dbh->do($update,undef,$current,$id); $dbh->commit() if ($rtn); }; if ($@) { warn "Append Failed: $@\nStatement: $DBI::lasth->{Statement} +"; eval { $dbh->rollback }; } }

        One dead unjugged rabbit fish later
Re: Appending text to a column in SQL
by imp (Priest) on Oct 18, 2006 at 02:42 UTC
    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.
      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...


        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");

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://578924]
Approved by Arunbear
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (4)
As of 2018-02-21 23:35 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (288 votes). Check out past polls.