How can I run a FEX file from the UNIX command line against a given database using a specific username and password to connect to that database?

I’ve tried using edastart, but it will only allow me to specify a password (i.e. using the –pass option) on the command line – not a user id. The general syntax that we’re using follows below:

edastart -t -pass ${db_password}

By enabling tracing (--traceon) and analyzing the trace logs, we were able to determine that the UNIX login id running the edastart program is the one that’s getting sent to the database. For example, if I’m logged into UNIX as “reportuser” and running edastart, then the id that gets sent to the target database is “reportuser”. The password that’s sent is the one specified using the –pass option (i.e. ${db_password} in the example above).

Side note - we are in the process of switching our data server security from PTH to DBMS.

This message has been edited. Last edited by: Kerry,

You would have to hard-code a connect string in your focexec or a profile, global or user, with amper variables. You can use the GETUSER subroutine to get the user id and use it and the passed password in your connect string.

There are other edastart options (-x, etc.) that would allow you to execute focexecs and pass variables. That might be another way.

Ginny -- thanks so much for your reply.

Could you give me an example of a connect string -- specifically, one that I could hard-code into the FEX? I'm relatively new to WebFOCUS, so I'm not sure how to do it.

Here is one for DB2. I don't know what your data base type is.

ENGINE DB2 SET CONNECTION_ATTRIBUTES  connection_name/&USER,&PASS

You would change the engine type and connection_name depending on your environment.

BTW, this is fairly sophisticated stuff for a newbie. This is architecture stuff. Good luck!

Thanks for so much for the command line -- I think that I'm one step closer now.

