1 / 26

5D – Advanced Search for Plex RPG Using SQL

5D – Advanced Search for Plex RPG Using SQL. Mark Murphy STAR Base Consulting, Inc. Bio. Mark Murphy STAR BASE Consulting, Inc. Cincinnati, OH Started with AS/400 and 2E in 1989 Using Plex since 1997

pavel
Download Presentation

5D – Advanced Search for Plex RPG Using SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 5D – Advanced Search for Plex RPG Using SQL Mark Murphy STAR Base Consulting, Inc.

  2. Bio • Mark Murphy • STAR BASE Consulting, Inc. • Cincinnati, OH • Started with AS/400 and 2E in 1989 • Using Plex since 1997 • Experience in Warehousing, Distribution, Retail, Manufacturing and Business Services markets 8/20/2007 Advanced Search for Plex RPG using SQL

  3. Overview Scenario: You have been asked to build a Customer Inquiry function that will sort customers by Customer ID, or Name. In addition it needs to be able to filter the customer records based on a portion of the Name, the State, or Zip Code. The environment for this inquiry function is iSeries. The Problem: While this would be a good task for a custom SQL query, the Plex generators will not embed SQL within RPG. The Approach: Using a little source code and language SQLRPG we can build a function that executes a custom SQL Select as part of a Block Fetch. 8/20/2007 Advanced Search for Plex RPG using SQL

  4. Components • There are just a few components needed • An SqlBlockFetch function • Some User Source to embed the SQL in RPG • A function to build the SQL statement • A few messages to contain the static portion of the SQL 8/20/2007 Advanced Search for Plex RPG using SQL

  5. SQLBlockFetch 8/20/2007 Advanced Search for Plex RPG using SQL

  6. User Source • There are 7 necessary User Sources • Declare Variables • Declare SQL Statement • Declare Cursor • Prepare Statement • Open Cursor • Close Cursor • Fetch 8/20/2007 Advanced Search for Plex RPG using SQL

  7. User Source – DeclareVariables 8/20/2007 Advanced Search for Plex RPG using SQL

  8. User Source – DeclareSqlStatement 8/20/2007 Advanced Search for Plex RPG using SQL

  9. User Source – Declare Cursor 8/20/2007 Advanced Search for Plex RPG using SQL

  10. User Source - PrepareStatement 8/20/2007 Advanced Search for Plex RPG using SQL

  11. User Source - OpenCursor 8/20/2007 Advanced Search for Plex RPG using SQL

  12. User Source - CloseCursor 8/20/2007 Advanced Search for Plex RPG using SQL

  13. User Source - Fetch 8/20/2007 Advanced Search for Plex RPG using SQL

  14. SQL Block Fetch • Data Access gives us the processing shell • Only three places to add code • Initialization • Position • Fetch 8/20/2007 Advanced Search for Plex RPG using SQL

  15. SQLBlockFetch - Initialize 8/20/2007 Advanced Search for Plex RPG using SQL

  16. SQLBlockFetch - Position 8/20/2007 Advanced Search for Plex RPG using SQL

  17. SQLBlockFetch - Position 8/20/2007 Advanced Search for Plex RPG using SQL

  18. SQLBlockFetch - Fetch 8/20/2007 Advanced Search for Plex RPG using SQL

  19. SQLBlockFetch - Fetch 8/20/2007 Advanced Search for Plex RPG using SQL

  20. Tying It All Together • Two things left • Replace BlockFetch with SQLBlockFetch in grid function • Add a function to build your SQL Statement 8/20/2007 Advanced Search for Plex RPG using SQL

  21. Function Replacement 8/20/2007 Advanced Search for Plex RPG using SQL

  22. The Action Diagram 8/20/2007 Advanced Search for Plex RPG using SQL

  23. The Action Diagram – Part 2 8/20/2007 Advanced Search for Plex RPG using SQL

  24. One Way to build the SQL Statement 8/20/2007 Advanced Search for Plex RPG using SQL

  25. Resources E-mail: mmurphy@starbaseinc.com Phone: (513) 245-0400 The Edge: edgeusergroup.pugmarks.net Plex Wiki: wiki.plexinfo.net 2E Wiki: wiki.2einfo.net 8/20/2007 Advanced Search for Plex RPG using SQL

  26. Synopsis SQL support for iSeries development in Plex, and for that matter 2E is limited at best, and designing reports that perform acceptably can be a daunting task when the requirements are complex.  This framework provides a way to easily develop high performance reports for your iSeries without the need for creating a lot of extra access paths to support your reports. 8/20/2007 Advanced Search for Plex RPG using SQL

More Related