Production Ready Macros for SAS Application Developers
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> Dependencies </h4>
18  @li mf_getengine.sas
19  @li mf_abort.sas
20 
21  @param libref the libref (not name) of the metadata library
22  @param open_passthrough= provide an alias to produce the CONNECT TO statement
23  for the relevant external database
24  @param sql_options= an override default output fileref to avoid naming clash
25  @param mDebug= set to 1 to show debug messages in the log
26  @param mAbort= set to 1 to call %mf_abort().
27 
28  @returns libname statement
29 
30  @version 9.2
31  @author Allan Bowe
32 
33 **/
34 
35 %macro mm_assigndirectlib(
36  libref /* libref to assign from metadata */
37  ,open_passthrough= /* provide an alias to produce the
38  CONNECT TO statement for the
39  relevant external database */
40  ,sql_options= /* add any options to add to proc sql statement eg outobs=
41  (only valid for pass through) */
42  ,mDebug=0
43  ,mAbort=0
44 )/*/STORE SOURCE*/;
45 
46 %local mD;
47 %if &mDebug=1 %then %let mD=;
48 %else %let mD=%str(*);
49 %&mD.put Executing mm_assigndirectlib.sas;
50 %&mD.put _local_;
51 
52 %if &mAbort=1 %then %let mAbort=;
53 %else %let mAbort=%str(*);
54 
55 %&mD.put NOTE: Creating direct (non META) connection to &libref library;
56 
57 %local cur_engine;
58 %let cur_engine=%mf_getengine(&libref);
59 %if &cur_engine ne META and &cur_engine ne %then %do;
60  %put NOTE: &libref already has a direct (&cur_engine) libname connection;
61  %return;
62 %end;
63 %else %if %upcase(&libref)=WORK %then %do;
64  %put NOTE: We already have a direct connection to WORK :-) ;
65  %return;
66 %end;
67 
68 /* need to determine the library ENGINE first */
69 %local engine;
70 data _null_;
71  length lib_uri engine $256;
72  call missing (of _all_);
73  /* get URI for the particular library */
74  rc1=metadata_getnobj("omsobj:SASLibrary?@Libref ='&libref'",1,lib_uri);
75  /* get the Engine attribute of the previous object */
76  rc2=metadata_getattr(lib_uri,'Engine',engine);
77  putlog "mm_assigndirectlib for &libref:" rc1= lib_uri= rc2= engine=;
78  call symputx("liburi",lib_uri,'l');
79  call symputx("engine",engine,'l');
80 run;
81 
82 /* now obtain engine specific connection details */
83 %if &engine=BASE %then %do;
84  %&mD.put NOTE: Retrieving BASE library path;
85  data _null_;
86  length up_uri $256 path cat_path $1024;
87  retain cat_path;
88  call missing (of _all_);
89  /* get all the filepaths of the UsingPackages association */
90  i=1;
91  rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
92  do while (rc3>0);
93  /* get the DirectoryName attribute of the previous object */
94  rc4=metadata_getattr(up_uri,'DirectoryName',path);
95  if i=1 then path = '("'!!trim(path)!!'" ';
96  else path =' "'!!trim(path)!!'" ';
97  cat_path = trim(cat_path) !! " " !! trim(path) ;
98  i+1;
99  rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
100  end;
101  cat_path = trim(cat_path) !! ")";
102  &mD.putlog "NOTE: Getting physical path for &libref library";
103  &mD.putlog rc3= up_uri= rc4= cat_path= path=;
104  &mD.putlog "NOTE: Libname cmd will be:";
105  &mD.putlog "libname &libref" cat_path;
106  call symputx("filepath",cat_path,'l');
107  run;
108 
109  %if %sysevalf(&sysver<9.4) %then %do;
110  libname &libref &filepath;
111  %end;
112  %else %do;
113  /* apply the new filelocks option to cater for temporary locks */
114  libname &libref &filepath filelockwait=5;
115  %end;
116 
117 %end;
118 %else %if &engine=REMOTE %then %do;
119  data x;
120  length rcCon rcProp rc k 3 uriCon uriProp PropertyValue PropertyName 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) !! trim(Delimiter) !! trim(PropertyValue);
133  output;
134  k+1;
135  rcProp = metadata_getnasn(uriCon, "Properties", k, uriProp);
136  end;
137  %&mD.put NOTE: Getting properties for REMOTE SHARE &libref library;
138  &mD.put _all_;
139  %&mD.put NOTE: Libname cmd will be:;
140  %&mD.put libname &libref &engine &properties slibref=&libref;
141  call symputx ("properties",trim(properties),'l');
142  run;
143 
144  libname &libref &engine &properties slibref=&libref;
145 
146 %end;
147 
148 %else %if &engine=OLEDB %then %do;
149  %&mD.put NOTE: Retrieving OLEDB connection details;
150  data _null_;
151  length domain datasource provider properties schema
152  connx_uri domain_uri conprop_uri lib_uri schema_uri value $256.;
153  call missing (of _all_);
154  /* get source connection ID */
155  rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
156  /* get connection domain */
157  rc1=metadata_getnasn(connx_uri,'Domain',1,domain_uri);
158  rc2=metadata_getattr(domain_uri,'Name',domain);
159  &mD.putlog / 'NOTE: ' // 'NOTE- connection id: ' connx_uri ;
160  &mD.putlog 'NOTE- domain: ' domain;
161  /* get DSN and PROVIDER from connection properties */
162  i=0;
163  do until (rc<0);
164  i+1;
165  rc=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
166  rc2=metadata_getattr(conprop_uri,'Name',value);
167  if value='Connection.OLE.Property.DATASOURCE.Name.xmlKey.txt' then do;
168  rc3=metadata_getattr(conprop_uri,'DefaultValue',datasource);
169  end;
170  else if value='Connection.OLE.Property.PROVIDER.Name.xmlKey.txt' then do;
171  rc4=metadata_getattr(conprop_uri,'DefaultValue',provider);
172  end;
173  else if value='Connection.OLE.Property.PROPERTIES.Name.xmlKey.txt' then do;
174  rc5=metadata_getattr(conprop_uri,'DefaultValue',properties);
175  end;
176  end;
177  &mD.putlog 'NOTE- dsn/provider/properties: ' /
178  datasource provider properties;
179  &mD.putlog 'NOTE- schema: ' schema // 'NOTE-';
180 
181  /* get SCHEMA */
182  rc6=metadata_getnasn("&liburi",'UsingPackages',1,lib_uri);
183  rc7=metadata_getattr(lib_uri,'SchemaName',schema);
184  call symputx('SQL_domain',domain,'l');
185  call symputx('SQL_dsn',datasource,'l');
186  call symputx('SQL_provider',provider,'l');
187  call symputx('SQL_properties',properties,'l');
188  call symputx('SQL_schema',schema,'l');
189  run;
190 
191  %if %length(&open_passthrough)>0 %then %do;
192  proc sql &sql_options;
193  connect to OLEDB as &open_passthrough(INSERT_SQL=YES
194  /* need additional properties to make this work */
195  properties=('Integrated Security'=SSPI
196  'Persist Security Info'=True
197  %sysfunc(compress(%str(&SQL_properties),%str(())))
198  )
199  DATASOURCE=&sql_dsn PROMPT=NO
200  PROVIDER=&sql_provider SCHEMA=&sql_schema CONNECTION = GLOBAL);
201  %end;
202  %else %do;
203  LIBNAME &libref OLEDB PROPERTIES=&sql_properties
204  DATASOURCE=&sql_dsn PROVIDER=&sql_provider SCHEMA=&sql_schema
205  %if %length(&sql_domain)>0 %then %do;
206  authdomain="&sql_domain"
207  %end;
208  connection=shared;
209  %end;
210 %end;
211 %else %if &engine=ODBC %then %do;
212  &mD.%put NOTE: Retrieving ODBC connection details;
213  data _null_;
214  length connx_uri conprop_uri value datasource up_uri schema $256.;
215  call missing (of _all_);
216  /* get source connection ID */
217  rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
218  /* get connection properties */
219  i=0;
220  do until (rc2<0);
221  i+1;
222  rc2=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
223  rc3=metadata_getattr(conprop_uri,'Name',value);
224  if value='Connection.ODBC.Property.DATASRC.Name.xmlKey.txt' then do;
225  rc4=metadata_getattr(conprop_uri,'DefaultValue',datasource);
226  rc2=-1;
227  end;
228  end;
229  /* get SCHEMA */
230  rc6=metadata_getnasn("&liburi",'UsingPackages',1,up_uri);
231  rc7=metadata_getattr(up_uri,'SchemaName',schema);
232  &mD.put rc= connx_uri= rc2= conprop_uri= rc3= value= rc4= datasource=
233  rc6= up_uri= rc7= schema=;
234 
235  call symputx('SQL_schema',schema,'l');
236  call symputx('SQL_dsn',datasource,'l');
237  run;
238 
239  %if %length(&open_passthrough)>0 %then %do;
240  proc sql &sql_options;
241  connect to ODBC as &open_passthrough
242  (INSERT_SQL=YES DATASRC=&sql_dsn. CONNECTION=global);
243  %end;
244  %else %do;
245  libname &libref ODBC DATASRC=&sql_dsn SCHEMA=&sql_schema;
246  %end;
247 %end;
248 %else %if &engine=POSTGRES %then %do;
249  %put NOTE: Obtaining POSTGRES library details;
250  data _null_;
251  length database ignore_read_only_columns direct_exe preserve_col_names
252  preserve_tab_names server schema authdomain user password
253  prop name value uri urisrc $256.;
254  call missing (of _all_);
255  /* get database value */
256  prop='Connection.DBMS.Property.DB.Name.xmlKey.txt';
257  rc=metadata_getprop("&liburi",prop,database,"");
258  if database^='' then database='database='!!quote(trim(database));
259  call symputx('database',database,'l');
260 
261  /* get IGNORE_READ_ONLY_COLUMNS value */
262  prop='Library.DBMS.Property.DBIROC.Name.xmlKey.txt';
263  rc=metadata_getprop("&liburi",prop,ignore_read_only_columns,"");
264  if ignore_read_only_columns^='' then ignore_read_only_columns=
265  'ignore_read_only_columns='!!ignore_read_only_columns;
266  call symputx('ignore_read_only_columns',ignore_read_only_columns,'l');
267 
268  /* get DIRECT_EXE value */
269  prop='Library.DBMS.Property.DirectExe.Name.xmlKey.txt';
270  rc=metadata_getprop("&liburi",prop,direct_exe,"");
271  if direct_exe^='' then direct_exe='direct_exe='!!direct_exe;
272  call symputx('direct_exe',direct_exe,'l');
273 
274  /* get PRESERVE_COL_NAMES value */
275  prop='Library.DBMS.Property.PreserveColNames.Name.xmlKey.txt';
276  rc=metadata_getprop("&liburi",prop,preserve_col_names,"");
277  if preserve_col_names^='' then preserve_col_names=
278  'preserve_col_names='!!preserve_col_names;
279  call symputx('preserve_col_names',preserve_col_names,'l');
280 
281  /* get PRESERVE_TAB_NAMES value */
282  /* be careful with PRESERVE_TAB_NAMES=YES - it will mean your table will
283  become case sensitive!! */
284  prop='Library.DBMS.Property.PreserveTabNames.Name.xmlKey.txt';
285  rc=metadata_getprop("&liburi",prop,preserve_tab_names,"");
286  if preserve_tab_names^='' then preserve_tab_names=
287  'preserve_tab_names='!!preserve_tab_names;
288  call symputx('preserve_tab_names',preserve_tab_names,'l');
289 
290  /* get SERVER value */
291  if metadata_getnasn("&liburi","LibraryConnection",1,uri)>0 then do;
292  prop='Connection.DBMS.Property.SERVER.Name.xmlKey.txt';
293  rc=metadata_getprop(uri,prop,server,"");
294  end;
295  if server^='' then server='server='!!server;
296  call symputx('server',server,'l');
297 
298  /* get SCHEMA value */
299  if metadata_getnasn("&liburi","UsingPackages",1,uri)>0 then do;
300  rc=metadata_getattr(uri,"SchemaName",schema);
301  end;
302  if schema^='' then schema='schema='!!schema;
303  call symputx('schema',schema,'l');
304 
305  /* get AUTHDOMAIN value */
306  /* this is only useful if the user account contains that auth domain
307  if metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then do;
308  rc=metadata_getnasn(uri,"Domain",1,urisrc);
309  rc=metadata_getattr(urisrc,"Name",authdomain);
310  end;
311  if authdomain^='' then authdomain='authdomain='!!quote(trim(authdomain));
312  */
313  call symputx('authdomain',authdomain,'l');
314 
315  /* get user & pass */
316  if authdomain='' & metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then
317  do;
318  rc=metadata_getattr(uri,"UserID",user);
319  rc=metadata_getattr(uri,"Password",password);
320  end;
321  if user^='' then do;
322  user='user='!!quote(trim(user));
323  password='password='!!quote(trim(password));
324  end;
325  call symputx('user',user,'l');
326  call symputx('password',password,'l');
327 
328  &md.put _all_;
329  run;
330 
331  %if %length(&open_passthrough)>0 %then %do;
332  %put WARNING: Passthrough option for postgres not yet supported;
333  %return;
334  %end;
335  %else %do;
336  %if &mdebug=1 %then %do;
337  %put NOTE: Executing the following:/;
338  %put NOTE- libname &libref POSTGRES &database &ignore_read_only_columns;
339  %put NOTE- &direct_exe &preserve_col_names &preserve_tab_names;
340  %put NOTE- &server &schema &authdomain &user &password //;
341  %end;
342  libname &libref POSTGRES &database &ignore_read_only_columns &direct_exe
343  &preserve_col_names &preserve_tab_names &server &schema &authdomain
344  &user &password;
345  %end;
346 %end;
347 %else %if &engine= %then %do;
348  %put NOTE: Libref &libref is not registered in metadata;
349  %&mAbort.mf_abort(
350  msg=ERROR: Libref &libref is not registered in metadata
351  ,mac=mm_assigndirectlib.sas);
352  %return;
353 %end;
354 %else %do;
355  %put WARNING: Engine &engine is currently unsupported;
356  %put WARNING- Please contact your support team.;
357  %return;
358 %end;
359 
360 %mend;
361 
  • Generated by doxygen For more information visit the Boemska MacroCore library.