Passthru SQL (from Fraktal SAS Programming): Unterschied zwischen den Versionen

Aus phenixxenia.org
Zur Navigation springen Zur Suche springen
K
K
 
(4 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 8: Zeile 8:
 
|übersicht=DBMS Interaction (from Fraktal SAS Programming)
 
|übersicht=DBMS Interaction (from Fraktal SAS Programming)
 
}}
 
}}
 +
 +
== 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 ==
  
 
{| class="wikitable"
 
{| class="wikitable"
Zeile 19: Zeile 35:
 
|-
 
|-
 
|
 
|
−
  connect to oracle (user="&DB_USR." password="&DB_PWD." path="&DB_PTH.");
+
  connect to oracle  
 +
(user="&DB_USR."  
 +
password="&DB_PWD."  
 +
path="&DB_PTH."
 +
);
 
| '''Open connection to the DBMS using your credentials stored in [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]]'''
 
| '''Open connection to the DBMS using your credentials stored in [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]]'''
 
|-
 
|-
Zeile 28: Zeile 48:
 
|
 
|
 
  (
 
  (
−
| '''Open passthru SQL channel using a left bracket'''
 
−
|-
 
−
| 
 
 
  create table my_ora_tab as
 
  create table my_ora_tab as
 
  select *  
 
  select *  
 
   from ops$&DB_USR..&MYTABLE.
 
   from ops$&DB_USR..&MYTABLE.
−
| '''Type your Oracle SQL code as if you were using some Oracle client or frontend'''
 
−
|-
 
−
|
 
 
  )
 
  )
−
| '''Close passthru SQL channel using a right bracket'''
+
| '''Inside brackets type your DBMS SQL code as if you were using some DBMS client or frontend; [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]] are resolved before code is passed to any processing system'''
 
|-
 
|-
 
|
 
|
 
     by oracle
 
     by oracle
−
| '''Trigger execution using the DBMS connection opened before'''
+
| '''Specify the executing DBMS by connection name opened before'''
 
|-
 
|-
 
|
 
|
 
  ;
 
  ;
−
| '''Execute the query by issuing the SAS statement terminator ';''''
+
| '''''Pass the code through to DBMS'' by issuing the SAS statement terminator ';''''
 
|-
 
|-
 
|
 
|

Aktuelle Version vom 2. Juli 2014, 14:33 Uhr

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

Abgerufen von „https://phenixxenia.org/w/index.php?title=Passthru_SQL_(from_Fraktal_SAS_Programming)&oldid=3748“