Passthru SQL (from Fraktal SAS Programming): Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
K |
K |
||
(3 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 36: | Zeile 52: | ||
from ops$&DB_USR..&MYTABLE. | from ops$&DB_USR..&MYTABLE. | ||
) | ) | ||
− | | '''Inside brackets type your DBMS SQL code as if you were using some DBMS client or frontend''' | + | | '''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 | ||
− | | ''' | + | | '''Specify the executing DBMS by connection name opened before''' |
|- | |- | ||
| | | |
Aktuelle Version vom 2. Juli 2014, 14:33 Uhr
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 |