Insert 1518 lines of text by SQL Param will prompt error "Operand type clash: text is incompatible with image"

[sherlyn_20]

Hi!

I have a MSSQL table called "T_Test" with two columns "Code" and "Note". The Note column's datatype is Image. If I load the field structure into TClientDataSet, I found out the Note column's field type is TBlobField. Then i do the following steps:

1) Create a MSSQL database
2) Execute create table SQL below:

Create table T_Test
(
Code VarChar(20) NOT NULL,
Note Image,
Primary Key(Code)
);

3) Drop a TSQLConnection connect to the MSSQL database
4) Drop a TSQLDataSet connect to the TSQLConnection

5) Copy the SQL below to TSQLDataSet's CommandText property

SELECT * FROM T_TEST

6) Drop a TDataSetProvider connect to TSQLDataSet

7) Drop a TClientDataSet connect to TDataSetProvider

8) Try the cases below.

Case 1: (Insert 1517 lines of text by SQL Statement: No Error)
--------------------------------------------------------------
- Insert 1517 lines into TStringList and insert TStringList.Text into "Note" field by insert SQL

procedure TForm1.FormCreate(Sender: TObject);
var L: TStringList;
i: integer;
begin
SQLConnection1.Open;
ClientDataSet1.Open;
SQLConnection1.Execute('INSERT INTO T_TEST(CODE, NOTE) VALUES(''abc'', NULL)', P);

L := TStringList.Create;
try
for i := 1 to 1517 do
L.Add(IntTostr(i));

P := TParams.Create(Self);
with P.CreateParam(ftString, 'Note', ptInput) do
Value := L.Text;
SQLConnection1.Execute('UPDATE T_TEST SET NOTE=:NOTE WHERE Code=''abc''', P);
end;


Case 2: (Insert 1518 lines of text by SQL statement : "Error: Operand type clash: text is incompatible with image")
--------------------------------------------------------------------
- The code is same as Case 1 but modify the for-loop statement "1517" to "1518".
- Insert 1518 lines of text to Image field using SQL statement will failed in this case.


Case 3: (Insert 1518 lines of text by TClientDataSet: No Error)
---------------------------------------------------------------
- Just now we see the case 2 which insert 1518 lines of text failed by using SQL statement.
- If insert 1518 lines of text using TClientDataSet as the code below will success.

procedure TForm1.FormCreate(Sender: TObject);
var L: TStringList;
i: integer;
M: TMemoryStream;
begin
SQLConnection1.Open;
ClientDataSet1.Open;

L := TStringList.Create;
M := TMemoryStream.Create;
try
for i := 1 to 1518 do
L.Add(IntTostr(i));

L.SaveToStream(M);
M.Seek(0, soFromBeginning);
with ClientDataSet1 do begin
Append;
FindField('Code').AsString := 'DEF';
TBlobField(FindField('Note')).LoadFromStream(M);
ApplyUpdates(0);
end;
finally
M.Free;
L.Free;
end;
end;


Conclusion
----------
- MSSQL has text and image datatype (which same like blob datatype in Firebird database).
- In Firebird, we can insert both text and image to Firebird's blob field where blob type in TClientDataSet is TBlobField
- In MSSQL, Text datatype in TClientDataSet is TMemoField, whereas Note datatype in TClientDataSet is TBlobField
- As i assume if i insert large string data to MSSQL's Image field it should work well as it is TBlobField, but it prompted error

Does anyone can give me solution or explanation for these? I would really appreciate it

Thanks in advance.

From,
Sherlyn Chew

[sherlyn_20]

Sorry that it was my mistake, if i change the param type from ftString to ftBlob, everything will be fine.

P.CreateParam(ftBlob, 'Note', ptInput)

Thanks.

Regards,
Sherlyn Chew