Passthru SQL (from Fraktal SAS Programming)

Aus phenixxenia.org
Wechseln zu: Navigation, Suche

Zurück

Übersicht

Vorwärts

What is this?

The term "passthru" is short for "pass through" and is to express that SQL code "passed" is processed by another system that is connected by appropriate middleware in a way that system borders are not visible when looking at the code.

"SAS" implemented this technique to allow the advanced and SQL experienced programmer full control over the SQL code processed on the DBMS side. Without passthru SQL the code sent to the DBMS is prepared (generated and optimized) on the SAS side only.

We will use a maximum simplified task to be coded in SQL:

  • A DBMS data table shall be created
  • Data for which are kept in another DBMS data table
  • The DBMS talks ANSI SQL
  • SAS can connect to the DBMS using SAS/Connect software
  • The solution uses Proc SQL from Base SAS software


Documented Code Example

Code executed Function performed
proc sql;
Start the SQL interpreter inside SAS
connect to oracle 
(user="&DB_USR." 
password="&DB_PWD." 
path="&DB_PTH."
);
Open connection to the DBMS using your credentials stored in Macro Variables
execute
Start coding a passthru SQL code segment
(
create table my_ora_tab as
select * 
  from ops$&DB_USR..&MYTABLE.
)
Inside brackets type your DBMS SQL code as if you were using some DBMS client or frontend; Macro Variables are resolved before code is passed to any processing system
    by oracle
Specify the executing DBMS by connection name opened before
;
Pass the code through to DBMS by issuing the SAS statement terminator ';'
disconnect from oracle;
Close connection to DBMS
quit;
Terminate SAS SQL interpreter status

Zurück

Übersicht

Vorwärts