mm_assigndirectlib.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Assigns library directly using details from metadata
4  @details Queries metadata to get the libname definition then allocates the
5  library directly (ie, not using the META engine).
6  usage:
7 
8  %mm_assignDirectLib(MyLib);
9  data x; set mylib.sometable; run;
10 
11  %mm_assignDirectLib(MyDB,open_passthrough=MyAlias);
12  create table MyTable as
13  select * from connection to MyAlias( select * from DBTable);
14  disconnect from MyAlias;
15  quit;
16 
17  <h4> SAS Macros </h4>
18  @li mf_getengine.sas
19  @li mp_abort.sas
20 
21  @param [in] libref the libref (not name) of the metadata library
22  @param [in] open_passthrough= () Provide an alias to produce the CONNECT TO
23  statement for the relevant external database
24  @param [in] sql_options= () Add any options to add to proc sql statement,
25  eg outobs= (only valid for pass through)
26  @param [in] mDebug= (0) set to 1 to show debug messages in the log
27  @param [in] mAbort= (0) set to 1 to call %mp_abort().
28 
29  @returns libname statement
30 
31  @version 9.2
32  @author Allan Bowe
33 
34 **/
35 
36 %macro mm_assigndirectlib(
37  libref
38  ,open_passthrough=
39  ,sql_options=
40  ,mDebug=0
41  ,mAbort=0
42 )/*/STORE SOURCE*/;
43 
44 %local mD;
45 %if &mDebug=1 %then %let mD=;
46 %else %let mD=%str(*);
47 %&mD.put Executing mm_assigndirectlib.sas;
48 %&mD.put _local_;
49 
50 %if &mAbort=1 %then %let mAbort=;
51 %else %let mAbort=%str(*);
52 
53 %&mD.put NOTE: Creating direct (non META) connection to &libref library;
54 
55 %local cur_engine;
56 %let cur_engine=%mf_getengine(&libref);
57 %if &cur_engine ne META and &cur_engine ne and %length(&open_passthrough)=0
58 %then %do;
59  %put NOTE: &libref already has a direct (&cur_engine) libname connection;
60  %return;
61 %end;
62 %else %if %upcase(&libref)=WORK %then %do;
63  %put NOTE: We already have a direct connection to WORK :-) ;
64  %return;
65 %end;
66 
67 /* need to determine the library ENGINE first */
68 %local engine;
69 data _null_;
70  length lib_uri engine $256;
71  call missing (of _all_);
72  /* get URI for the particular library */
73  rc1=metadata_getnobj("omsobj:SASLibrary?@Libref ='&libref'",1,lib_uri);
74  /* get the Engine attribute of the previous object */
75  rc2=metadata_getattr(lib_uri,'Engine',engine);
76  putlog "mm_assigndirectlib for &libref:" rc1= lib_uri= rc2= engine=;
77  call symputx("liburi",lib_uri,'l');
78  call symputx("engine",engine,'l');
79 run;
80 
81 /* now obtain engine specific connection details */
82 %if &engine=BASE %then %do;
83  %&mD.put NOTE: Retrieving BASE library path;
84  data _null_;
85  length up_uri $256 path cat_path $1024;
86  retain cat_path;
87  call missing (of _all_);
88  /* get all the filepaths of the UsingPackages association */
89  i=1;
90  rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
91  do while (rc3>0);
92  /* get the DirectoryName attribute of the previous object */
93  rc4=metadata_getattr(up_uri,'DirectoryName',path);
94  if i=1 then path = '("'!!trim(path)!!'" ';
95  else path =' "'!!trim(path)!!'" ';
96  cat_path = trim(cat_path) !! " " !! trim(path) ;
97  i+1;
98  rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
99  end;
100  cat_path = trim(cat_path) !! ")";
101  &mD.putlog "NOTE: Getting physical path for &libref library";
102  &mD.putlog rc3= up_uri= rc4= cat_path= path=;
103  &mD.putlog "NOTE: Libname cmd will be:";
104  &mD.putlog "libname &libref" cat_path;
105  call symputx("filepath",cat_path,'l');
106  run;
107 
108  %if %sysevalf(&sysver<9.4) %then %do;
109  libname &libref &filepath;
110  %end;
111  %else %do;
112  /* apply the new filelocks option to cater for temporary locks */
113  libname &libref &filepath filelockwait=5;
114  %end;
115 
116 %end;
117 %else %if &engine=REMOTE %then %do;
118  data x;
119  length rcCon rcProp rc k 3 uriCon uriProp PropertyValue PropertyName
120  Delimiter $256 properties $2048;
121  retain properties;
122  rcCon = metadata_getnasn("&liburi", "LibraryConnection", 1, uriCon);
123 
124  rcProp = metadata_getnasn(uriCon, "Properties", 1, uriProp);
125 
126  k = 1;
127  rcProp = metadata_getnasn(uriCon, "Properties", k, uriProp);
128  do while (rcProp > 0);
129  rc = metadata_getattr(uriProp , "DefaultValue",PropertyValue);
130  rc = metadata_getattr(uriProp , "PropertyName",PropertyName);
131  rc = metadata_getattr(uriProp , "Delimiter",Delimiter);
132  properties = trim(properties) !! " " !! trim(PropertyName)
133  !! trim(Delimiter) !! trim(PropertyValue);
134  output;
135  k+1;
136  rcProp = metadata_getnasn(uriCon, "Properties", k, uriProp);
137  end;
138  %&mD.put NOTE: Getting properties for REMOTE SHARE &libref library;
139  &mD.put _all_;
140  %&mD.put NOTE: Libname cmd will be:;
141  %&mD.put libname &libref &engine &properties slibref=&libref;
142  call symputx ("properties",trim(properties),'l');
143  run;
144 
145  libname &libref &engine &properties slibref=&libref;
146 
147 %end;
148 
149 %else %if &engine=OLEDB %then %do;
150  %&mD.put NOTE: Retrieving OLEDB connection details;
151  data _null_;
152  length domain datasource provider properties schema
153  connx_uri domain_uri conprop_uri lib_uri schema_uri value $256.;
154  call missing (of _all_);
155  /* get source connection ID */
156  rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
157  /* get connection domain */
158  rc1=metadata_getnasn(connx_uri,'Domain',1,domain_uri);
159  rc2=metadata_getattr(domain_uri,'Name',domain);
160  &mD.putlog / 'NOTE: ' // 'NOTE- connection id: ' connx_uri ;
161  &mD.putlog 'NOTE- domain: ' domain;
162  /* get DSN and PROVIDER from connection properties */
163  i=0;
164  do until (rc<0);
165  i+1;
166  rc=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
167  rc2=metadata_getattr(conprop_uri,'Name',value);
168  if value='Connection.OLE.Property.DATASOURCE.Name.xmlKey.txt' then do;
169  rc3=metadata_getattr(conprop_uri,'DefaultValue',datasource);
170  end;
171  else if value='Connection.OLE.Property.PROVIDER.Name.xmlKey.txt' then do;
172  rc4=metadata_getattr(conprop_uri,'DefaultValue',provider);
173  end;
174  else if value='Connection.OLE.Property.PROPERTIES.Name.xmlKey.txt' then
175  do;
176  rc5=metadata_getattr(conprop_uri,'DefaultValue',properties);
177  end;
178  end;
179  &mD.putlog 'NOTE- dsn/provider/properties: ' /
180  datasource provider properties;
181  &mD.putlog 'NOTE- schema: ' schema // 'NOTE-';
182 
183  /* get SCHEMA */
184  rc6=metadata_getnasn("&liburi",'UsingPackages',1,lib_uri);
185  rc7=metadata_getattr(lib_uri,'SchemaName',schema);
186  call symputx('SQL_domain',domain,'l');
187  call symputx('SQL_dsn',datasource,'l');
188  call symputx('SQL_provider',provider,'l');
189  call symputx('SQL_properties',properties,'l');
190  call symputx('SQL_schema',schema,'l');
191  run;
192 
193  %if %length(&open_passthrough)>0 %then %do;
194  proc sql &sql_options;
195  connect to OLEDB as &open_passthrough(INSERT_SQL=YES
196  /* need additional properties to make this work */
197  properties=('Integrated Security'=SSPI
198  'Persist Security Info'=True
199  %sysfunc(compress(%str(&SQL_properties),%str(())))
200  )
201  DATASOURCE=&sql_dsn PROMPT=NO
202  PROVIDER=&sql_provider SCHEMA=&sql_schema CONNECTION = GLOBAL);
203  %end;
204  %else %do;
205  LIBNAME &libref OLEDB PROPERTIES=&sql_properties
206  DATASOURCE=&sql_dsn PROVIDER=&sql_provider SCHEMA=&sql_schema
207  %if %length(&sql_domain)>0 %then %do;
208  authdomain="&sql_domain"
209  %end;
210  connection=shared;
211  %end;
212 %end;
213 %else %if &engine=ODBC %then %do;
214  %&mD.put NOTE: Retrieving ODBC connection details;
215  data _null_;
216  length connx_uri conprop_uri value datasource up_uri schema domprop_uri authdomain $256.;
217  call missing (of _all_);
218  /* get source connection ID */
219  rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
220  /* get connection properties */
221  i=0;
222  do until (rc2<0);
223  i+1;
224  rc2=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
225  rc3=metadata_getattr(conprop_uri,'Name',value);
226  if value='Connection.ODBC.Property.DATASRC.Name.xmlKey.txt' then do;
227  rc4=metadata_getattr(conprop_uri,'DefaultValue',datasource);
228  rc2=-1;
229  end;
230  end;
231 
232  /* get auth domain */
233  autrc=metadata_getnasn(connx_uri,"Domain",1,domprop_uri);
234  arc=metadata_getattr(domprop_uri,"Name",authdomain);
235  if not missing(authdomain) then authdomain=cats('AUTHDOMAIN=',authdomain);
236  call symputx('authdomain',authdomain,'l');
237 
238  /* get SCHEMA */
239  rc6=metadata_getnasn("&liburi",'UsingPackages',1,up_uri);
240  rc7=metadata_getattr(up_uri,'SchemaName',schema);
241  &mD.put rc= connx_uri= rc2= conprop_uri= rc3= value= rc4= datasource=
242  rc6= up_uri= rc7= schema=;
243 
244  call symputx('SQL_schema',schema,'l');
245  call symputx('SQL_dsn',datasource,'l');
246  run;
247 
248  %if %length(&open_passthrough)>0 %then %do;
249  proc sql &sql_options;
250  connect to ODBC as &open_passthrough
251  (INSERT_SQL=YES DATASRC=&sql_dsn. CONNECTION=global);
252  %end;
253  %else %do;
254  libname &libref ODBC DATASRC=&sql_dsn SCHEMA=&sql_schema &authdomain;
255  %end;
256 %end;
257 %else %if &engine=POSTGRES %then %do;
258  %put NOTE: Obtaining POSTGRES library details;
259  data _null_;
260  length database ignore_read_only_columns direct_exe preserve_col_names
261  preserve_tab_names server schema authdomain user password
262  prop name value uri urisrc $256.;
263  call missing (of _all_);
264  /* get database value */
265  prop='Connection.DBMS.Property.DB.Name.xmlKey.txt';
266  rc=metadata_getprop("&liburi",prop,database,"");
267  if database^='' then database='database='!!quote(trim(database));
268  call symputx('database',database,'l');
269 
270  /* get IGNORE_READ_ONLY_COLUMNS value */
271  prop='Library.DBMS.Property.DBIROC.Name.xmlKey.txt';
272  rc=metadata_getprop("&liburi",prop,ignore_read_only_columns,"");
273  if ignore_read_only_columns^='' then ignore_read_only_columns=
274  'ignore_read_only_columns='!!ignore_read_only_columns;
275  call symputx('ignore_read_only_columns',ignore_read_only_columns,'l');
276 
277  /* get DIRECT_EXE value */
278  prop='Library.DBMS.Property.DirectExe.Name.xmlKey.txt';
279  rc=metadata_getprop("&liburi",prop,direct_exe,"");
280  if direct_exe^='' then direct_exe='direct_exe='!!direct_exe;
281  call symputx('direct_exe',direct_exe,'l');
282 
283  /* get PRESERVE_COL_NAMES value */
284  prop='Library.DBMS.Property.PreserveColNames.Name.xmlKey.txt';
285  rc=metadata_getprop("&liburi",prop,preserve_col_names,"");
286  if preserve_col_names^='' then preserve_col_names=
287  'preserve_col_names='!!preserve_col_names;
288  call symputx('preserve_col_names',preserve_col_names,'l');
289 
290  /* get PRESERVE_TAB_NAMES value */
291  /* be careful with PRESERVE_TAB_NAMES=YES - it will mean your table will
292  become case sensitive!! */
293  prop='Library.DBMS.Property.PreserveTabNames.Name.xmlKey.txt';
294  rc=metadata_getprop("&liburi",prop,preserve_tab_names,"");
295  if preserve_tab_names^='' then preserve_tab_names=
296  'preserve_tab_names='!!preserve_tab_names;
297  call symputx('preserve_tab_names',preserve_tab_names,'l');
298 
299  /* get SERVER value */
300  if metadata_getnasn("&liburi","LibraryConnection",1,uri)>0 then do;
301  prop='Connection.DBMS.Property.SERVER.Name.xmlKey.txt';
302  rc=metadata_getprop(uri,prop,server,"");
303  end;
304  if server^='' then server='server='!!quote(cats(server));
305  call symputx('server',server,'l');
306 
307  /* get SCHEMA value */
308  if metadata_getnasn("&liburi","UsingPackages",1,uri)>0 then do;
309  rc=metadata_getattr(uri,"SchemaName",schema);
310  end;
311  if schema^='' then schema='schema='!!schema;
312  call symputx('schema',schema,'l');
313 
314  /* get AUTHDOMAIN value */
315  /* this is only useful if the user account contains that auth domain
316  if metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then do;
317  rc=metadata_getnasn(uri,"Domain",1,urisrc);
318  rc=metadata_getattr(urisrc,"Name",authdomain);
319  end;
320  if authdomain^='' then authdomain='authdomain='!!quote(trim(authdomain));
321  */
322  call symputx('authdomain',authdomain,'l');
323 
324  /* get user & pass */
325  if authdomain='' & metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then
326  do;
327  rc=metadata_getattr(uri,"UserID",user);
328  rc=metadata_getattr(uri,"Password",password);
329  end;
330  if user^='' then do;
331  user='user='!!quote(trim(user));
332  password='password='!!quote(trim(password));
333  end;
334  call symputx('user',user,'l');
335  call symputx('password',password,'l');
336 
337  &md.put _all_;
338  run;
339 
340  %if %length(&open_passthrough)>0 %then %do;
341  %put %str(WARN)ING: Passthrough option for postgres not yet supported;
342  %return;
343  %end;
344  %else %do;
345  %if &mdebug=1 %then %do;
346  %put NOTE: Executing the following:/;
347  %put NOTE- libname &libref POSTGRES &database &ignore_read_only_columns;
348  %put NOTE- &direct_exe &preserve_col_names &preserve_tab_names;
349  %put NOTE- &server &schema &authdomain &user &password //;
350  %end;
351  libname &libref POSTGRES &database &ignore_read_only_columns &direct_exe
352  &preserve_col_names &preserve_tab_names &server &schema &authdomain
353  &user &password;
354  %end;
355 %end;
356 %else %if &engine=ORACLE %then %do;
357  %put NOTE: Obtaining &engine library details;
358  data _null_;
359  length assocuri1 assocuri2 assocuri3 authdomain path schema $256;
360  call missing (of _all_);
361 
362  /* get auth domain */
363  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri1);
364  rc=metadata_getnasn(assocuri1,'Domain',1,assocuri2);
365  rc=metadata_getattr(assocuri2,"Name",authdomain);
366  call symputx('authdomain',authdomain,'l');
367 
368  /* path */
369  rc=metadata_getprop(assocuri1,
370  'Connection.Oracle.Property.PATH.Name.xmlKey.txt',path);
371  call symputx('path',path,'l');
372 
373  /* schema */
374  rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
375  rc=metadata_getattr(assocuri3,'SchemaName',schema);
376  call symputx('schema',schema,'l');
377  run;
378  %put NOTE: Executing the following:/; %put NOTE-;
379  %put NOTE- libname &libref ORACLE path=&path schema=&schema;
380  %put NOTE- authdomain=&authdomain;
381  %put NOTE-;
382  libname &libref ORACLE path=&path schema=&schema authdomain=&authdomain;
383 %end;
384 %else %if &engine=SQLSVR %then %do;
385  %put NOTE: Obtaining &engine library details;
386  data _null;
387  length assocuri1 assocuri2 assocuri3 authdomain path schema userid
388  passwd $256;
389  call missing (of _all_);
390 
391  rc=metadata_getnasn("&liburi",'DefaultLogin',1,assocuri1);
392  rc=metadata_getattr(assocuri1,"UserID",userid);
393  rc=metadata_getattr(assocuri1,"Password",passwd);
394  call symputx('user',userid,'l');
395  call symputx('pass',passwd,'l');
396 
397  /* path */
398  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri2);
399  rc=metadata_getprop(assocuri2,
400  'Connection.SQL.Property.Datasrc.Name.xmlKey.txt',path);
401  call symputx('path',path,'l');
402 
403  /* schema */
404  rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
405  rc=metadata_getattr(assocuri3,'SchemaName',schema);
406  call symputx('schema',schema,'l');
407  run;
408 
409  %put NOTE: Executing the following:/; %put NOTE-;
410  %put NOTE- libname &libref SQLSVR datasrc=&path schema=&schema ;
411  %put NOTE- user="&user" pass="XXX";
412  %put NOTE-;
413 
414  libname &libref SQLSVR datasrc=&path schema=&schema user="&user" pass="&pass";
415 %end;
416 %else %if &engine=TERADATA %then %do;
417  %put NOTE: Obtaining &engine library details;
418  data _null;
419  length assocuri1 assocuri2 assocuri3 authdomain path schema userid
420  passwd $256;
421  call missing (of _all_);
422 
423  /* get auth domain */
424  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri1);
425  rc=metadata_getnasn(assocuri1,'Domain',1,assocuri2);
426  rc=metadata_getattr(assocuri2,"Name",authdomain);
427  call symputx('authdomain',authdomain,'l');
428 
429  /*
430  rc=metadata_getnasn("&liburi",'DefaultLogin',1,assocuri1);
431  rc=metadata_getattr(assocuri1,"UserID",userid);
432  rc=metadata_getattr(assocuri1,"Password",passwd);
433  call symputx('user',userid,'l');
434  call symputx('pass',passwd,'l');
435  */
436 
437  /* path */
438  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri2);
439  rc=metadata_getprop(assocuri2,
440  'Connection.Teradata.Property.SERVER.Name.xmlKey.txt',path);
441  call symputx('path',path,'l');
442 
443  /* schema */
444  rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
445  rc=metadata_getattr(assocuri3,'SchemaName',schema);
446  call symputx('schema',schema,'l');
447  run;
448 
449  %put NOTE: Executing the following:/; %put NOTE-;
450  %put NOTE- libname &libref TERADATA server="&path" schema=&schema ;
451  %put NOTe- authdomain=&authdomain;
452  %put NOTE-;
453 
454  libname &libref TERADATA server="&path" schema=&schema authdomain=&authdomain;
455 %end;
456 %else %if &engine= %then %do;
457  %put NOTE: Libref &libref is not registered in metadata;
458  %&mAbort.mp_abort(
459  msg=%str(ERR)OR: Libref &libref is not registered in metadata
460  ,mac=mm_assigndirectlib.sas);
461  %return;
462 %end;
463 %else %do;
464  %put %str(WARN)ING: Engine &engine is currently unsupported;
465  %put %str(WARN)ING- Please contact your support team.;
466  %return;
467 %end;
468 
469 %mend mm_assigndirectlib;