out_loc '09 ' and '11 ' map to Draper Utah - which most know as Salt Lake.
Think of out_loc as a 'sales location': in each 'division' customer outlets are serviced from 'sales locations'
set by geographic sales and delivery issues, and we have several 'divisions' each with a data lib.
We keep a file mapping locations to the lib to which each belongs.
IBM System/3 technology upon which the sun sets - for another year or so . . . about year 15 now . . .
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, September 03, 2014 12:18 PM
To: Midrange Systems Technical Discussion
Subject: RE: testing an SQL procedure
Is out_loc simply the library name? Or is this one of those things where its a data structure parameter composed of concatenating a bunch of fields together?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Gary Thompson <gthompson@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/03/2014 01:50 PM
Subject: RE: testing an SQL procedure
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Chuck,
out_loc values come from queries used in a month-end process which
collects
data from 'division' libraries. Each division has several locations. Data
is used to
post results to corporate accounts and we keep detail for historical
reporting.
The timing of when a division is processed is user-controlled.
I chose to 'adjust' library as part of creating sql statements because
queries
must be adjusted for period begin/end dates and other variables, but the
ADDLIBLE suggestion is one also used here.
Another goal is to create code useable for projects such as MS Excel
workbooks
which query data created by this app.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Wednesday, September 03, 2014 10:28 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: testing an SQL procedure
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.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