I tried something like the following (we're using Oracle):

ENGINE ORA SET CONNECTION_ATTRIBUTES MYDB/myuser,mypass
ENGINE ORA SET SERVER MYDB

When I enter SQL commands, the connection to the MYDB database is made, and the expected output returns. See below:


>>ENGINE ORA SELECT USER FROM DUAL
>END

NUMBER OF RECORDS IN TABLE= 1 LINES= 1


USER
----
MYUSER


However, when I try to issue WebFOCUS code, it seems that this code is being sent directly to the database (Oracle)for translation rather than being passed to the WebFOCUS interpereter first, translated to SQL, and then passed to Oracle. Consequently, I receive the error below:

>>ENGINE ORA
>TABLE FILE DUAL
>PRINT *
>END
(FOC1400) SQLCODE IS 900 (HEX: 00000384)
: ORA-00900: invalid SQL statement
(FOC1414) EXECUTE IMMEDIATE ERROR.
>>


Is there a way to submit the "TABLE FILE..." code above so that it's first translated by the WebFOCUS interpereter and then passed to Oracle, using the MYDB connection that I set up?

quote:

ORA-00900: invalid SQL statement

The reason for that error is because the ENGINE statement should be
ENGINE SQLORA not ENGINE ORA

That mistake makes WebFOCUS generate none ORACLE SQL

JG - Thanks for your reply. What I'm trying to do is make a database connection once I'm already in edastart, and then send WebFOCUS code (i.e. TABLE FILE DUAL...) -- not SQL -- to this database connection.

I tried your suggestion, but I'm still getting an error because the WebFOCUS code is going directly to Oracle rather than being translated first by WebFOCUS and then passed to Oracle.

>>ENGINE SQLORA
>TABLE FILE DUAL
>PRINT *
>END
(FOC1400) SQLCODE IS 900 (HEX: 00000384)
: ORA-00900: invalid SQL statement
(FOC1414) EXECUTE IMMEDIATE ERROR.

Any other ideas?

Thanks again for your help.

That makes sense, doesn't it?
When you start a statement with ENGINE SQLORA, the rest of the statement is sent directly to oracle. The 'rest' is defined as either the remainder of the line, or until the word END has been encountered. The rest of your line is empty, so everything up to END is sent to Oracle. And that product does not understand TABLE FILE.
Try to issue ENGINE ORA SET SERVER MYDB and then on the next line your TABLE FILE. That should work, since now the ENGINE statement is complete.

GamP,

Thanks for your response. I'm starting to think that the issue may have something to do with our security setup, which we are in the process of changing from PTH to DBMS. We're able to execute reports from Dev Studio and from the web-based MRE without any problems using DBMS -- it's only when we run edastart (with DBMS) that we're encountering problems.

I tried your suggestion and got a different error:

>>ENGINE ORA SET CONNECTION_ATTRIBUTES MYDB/myuser,mypass
>>ENGINE ORA SET SERVER MYDB
>>TABLE FILE DUAL
>PRINT *
>END
(FOC1400) SQLCODE IS 1005 (HEX: 000003ED)
(FOC1394) CONNECT FAILURE
: ORA-01005: null password given; logon denied
(FOC1406) SQL OPEN CURSOR ERROR. : DUAL
>>

I believe what's happening here is that edastart is trying to connect to MYDB with the unix id that's running edastart and no password. The unix id doesn't have a corresponding database account in MYDB. We can supply the password on the edastart command line with the "-pass" option, but we still can't seem to pass a user other than the one that's running edastart -- unless we use SQLORA to send SQL directly to the database. What we want to do is connect with a specified user and run WebFOCUS code.

Perhaps it's not possible to do what I'm trying to do if we're using DBMS security. It seems like we don't have any control over the user name that gets sent to the database -- again, unless we're sending SQL straight to Oracle.

Any other ideas?

I don't think this will ever work then with edastart and DBMS security. You might want to try running rdaapp.sh in a batch-type script and supply all of the parameters it requires including a userid and password.

edastart is a stand-alone tscom while rdaapp.sh is a client api tool.

Ginny,

Thanks again for your response, and especially for letting me know about the rdaapp.sh script -- until now, I was not familiar with it. This script almost met our needs perfectly, except that I couldn't find a way to pass a password to the initial password prompt in a scripted mode. (I had to enter it interactively.) I also think I read something about ibi not supporting use of this script in production and intending it only for testing purposes. I may have misunderstood what I read though, and it seems like other folks on this forum may be using it based on some other posts that I read.

The good news is that my investigation into the rdaapp.sh script made me realize that one of our ACX files wasn't set up as I had expected. Once I changed the file, the edastart approach actually worked!

I think the problem was that the (bad) ACX file for DUAL was pointing to a database other than MYDB. My hunch is that the straight SQL worked despite the bad ACX file because the SQL statement got the connection info from my locally defined SET_CONNECTION ATTRIBUTES... statement and bypassed what was in the ACX file. When I tried to issue FOCUS code, however, FOCUS read the (bad) ACX file first, and attempted to connect to the database listed there, which was not MYDB. That explains the error that I was getting:

ORA-01005: null password given; logon denied

FOCUS basically said: okay, I'll try connecting to the database in the ACX file using the UNIX id under which you're running edastart and the password that you specified on the edastart command line. I didn't specify a password (via the -pass option), hence the "null password given" message.

Changing the ACX file to point to MYDB, however, didn't remove the need to define the MYDB connection locally in my edastart session. Unless I defined the local connection, I got the following error:

(FOC1677) UNDECLARED SERVER "MYDB" REFERENCED FOR ORACLE INTERFACE

I'm guessing that this happened because the MYDB connection wasn't defined at a higher level (in edasprof.prf??), so FOCUS basically said: you haven't defined this connection anywhere, so I can't run your code. Once I defined the connection locally in my edastart session, I was able to execute FOCUS code against that connection without any problems.

Incidentally, the ": ORA-00900: invalid SQL statement" error was occurring because I was preceeding the statement with "ENGINE ORA". GamP picked up on this error.

The help that I have received on this forum has been invaluable -- I can't tell you and the others who responded how much I appreciate it. This is my first post on this forum and I was amazed at how willing folks have been to offer their assistance -- especially given that I'm a "newbie".

THANK YOU AGAIN!