07-07-2017 12:18 PM - edited 07-07-2017 12:18 PM
I am looking to create a regular script or macro allowing me to:
Using SAS/CONNECT: libname MAIN odbc user=***** password=*****dsn= myoracle schema=Table1 preserve_tab_names=yes;
If username connects to table then do;
Run oracle script 1;
Run oracle script 2;
07-07-2017 07:28 PM
Try the Libref function - this checks whether a libref has been assigned and returns 0 if it has and a non-zero value if not - the full documentation is here
07-07-2017 08:37 PM
If you issue the libname statement only once then only one attemt gets made to connect to the database. Just using the libref downstream won't create another connection attempt (especially as you're not using option defer=yes).
If your code tries to connect multiple times then you must have code with multiple libname statements.
In case you run all these 3 SQL's in the same session then just have a single libname statement on top of your script.
What at @ChrisBrooks proposes would allow you to check if a connection could get established and lets you implement code that ends in a controlled manner if something goes wrong. It wouldn't protect you though from SAS making a connection attempt first and your account would still get locked out after 3 such unsuccessful attempts.
One way to get around this:
Have a SAS Admin talk with the Oracle DBA's so he/she can define an authentication domain in SAS Metadata using a generic account where the password never expires or where admins managed the generic account and keep passwords in sync becomes an IT function. With such a domain defined you won't need to pass credentials in your libname statements anymor, you just need metadata read access to the definition in SAS Metadata.
If that's not possible then another option I can think of:
1. Implement a SAS Autocall macro
2. Have the macro issue the libname statement
3. Build in the check as proposes by @ChrisBrooks
4. Maintain a permanent control table in SAS where you write an entry in case you couldn't establish a connection
If possible: Only write this entry if the reason was an expired password. Not sure though if you get a specific return code for this.
5. In your macro always check first your control table for an entry of failed connection attempt and only issue the libname statement if there hasn't been such a failed attempt.
...and just as an idea: You could also add a timestamp to the entries for failed attempts and only consider entries not older than 30 minutes or so. Or you just have an adhoc script which cleans out the control table once you've changed your password.