Reference Iseries or network userid

[ppbedz]

I am trying to create a data-base driven selection drop-down that lists the departments a user is authorized to view. Is there a way to reference the "signon" userid in Profound so I can code selection criteria in the drop-down? I tried creating a view that does the work, but the userid that is being fed into the view does not generate any records. The same code works properly on the Iseries.
Here is the view I created:

create view audeptview as
SELECT DPWHID, DPDEPT , DPDPNM FROM wodpm inner join mcgaudet on
user = aduser and adtype = 'GLDPT' WHERE advalue = dpdept or
advalue = '*ALL'

Thank you

[Scott Klement]

You can join tables based on special registers and constant values? I had no idea that you could join on anything besides columns... I'm having a hard time understanding how your view works... this idea of joining on something that doesn't belong to either table is completely alien to me.

You say that it works on iSeries. But... that's where Profound UI runs... unless you're making a distinction between iSeries vs Power Systems hardware? I'm just lost.

You could try replacing 'user' with 'current user' and see if that works better. But that's basically a shot in the dark, since I don't understand the code, I don't know what to suggest.

[ppbedz]

Yes, the join works properly on the Iseries. It does not like "current user". Profound does not seem to be replacing the contents of "user" with the same value as the Iseries. How does the currentUser api work in Profound? I was trying to see if I could use that as a parm in my selection criteria and change my logic to work around that, but I don't understand how or where to use it.

[SeanTyree]

The issue with using the special SQL register user is that the jobs run under userid QTMHHTTP. You will probably need to set a hidden field value to the current user in your RPG and use that field as an SQL parameter in your statement.

Sean

[Scott Klement]

The Rich Display does run the SQL statement on IBM i (which is what I assume you mean when you say "iSeries"). So in both cases, the SQL statement is running on the same computer by the same database engine.

The difference, I suspect, is the nature of the job that's running it. A standard Interactive or Batch job on IBM i knows the userid when it starts, and therefore the job name actually inclides that userid. Such as 123456/SKLEMENT/DSP01 (would be a job name for user named SKLEMENT). However, in a Rich Display, the job is submitted by the web server, and the job name is something like 123456/QTMHHTTP/PROFOUNDUI (so the user in the job name is the HTTP server's userid.) However, once Profound gets control it'll "synchronize" the userid to your Interactive job. So the userid changes in the middle of the job -- but the job id stays the same. So the jobid still says "QTMHHTTP" while the userid has actually switched to whatever your userid happens to be (PPBEDZ? or whatever it is.)

So that's the likely difference. In RPG programs, there are two spots in the PSDS, one that gets the user for the jobid, and one that gets the currently active user. So if this were an RPG program, I could easily tell you how to solve this, it'd just be a matter of using a different spot in the PSDS. But it's not, of course, it's an SQL statement.

For SQL, however, I had to do some research. It looks to me like the SESSION_USER is what you are looking for? So with your original idea, I would guess (and I don't really understand your original VIEW, so I could be wrong) that you could do this:

Code: Select all

create view audeptview as
SELECT DPWHID, DPDEPT , DPDPNM FROM wodpm inner join mcgaudet on
SESSION_USER = aduser and adtype = 'GLDPT' WHERE advalue = dpdept or
advalue = '*ALL'

Simply using SESSION_USER in place of USER, may do it? I don't know, because I really don't understand how that join works... (If it were in the WHERE clause, however, it'd make more sense to me.)

In your most recent post, however, you aren't using the JOIN anymore, so that makes things much easier. If it's okay to omit the join (and therefore, you don't need to use a VIEW anymore) then this should be pretty easy. You could just do

3-25-2015 7-13-23 PM.png (2.41 KiB) Viewed 477 times

If you wish to use our currentUser() API, that should also work. It's important to understand, however, that this isn't an SQL function. You can't call it directly in an SQL statement! What you would have to do is use a "parameter marker" (which is like a variable) in the statement, and fill-it in using the parameter value property.

So if you wanted to do that, you'd do this:

3-25-2015 7-17-36 PM.png (3.01 KiB) Viewed 477 times

The ? is a "parameter marker" (that's the SQL term for it) and can only take the place of a variable value. The "parameter value" property can be used to insert a value into the parameter marker at run time -- so in this case, I use "script:" to tell Profound UI to run some JavaScript code, and then I can call the currentUser() API to retrieve the current user. That current user will be inserted where the question mark (aka "parameter marker") is in the SQL statement.

So that's the other way to do it, here.. but I would think just using SESSION_USER would be simpler (assuming that it works for you... it worked fine in my tests.)

[ppbedz]

Thank you for the very thorough explanation. I recreated my view with the "SESSION_USER" and my drop-down list worked! I plan to test the other 2 options as well. The information for coding the APIs will be useful in the future. Thanks so much for your time!

[SeanTyree]

Scott, thanks for finding the information about the SESSION_USER. That's one I didn't know about and will be able to use in the future. I have been populating a hidden field w/ the current user from the RPG and leveraging the value of the hidden field in my SQL statements.

Sean