topic Re: How can I use SAS 9.4 jdbc driver in the Spring boot application in Developers

How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Tue, 28 May 2019 17:31:01 GMT

Hi,

I am building a Spring boot web application using SAS as database.

In the application.properties file, I use:

spring.datasource.url=jdbc:sasiom://link:port
spring.datasource.username=****
spring.datasource.password=*****
spring.datasource.driver-class-name=com.sas.rio.MVADriver

After I run the application on Tomcat, it will throw:

Failed to bind properties under '' to com.zaxxer.hikari.HikariDataSource:

Property: driverclassname
Value: com.sas.rio.MVADriver
Origin: "driverClassName" from property source "source"
Reason: Unable to set value for property driver-class-name

Action:

Update your application's configuration

All the jdbc driver jar files are included in the class path. Can anyone give me some sample codes to create the spring datasource with SAS 9.4?

Thanks

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Thu, 30 May 2019 21:05:28 GMT

Make sure you have all the correct libraries in your classpath:

log4j.jar
sas.core.jar
sas.security.sspi.jar
sas.svc.connection.jar

In your application.properties

spring.datasource.url=jdbc:sasiom://workspace.server.hostname.com:8591
spring.datasource.username=myusername
spring.datasource.password=mypassword
spring.datasource.driver-class-name=com.sas.rio.MVADriver

DemoApplication.java

package com.github.friedegg.demo;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@SpringBootApplication
public class DemoApplication {

  public static void main(String[] args) {
    SpringApplication.run(DemoApplication.class, args);
  }

  @Bean
  public CommandLineRunner commandLineRunner(ApplicationContext ctx) {
    return args -> {
      DataSource ds = (DataSource)ctx.getBean("dataSource");
      JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
      String SQL = "select * from sashelp.class";
      List<Student> students = jdbcTemplate.query(SQL, new SashelpClassMapper());
      for (Student student : students) {
        System.out.printf("%s is %d years old.%n", student.name, student.age);
      }
    };
  }

  class Student {
    private String name;
    private Integer age;

    public Integer getAge() {
      return age;
    }

    public void setAge(Integer age) {
      this.age = age;
    }

    public String getName() {
      return name;
    }

    public void setName(String name) {
      this.name = name;
    }
  }

  class SashelpClassMapper implements RowMapper<Student> {
    public Student mapRow(ResultSet rs, int rn) throws SQLException {
      Student student = new Student();
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      return student;
    }
  }
}

Console Output:

Alfred   is 14 years old.
Alice    is 13 years old.
Barbara  is 13 years old.
Carol    is 14 years old.
Henry    is 14 years old.
James    is 12 years old.
Jane     is 12 years old.
Janet    is 15 years old.
Jeffrey  is 13 years old.
John     is 12 years old.
Joyce    is 11 years old.
Judy     is 14 years old.
Louise   is 12 years old.
Mary     is 15 years old.
Philip   is 16 years old.
Robert   is 12 years old.
Ronald   is 15 years old.
Thomas   is 11 years old.
William  is 15 years old.

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Thu, 30 May 2019 22:58:32 GMT

@FriedEgg Thanks for your reply. After I add log4j.jar, the compiling error is solved. That is great. Have you ever tried to use ORM like hibernate and mybatis with SAS? When I use mybatis-spring-boot-starter, it will throw the following errors:

Driver does not support get/set network timeout for connections. (com.sas.rio.MVAConnection.getNetworkTimeout()I)

 

com.sas.rio.MVASQLExceptionUnsupported: Method not supported.

at com.sas.rio.MVAStatement.setQueryTimeout(MVAStatement.java:508) ~[connection-1.jar:904300.0.0.20150204190000_v940m3]

 

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setQueryTimeout(HikariProxyPreparedStatement.java) [HikariCP-2.7.9.jar:na]

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Wed, 05 Jun 2019 21:19:20 GMT

It is definitely possible, but it's important to note that the JDBC driver isn't exactly... complete... (by which, I mean, a number of features that other databases more commonly used in this context are missing, such as the various timeouts)

