On 02-Sep-2014 17:08 -0500, Gary Thompson wrote:

Using embedded SQL I can now call the SQL proc and get the
desired doc_num returned.

out_loc = 'ABCD';
cto_num = 1234567;
bat_dat = 1140902;
doc_num = *Blank;
Exec SQL CALL mysqlproc (:out_loc, :cto_num, :bat_dat, :doc_num);

Setting Break Points in the SQL proc worked but I was surprised at
the number of F10-Step and F12-Resume key presses needed to run the

The /debug view/ used for presenting the SQL source [DBGVIEW=*SOURCE] effectively has an underlying debug view that, for any particular SQL statement, may correlate to several C statements that implement just that one SQL statement. Unfortunately the debugger actually performs separately, each of the C statements used for implementation, until the one SQL source-view statement is complete; that seems a plausible explanation for the alluded need to perform so many step+resume "key presses". While that effect could probably be /remedied/ [it is of course, just software], I presume the cost to do so could not be justified, thus the providers of the debugger are leaving the many SQL programmers who are doing the debugging to be annoyed by the effect.

Next step is making the SQL proc query to get the library/schema
name from the out_loc input parm.

We have a local file which stores library by out_loc, but any hints
on coding this in an SQL proc will be very much appreciated. (I'm
reading 'Stored Procedures' and '7.1 Database SQL' PDFs)

I am not clear what is being implied about the OUT_LOC value. I infer the implication is that a library name is being obtained from somewhere [e.g. a data area] during run-time, thus the static DEFAULT SCHEMA or static PATH values as defined at compile-time is incorrect for the run-time. That is easily enough resolved by adjusting the library-list [e.g. ADDLIBLE POSITION(*FIRST) with the retrieved library name on the Library (LIB) specification] while both using the System Naming option and avoiding library-qualified specifications in the SQL statements.

One question I have is what exactly makes an SQL statement

Dynamic SQL means that the SQL statement is defined at run-time vs at compile-time. With dynamic SQL a statement is [implicitly or explicitly] PREPAREd from a string variable [or expression; e.g. a concatenation of clauses and\or elements of clauses of the overall statement]. An embedded statement is coded as SQL statement(s) quite directly into the code, just as any HLL /statement/ is coded; the embedded statement is pre-compiled and then compiled, such that the statement is defined effectively in-full at compile-time. The dynamic SQL, as a string, is [effectively] unable to be evaluated until run-time, so will not be established as compile-time.

The difference between embedded SQL and dynamic SQL could be described as what would be the difference between coding in RPG using the following contrived and overly simplified examples, the first representing a normal\embedded RPG statement being coded and the second representing a pseudo-language dynamic equivalent; both performing a concatenation of a literal period character onto the end of a string that is defined by the variable named Sentence:
EVAL Sentence+='.'; // variable, operator, and operand(s)
EXEC 'EVAL Sentence+=''.'''; // perform what is defined by a string

I recently saw a statement that changing a table name requires
dynamic SQL,

A table-reference can not be defined by a [host] variable. There are a variety of means to effect /changing/ the effective table-reference without using dynamic SQL; thus dynamic is not /required/.

and have thought for some time that using dynamic SQL is more
reliable when switching libraries so that's been my habit for some

If a supported\proper means to effect changing the library name for a statement is not _reliable_, then either there is a defect or there is a usage problem. The most common means that have been used since the S/38 to effect such a change are Library List (*LIBL) processing and [possibly in combination with] the Override To Database File (OVRDBF); the SQL support System Naming [NAMING(*SYS)] and the OVRDBF is just as functional for SQL [for a To-File (TOFILE) and Member (MBR) name anyhow] as for non-SQL database open and I\O activity. The SQL also offers ALIAS support. Another alternative [along with other nuances that must be understood], the SQL naming uses the authorization id [defaulting to and typically the user name] to qualify unqualified references.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact