MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2014

RE: testing an SQL procedure



fixed

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.






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

This mailing list archive is Copyright 1997-2014 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