Solved

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

Posted on 2004-10-22
1,526 Views
Last Modified: 2012-06-22
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
Question by:bjones8888
    3 Comments
     

    Author Comment

    by:bjones8888
    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
     
    LVL 14

    Accepted Solution

    by:
    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
     
    LVL 1

    Expert Comment

    by:vacerose
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
    Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now