INSERT EMPTY BLOB TO NOT NULL COLUMN

[[email protected]]

EN: If I have a table with column type "image not null" is it possible to insert a reference to data that are empty.
CZ: Pokud mam tabulku se sloupcem typu "image not null" je možné do něj vložit odkaz na data, která jsou prázdná.

Example SQL:
DROP TABLE Test
GO
CREATE TABLE Test (
ID int NOT NULL PRIMARY KEY,
Data image not null
)
GO
INSERT INTO Test VALUES(1, 0x12)
INSERT INTO Test VALUES(2, 0x)
GO
SELECT * FROM Test
-- return
-- 1 0x12
-- 2 0x
GO

-- Change data in ID=1 for empty image (empty is not NULL!)
DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(Data)
FROM Test
WHERE ID=1
WRITETEXT Test.Data @ptrval '';
GO
SELECT * FROM Test -- return 1 0x
-- return
-- 1 0x
-- 2 0x

Example DELPHI:
var
mStream: TMemoryStream;
Query: TMSQuery;
Connection: TMSConnection;
begin
Connection := TMSConnection.Create(nil);
try
Connection.Database := 'Test';
Connection.Authentication := auWindows;
Connection.Server := 'JAVI-PC';
Connection.Connect;
Query := TMSQuery.Create(nil);
Query.Connection := Connection;
try
mStream := TMemoryStream.Create;
try
mStream.Write([1, 2], 2);
Query.Params.CreateParam(ftInteger, 'ID', ptInput).AsInteger := 3;
Query.Params.CreateParam(ftBlob, 'Data', ptInput).SetBlobData(mStream.Memory, mStream.Size);
Query.SQL.Text := 'INSERT INTO Test(ID, Data) VALUES(:ID, :Data)';
Query.Execute;

mStream.Clear;
Query.Params.Clear;
Query.Params.CreateParam(ftInteger, 'ID', ptInput).AsInteger := 4;
Query.Params.CreateParam(ftBlob, 'Data', ptInput).SetBlobData(mStream.Memory, mStream.Size);
Query.SQL.Text := 'INSERT INTO Test(ID, Data) VALUES(:ID, :Data)';
Query.Execute;

---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EMSError with message 'The statement has been terminated.
Cannot insert the value NULL into column 'Data', table 'test.dbo.Test'; column does not allow nulls. INSERT fails.'. Process stopped. Use Step or Run to continue.
---------------------------
OK Help
---------------------------

finally
Query.Free;
end;
finally
Connection.Close
end;
finally
Connection.Free;
end;
end;

EN:How do I set the empty blob Query, Query to think that it is a NULL value?
CZ:Jak nastavim do Query prazdny blob, aby si Query nemyslel, že se jedná o NULL hodnotu?

[Dimon]

To solve the problem you should use the following code:

Code: Select all

  if mStream.Size > 0 then
    Query..Params.CreateParam(ftBlob, 'Data', ptInput).SetBlobData(mStream.Memory, mStream.Size)
  else
    Query..Params.CreateParam(ftString, 'Data', ptInput).AsString := '';

[[email protected]]

Thank you very much the proposed solution works. Sincerely, Jan Javůrek.

[[email protected]]

column type IMAGE insert size = 0 varchar(1) '' OK

How insert data size = 0 to column type VARBINARY(MAX) not null.

column DriverParams
Query.Params.CreateParam(ftString, 'DriverParams', ptInput).AsString := '';

exec sp_executesql N'INSERT INTO DefinedB2BImports(ID,DriverName,OBJVERSION,Description,DriverGUID,CLSID,DriverParams,CLSIDName) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8)',N'@P1
varchar(10),@P2 varchar(34),@P3 int,@P4 varchar(40),@P5 varchar(26),@P6 varchar(26),@P7 varchar(1),@P8 varchar(15)','1000000000','Import dokladů do faktur přijatých',1,'Import faktury vydané
do faktury přijaté','2TIXBDUFGIEOJIPYQUM4JJBBPC','42HE04FZGJD13ACM03KIU0CLP4','','Faktura přijatá'

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

[Dimon]

To solve the problem you can use the following code:

Code: Select all

UniQuery.ParamByName('Data').DataType := ftVariant;
UniQuery.ParamByName('Data').Value := '';