36%macro mm_assigndirectlib(
45%
if &mDebug=1 %then %let mD=;
47%&mD.put Executing mm_assigndirectlib.sas;
50%
if &mAbort=1 %then %let mAbort=;
51%
else %let mAbort=%str(*);
53%&mD.put NOTE: Creating direct (non META) connection to &libref library;
56%let cur_engine=%mf_getengine(&libref);
57%
if &cur_engine ne META and &cur_engine ne and %length(&open_passthrough)=0
59 %put NOTE: &libref already has a direct (&cur_engine) libname connection;
62%
else %
if %upcase(&libref)=WORK %then %
do;
63 %put NOTE: We already have a direct connection to WORK :-) ;
70 length lib_uri engine $256;
71 call missing (of _all_);
73 rc1=metadata_getnobj(
"omsobj:SASLibrary?@Libref ='&libref'",1,lib_uri);
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');
82%
if &engine=BASE %then %
do;
83 %&mD.put NOTE: Retrieving BASE library path;
85 length up_uri $256 path cat_path $1024;
87 call missing (of _all_);
90 rc3=metadata_getnasn(
"&liburi",
'UsingPackages',i,up_uri);
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) ;
98 rc3=metadata_getnasn(
"&liburi",
'UsingPackages',i,up_uri);
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');
108 %
if %sysevalf(&sysver<9.4) %then %
do;
109 libname &libref &filepath;
113 libname &libref &filepath filelockwait=5;
117%
else %
if &engine=REMOTE %then %
do;
119 length rcCon rcProp rc k 3 uriCon uriProp PropertyValue PropertyName
120 Delimiter $256 properties $2048;
122 rcCon = metadata_getnasn(
"&liburi",
"LibraryConnection", 1, uriCon);
124 rcProp = metadata_getnasn(uriCon,
"Properties", 1, uriProp);
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);
136 rcProp = metadata_getnasn(uriCon,
"Properties", k, uriProp);
138 %&mD.put NOTE: Getting properties
for REMOTE SHARE &libref library;
140 %&mD.put NOTE: Libname cmd will be:;
141 %&mD.put libname &libref &engine &properties slibref=&libref;
142 call symputx (
"properties",trim(properties),
'l');
145 libname &libref &engine &properties slibref=&libref;
149%
else %
if &engine=OLEDB %then %
do;
150 %&mD.put NOTE: Retrieving OLEDB connection details;
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_);
156 rc=metadata_getnasn(
"&liburi",
'LibraryConnection',1,connx_uri);
158 rc1=metadata_getnasn(connx_uri,
'Domain',1,domain_uri);
159 rc2=metadata_getattr(domain_uri,
'Name',domain);
160 &mD.putlog /
'NOTE: '
161 &mD.putlog
'NOTE- domain: ' domain;
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);
171 else if value=
'Connection.OLE.Property.PROVIDER.Name.xmlKey.txt' then
do;
172 rc4=metadata_getattr(conprop_uri,
'DefaultValue',provider);
174 else if value=
'Connection.OLE.Property.PROPERTIES.Name.xmlKey.txt' then
176 rc5=metadata_getattr(conprop_uri,
'DefaultValue',properties);
179 &mD.putlog
'NOTE- dsn/provider/properties: ' /
180 datasource provider properties;
181 &mD.putlog
'NOTE- schema: ' 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');
193 %
if %length(&open_passthrough)>0 %then %
do;
194 proc sql &sql_options;
195 connect to OLEDB as &open_passthrough(INSERT_SQL=YES
197 properties=(
'Integrated Security'=SSPI
198 'Persist Security Info'=True
199 %sysfunc(compress(%str(&SQL_properties),%str(())))
201 DATASOURCE=&sql_dsn PROMPT=NO
202 PROVIDER=&sql_provider SCHEMA=&sql_schema CONNECTION = GLOBAL);
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"
213%
else %
if &engine=ODBC %then %
do;
214 %&mD.put NOTE: Retrieving ODBC connection details;
216 length connx_uri conprop_uri value datasource up_uri schema domprop_uri authdomain $256.;
217 call missing (of _all_);
219 rc=metadata_getnasn(
"&liburi",
'LibraryConnection',1,connx_uri);
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);
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');
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=;
244 call symputx(
'SQL_schema',schema,
'l');
245 call symputx(
'SQL_dsn',datasource,
'l');
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);
254 libname &libref ODBC DATASRC=&sql_dsn SCHEMA=&sql_schema &authdomain;
257%
else %
if &engine=POSTGRES %then %
do;
258 %put NOTE: Obtaining POSTGRES library details;
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_);
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');
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');
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');
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');
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');
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,
"");
304 if server^=
'' then server=
'server='!!quote(cats(server));
305 call symputx(
'server',server,
'l');
308 if metadata_getnasn(
"&liburi",
"UsingPackages",1,uri)>0 then
do;
309 rc=metadata_getattr(uri,
"SchemaName",schema);
311 if schema^=
'' then schema=
'schema='!!schema;
312 call symputx(
'schema',schema,
'l');
322 call symputx(
'authdomain',authdomain,
'l');
325 if authdomain=
'' & metadata_getnasn(
"&liburi",
"DefaultLogin",1,uri)>0 then
327 rc=metadata_getattr(uri,
"UserID",user);
328 rc=metadata_getattr(uri,
"Password",password);
331 user=
'user='!!quote(trim(user));
332 password=
'password='!!quote(trim(password));
334 call symputx(
'user',user,
'l');
335 call symputx(
'password',password,
'l');
340 %
if %length(&open_passthrough)>0 %then %
do;
341 %put %str(WARN)ING: Passthrough option
for postgres not yet supported;
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
351 libname &libref POSTGRES &database &ignore_read_only_columns &direct_exe
352 &preserve_col_names &preserve_tab_names &server &schema &authdomain
356%
else %
if &engine=ORACLE %then %
do;
357 %put NOTE: Obtaining &engine library details;
359 length assocuri1 assocuri2 assocuri3 authdomain path schema $256;
360 call missing (of _all_);
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');
369 rc=metadata_getprop(assocuri1,
370 'Connection.Oracle.Property.PATH.Name.xmlKey.txt',path);
371 call symputx(
'path',path,
'l');
374 rc=metadata_getnasn(
"&liburi",
'UsingPackages',1,assocuri3);
375 rc=metadata_getattr(assocuri3,
'SchemaName',schema);
376 call symputx(
'schema',schema,
'l');
378 %put NOTE: Executing the following:/; %put NOTE-;
379 %put NOTE- libname &libref ORACLE path=&path schema=&schema;
380 %put NOTE- authdomain=&authdomain;
382 libname &libref ORACLE path=&path schema=&schema authdomain=&authdomain;
384%
else %
if &engine=SQLSVR %then %
do;
385 %put NOTE: Obtaining &engine library details;
387 length assocuri1 assocuri2 assocuri3 authdomain path schema userid
389 call missing (of _all_);
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');
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');
404 rc=metadata_getnasn(
"&liburi",
'UsingPackages',1,assocuri3);
405 rc=metadata_getattr(assocuri3,
'SchemaName',schema);
406 call symputx(
'schema',schema,
'l');
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";
414 libname &libref SQLSVR datasrc=&path schema=&schema user=
"&user" pass=
"&pass";
416%
else %
if &engine=TERADATA %then %
do;
417 %put NOTE: Obtaining &engine library details;
419 length assocuri1 assocuri2 assocuri3 authdomain path schema userid
421 call missing (of _all_);
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');
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');
444 rc=metadata_getnasn(
"&liburi",
'UsingPackages',1,assocuri3);
445 rc=metadata_getattr(assocuri3,
'SchemaName',schema);
446 call symputx(
'schema',schema,
'l');
449 %put NOTE: Executing the following:/; %put NOTE-;
450 %put NOTE- libname &libref TERADATA server=
"&path" schema=&schema ;
451 %put NOTe- authdomain=&authdomain;
454 libname &libref TERADATA server=
"&path" schema=&schema authdomain=&authdomain;
456%
else %
if &engine= %then %
do;
457 %put NOTE: Libref &libref is not registered in metadata;
459 msg=%str(ERR)OR: Libref &libref is not registered in metadata
460 ,mac=mm_assigndirectlib.sas);
464 %put %str(WARN)ING: Engine &engine is currently unsupported;
465 %put %str(WARN)ING- Please contact your support team.;
469%mend mm_assigndirectlib;