Help using Base SAS procedures

LIBNAME ODBC with NOPROMPT option on Unix

Reply
Contributor
Posts: 27

LIBNAME ODBC with NOPROMPT option on Unix

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.

Respected Advisor
Posts: 2,993

Re: LIBNAME ODBC with NOPROMPT option on Unix

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?

Contributor
Posts: 27

Re: LIBNAME ODBC with NOPROMPT option on Unix

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.

Respected Advisor
Posts: 2,993

Re: LIBNAME ODBC with NOPROMPT option on Unix

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:

Contributor
Posts: 27

Re: LIBNAME ODBC with NOPROMPT option on Unix

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.

Respected Advisor
Posts: 2,993

Re: LIBNAME ODBC with NOPROMPT option on Unix

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.

Valued Guide
Posts: 3,206

Re: LIBNAME ODBC with NOPROMPT option on Unix

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

---->-- ja karman --<-----
Contributor
Posts: 27

Re: LIBNAME ODBC with NOPROMPT option on Unix

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.

Valued Guide
Posts: 3,206

Re: LIBNAME ODBC with NOPROMPT option on Unix

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

---->-- ja karman --<-----
Contributor
Posts: 27

Re: LIBNAME ODBC with NOPROMPT option on Unix

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

Valued Guide
Posts: 3,206

Re: LIBNAME ODBC with NOPROMPT option on Unix

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?

---->-- ja karman --<-----
Contributor
Posts: 27

Re: LIBNAME ODBC with NOPROMPT option on Unix

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.

Ask a Question
Discussion stats
  • 11 replies
  • 664 views
  • 0 likes
  • 3 in conversation