BorlandTalk.com

Sherlyn · Guest

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 · Guest

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

Mikael Eriksson · Guest

Sherlyn skrev: