Date:         Thu, 22 Jun 2006 09:18:24 -0400
Reply-To:     "Rickards, Clinton (GE Consumer Finance)"
              <clinton.rickards@GE.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Rickards, Clinton (GE Consumer Finance)"
              <clinton.rickards@GE.COM>
Subject:      Re: Pass-thru SQL in Enterprise Guide
Comments: To: ahowell@NETSPACE.NET.AU
In-Reply-To:  A<1150935144.4499e068c4ef3@webmail.netspace.net.au>
Content-Type: text/plain; charset="iso-8859-1"

Andrew,

See if you have the LIBCONOP macro available. This is a macro that walks the metadata and retrieves the stored login and DBMS information and place it into a set of macro variables. You then construct the connect statement with those variables. In your case, something like:

%LibConOp(sesetl_sasLibRef=cdcifrd,
          sesetl_OMRSASLibraryObjName=,
          sesetl_ChkDepRepos=Y
          ) ;
%put sesetl_ConnectionOptions=&sesetl_ConnectionOptions;
%put sesetl_EngineType=&sesetl_EngineType;

proc sql;
  connect to &sesetl_EngineType
               (&sesetl_BestLogin
                &sesetl_ConnectionOptions
                buffsize=5000 );
  create table work.mytable as
    select * from connection to oracle
    (
      select count(*) as Count from table1
    );
  disconnect from oracle;
quit;

If you don't have the macro, contact Tech Support. It uses a SCL entry (sashelp.regdata.LibraryConnectionOptions.scl) that may or not be available in your server installation (it is in our 9.1.3 install).

HTH,

Clint
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of
Andrew Howell
Sent: Wednesday, June 21, 2006 8:12 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Pass-thru SQL in Enterprise Guide


Greetings,

Currently running EG3 on SAS8 (Unix server), although the question is equally
applicable for SAS9 and/or any server platform..

The following pseudo-code runs ok as a "SAS Code" module in EG.

e.g,

proc sql;
  connect to oracle (user=scott pass=tiger schema=xyz);
  create table work.mytable as
    select * from connection to oracle
    (
      select count(*) as Count from table1
    );
  disconnect from oracle;
quit;

What I would like is to change to "connect" statement to prompt the user -
something akin to what happens with the SIGNON command in the Program Editor -
so the end-users use their authentication, not mine.

e.g,

  connect to oracle (user=_PROMPT_ pass=PROMPT schema=_PROMPT_);
or connect to oracle (_PROMPT_);
or connect to oracle (PROMPT=YES);

etc.

Thanks,
Andrew.


------------------------------------------------------------
This email was sent from Netspace Webmail: http://www.netspace.net.au