topic Re: SAS ACCESS JDBC in SAS Data Management

SAS ACCESS JDBC

dtravassos — Fri, 07 Jun 2019 21:49:41 GMT

Hey All,

I got the SAS/ACCESS interface with JDBC installed but I'm still getting the error:

libname x JDBC driverclass="com.jaspersoft.jdbc.driver.JasperDriver"
url="jdbc:jaspersoft@xxxxxxxxxxxxxxxxxxxxxxxxxxx"
user='xxxxxxx' password=XXXXXXXXXXX classpath="c:\users\danielle.cousseiro\lib";

ERROR: The JDBC engine cannot be found.
ERROR: Error in the LIBNAME statement.

The JDBC driver for the third party database is located in c:\users\danielle.cousseiro\lib.

When I run proc product_status, it does not show SAS/ACCESS interface with JDBC, although it is installed. Any clues?

For Base SAS Software ...
Custom version information: 9.4_M6
Image version information: 9.04.01M6P110718
For SAS/STAT ...
Custom version information: 15.1
For SAS/GRAPH ...
Custom version information: 9.4_M6
For SAS/ETS ...
Custom version information: 15.1
For SAS/OR ...
Custom version information: 15.1
For SAS/IML ...
Custom version information: 15.1
For SAS/QC ...
Custom version information: 15.1
For SAS/SHARE ...
Custom version information: 9.4_M5
For High Performance Suite ...
Custom version information: 2.2_M7
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M6
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M6

I saw a question related to the ODBC driver and the user had to perform the installation. I',m not sure if this would be the case as well

Many thanks

Danielle

Re: SAS ACCESS JDBC

Reeza — Fri, 07 Jun 2019 22:02:58 GMT

Does it show when you run PROC SETINIT?

Re: SAS ACCESS JDBC

Ksharp — Sat, 08 Jun 2019 13:11:21 GMT

ERROR: The JDBC engine cannot be found.

There is no such Access JDBC in SAS, Try ODBC if you have it (set up a connection/dsn in data source of control panel for Windows OS ).

Re: SAS ACCESS JDBC

dtravassos — Sat, 08 Jun 2019 20:31:53 GMT

Hi,

I would if I could, but our vendor said the database only accepts JDBC.

If there's no such thing as JDBC to SAS, why I just bought the software called SAS/ACCESS interface to JDBC? Perhaps SAS is tricking me 🙂

Re: SAS ACCESS JDBC

Reeza — Sun, 09 Jun 2019 01:11:34 GMT

What does the SETINIT show? That's what your license includes. Since this is a new addition, I'm assuming someone needed to install it for you. I'm guessing that didn't happen. In either case, you can also use SAS/CONNECT with JDBC drivers.

Re: SAS ACCESS JDBC

SASKiwi — Sun, 09 Jun 2019 09:12:07 GMT

SAS/ACCESS to JDBC was released in Nov 2018 in 9.4M6 so it is available: https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1k1dk2k5q2rsan10z2f41q2f1jl.htm&docsetVersion=9.4&locale=en

If PROC PRODUCT_STATUS does not show JDBC, then it hasn't been installed. Check with your SAS Administrator to see if it was purchased or not.

Re: SAS ACCESS JDBC

SASKiwi — Mon, 10 Jun 2019 01:54:33 GMT

Just to be more precise your SAS software would need to be in Revision 18w47:https://communities.sas.com/t5/SAS-Product-Release/SAS-Release-Event-Revision-18w47/ba-p/519154 or later to contain SAS/ACCESS to JDBC.

You can confirm your SAS revision by running this statement: %put &sysvlong4;

Re: SAS ACCESS JDBC

dtravassos — Mon, 10 Jun 2019 15:42:52 GMT

Hi Reeza,

Thnak you for your reply. I got the software installed (SAS/ACCESS interface with JDBC) however proc SETINIT does not show it as installed and that's what i'm trying to figure it out with the support. I saw at the website that SAS/ACCESS interface with JDBC works for SASViya and I'm wondering if it works with EG too. I'm not sure if that's the case.

Re: SAS ACCESS JDBC

Reeza — Mon, 10 Jun 2019 16:20:24 GMT

This is very much a tech support issue, your best bet is to keep working with them.

Re: SAS ACCESS JDBC

SASKiwi — Mon, 10 Jun 2019 20:13:52 GMT

I guess you are using SAS 9.x with SAS EG. What does proc product_status report now? Does it now include JDBC? My understanding is that SAS/ACCESS to JDBC is available with both SAS 9.4M6 and Viya.

Re: SAS ACCESS JDBC

