RE: Using CONNECT in an SQL Procedure.

This is a syntax error.

I think Chuck is on the right track with the static SQL disallowing
character literals for a password.
Perhaps all it takes is to declare a host variable and set the password
there?
Something like:

DECLARE myPwd VARCHAR(10);
SET myPwd = 'mypassword';
CONNECT TO MISI9001 USER MYUSER USING myPwd;

Similar may be true for USER.

BTW, you should really provide the entire spooled file print out (with
source included in it I hope) as well as what command are you using to
create/compile the stored procedure. You'd get more accurate responses that
way.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Using CONNECT in an SQL Procedure.


Thanks for suggestions.

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.

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

For example, dropping the using 'password' clause

4 connect to misi9001 user myuser;

causes the syntax parser to throw:

SQL0104 30 4 Position 32 Token ; was not valid. Valid tokens: USING.

The original error from the compiler listing seems to be an issue with what
happens to the password string. I encode the password in the using clause in
the script:

4 connect to misi9001 user myuser using 'mypwd';

However the compiled listing shows:

79 EXEC SQL CONNECT TO MISI9001 USER MYUSER USING ' ;

And the errors generated are:

5722SS1 V5R4M0 060210 Create SQL ILE C Object
TFRTBLDTA
DIAGNOSTIC MESSAGES
MSG ID SEV RECORD TEXT
SQL0010 35 79 Position 48 String constant beginning ' ; ' not
delimited.
SQL0107 30 79 Position 48 ' ; too long. Maximum 128 characters.
SQL0084 30 79 Position 10 SQL statement not allowed.
Message Summary

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.