• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1621
  • Last Modified:

Delphi ADO sql statement for inserting a CLOB into Oracle 8/9

I have an application that needs to insert a record into a table which includes a CLOB field.  I've seen comments about dbms_log and about empty_clob(), but would really appreciate someone who's done this to put together a couple of sql statements that I can execute to get the job done.

I have a TMemo control on screen (not data bound) and I need to pass the text over and get it inserted into the table.  What I've tried so far is

ADOCmd.SQL := 'Insert into Memos (Recno, Notes) values (1234, ' + moDescrip.Text + ')';
ADOCmd.Execute;

For someone who's done this before, this should be quick and easy points.  :-)
Thanks.
0
bjones8888
Asked:
bjones8888
1 Solution
 
bjones8888PresidentAuthor Commented:
Oops.  Forgot to mention.  It seems to work ok with small amounts of text (< 254 characters).  As soon as I go to larger amounts of text, I get an access violation.  It's wrapped pretty deeply so I'm not sure what the actual error message is that it generates.
0
 
DragonSlayerCommented:
Would it help if you use parameters instead?

with ADOCmd do
begin
  CommandText := 'INSERT INTO Memos (RecNo, Notes) VALUES (:RecNo, :Notes)';
  Parameters.ParamValues['RecNo'] := 1234;
  Parameters.ParamValues['Notes'] := moDescript.Text;
  Execute;
end;
0
 
vaceroseCommented:
When dealing with large data I have had success using a parameterized query and streams.

ms:=TMemoryStream.Create;
moDescrip.Lines.SaveToStream(ms);
ms.Position:=0;
ADOCmd.SQL := 'Insert into Memos (Recno, Notes) values (1234, :pNotes)';
ADOCmd.Prepared:=true;
ADOCmd.Parameters.ParamByName('pNotes').LoadFromStream(ms);
ADOCmd.Execute;
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now