dtravassos — Tue, 11 Jun 2019 15:52:18 GMT

Hi Reeza,

Yes, agreed. I was just wondering if someone had the same issue and could give me some help.

Thank you

Danielle

Re: SAS ACCESS JDBC

dtravassos — Tue, 11 Jun 2019 16:00:42 GMT

Hi SASKiwi,

Thank you fro your reply. I'm new to this installation process, so not sure if my answers are correct.

What I have here is SAS 9.4 with EG 7.1.

I hope JDBC works with both. I'm intrigued that all the documentation specifies SAS Viya. For example, the link below:

https://documentation.sas.com/?docsetId=dplyml0phy0lax&docsetTarget=n0xg0w4vqvat8gn102yocth6r1fa.htm&docsetVersion=3.4&locale=en

It says Note: The information in this section is applicable only if you ordered SAS/ACCESS Interface to JCBC (on SAS Viya).

But I also have SAS Studio and this link below says :

From SAS Studio, edit and run the following SAS code to verify the SAS/ACCESS to JDBC LIBNAME statement:

libname jdbc driver="full-jdbc-driver-name"
 URL="jdbc-connection-url" username="user-ID" password="user-password";

If SAS/ACCESS to JDBC was successfully deployed, the execution of the LIBNAME statement will return results without error.

When I run the code on SAS Studio, I also get the error like it wasn't deployed correctly.

ERROR: The JDBC engine cannot be found.

ERROR: Error in the LIBNAME statement.

So I don't know. Perhaps I have to reinstall the whole thing.

Re: SAS ACCESS JDBC

dtravassos — Tue, 11 Jun 2019 16:03:11 GMT

that's what this code gives me.

ODS _ALL_ CLOSE;

12 OPTIONS DEV=PNG;

13 GOPTIONS XPIXELS=0 YPIXELS=0;

14 FILENAME EGSR TEMP;

15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR

16 STYLE=HtmlBlue

17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")

18 NOGTITLE

19 NOGFOOTNOTE

20 GPATH=&sasworklocation

21 ENCODING=UTF8

22 options(rolap="on")

23 ;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

25 GOPTIONS ACCESSIBLE;

26 %put &sysvlong4;

9.04.01M6P11072018

Re: SAS ACCESS JDBC

Reeza — Tue, 11 Jun 2019 19:13:16 GMT

You should definitely be able to install the component. You never did confirm that SETINIT shows you have the appropriate license though. If it does, you can just install the component.

SetINIT verifies what's licensed, product_status what is installed.

Re: SAS ACCESS JDBC

SASKiwi — Tue, 11 Jun 2019 20:10:31 GMT

This means your SAS software is Version 9.4M6 released on 7 Nov 2018. This is the release where ACCESS to JDBC got added. You need to confirm using PROC SETINIT to see if you are licensed for it and whether it would have been in your software order or not. Only licensed products are in your software order download.

Re: SAS ACCESS JDBC

JBailey — Wed, 19 Jun 2019 17:36:20 GMT

Hi @dtravassos

SAS/ACCESS Interface to JDBC is, indeed, a new product!

The following messages indicate that SAS/ACCESS Interface to JDBC is either not installed, or installed incorrectly.

ERROR: The JDBC engine cannot be found.

ERROR: Error in the LIBNAME statement.

Sometimes admins try to install only the SAS/ACCESS engine on an old SAS Foundation install. In the case of a new product, such as SAS/ACCESS Interface to JDBC, it will not work on an older SAS release. The error messages will likely be different than those mentioned above.

Just for fun, here are a couple of examples of JDBC LIBNAME statements.

Up first, AWS Athena

/* The options can span line if the new line starts in column 1 and */
/* there is no space after the ; at the end of the line.           */

libname athena jdbc driver="com.simba.athena.jdbc.Driver"
classpath="C:\lib"
URL="jdbc:awsathena://athena.us-east-1.amazonaws.com:443;
s3_staging_dir=s3://bogus-athena/staging/;
S3OutputLocation=s3://bogus-athena/results/;
AwsCredentialsProviderClass=com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider;
AwsCredentialsProviderArguments=617292774228-sandbox" DATABASE=jeffdb;

Next, Teradata.

libname tera jdbc classpath="/opt/jdbcdrivers" 
                      class="com.teradata.jdbc.TeraDriver"
                      user="myuser" password="BogusPW1"
                      schema=myuser
                      URL="jdbc:teradata://tdserv.somecompany.com"
                      PRESERVE_NAMES=YES;

if you get something wrong while connecting, you will see the infamous Java error message dump.

Best wishes,

Jeff