Spring Boot and SQL Anywhere 17 Connection Error: "Invalid ODBC handle" (windows)

[former_SQLA_member1694995]

I'm trying to use Spring Boot to connect to SQL Anywhere 17, but I'm encountering an error regardless of the connection string I use in my application.yml configuration. Here is my configuration in application.yml:

spring:
  datasource:
    url:jdbc:sqlanywhere:UserID=username;Password=password;Host=ip:port;ServerName=top;DatabaseName=DBNAMe;
    driver-class-name: sap.jdbc4.sqlanywhere.IDriver

I have the necessary dbjdbc17.dll and sajdbc4.jar in my classpath and modules. However, when I run my application, I receive the following error:

java.sql.SQLException: Invalid ODBC handle
    at sap.jdbc4.sqlanywhere.IDriver.makeODBCConnection(Native Method) ~[sajdbc4.jar:na]
    at sap.jdbc4.sqlanywhere.IDriver.connect(IDriver.java:775) ~[sajdbc4.jar:na]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121) ~[HikariCP-5.0.1.jar:na]

I've tried various connection string formats, but I keep encountering this "Invalid ODBC handle" error. Can someone help me understand what might be causing this issue and how to resolve it?

Thank you for your assistance!

[VolkerBarth · Accepted solution]

I don't use Spring Boot, so can't comment on the correct configuration - but like to give a wild guess:

JDBC requires more files than you have listed, so you migt check whether these are all installed and available (particularly a language file):

JDBC Client Deployment.

Furthermore, SQL Anywhere generally uses environment variables to locate its components, too, see here...

[former_SQLA_member1694995 · Accepted solution]

Hi Volker Barth, Thanks a lot it was indeed those files i was missing. I've got further now it seems, i do still get an error: [SAP][JDBC Driver][SQL Anywhere]Login mode 'Integrated' not permitted by login_mode setting

Can i set the login_mode to Standard via the connection string? I only need to be able read a view from the db. Or will i need to ask the database admin for the Integrated setting?

Kind regards and thanks again.

[VolkerBarth · Accepted solution]

No, you cannot modify the login_mode via a connection string (and that would be dangerous...). You (or probably the db admin) need to modify the login_mode OPTION if a modification is required. (If, so, take note of the warnings for permanent changes...)

In your sample, you provide UserID and PWD connection parameters, so I don't understand the mentioned error message - IMHO the error "Login mode 'Integrated' not permitted by login_mode setting" does only appear when integrated logins are NOT allowed and your connection string (and optional SQLCONNECT environment variable) does NOT contain credentials, i.e. when there are no UserID/UID and Password/PWD entries...

---

If you connect directly (outside Spring Boot) to the database, what does "select connection_property('login_mode')" return?

[former_SQLA_member1694995]

Hi Volker,

Sorry my mistake I changed UserID to username without really realizing the difference. I've changed it back to UserID and now it works.

I had one more problem with the connection and that was hibernate not knowing the SQLanywhere Dialect. This error: Unable to determine Dialect for SQL Anywhere 17.0

For the people encoutering this problem. You can solve this using a bean like this:

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dataSource);
    em.setPackagesToScan("your.package");

    em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

    Properties jpaProperties = new Properties();
    jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect");
    em.setJpaProperties(jpaProperties);

    return em;
}

There might be a better solution but this worked for me.

Thanks for all the help!

[VolkerBarth]

I don't use Hibernate but there are official (albeit possibly outdated) SQL Anywhere dialects, and SQL Anywhere differs from your mentioned MS SQL Server in many aspects:

https://github.com/sqlanywhere/hibernate