Authentication script

[#1]

Hello everyone!

I'm trying to setup authentication using a combination of an inline table and a table from SQL using this script:

Section Access;

LOAD * INLINE [
 ACCESS, USERID, PASSWORD
 Admin, admin, admin
];

CONCATENATE LOAD 'Admin' as ACCESS,
 ULogin as USERID,
 UPassword as PASSWORD;
SQL SELECT *
FROM "ICS_AMD".dbo."usys_tblUsers";

The admin user from the inline table works fine, but none of the users in my SQL table work. I took out the Section Access command and looked at the table being brought in and it appears everything is working correctly. Am I doing something wrong?

Thanks for any insight provided!

[Anonymous]

Try using upper() function:
'ADMIN' as ACCESS
upper(ULogin) as USERID
upper(UPassword) as PASSWORD

I assume that the ULogin and UPassword should be in all caps, but don't rely on this.

[#3 · Accepted solution]

Thanks for the reply Michael!

I ended up finding some posts on this last night after I made this post (its amazing what the right search terms can do) and did exactly what you outlined above. It still wouldn't work. The account loaded in the inline would work, but nothing from the SQL side would work. Strangely, I got it to work by doing what you suggested in a separate temporary table above the Section Access and then loading the contents of that temporary table straight into the Section Access like this.

UsersTemp:
SQL SELECT 'ADMIN' as ACCESS,
upper(ULogin) as USERID,
upper(UPassword) as PASSWORD
FROM "ICS_AMD".dbo."usys_tblUsers";
Section access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD
ADMIN, ADMIN, ADMIN
];
CONCATENATE LOAD ACCESS,
USERID,
PASSWORD
RESIDENT UsersTemp;
Section Application;
Drop Table UsersTemp;

When I take the exact code used to create UsersTemp and stick it after the concatenate in the Section Access, it doesn't work. It will only work the way I have it here.

I got the idea to try it from something I found in the QV documentation on page 512 of Book 1. It says "All the fields listed in Load or Select statements in the section access must be written in UPPER CASE. Any field name containing lower case letters in the database will be converted to upper case before being read by the Load or Select statement."

So for whatever its worth, thats my story. If anything, I think it is of value to document it here in the forums.

Thanks again Michael!