Parsing SQL statements in Delphi with Oracle [Edit]

Hi,

I have a requirement for a DBA to compile once and run many times sql sentences.

I tried with BDE, ADO and DBExpress but in all cases the number of parsing is equals executions

Example in delphi: 

procedure TForm1.Button1Click(Sender: TObject);
var
  i : integer;
begin
  Query1.Close;
  for I := 1 to 20 do
  begin
    Query1.SQL.Clear;
    Query1.SQL.Add(' SELECT COUNT(8) FROM CONTAB WHERE CONTAB_FEHOULMO__B = :fecha');
    Query1.ParamByName('fecha').value :=  edit1.text ;
    Query1.Prepare;
    Query1.OPEN;
  end;
end;

the trace in oracle show :

call     count       cpu    elapsed       disk      query    current        rows
________________________________________________________
*Parse         20   -      0.00  -     0.00   -       0  -        0    -      0   -         0*
*Execute     20   -     0.00   -     0.00    -      0   -       0    -      0    -        0*
Fetch        20     -    0.00    -     0.00   -       0   -     460  -       0    -       20
_______________________________________________________
total       60      0.00       0.01          0        460          0          20


parse = execute = 20 times.

how do I to parsing one single time and execute 20 times ..?

thanks

Edited by: Victor Ladino on Aug 23, 2012 4:30 PM

Edited by: Victor Ladino on Aug 23, 2012 4:35 PM
0
Victor
8/23/2012 11:37:27 PM
embarcadero.delphi.non-tech 5933 articles. 1 followers. Follow

2 Replies
837 Views

Similar Articles

[PageSpeed] 8

Victor Ladino wrote:
> Hi,
> 
> I have a requirement for a DBA to compile once and run many times sql sentences.
> 
> I tried with BDE, ADO and DBExpress but in all cases the number of parsing is equals executions
> 
> Example in delphi: 
> 
> procedure TForm1.Button1Click(Sender: TObject);
> var
>   i : integer;
> begin
>   Query1.Close;
>   for I := 1 to 20 do
>   begin
>     Query1.SQL.Clear;
>     Query1.SQL.Add(' SELECT COUNT(8) FROM CONTAB WHERE CONTAB_FEHOULMO__B = :fecha');
>     Query1.ParamByName('fecha').value :=  edit1.text ;
>     Query1.Prepare;
>     Query1.OPEN;
>   end;
> end;
> 
> the trace in oracle show :
> 
> call     count       cpu    elapsed       disk      query    current        rows
> ________________________________________________________
> *Parse         20   -      0.00  -     0.00   -       0  -        0    -      0   -         0*
> *Execute     20   -     0.00   -     0.00    -      0   -       0    -      0    -        0*
> Fetch        20     -    0.00    -     0.00   -       0   -     460  -       0    -       20
> _______________________________________________________
> total       60      0.00       0.01          0        460          0          20
> 
> 
> parse = execute = 20 times.
> 
> how do I to parsing one single time and execute 20 times ..?
> 
> thanks
> 
> Edited by: Victor Ladino on Aug 23, 2012 4:30 PM
> 
> Edited by: Victor Ladino on Aug 23, 2012 4:35 PM

Set your SQL outside of the loop, changing the SQL always unprepares the 
previously prepared statement.

procedure TForm1.Button1Click(Sender: TObject);
var
   i : integer;
begin
   Query1.Close;
   Query1.SQL.Add(' SELECT COUNT(8) FROM CONTAB WHERE CONTAB_FEHOULMO__B = :fecha');
   Query1.Prepare;
   for I := 1 to 20 do
   begin
     if Query1.Open then
       Query1.Close;
     Query1.ParamByName('fecha').value :=  edit1.text ;
     Query1.OPEN;
   end;
end;


