Re: Using CONNECT in an SQL Procedure.

Eftimios Pantzopoulos wrote:

I have looked at the documentation and actually lifted the
original code from the IBM site. The error is occurring during the compilation process, and not the syntax parsing
phase.

Which example? An example of dynamic SQL presumably.? Since the statement is not used in PREPARE or EXECUTE IMMEDIATE, is it not static SQL as compiled in the SQL PROCEDURE? If so, the CONNECT statement documents that the delimited character string "literal form of the password is not allowed in static SQL or REXX." The documentation for SQL0084 suggests that a "CONNECT with constants <ed: literals> specified for user ID and password is not allowed in a precompiled program." Of course that same SQL0084 documentation contradicts the above noted /not allowed in static SQL/ [albeit, I believe, incorrectly; STRSQL is dynamic, and CONNECT is allowed].

I have tried it with and without the begin/end block, to no avail.
<<SNIP>>

In this post, the actual source seems to have been omitted. Was a valid routine-body properly defined yet, as a compound-statement? Simply stating "tried it" does not allow for the review of the actual source that was /tried/. Additionally is /myuser/ the actual token being specified, or is perhaps another real *USRPRF name being used? If not the actual source, then giving munged information might be obfuscating the origin of the problem; e.g. a reserved word in a statement could be origin for a problem [if not delimited]. Similarly for the password as delimited literal, an unlike representation of its specification may obfuscate the origin of the problem; e.g. depending on the interface being used, a /long/ password might extend beyond the limits of the script processor, like the 80-bytes limit in RUNSQLSTM.

connect to misi9001 user myuser; causes the syntax parser to throw: SQL0104 30 4 Position 32 Token ; was not valid.
Valid tokens: USING.

The syntax error should be expected. The syntax diagram does not indicate either clause as optional within the "authorization:" part of the statement; either the full /authorization/ is omitted, or both USER and USING are required per the syntax.

It seems like it's not expecting the password in the statement In fact the EXEC statement hasn't just hidden it. It seems to
have omitted the password and the trailing quote. I'm presuming
that a CONNECT is permitted in a script.

For mention of /script/, does that intend to imply the source for the CREATE statement is being processed by a RUNSQLSTM CL command invocation? The CONNECT is presumably not /in a script/ directly, but in a CREATE PROCEDURE which is in a script; quite different really, as the former is executed and the latter is compiled. Regardless of interface, be sure the connection that is established is to the *LOCAL database, when the CREATE executes; e.g. do not issue a CREATE PROCEDURE in an interactive SQL after a request to CONNECT TO RemoteDB, and on RUNSQLSTM perhaps it is not valid to specify an RDB.

FWiW: Since the server name and user name should be upper-case, although the listing shows they were [but possibly only folded to upper as a side-effect of an attempt to eliminate the password appearing in the listing?], there would be no harm in actually upper casing the values in the source.

Regards, Chuck