Is it possible to specify a libname with a noprompt option on unix?
libname TEST odbc noprompt='driver=/drivers/mydriver.so;datasource=TEST;uid=xxxx;pwd=xxx;' ;
I cannot seem to get this to work. I do not want to use the odbc.ini file because it requires engaging a sysadmin every time and we have a lot of data sources. We would be issuing tickets non-stop to make that happen.
I use this all the time for ODBC on Windows, including driver selection, but I don't have access to Unix to try it out. I believe the options there are more limited
The documentation suggests that the ODBC.INI file must be used at least for driver set up:
What database are you using?
Every one known to man...seriously. What I am trying to connect right now is Cisco data virtualization.However, we literally have every database you can think of.
I need to know how to do it on unix, that is my main goal. I need to know doable or not so I can stop the pursuit or try something new.
I can understand your frustration. The problem is that the set up is different for every database. Some may require set up via the ODBC.INI and some may not. For example with Oracle you configure their TNSNAMES.ORA file, for SQL Server you need to configure ODBC.INI, then there are the environment variables that need setting too.
See this example for how involved it can get:
Yes, I am very familiar with the issue, and I appreciate the help, but I need to know the specific item I am dealing with not the other Access engines: only the ODBC one.
I am not frustrated with the problem, I just have to find out when to cut losses.
This is not a small SAS installation but a very large one. We have a large team that manages our environment. I have been coding SAS for a long time so I know what my challenges are.
Bottom-line, I do not want to ask for a new entry in the ODBC.ini every time we have to connect a new datasource. If that is the only choice, it is not desirable but it is what it is but I want to explore all possibilities.
Unfortunately I think this is a Unix-specific issue. Under Windows ODBC drivers are automatically registered, so it is possible to use the NOPROMPT LIBNAME option in many cases to do a complete configuration.
This is an example of what works under Windows without any other configuration: libname TEST odbc noprompt = "server=MYSQLServer;DRIVER=SQL Server;Trusted Connection=yes" QUALIFIER = Mydatabase SCHEMA = Myschema;
However everything I've read about Unix ODBC setup suggests you have to do a lot of external configuration to get it working.
For most database types the connections you should be able to be done in a easy jdbc way leaving it all to the programmer.
It are ICT departments blocking that and wanting to be in control of those definitions. The new threat is having that all done by ICT staff in SAS metadata.
Let us see to get rid of that.
I know with TD there is a machine (DNS) connection, then only a schema/database is needed.
The oracle example is more easy for me as it is documented now SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Seventh Edition See the path settings. Those are the settings found in the tnsnames.ora. Ergo: You don not need that file tnsname.ora .
With ODBC the Libname states SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Seventh Edition required prompt complete are not supported with Unix but noprompt is. There is dependency with the ODBC driver as you have many (Unix) and not just one (Windows) that is preinstalled. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Seventh Edition
For the necessary confusion http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf look how often dedicated drivers are using the odbc.ini almost everybody is using that. This progress one is mentioned for MS-SQL Unix Linux ODBC Driver from Progress DataDirect
The jdbc has a connection object not using the odb.ini (server port etc) but is different for each type of dbms. SAS has documented the wasy how to connect th SAS/Share SAS(R) 9.4 Drivers for JDBC: Cookbook (wrong direction you want from SAS to others ) this one are some examples 52777 - Examples of SAS/ACCESS® Interface to ODBC LIBNAME code used to access a Microsoft SQL Server... but not complete to your question
I have done a lot of research before I made it to the forums. However, no where that I can find documents whether it is possible. I need to know the extent of what is doable with noprompt because it is the only option not dealt with in the ODBC Access documentation. COMPLETE and PROMPT are both labelled explicitly that they are not available on Unix. That means noprompt works but it has limitations. Ok, what are those limitations.
In my situation, none of the other Access engines matter. We have all of them and all are used. However, the problem I am dealing with is specific to the ODBC driver. SAS, AFAIK, does not have an Access engine for JDBC (wish it did) so I cannot use it for JDBC consumption. I could use another means such as SOAP/REST but that is hard for most SAS programmers so I do not want to look at that yet.
AlanC,, I mentioned the JDBC approach as it using a connection string bypassing the odbc.ini on the same ODBC client installation.
Conclusion: It should be technically possible to have a DBMS ODBC connection in SAS not needing that ini file. We do not know whether and how SAS would do this.
JDBC is java code you can run java code from within SAS. SAS(R) 9.4 Language Reference: Concepts, Fifth Edition
Conclusion: when nothing as libname is workable that could be a way out.
The major point could be: Which SAS version you are running and which ODBC software is installed on the Unix. You didn't mention anything about that. The remarks are rather cumbersome that those choice is having much impact on the coding with ODBC interfaces. "see your odbc client installation manual". Yup which one?
When you have that and a jdbc sample is given put that in the noprompt area. Reviewing that approach to jdbc-oracle it is the same string going into the path option.
Any error message should help. A firewall or other one could be a blocking one. First try something easy that is working.
There must be a connectionstring like MySQL Connector/ODBC 5.1 Connection Strings - ConnectionStrings.com
Different coding as of the odbdc.ini file like MySQL :: MySQL Connector/ODBC Developer Guide :: 5.2 Connector/ODBC Connection Parameters
Sorry to react that soon.... https://documentation.progress.com/output/DataDirect/preview/odbcmongohelp80/index.html#page/mongodb... About the Driver : Using the Driver : Configuring and Connecting to Data Sources : Configuring the Product on UNIX/Linux : DSN-less Connections
There are the names of the drivers being mentioned in the odbcinst file. "At the beginning of the file is a section named [ODBC Drivers] that lists installed drivers, for example, DataDirect 8.0 MongoDB=Installed"
The sample are giving more details on the complete list:
DRIVER=<ODBC Driver Name>;HOST=<Hostname>; PORT=<Port>;DB=<DatabaseName>;UID=<Username>;PWD=<Password>;DIL=<DEFAULT ISOLATION LEVEL>
How to setup the connection string for an ODBC DSN-less connection to a Progress database?
Thanks for all of the help.
I have been working with SAS tech support on how to achieve this and they have been successful in making it happen. We will be documenting the approach and putting a paper out on how it is done.
@acfarrer - Maybe this post will help: https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...
I gave up trying to embed all the connection parameters in noprompt='' so that everything was contained in a single SAS program . The OP was trying to avoid engaging Admins to change the system $ODBCINI values. Instead, I created my own versions in $HOME/odbc and referenced them like this:
/* Connect to postgres */
%put %sysget(LD_LIBRARY_PATH) ;
options set=ODBCHOME="%sysget(HOME)/odbc" ;
%put %sysget(ODBCHOME) ;
options set=ODBCINI="%sysget(HOME)/odbc/odbc.ini" ;
%put %sysget(ODBCINI) ;
data _null_ ;
infile '!ODBCINI' truncover ;
put _infile_ ;
libname fsdb odbc datasrc='fsdb' schema = public ;
Setting $ODBHOME seems to be the only way to reference the Driver= definition:
$ more $HOME/odbc/odbcinst.ini
# Example driver definition
Description = ODBC for PostgreSQL
#Driver = /opt/sas/sashome/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/psqlodbcw.so
Driver = /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddpsql27.so
Both drivers above seem to work but I am still working on write access.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.