-- 
Jeff Overcash (TeamB)
       (Please do not email me directly unless  asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted  corridors of Whitehall.
              (Fish)
0
Jeff
8/24/2012 2:10:40 AM
Hi,

this worked perfectly

thank you very much


> {quote:title=Jeff Overcash wrote:}{quote}
> Victor Ladino wrote:
> > Hi,
> > 
> > I have a requirement for a DBA to compile once and run many times sql sentences.
> > 
> > I tried with BDE, ADO and DBExpress but in all cases the number of parsing is equals executions
> > 
> > Example in delphi: 
> > 
> > procedure TForm1.Button1Click(Sender: TObject);
> > var
> >   i : integer;
> > begin
> >   Query1.Close;
> >   for I := 1 to 20 do
> >   begin
> >     Query1.SQL.Clear;
> >     Query1.SQL.Add(' SELECT COUNT(8) FROM CONTAB WHERE CONTAB_FEHOULMO__B = :fecha');
> >     Query1.ParamByName('fecha').value :=  edit1.text ;
> >     Query1.Prepare;
> >     Query1.OPEN;
> >   end;
> > end;
> > 
> > the trace in oracle show :
> > 
> > call     count       cpu    elapsed       disk      query    current        rows
> > ________________________________________________________
> > *Parse         20   -      0.00  -     0.00   -       0  -        0    -      0   -         0*
> > *Execute     20   -     0.00   -     0.00    -      0   -       0    -      0    -        0*
> > Fetch        20     -    0.00    -     0.00   -       0   -     460  -       0    -       20
> > _______________________________________________________
> > total       60      0.00       0.01          0        460          0          20
> > 
> > 
> > parse = execute = 20 times.
> > 
> > how do I to parsing one single time and execute 20 times ..?
> > 
> > thanks
> > 
> > Edited by: Victor Ladino on Aug 23, 2012 4:30 PM
> > 
> > Edited by: Victor Ladino on Aug 23, 2012 4:35 PM
> 
> Set your SQL outside of the loop, changing the SQL always unprepares the 
> previously prepared statement.
> 
> procedure TForm1.Button1Click(Sender: TObject);
> var
>    i : integer;
> begin
>    Query1.Close;
>    Query1.SQL.Add(' SELECT COUNT(8) FROM CONTAB WHERE CONTAB_FEHOULMO__B = :fecha');
>    Query1.Prepare;
>    for I := 1 to 20 do
>    begin
>      if Query1.Open then
>        Query1.Close;
>      Query1.ParamByName('fecha').value :=  edit1.text ;
>      Query1.OPEN;
>    end;
> end;
> 
> 
> -- 
> Jeff Overcash (TeamB)
>        (Please do not email me directly unless  asked. Thank You)
> And so I patrol in the valley of the shadow of the tricolor
> I must fear evil. For I am but mortal and mortals can only die.
> Asking questions, pleading answers from the nameless
> faceless watchers that stalk the carpeted  corridors of Whitehall.
>               (Fish)
0
Victor
8/24/2012 9:36:58 PM
Reply:

Similar Artilces:

SEPA components for Delphi with Source Code (Delphi 5
Hi all, in the european union change next year the Bankingformat to the SEPA Format. All peoples and companies must change the bankingssoftware and the costumer data form acountnummers in the new IBAN and BIC numbers. See: http://www.arma-it.de/shop/artikelueber.php?wgruppeid=211&wgruppe_offen=211 Functions: - generate SEPA XML'S - Calc IBAN - BIC Database (DE,AT and CH) Questions: vertrieb@arma-it.de PS: Bankinssoftware for Develpoers (Germany only) http://www.arma-it.de/shop/artikelueber.php?wgruppeid=212&wgruppe_offen=212 El 26/10/13 21:38, A...

Delphi Paradox to Delphi SQL Anywhere
I need to convert a Delphi 3 app from Paradox tables to something more flexible/powerful. My app creates a limited number of complex transactions ( i.e. 200 to 300 transactions per month - each transaction has 300 to 400 fields in the primary file and links to about a dozen support files. What programming issues should I watch for in this conversion ( i.e. is using quicken style incremental search combo boxes a problem due to network traffic, etc. ) ??? What would be the conversion steps/Issues involved with this process ??? Obviously tables/relationships need to be created a...

Convert Delphi 7 to Delphi 2009 [Edit]
Hi! Please, I have a code done in Delphi 7... Now I want to convert Delphi 7 to Delphi 2009 but I'm having problems when compile the code. Error message: +[DCC Fatal Error] Far.dpr(9): F2063 Could not compile used unit 'System.pas'+ Please can somebody help me? Source Download (349KB): http://rapidshare.com/files/256191328/FileManager.rar.html Mirror Source Download: http://www.megaupload.com/?d=12GYLPT0 Password: delphi Thank u so much! (sorry for my bad english, it is not my native language) Edited by: loquax loquax on Jul 15, 2009 12:24 PM Sorry, bu...

New to Delphi
Hi I am new to Delphi & want to know good resources for learning to use it. Is there a list of good Delphi learning resources (books/online tutorials) somewhere? What recommendations can anyone make for someone new to Delphi (& only a hobby programmer at the moment)? A couple of weeks ago I started with DelphiXE4 trial & have Googled for a few Delphi resources but I haven't found anything very good yet for me as a beginner. I'm just struggling along trying to learn as I go! (I'm presently struggling with DragandDrop...see [http://stackoverflow.com/questions/17232...

Migrating from Delphi 6 to Delphi XE 3! [Edit]
All, I am a Delphi developer working in an windows form application developed using Delphi 6. Now, we are planning to upgrade the development tool. Can anyone provide me information related to major roadblocks that we can face while migrating from Delphi 6 to Delphi XE 3? Should we migrate to Delphi XE 3 or any other preferred version of Delphi based on the fact that our target users will be using Windows 7 or Windows 8? Do we have any tools or utilities to migrate the source code from Delphi 6 to higher version of Delphi? Also, any suggestions related to best practices are welcome....

Delphi vs Winform
This might be a little techie, but I think most people here are familiar with Delphi to understand the difference between a TWinControl and TGraphicControl. I was trying to create a C# Winform project based on a Delphi prototype and ran into this problem which I posted to some Winform forums ... This led me to an *obvious* deficiency in Winforms programming. I'd like to hear your thoughts. My question to the Winforms forums describing the problem is copied below: <snip> Hi, I mostly do Delphi development but would like to start my next small work project in Visual S...

Code works in Delphi 7 but not in Delphi 2010 [Edit]
hello, i have a procedure that open's a file by passing the file name as the parameter to the executable. something like this {code} C : \ P r o g r a m F i l e s \ Da c k e r \ D r a c k e r . e x e " G : \ D E l p h i 7 \ D e l p h i 7 A p p _ l o g . t " {code} The source code is {code} procedure OpenFileWithExe var hReg: HKEY; Ret: Longint; RegDataType, RegDataSize: DWORD; CmdLine: array [0..560] of Char; Len: Integer; SInfo: TStartupInfo; PInfo: TProcessInformation; begin Ret := windows.RegOpenKeyEx(HKEY_CURRENT_USER, ...

Update Delphi 2005 to Delphi 2010, which do i need? [Edit]
Hello, we have one Delphi 2005 Professional here and want to upgrade to Delphi 2010, but we are a bit confused. Our distributor in Germany, ingram-micro offers several upgrade products: UPG DELPHI 2010 PRO - NAMED USER UPG DELPHI 2010 PRO - NAMED USER ELS UPG DELPHI 2010 PRO - NAMED USER FLEX RAD STUDIO 2010 PRO - UPG UPG RAD STUDIO 2010 PRO - NAMED ELC UPG RAD STUDIO 2010 PRO - NAMED FLEX UPG DELPHI 2010 PRO - CONCURRENT ELS UPG DELPHI 2010 PRO - CONCURRENT FLEX What do they mean exactly and which one do i need? At the moment delphi is installed on two PCs and we want to use ...

Delphi and Delphi for .Net
It seems that Delphi for .Net is slower than Delphi Win32 native applicaiton. I would like to know is it true all .Net application is slower than Win32 native applicaiton or it is Delphi for .Net only. Your information is great appreciated, Inung On 2011-06-21 18:20:17 +0100, Inung Huang said: > It seems that Delphi for .Net is slower than Delphi Win32 native applicaiton. > I would like to know is it true all .Net application is slower than > Win32 native applicaiton or it is Delphi for .Net only. If you are only running the code in the application once then, yes, yo...

Delphi 7 to Delphi XE: TBlobField to XML [Edit]
Hi, I'm migrating a Delphi7 application to Delphi XE. I'm using a TClientDataSet to communicate, by using a XML frame, with my server. In this TClientDataSet I'm using a TBlobField which is an array of 384 byte. The blobField is allocate by a code like this : {code} myStream : TStream; myStream := aClientDataSet.CreateBlobStream(myBlobField, bmwrite); vResult := myStream.Write(ArrayOf384Byte[0], length(ArrayOf384Byte)); //vResult = 384 => GooD ! (...) {code} For communicate with the server, we have to decode the Blobfield in XML before to sending it. We have...

Sybase SQL Anywhere <--> Delphi non-BDE,ODBS,SQL Links Data Access.
Betatesters are welcome! Non BDE, but BDE-like ... http://www.microolap.com/MODBConnectivity.html Edward Smirnov pr@microolap.com Hi Ed, This is the sqlanywhere general forum. If you want us to try your product, at least describe it technically. I notice no readme, help files, or faq's at your site..... Edward Smirnov wrote: > > Betatesters are welcome! > > Non BDE, but BDE-like ... > > http://www.microolap.com/MODBConnectivity.html > > Edward Smirnov > pr@microolap.com I agree with Al. Although I'm curious, and co...

Delphi 2006 to Delphi XE Crystal BPL problem [Edit]
I am in the process of migrating our application from Delphi 2006 to Delphi XE and, for ease of use, I have both 2006 and XE installed on my development computer. I access Crystal Reports XI RDC in our application and so I rebuilt the BPL in XE in order to add the new package to XE. However, when I try to add the package to XE I get the error message: “Registration procedure, Craxddrt_tbl.Register in the package c:\Documents and Settings\All Uers\Documents\RADS Studio\8.0\BPL\Crystal.bpl raised exception class EFilerError: Component TDatabase can’t be registered by package crystal.bpl be...

ShowMessage/MessagDlg wordwrap Delphi 2006
Hi, I have used Delphi 2006 up to now. ShowMessage allowed a long text without a word wrapping. Now with Delphi XE I notice that a migrated program shows smaller message window sizes and a previously carefully aligned message now appears wrapped and misaligned. How to get back to the previous behaviour? I'm not experienced to create my custom form, so a code snippet may help. Thanks, Uli Edited by: Ulrich Brueggemann on Sep 18, 2010 8:46 AM > I have used Delphi 2006 up to now. > ShowMessage allowed a long text without a word wrapping. > > Now with Delphi ...

Text.Write speed
Hi ! I have speed troubles for using Text.Write method in Delphi 2006, particularly with distant files (local network). For example, with the following code ("F" is a text file ; "Line" is an about 200-character string): ----- AssignFile(F, FileName); try Rewrite(F); for l := 1 to 100 do begin for c := 1 to Length(Line) do Write(F, Line[c]); Writeln(F); end; finally CloseFile(F); end; ----- On a distant file: Delphi 5 -> 0.08 seconds Delphi 2006 -> close to 8 seconds Is there a speci...

Web resources about - Parsing SQL statements in Delphi with Oracle [Edit] - embarcadero.delphi.non-tech

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Jo-Ann Miller sacking: Queensland Premier's statement in full
Annastacia Palaszczuk issues a statement in wake of ethics committee report.

Jo-Ann Miller sacking: Queensland Premier's statement in full
Annastacia Palaszczuk issues a statement in wake of ethics committee report.

Malcolm Turnbull's statement to UN climate conference in Paris
Malcolm Turnbull joined other world leaders in Paris for the United Nations climate talks on Monday. This is his statement.

Freddie Gray case: Officer William G. Porter's recorded statement played in court - Baltimore Sun
Officer William G. Porter told investigators in a recorded interview played for jurors Friday that he did not believe that Freddie Gray was truly ...

Obama releases emotional statement on Planned Parenthood shooting - Business Insider Deutschland
President Barack Obama reacts to the Planned Parenthood shooting in Colorado Springs, Colorado.

Jury Sees Officer’s Video Statement In Freddie Gray Trial
More key testimony as the prosecution continues to call witnesses in the trial of city police officer William Porter.

Today’s Top Election 2016 News Is Trump’s Vindication Over Muslim Statement
Today’s election 2016 news looks at how one of Donald Trump’s most controversial claims was actually correct, although exaggerated. Here’s how ...

The Latest: Islamic State group airs statement praising California mass shootings
The latest on the mass shooting in San Bernardino, California (all times local): 2:40 a.m.

Resources last updated: 12/7/2015 12:49:35 AM