LIBNAME ODBC with NOPROMPT option on Unix

[AlanC]

Is it possible to specify a libname with a noprompt option on unix?

For example:

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.

[SASKiwi]

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:

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n1ht6mv0wybbzkn1x4...

What database are you using?

[AlanC]

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.

[SASKiwi]

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:

[AlanC]

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.

[SASKiwi]

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.

[jakarman]

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

[AlanC]

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.

[jakarman]

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

[AlanC]

SAS 9.4, DataDirect ODBC.Let me read some more on what you have posted.

[jakarman]

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>

Progress KB - How to setup the connection string for an ODBC DSN-less connection to a Progress datab...

How to setup the connection string for an ODBC DSN-less connection to a Progress database?

[AlanC]

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]

How was this resolved? I recall coming up with a solution over 10 years ago but cannot find the code again. I think all the name=values from odbc.ini could be wrapped in noprompt=''

[SASKiwi]

@acfarrer - Maybe this post will help: https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

[acfarrer]

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 ;
input ;
put _infile_ ;
run ;

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
[PostgreSQL]
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.