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

[SASEdmonton]

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

[FriedEgg]

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.

[SASEdmonton]

@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]

[FriedEgg]

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

[SASEdmonton]

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

[FriedEgg]

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

[SASEdmonton]

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.

[FriedEgg]

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'"

[SASEdmonton]

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]

[SASEdmonton]

Can anyone from SAS help answer this question? Thanks.

[FriedEgg]

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?

[SASEdmonton]

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.

[SASEdmonton]

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

[FriedEgg]

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

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