RE: Embedded SQL Question

Robert,

I think the suggestion is to avoid the use of dynamic SQL. A static statement allows the access plan for the statement to be cached and reused on each invocation, resulting in faster response-time.

Eric

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Robert Munday
Sent: Friday, April 18, 2008 1:50 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Embedded SQL Question


Alan,


-----Original Message-----
From: Alan Campin <Alan.Campin@xxxxxxxxxxxxxxxx>

<<< Off the question but why the prepare statement? >>>

I am able to use one PREPARE for many situations. Depending upon conditions, I can structure my PREPARE statement and execute it with common code. You should have seen what I had to do before I learned how to write a prepare... full SQL structures based on a number of user-selectable conditions. Now all I need to do is populate each segment and string them together:

// Assemble Select Statement For The Workfile
QQS = 'Select * From FILEA '; // Select All Fields From This File

// Set Where For Records Having Days Past Due In Range For The Selected Agency
QQW = 'Where GRGDPD Between ? And ? ' + // Days Past Due In Range
' And GRGAID = ? '; // For This Agency

// Set Order By Sort Method
QQO = 'Order By GRBAA '; // Order By Account Number

// Move SQL Statements To A Single Field
QQSQL = %Trim(QQS) + ' ' + %Trim(QQW) + ' ' + %Trim(QQO) + For_Read; // Move Statements

// Declare, Then Open Cursor
Exec SQL Prepare SQL_Stmt From :QQSQL; // Prepare Executable Statement
Exec SQL Declare @C1 Cursor For SQL_Stmt; // Declare Cursor
Exec SQL Open @C1 // Open Cursor...
Using :Days_From, :Days_To, :Proc_Agency; // Using These Values


I can fill QQS (Select), QQW (Where) and QQO (Order) with any number of variable conditions and still use my one cursor statement set.



Robert Munday
Munday Software Consultants
Montgomery, AL
on assignment in Columbia, SC