Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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!

asked 28 Sep '23, 03:33

jonas's gravatar image

jonas
31116
accept rate: 0%

edited 28 Sep '23, 04:17

Volker%20Barth's gravatar image

Volker Barth
40.4k363552824


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...

permanent link

answered 28 Sep '23, 04:27

Volker%20Barth's gravatar image

Volker Barth
40.4k363552824
accept rate: 34%

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.

(28 Sep '23, 05:20) jonas
Replies hidden

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?

(28 Sep '23, 05:40) Volker Barth

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!

permanent link

answered 28 Sep '23, 08:03

jonas's gravatar image

jonas
31116
accept rate: 0%

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

(28 Sep '23, 08:14) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×248
×78
×49

question asked: 28 Sep '23, 03:33

question was seen: 597 times

last updated: 28 Sep '23, 08:14