Run JDBC Query

[danielziv]

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;

[JBailey · Accepted solution]

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

[danielziv]

Thanks @JBailey 

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

[JBailey]

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

[BrunoMueller]

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.