Why Use Embedded SQL Within RPG? I want my code to last.

[jvoris]

Tom, you are 100% right. Most of us converts like the dynamic selection, the dynamic sort, and the better joins we get from SQL over old fashioned logicals. Although 99% of us out there are doing "record at a time" cursor FETCH statements in our code just as your examples show, I have yet to find good examples where I can reap the really big performance boost that "record-set" processing would buy me (other than a mass update using UPDATE filename SET xxx WHERE . . )

What I find hindering adoption is that SQLSTT status codes for Found and Not Found and End of Group are lacking in production programs, and end up giving us problems later. Maybe an article from you, showing good examples of SQL with SQLSTT handlers could be a follow-on article. (There are some examples at this site and elsewhere of SQLSTT, but it never hurts to drive good practices home by repetition.) And I find myself curious how tests of SQLSTT would look in these other languages.

Although I still often fall back to older tried and true CHAIN / READE styles in my code, I find that during the design phases, I force myself to think "Embedded SQL" for production code for the simple reason that if my code is to be something that will last the test of time, it will probably migrate - or be copied and pasted - into another language.

[T.Snyder]

You got it! I'll make sure to write an article up on that. Thanks for the suggestion!

I usually keep exception handling at bare bone minimum in the articles to focus on the topic. But, I think exception handling deserves it's own topic as you said.

When I starting to adopt embedded SQL, it was a conscious choice. At first it seemed like a hassle because RPG is already simple and I know how to do it, so why go out of my way to make it more complicated. Then, after I pushed myself through some RPG programs using pure embedded SQL for my file I/O. I decided to take a break and write a nice and simple chain and read program. Wouldn't you know it, I had a master file that needed to find all the records in a detail file that contained the key from the master file, and there was no existing logical over the detail file. So, at that moment I had the embedded SQL skills available in my head as an option and it would have been more work not to use it. So, for me embedded SQL is more the norm than the exception these days.

Thanks and have a great day!
Tom

[efnkay]

Attention all personnel: "using embedded SQL. You can specify the way that the files are sorted during run time. And you can do this without the need to create a logical file." Thanks Tom. When I started using embedded SQL, that alone was one of the biggest hooo-rah's you could get as a "former" native I/O user. In fact with the exception of new physical file creation, I have yet to see the need to create a logical file in our databases. Because as usual there are already to many, many having duplicate indexes spread out across many program references. (Ever seen Synon, Advantage generated code?) I am a proponent of always specifying the physical file in your SQL statements and let the optimizer choose what index to scan for your result set(s). SQL - I write, I read, I live. :-)))

[T.Snyder]

Thanks efnkay! No, I've never seen Synon, Advantage generated code. I have some more embedded sql articles coming in the future, I'm glad that you enjoyed this one. I like your Motto

[nevster]

I like using embedded sql for the powerful one liners that can retrieve totals, etc..

select count(field1), sum(numfield2), sum(numfield2)
into :NoRecords, :Totalnumfield1, :TotalNumfield2
from myfile where field1 = :code123 etc....;

test them in run sql scripts in Systemi Navigator first though.