Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL Connect To Using is Ignoreing User
has no ratings.
Published: 25 Jan 2013
Revised: 25 Jan 2013 - 1974 days ago
Last viewed on: 21 Jun 2018 (3271 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL Connect To Using is Ignoreing User Published by: Bob Cozzi on 25 Jan 2013 view comments(1)

I have an application that connects to a remote database using embedded SQL.

I use:

EXEC SQL CONNECT TO :myRMT USER :rmtUser USING :rmtPWD;

But when I look in the joblog, it is using the current job's user profile to create the connect, not the user I specify in the RMTUSER variable.

The remote systems (the ones calling the SQL CONNECT TO statement) are on IBM i5 v5r4 whereas the headquarters systems is on IBM i v7r1 so 3-level names aren't an option yet.

I really don't want to create the same user on both system as we have people who are not permitted to get to certain systems, but those same systems house data to which they need application-level access.

Here's the cut/paste from the program itself:

Inside the field named store.rmtUser is 'BOBBY' and yet when the connection is attempted it is using the User Profile from the job instead.

if (store.rmt <> '');                                        
   if (store.firstTime);                                     
      EXEC SQL CONNECT TO   :store.rmt                       
                       USER :store.rmtUser USING :store.rmtPwd;          
      store.firstTime = *OFF;                                
   else;                                                     
      exec SQL SET CONNECTION :store.rmt;                    
   endif;                                                    
endif;                                                       

It is in a fetch loop and I am getting data from the local source machine and the remote system for the display.

I though CONNECT TO   USER USING would do the trick. Any suggestions?

[UPDATE: More info... if the user calling the app has USRCLS(*SECOFR) the CONNECT TO USER USING works by connecting to the remote server with the user in the RMTUSER variable.

The specific user we were trying to get working, has USRCLS(*SYSOPR) and no, I did not setup this system.

I wonder what needs to change to make this thing work?]

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS