I've just had a chance to do some testing on this.
It looks like the problem is your code attempts to use the UPDATETEXT command on a text field which is currently set to NULL. Certainly, attempting to do this will produce the error message you report.
You can test this using the attached script, which creates a table (text_text) with an id column, a varchar field and a text field, inserts two rows with the text field set to null, updates the first using a regular update statement then amends it with UPDATETEXT, then updates the second (null) text field using UPDATETEXT.
CREATE TABLE [test_text] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[vc] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[txt] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert test_text (vc)
select 'hello, number 1'
go
insert test_text (vc)
select 'hello, number 2'
go
update test_text set txt = '12325rfegsfdgervsdfgvsdfgvfdsgv'
where id = 1
go
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(tt.txt)
FROM test_text tt
WHERE tt.id = 1
UPDATETEXT test_text.txt @ptrval 16 1 'b'
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(tt.txt)
FROM test_text tt
WHERE tt.id = 2
UPDATETEXT test_text.txt @ptrval 16 1 'b'
GO
Perhaps ODBC statements use UPDATETEXT rather than a conventional update statement. I realise it's late in the day to be suggesting this, but do you have to use ODBC rather than OLEDB?
UPDATE: A further test reveals that executing the SQL directly using Win32::ODBC does not show a similar error. I don't have time to run the test using DBD::ODBC, but it's possible that that's where the problem lies. | [reply]
[d/l] |
It would seem that *something* is calling UpdateText-although I am not doing it directly. I am only using ANSI sql.
It is curious why i cannot get this test/example (below) to fail:
If I have a table with (mrID, mrDESCRIPTION) which is an (int, text)--
I can have $data be an empty string, or hava a value.
eval
{
$dbh->do("DELETE FROM MASTER1 WHERE mrID=999");
$dbh->do("INSERT INTO MASTER1 (mrID) VALUES (999)");
$sql = "UPDATE MASTER1 SET mrDESCRIPTION=? WHERE mrID=999";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $data, DBI::SQL_LONGVARCHAR);
$sth->execute();
$dbh->commit();
};
This is a simplified version of what IS apparently failing for the user... hmm. And thanks for the example!
Matt | [reply]
[d/l] |