INSERT EMPTY BLOB TO NOT NULL COLUMN

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

INSERT EMPTY BLOB TO NOT NULL COLUMN

Post by jan.javurek@floresps.cz » Fri 29 Jan 2010 10:07

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 29 Jan 2010 14:19

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

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

Post by jan.javurek@floresps.cz » Fri 29 Jan 2010 22:34

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

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

Problem with data type VARBINARY(MAX) not null

Post by jan.javurek@floresps.cz » Sun 14 Mar 2010 16:38

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 23 Apr 2010 07:24

To solve the problem you can use the following code:

Code: Select all

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

Post Reply