The paradigm is a little different.
OPNQRYF is sort of like an "override". The program that's reading the
output from OPNQRYF may not even know that OPNQRYF has been run... it's
just reading a file sequentially...
SQL is code inside your program. The program issues the SQL
statement(s), and then tells SQL to read records... so you no longer
use the RPG F, I, O specs, and you no longer use operations like SETLL,
READ, CHAIN, etc. It's purely SQL statements. You tell SQL exactly
which variables (or structures) to read into your program.... whereas
OPNQRYF just reads whole records via RPG native I/O.
I find this deeper integration into the program logic to make SQL a lot
easier to write than OPNQRYF.
SQL also has the potential to run faster. If your query gets processed
by the SQE (the new query engine) rather than the old one, the SQL query
will be much faster than an OPNQRYF (which is locked into the old one).
Also, because SQL can choose which fields to read (as opposed to OPNQRYF
havign to read a whole record format each time) it can run a little faster.
SQL also has a lot of features that OPNQRYF doesn't have. For example,
you can create files with SQL. You can write or update records with SQL.
Also, SQL is an industry standard. If you want to work with a different
database some day (SQL Server, Oracle, MySQL, DB2 on other platforms,
etc) you'll use SQL statements on those platforms. OPNQRYF is only
available on i5/OS for i5/OS's built-in database. SQL is everywhere.
There are also things like LOBs that are only available via SQL. For
example, if you're developing a time and attendance system for employees
you might want to offer the ability to attach a Word document or PDF
showing any raises offered to the employee, any warning letters sent,
etc. You might even want to keep a picture of the employee so the HR
folks can identify him/her. You can do that by uploading these objects
(Word documents, PDFs, pictures) into a BLOB -- which requires SQL.
It's nice to be able to keep them right there in the database instead of
havign separate files in the IFS sometimes....
That's just a few thoughts off the top of my head... and obviously some
of these things are more advanced concepts than others... just food for
thought.
Tim Gornall wrote:
There appears to be much SQL talent on this list, and I'm thinking I may be
way behind the times. So here's a "how are you guys doing it" question.
A typical example: A request comes in for a new report. It will require
the user to enter some selection data (let's say from and to dates), then
process a bunch of records within RPG. I'm still using open query files,
record select logic in RPG, etc. Are some of you using SQL to do this, is
it a better way, or just different? Would anyone like to share example code
of a clean lean application using SQL?
Thanks, Tim
midrange.com
