Re: PROBLEM, Re: Almost a year and a half ago, I did some work with SQL UDFs.

hi James,

The currently signed on user is in positions 358-367 of the SDS.

(Positions 254-263 are the user component of the jobId -- not the proper field for what you're doing. The JobId is something like 031575/QUSER/QZDASOINIT, and does not change when the user profile changes.)

So the proper field for the userid is 358-367. But, there's one gotcha for _that_ field: It's only set when your RPG program starts. It does not change if the userid changes in the middle of your program.

As such, I recommend a simple subprocedure like this:

P getUser B
D PI 10a
D myUser s 10a inz(*user)
c return myUser
P E

The advantage to this technique is that the userid is set when the subprocedure is called (since that's when it initializes it's myUser variable) and therefore if you call it again later (after the user has changed again) you'll get the updated userid.

Plus, I just like inz(*user) better than hard-coding positions into an SDS... it's just less arcane, IMHO.

-SK




On 9/6/2012 1:33 PM, James Lampert wrote:

Oh, boy, I just discovered some trouble.

As it turns out, I still have a censored view from my previous experiments.

The censorship, based on current signed-on user (as derived from the PGSDS)

D SDS
D LIB 81 90
D DUSER 254 263

works perfectly if I open the view with QuestView, or if I open the view
with STRSQL.

But if I look at the view from Squirrel SQL, through a JDBC connection,
the censorship doesn't work: I see records I'm not supposed to.

I see a QZDASOINIT job with these attributes (I've censored references
to my own user profile):

Display Job Status Attributes

Job: QZDASOINIT User: QUSER Number: 031575

Status of job . . . . . . . . . . . . . . . : ACTIVE
Current user profile . . . . . . . . . . . : <my-user-profile>
Job user identity . . . . . . . . . . . . . : <my-user-profile>
Set by . . . . . . . . . . . . . . . . . : *DEFAULT

I get the feeling that the PGSDS of my service program is returning
QUSER instead of my own user profile (with which I established the JDBC
connection).

How do I get around that, so the censorship function acts on the correct
user profile?

And I suppose this kills DETERMINISTIC, since the JDBC runs in a
prestart job. Or is there a way that the call to the UDF

WHERE CENSORACCT(U01430, U01429) = 'P'

in the view definition itself can pass (as somebody else suggested) the
user ID?

(And note, U01430 and U01429 are not user profile names; they're numbers
associated with user profiles).

--
JHHL