SQL vs. OPNQRYF--The Battle Continues

[David Abramowitz]

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
SQL/400 has been around for a while now, and really hasn't caught on with most AS/400 shops. The reason is basic. That is the *BASE features of the AS/400, i-series, etc. does not include SQL! DDS, and OPNQRYF are part of the base OS, and until SQL becomes as accessible, it will not be used. Dave

[tobrien@jackhenry.com]

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
I work for a company that has thousands of AS/400 customers. Why should we expect our customers to purchase an additional product(SQL from IBM) when that money could be better spent on more of OUR products? I don't believe that SQL will be a viable replacement until it is included free with the OS.

[R.Cozzi]

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
Others have already pointed out, SQL is essentially free on the machine. As a software vendor, you can also purchase the IBM product that allows you to use embedded SQL in your RPG code. Your customers will NOT need SQL on their machines in order to run that code. If you use ASC's SEQUEL product, you get a lot more power, but I don't know their pricing, whether or not they have a runtime module that can be liscensed.

[R.Cozzi]

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
You're not going to get me to disagree. But the point is, they DON'T TEACH DDS in College (primarily). As to the issue with JDE, they have an "interesting" database to say the least. I just won't go there. But, RPG has issues with SQL that are the fault of both IBM Rochester and Toronto. I just hope they work those out before we all just give up and go home.

[J.Wells]

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
I'll show my ignorance here - "...This allowed me to connect to remote databases..." - Can you point me to a reference book that explains how to do this? Thanks, Joe

[J.Wells]

Thanks, I will take a look. Joe

[coteijgeler@chello.nl]

I have to respond on this. I do not think you can compare OPNQRYF and embedded SQL entirely. Both make use of the same query engine on the AS/400. OPNQRYF file has the limitations of CL, as it is a CL command. It will not deal with data types like floating point or integers. I see OPNQRYF as an interface to SQL in CLP, and it fulfills two SQL functions: prepare an SQL statement and open a cursor. To process the open data path you need a HLL language. If you want that OPNQRYF is as flexible as SQL, then IBM have to enhance that CL command. But then it should enhance other CL commands as well, which it has not done for a long period now. I agree with you, that using OPNQRYF is cumbersome, but so is embedded SQL in an RPG programme. Just my two Euro-cents. Regards, Carel Teijgeler

[ukpi1b]

Some notes to discussion. 1. One has to get an idea how data looks like before writing any program. You are lucky if your shop has DBU or any other utility. There are plenty examples when the only available tools are DFU, QRY400, QMQRY. You still can create SQL looking like tool within CL and QMQRY at no cost at all. OPNQRYF uses dummy file to join several files. It is much easier to use SQL to get data for this join even with EXSQLSTM. 2. SQL is great tool for testing. In some cases, couple SQL statements could replace several pages RPG program and any step could be easily verified. I have never seen OPNQRYF as a part of testing procedure; usually it is OPNQRYF selection that needs to be tested. 3. I still use SQL and OPNQRYF because of client requirements, company policy or standards.

[Guest.Visitor]

I can put an OPNQRYF statment in a CL to add filters to an interactive pgm or reports and elimate the need to change the RPG pgms that follow. This is faster and easier. It also makes it better for the next guy who follows. I do use SQL embedded in RPG. I find it cumbersome to retro-fit in this scenario. Bottomline I use & know both. The main point is, it sure would be nice to have SQL supported fully in CL with dynamic variables, why would anyone disagree with this point??? robberendt- I like SQL but I won't go out of my way to use it when OPNQRYF is the best answer. It is the best answer in some cases. Some esoteric way to use a free third party tool to build a string with dynamice variables then execute an API I'm guessing is not the soloution just to dodge OPNQRYF or to say you can in fact use SQL in CL w/o limitations.