RE: Display file dynamically

Yes, we heard they run slower, which can be expected
We'll check these out later and let you know which worked best for us 

Than you Rob!

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, February 26, 2007 3:56 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Display file dynamically

Prepared statements are nice.  However, I "think" they are all thrown
into the CQE and not the enhanced SQE.  Also, there is some stuff that
gets stored in the program after you actually run it that helps
performance on the next execution of the sql statement.  You can see
that with PRTSQLINF. 
 An example is:
DECLARE C1 CURSOR FOR SELECT CAST ( SUBSTRING ( BIGTEXT , 1 , 10 ) AS
DATE
) AS 
    THEDATE FROM BLASTME
  SQL4021  Access plan last saved on 02/20/07 at 16:23:57.  
  SQL4020  Estimated query run time is 0 seconds.  
  SQL402D  Query attributes overridden from query options file QAQQINI
in library QUSRSYS.
  SQL4010  Table scan access for table 1.  
UPDATE AAAKEY SET COMP = 1 WHERE LOC = 'a'  
  SQL4021  Access plan last saved on 02/20/07 at 16:23:56.  
  SQL4020  Estimated query run time is 0 seconds.  
  SQL402D  Query attributes overridden from query options file QAQQINI
in library QUSRSYS.
  SQL4008  Index AAAKEY used for table 1.  
  SQL4026  Index only access used on table number 1.  
  SQL4011  Index scan-key row positioning used on table 1.  
  SQL4006  All indexes considered for table 1.  
GET DIAGNOSTICS : H = ROW_COUNT
OPEN C1  
5722SS1 V5R4M0 060210     Print SQL information          Program 
ROB/AAASQL
WHENEVER SQLERROR CONTINUE
FETCH C1 INTO : H  
                               * * * * *  E N D  O F  L I S T I N G  * *
*
* * 

However, I don't think prepared statements get any of this benefit.


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Phil Kestenbaum" <pkestenbaum@xxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/26/2007 03:35 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Display file dynamically






Nice and Elegant
We were also thinking of using a prepared statement 
Can anyone share their experience ?
 
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, February 26, 2007 3:22 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Display file dynamically

Here's one way:
     C/EXEC SQL
     C+ Declare C1 cursor for
     C+  Select System_Table_Schema as library,
     C+         System_Table_Name   as file
     C+  from qsys2/systables
     C+  Order by
     C+  case
     C+    When :SortField='System_Table_Schema'
     C+      Then System_Table_Schema
     C+    When :SortField='System_Table_Name'
     C+      Then System_Table_Name
     C+    End
     C/END-EXEC

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Phil Kestenbaum" <pkestenbaum@xxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/26/2007 02:52 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Display file dynamically







How do you access a table where the name of the field to be used in the
ORDER BY clause is a variable ?
(we are using SQLCobol)

          EXEC SQL 

               DECLARE my-cursor CURSOR FOR 

               SELECT * 

               FROM my-table 

               ORDER BY :my-order-by-field 




This message contains information proprietary to our company. It is 
intended to be read only by the individual or entity named above or
their 
designee.
Any distribution of this message or the information contained herein 
without written permission from our company is strictly prohibited. If
the 
reader of this message is not the intended recipient or an agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that you have received this document in error and that any 
review, dissemination, distribution, or copying of this message is 
strictly prohibited. If you have received this communication in error, 
please notify us immediately by e-mail, and delete the original message.