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
proc?
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'?
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
time.
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.
midrange.com