The biggest issue you're going to have with something like mybatis is that automapping uses the method ResultSetMetadata.getColumnLabel, which, in SAS returns the column label, naturally. But in general it returns the column name alias. This difference in behavior causes mybatis to fail and this can be corrected by setting the configuration property use-column-label=false.

mybatis.configuration.use-column-label=false

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Wed, 05 Jun 2019 20:43:12 GMT

Hi FriedEgg,

Thanks for your reply. I have updated the setting. It still returns the errors:

Driver does not support get/set network timeout for connections. (com.sas.rio.MVAConnection.getNetworkTimeout()I)

Do you have any sample project I can check to set up the environment?

Thanks

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Wed, 05 Jun 2019 21:20:51 GMT

This shows up as a info line in the log, to me. Possibly a difference in our versions of SAS (I am using 9.4m6).

https://github.com/FriedEgg/SAS-JDBC-Spring-Boot-Example

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Thu, 20 Jun 2019 17:38:30 GMT

Hi,

Thanks. It works.

I have another question. How can I assign the SAS library if I use mybatis? Previously, we use sasLanguage to submit statements. But there is no way to do the same thing when using mybatis. Because we are using sas share server, we need to assign the library.

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Mon, 24 Jun 2019 23:11:59 GMT

You have three better options (in order of my opinion):

1) Define the libraries in metadata

2) Define the libraries in the jdbc connection. Something like the following

3) Put the libname into the application server autoexec

spring.datasource.connectionProperties: librefs="appdev 'C:\foo\bar\data'"

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Thu, 27 Jun 2019 20:45:14 GMT

Hi, Thanks for your reply. We use the third way to connect to the SAS share server. The select query works fine. But it will throw error when I do the insert query. Do you know why the method is not supported? Thanks.

package com.demo.dao;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import com.demo.model.User;

@Service
@Mapper
public interface UserDAO {
	String TABLE_NAME = " sc.test ";
	String INSERT_FIELDS = " age, name, gender ";

	@Select({ "select * ", " from ", TABLE_NAME })
	List<User> getAllUsers();

	@Insert({ "insert into ", TABLE_NAME, "(", INSERT_FIELDS,
			") values (#{age},#{name},#{gender})" })
	void addUser(User user);
}

### Error updating database.  Cause: com.sas.rio.MVASQLExceptionUnsupported: Method not supported.
### SQL: insert into   sc.test  (  age, name, gender  ) values (?,?,?)
### Cause: com.sas.rio.MVASQLExceptionUnsupported: Method not supported.
; ]; Method not supported.; nested exception is com.sas.rio.MVASQLExceptionUnsupported: Method not supported.] with root cause

com.sas.rio.MVASQLExceptionUnsupported: Method not supported.
	at com.sas.rio.MVAConnection.prepareStatement(MVAConnection.java:1025) ~[connection-1.jar:904300.0.0.20150204190000_v940m3]
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:325) ~[HikariCP-2.7.9.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-2.7.9.jar:na]

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Wed, 10 Jul 2019 18:00:41 GMT

Can anyone from SAS help answer this question? Thanks.

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Fri, 12 Jul 2019 15:05:53 GMT

No thoughts immediately come to mind as to why your insert would have issues, but I do want to comment on you driver choice. Why use the MVA driver and define a library for SAS Share instead of using the SAS Share driver and connecting to that service directly?

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Fri, 12 Jul 2019 15:47:51 GMT

Because SAS share server is a bit slow for us, we want to connect to the work space server and only use share server for the queries which will modify the tables.

Can you add the insert query example in your sample project? It would be really helpful for us. Thanks.

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

SASEdmonton — Mon, 16 Sep 2019 23:00:32 GMT

We still cannot get the insert query work. Can any SAS experts help us with this?

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Tue, 24 Sep 2019 22:35:15 GMT

I updated the example on GitHub to include an insert. Good luck.

https://github.com/FriedEgg/SAS-JDBC-Spring-Boot-Example