Re: Is there a better way to run SQL in a CL pgm?

On 09-Apr-2012 14:58 , Vern Hamberg wrote:

LOL - I'm lucky to have flipped the order, then!

Perhaps, but because both the term "embedding" and "embedded" are a form of "embed", the usages and meanings are somewhat nuanced. The SQL documentation has long described the use of the REXX *EXECSQL with a section entitled "Embedding SQL statements [in REXX applications]", suggesting under that heading that "An SQL statement can be placed anywhere a REXX command can be placed." And each HLL /host/ language [including REXX] with SQL support also have the topic for "Embedding SQL".

However a distinction is also made in places where the documentation refers specifically to "Embedded SQL"; e.g. with a presumed level of support such as host variables and\or compiled. There are descriptions of /running/ a "host language program with _embedded SQL_ statements" making note of a "precompile and compile" that would be performed, after which the program can be /run/ just as any other program compiled in that /host/ HLL. Then when discussing Coding SQL in REXX, although tucked under a heading of "Embedded SQL programming" there is a statement that conflicts with the prior described concept of using a pre-compiler for "embedded"; i.e. "REXX procedures <ed: programs> do not have to be preprocessed."

Anyhow... for the benefit of anyone who has coded "embedded SQL" in a [pre-]compiled HLL but is unfamiliar with the use of SQL in REXX, they would likely experience great frustration by thinking they can code some *EXECSQL in REXX statements in the same manner that they can code EXEC SQL in whichever language they already code "embedded SQL". That is _the reason_ why I thought to _clarify_ the reference to "embedded"; even knowing that the documentation does not take care to always make the same distinction.

I believe that the SQL licensed product (57xx-ST1) has to be
installed in order to use the *EXECSQL environment - that may have
changed - unlike compiled RPG with embedded SQL - these do not
require the licensed product on the system where the program is
running.

The *EXECSQL language environment for REXX had no pre-requisite of the 57##ST1 LPP since sometime in V4 best I can recall. However I did find a document on the web that implies such a requirement may have existed for all releases prior to v5r1. I thought since at least v4r4 there was no pre-requisite, so I searched the web. But I could not corroborate v4r4, although not since v5r1 for sure; the SQL Programming Gd has the following pre-requisite in v4r3 [I can not find that same manual in v4r4 or v4r5] but for v5r1 of that same manual the following pre-requisite is no longer listed:

"# SQL REXX Interface

The SQL REXX interface allows you to run SQL statements in a REXX procedure. This interface is part of the DB2 Query Manager and SQL Development Kit licensed program."

This would be a requirement that'd make REXX less of a useful
alternative, methinks.

That requirement having been long gone, not much of an concern. I think whatever limitations, both on what SQL statements and how they can be used, is a bigger concern for usefulness of *EXECSQL in REXX. Other more general issues with the REXX language may be viewed as limiting the usefulness; e.g. not ILE, activation [group] is an issue.

Regards, Chuck

On 4/9/2012 4:12 PM, CRPence wrote:

On 29-Mar-2012 14:43 , Vern Hamberg wrote:

And REXX can do SQL embedded.

Perhaps worth clarifying, for those less acquainted, that the
above expressed "REXX can do SQL embedded" does not equate with
[and should not be confused with a potential and incorrect
inference that] the "REXX can do embedded SQL":

REXX can perform much of SQL via the *EXECSQL environment and
perform the CL via the *COMMAND environment. Both language
environments are provided within the REXX language, whereby the
statements\commands of those other two languages [as supported by
their language environments] can be _interspersed_ with the REXX
language statements. REXX can not however, do "embedded SQL",
because REXX is an interpreted language; i.e. not compiled and thus
there is no SQL pre-compiler for the REXX language.