topic Re: Run JDBC Query in Developers

Run JDBC Query

danielziv — Tue, 05 Feb 2019 15:45:46 GMT

Hi,

I currently run on a free trial of SAS Viya and wish to verify that I can query my DB before we purchase.

I wish to connect via JDBC and can not find a full working example of a select statement online.

Can someone please assist?

Attached below the example I've written by documentation I found online

cas casauto;

caslib jdcaslib desc='JDBC Caslib' 
   dataSource=(srctype='jdbc',
               url="jdbc:Sqream://3.16.23.107:5001/master",
               username='sqream', 
               password='sqream'
               class="com.sqream.jdbc.SQDriver"
               classpath="/Public/SQream/SqreamJDBC.jar",
				schema="public");


proc casutil;
   list files incaslib="jdcaslib";               
   load casdata="nation" incaslib="jdcaslib" outcaslib="casuser"
        casout="DBdata_from_jdcaslib";           
   list tables incaslib="casuser";               
   contents casdata="DBdata_from_jdcaslib" incaslib="casuser";
quit;

Re: Run JDBC Query

JBailey — Tue, 26 Feb 2019 21:57:17 GMT

Hi @danielziv,

Does this help? It is from a great SGF 2019 paper that will be published soon. A friend of mine is writing it.

caslib clibTera sessref=mysess 
                    datasource=(srctype="jdbc", 
                                       classpath="/opt/teradata/client/JDBC", 
                                       driverclass="com.teradata.jdbc.TeraDriver",
                                       URL="jdbc:teradata://teradata.sas.com",
                                       username="teraUser", 
                                       password="TeraUser1", 
                                       schema="db");

proc cas;
     session mysess;
     action save / caslib="clibTera" name="my2016Cars"
        table={caslib="clibTera" name="mycars"
                   vars={“make”}
                   where=”year = 2016”};
run;

I will add the link to the paper when it is published.

Best wishes,

Jeff

Re: Run JDBC Query

BrunoMueller — Mon, 04 Mar 2019 13:52:35 GMT

Does the example you provided do what you want?

The LOAD statement as used will load all the data into the CAS table.

Do you want to run a SQL query and then store the result of it in a CAS table? If yes I suggest you have a look at how to use Proc FEDSQL with CAS.

Re: Run JDBC Query

danielziv — Sun, 10 Mar 2019 12:48:50 GMT

Thanks @JBailey

Unfortunately, this suggestion did not solve our issue... we get an error of "catalog name not found" with everything we try...

Re: Run JDBC Query

JBailey — Tue, 12 Mar 2019 11:26:24 GMT

Hi @danielziv

This entry was marked as solved because I provided a working example. The communities manager marks completed discussions as solved because it helps people who land on the page from Google. Plus, and this is huge, SAS Communities members prefer that we keep discussions to a single topic.

Create another discussion showing the code you are using, and the error you are receiving, and I will take a look at it. Add my alias to it so that I am notified that the new topic is there.

Best wishes,

Jeff