Devart Forums

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?

To solve the problem you should use the following code:

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

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.

To solve the problem you can use the following code: