CORE
mm_assigndirectlib.sas
Go to the documentation of this file.
1 
31 %macro mm_assigndirectlib(
32  libref /* libref to assign from metadata */
33  ,debug= /* set to YES for extra log info */
34  ,open_passthrough= /* provide an alias to produce the
35  CONNECT TO statement for the
36  relevant external database */
37  ,sql_options= /* add any options to add to proc sql statement eg outobs=
38  (only valid for pass through) */
39  ,mDebug=0
40  );
41 
42 %local mD;
43 %if &mDebug=1 %then %let mD=;
44 %else %let mD=%str(*);
45 %&mD.put Executing mm_assigndirectlib.sas;
46 %&mD.put _local_;
47 
48 %&mD.put NOTE: Creating direct (non META) connection to &libref library;
49 
50 %if %upcase(&libref)=WORK %then %do;
51  %put NOTE: We already have a direct connection to WORK :-) ;
52  %return;
53 %end;
54 /* need to determine the library ENGINE first */
55 data _null_;
56  length lib_uri engine $256;
57  call missing (of _all_);
58  /* get URI for the particular library */
59  rc1=metadata_getnobj("omsobj:SASLibrary?@Libref ='&libref'",1,lib_uri);
60  /* get the Engine attribute of the previous object */
61  rc2=metadata_getattr(lib_uri,'Engine',engine);
62  &mD.put rc1= lib_uri= rc2= engine=;
63  call symputx("liburi",lib_uri,'l');
64  call symputx("engine",engine,'l');
65 run;
66 
67 /* now obtain engine specific connection details */
68 %if &engine=BASE %then %do;
69  %&mD.put NOTE: Retrieving BASE library path;
70  data _null_;
71  length up_uri $256 path cat_path $1024;
72  retain cat_path;
73  call missing (of _all_);
74  /* get all the filepaths of the UsingPackages association */
75  i=1;
76  rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
77  do while (rc3>0);
78  /* get the DirectoryName attribute of the previous object */
79  rc4=metadata_getattr(up_uri,'DirectoryName',path);
80  if i=1 then path = '("'!!trim(path)!!'" ';
81  else path =' "'!!trim(path)!!'" ';
82  cat_path = trim(cat_path) !! " " !! trim(path) ;
83  i+1;
84  rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
85  end;
86  cat_path = trim(cat_path) !! ");";
87  %&mD.put NOTE: Getting physical path for &libref library;
88  &mD.put rc3= up_uri= rc4= cat_path= path=;
89  %&mD.put NOTE: Libname cmd will be:;
90  %&mD.put libname &libref &filepath;
91  call symputx("filepath",cat_path,'l');
92  run;
93 
94  libname &libref &filepath;
95 
96 %end;
97 %else %if &engine=REMOTE %then %do;
98  data x;
99  length rcCon rcProp rc k 3 uriCon uriProp PropertyValue PropertyName Delimiter $256 properties $2048;
100  retain properties;
101  rcCon = metadata_getnasn("&liburi", "LibraryConnection", 1, uriCon);
102 
103  rcProp = metadata_getnasn(uriCon, "Properties", 1, uriProp);
104 
105  k = 1;
106  rcProp = metadata_getnasn(uriCon, "Properties", k, uriProp);
107  do while (rcProp > 0);
108  rc = metadata_getattr(uriProp , "DefaultValue",PropertyValue);
109  rc = metadata_getattr(uriProp , "PropertyName",PropertyName);
110  rc = metadata_getattr(uriProp , "Delimiter",Delimiter);
111  properties = trim(properties) !! " " !! trim(PropertyName) !! trim(Delimiter) !! trim(PropertyValue);
112  output;
113  k+1;
114  rcProp = metadata_getnasn(uriCon, "Properties", k, uriProp);
115  end;
116  %&mD.put NOTE: Getting properties for REMOTE SHARE &libref library;
117  &mD.put _all_;
118  %&mD.put NOTE: Libname cmd will be:;
119  %&mD.put libname &libref &engine &properties slibref=&libref;
120  call symputx ("properties",trim(properties),'l');
121  run;
122 
123  libname &libref &engine &properties slibref=&libref;
124 
125 %end;
126 
127 %else %if &engine=OLEDB %then %do;
128  %&mD.put NOTE: Retrieving OLEDB connection details;
129  data _null_;
130  length domain datasource provider properties schema
131  connx_uri domain_uri conprop_uri lib_uri schema_uri value $256.;
132  call missing (of _all_);
133  /* get source connection ID */
134  rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
135  /* get connection domain */
136  rc1=metadata_getnasn(connx_uri,'Domain',1,domain_uri);
137  rc2=metadata_getattr(domain_uri,'Name',domain);
138  &mD.putlog / 'NOTE: ' // 'NOTE- connection id: ' connx_uri ;
139  &mD.putlog 'NOTE- domain: ' domain;
140  /* get DSN and PROVIDER from connection properties */
141  i=0;
142  do until (rc<0);
143  i+1;
144  rc=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
145  rc2=metadata_getattr(conprop_uri,'Name',value);
146  if value='Connection.OLE.Property.DATASOURCE.Name.xmlKey.txt' then do;
147  rc3=metadata_getattr(conprop_uri,'DefaultValue',datasource);
148  end;
149  else if value='Connection.OLE.Property.PROVIDER.Name.xmlKey.txt' then do;
150  rc4=metadata_getattr(conprop_uri,'DefaultValue',provider);
151  end;
152  else if value='Connection.OLE.Property.PROPERTIES.Name.xmlKey.txt' then do;
153  rc5=metadata_getattr(conprop_uri,'DefaultValue',properties);
154  end;
155  end;
156  &mD.putlog 'NOTE- dsn/provider/properties: ' /
157  datasource provider properties;
158  &mD.putlog 'NOTE- schema: ' schema // 'NOTE-';
159 
160  /* get SCHEMA */
161  rc6=metadata_getnasn("&liburi",'UsingPackages',1,lib_uri);
162  rc7=metadata_getattr(lib_uri,'SchemaName',schema);
163  call symputx('SQL_domain',domain,'l');
164  call symputx('SQL_dsn',datasource,'l');
165  call symputx('SQL_provider',provider,'l');
166  call symputx('SQL_properties',properties,'l');
167  call symputx('SQL_schema',schema,'l');
168  run;
169 
170  %if %length(&open_passthrough)>0 %then %do;
171  proc sql &sql_options;
172  connect to OLEDB as &open_passthrough(INSERT_SQL=YES
173  /* need additional properties to make this work */
174  properties=('Integrated Security'=SSPI
175  'Persist Security Info'=True
176  %sysfunc(compress(%str(&SQL_properties),%str(())))
177  )
178  DATASOURCE=&sql_dsn PROMPT=NO
179  PROVIDER=&sql_provider SCHEMA=&sql_schema CONNECTION = GLOBAL);
180  %end;
181  %else %do;
182  LIBNAME &libref OLEDB PROPERTIES=&sql_properties
183  DATASOURCE=&sql_dsn PROVIDER=&sql_provider SCHEMA=&sql_schema
184  %if %length(&sql_domain)>0 %then %do;
185  authdomain="&sql_domain"
186  %end;
187  connection=shared;
188  %end;
189 %end;
190 %else %if &engine=ODBC %then %do;
191  &mD.%put NOTE: Retrieving ODBC connection details;
192  data _null_;
193  length connx_uri conprop_uri value datasource up_uri schema $256.;
194  call missing (of _all_);
195  /* get source connection ID */
196  rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
197  /* get connection properties */
198  i=0;
199  do until (rc2<0);
200  i+1;
201  rc2=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
202  rc3=metadata_getattr(conprop_uri,'Name',value);
203  if value='Connection.ODBC.Property.DATASRC.Name.xmlKey.txt' then do;
204  rc4=metadata_getattr(conprop_uri,'DefaultValue',datasource);
205  rc2=-1;
206  end;
207  end;
208  /* get SCHEMA */
209  rc6=metadata_getnasn("&liburi",'UsingPackages',1,up_uri);
210  rc7=metadata_getattr(up_uri,'SchemaName',schema);
211  &mD.put rc= connx_uri= rc2= conprop_uri= rc3= value= rc4= datasource=
212  rc6= up_uri= rc7= schema=;
213 
214  call symputx('SQL_schema',schema,'l');
215  call symputx('SQL_dsn',datasource,'l');
216  run;
217 
218  %if %length(&open_passthrough)>0 %then %do;
219  proc sql &sql_options;
220  connect to ODBC as &open_passthrough
221  (INSERT_SQL=YES DATASRC=&sql_dsn. CONNECTION=global);
222  %end;
223  %else %do;
224  libname &libref ODBC DATASRC=&sql_dsn SCHEMA=&sql_schema;
225  %end;
226 %end;
227 %else %do;
228  %put NOTE: Engine &engine is currently unsupported;
229  %put NOTE- Please contact your support team.;
230  %return;
231 %end;
232 
233 %mend;
234