SQL Anywhere JDBC on Tomcat 7 64-bit

[Former Member]

Hi all,

I am currently trying to do a simple test with:

• SQL Anywhere 12.0.1.3994 64-bit.
• Java J2SE SDK jdk-7u51-windows-x64.exe (latest v7.x) 64-bit.
• Apache Tomcat apache-tomcat-7.0.52.exe (latest v7.x) 64-bit.

on Windows 7 64-bit

I'm following: http://dcx.sybase.com/index.html#1201/en/dbprogramming/jdbc-url-driver.html

The error message I am getting is: SQLAnywhereAccessPoint: java.sql.SQLException: No suitable driver found for jdbc:ianywhere:dsn=MY_DSN

I have verified the DSN with dbdsn -ls.

I have placed: sajdbc4.jar and dbjdbc12.dll into C:Program FilesApache Software FoundationTomcat 7.0lib

I have added sajdbc4.jar to the Eclipse project Build Path.

When starting Tomcat, the dlls become locked.

I've tried:

conn = DriverManager.getConnection("jdbc:ianywhere:dsn=MSV_TEST_Central:uid=dba:pwd=sql");
conn = DriverManager.getConnection(driverConnectionURL, connDetails.getUsername(), 
connDetails.getPassword());

But I can't get it working.

Any ideas?

Thanks,

Shao

[VolkerBarth]

Is the SQL Anywhere 4 JDBC driver in the JVM's class path?

[Former Member]

Thanks Volker. I've added the CLASSPATH environment variable to point to the Apache Lib directory and restarted, but I have the same problem.

[Breck_Carter]

Please show us more of the code, including driverConnectionURL

[Former Member]

    public EnvironmentDetails performOverviewChecks(EnvironmentDetails envDetails) {

Connection conn = null; 
        Statement stmt = null;
        ResultSet rs = null;
        recordCount = 0;

::::Java
        connDetails = envDetails.getSQLAConnectionDetails();

envDetails.setSQLADatabaseUpFlag(false);

try { 
            ::::Java
            //Class.forName(connDetails.getDriverClassName());

if (log.isDebugEnabled()) log.debug("Connection 1: " + driverConnectionURL + connDetails.getUsername() + connDetails.getPassword());
            if (log.isDebugEnabled()) log.debug("Connection 2: " + driverConnectionURL + ":uid=" + connDetails.getUsername() + ":pwd=" + connDetails.getPassword());

conn = DriverManager.getConnection("jdbc:ianywhere:dsn=MSV_TEST_Central");

stmt = conn.createStatement();

SQL = "SELECT FIRST paramName FROM SystemParameter";

if (log.isDebugEnabled()) log.debug("SQL: " + SQL);

rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                envDetails.setSQLADatabaseUpFlag(true);

if (log.isDebugEnabled()) recordCount++;
            }

if (log.isDebugEnabled()) log.debug("   - " + recordCount + " record(s) found");

rs.close();
            rs = null;

stmt.close();
            stmt = null;

if (envDetails.getSQLADatabaseUpFlag()) {
                stmt = conn.createStatement();

recordCount = 0;
                SQL = "SELECT FIRST name, DATEFORMAT(ml_subscription.last_upload_time, 'dd/mm/yyyy hh:nn:ss') " +
                      "FROM ml_user JOIN ml_subscription ON (ml_user.user_id = ml_subscription.user_id) " +
                      "ORDER BY ml_subscription.last_upload_time DESC";

if (log.isDebugEnabled()) log.debug("SQL: " + SQL);

envDetails.setLastSuccessfulFrontendSync(null, null);

rs = stmt.executeQuery(SQL);
                while (rs.next()) {
                    envDetails.setLastSuccessfulFrontendSync(rs.getString(1), rs.getString(2));

if (log.isDebugEnabled()) {
                        log.debug("   > " + envDetails.getLastSuccessfulFrontendDataSyncOperative() + ", " + envDetails.getLastSuccessfulFrontendDataSyncDT());
                        recordCount++;
                    }
                }

if (log.isDebugEnabled()) log.debug("   - " + recordCount + " record(s) found");

rs.close();
                rs = null;

stmt.close();
                stmt = null;

}

conn.close();
            conn = null;
        } catch (Exception e) {
            log.error(e);
        } finally {
            try { if (rs != null)   { rs.close();   } } catch (Exception ie) { log.fatal(ie); } finally { rs = null;   }
            try { if (stmt != null) { stmt.close(); } } catch (Exception ie) { log.fatal(ie); } finally { stmt = null; }
            try { if (conn != null) { conn.close(); } } catch (Exception ie) { log.fatal(ie); } finally { conn = null; }
        }

SQL = null;
        return envDetails;
    }
    

[VolkerBarth]

Just as a hint:

You can add formatted code within comments simply by enclosing the code between a <PRE> and <\\PRE> tag pair ... - way simpler than masking these tags here within my comment:)

[Breck_Carter]

The code shows "jdbc:ianywhere:dsn=MSV_TEST_Central" which does not agree with the error message you report: "No suitable driver found for jdbc:ianywhere:dsn=MY_DSN"

It also does not agree with the Help topic you point to, which shows "jdbc:sqlanywhere:DSN=SQL Anywhere 12 Demo"

Please show us the exact error message you get for the code you show, and please show us the exact contents of the DSN as it existed when the error message was produced.

[Former Member]

Hi Breck, the MY_DSN is my fault. I started to substitute information for posting, but didn't see it through. The name of the DSN doesn't matter though and it is correct at MSV_TEST_Central and not confidential.

[Breck_Carter]

What about the second difference? ianywhere versus sqlanywhere?

[Former Member]

Error log:

DEBUG 17/03 13:07:50 [http-apr-8080-exec-7] SQLAnywhereAccessPoint: SQL Anywhere JDBC Driver Connection URL: jdbc:ianywhere:dsn=MSV_TEST_Central
DEBUG 17/03 13:07:50 [http-apr-8080-exec-7] SQLAnywhereAccessPoint: Connection 1: jdbc:ianywhere:dsn=MSV_TEST_Centraldbasql
DEBUG 17/03 13:07:50 [http-apr-8080-exec-7] SQLAnywhereAccessPoint: Connection 2: jdbc:ianywhere:dsn=MSV_TEST_Central:uid=dba:pwd=sql
ERROR 17/03 13:07:50 [http-apr-8080-exec-7] SQLAnywhereAccessPoint: java.sql.SQLException: No suitable driver found for jdbc:ianywhere:dsn=MSV_TEST_Central

[Former Member]

The Eclipse project runs only this like:

conn = DriverManager.getConnection("jdbc:ianywhere:dsn=MSV_TEST_Central");

[Former Member]

dbdsn -ls

SQL Anywhere Data Source Utility Version 12.0.1.3994
SQL Anywhere System Data Sources:
SQL Anywhere 12 CustDB
QAnywhere 12 Demo
SQL Anywhere 12 Demo
Servitor Mobile Dev
MSV_TEST_Central
MSV_TEST_Central_DBMLSYNC
MSV_TEST_Servitor

[Breck_Carter]

That's a list of names. Please show us the contents of the DSN.

Also... should "ianywhere" be "sqlanywhere" like it says in the Help topic?

[Former Member]

Changed to sqlanywhere. I must have made a mistake when subbing in the actual text rather than the driverConnectionURL.

DEBUG 17/03 13:37:32 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: SQL Anywhere JDBC Driver Connection URL: jdbc:ianywhere:dsn=MSV_TEST_Central
DEBUG 17/03 13:37:32 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: Connection 1: jdbc:ianywhere:dsn=MSV_TEST_Centraldbasql
DEBUG 17/03 13:37:32 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: Connection 2: jdbc:ianywhere:dsn=MSV_TEST_Central:uid=dba:pwd=sql
ERROR 17/03 13:37:32 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: java.sql.SQLException: No suitable driver found for jdbc:sqlanywhere:dsn=MSV_TEST_Central

[Former Member]

SQL Anywhere Data Source Utility Version 12.0.1.3994
MSV_TEST_Central:   UID=dba;PWD=sql;DBN=Central;DBF='C:\\Servitor Mobile\\Test\\Central\\db\\Central.db';ServerName=MSV_TEST_LT7868;START='dbsrv12.exe -x tcpip(HOST=10.44.118.6;PORT=6050)';ASTOP=NO;INT=NO

[VolkerBarth]

What does happen when you use a non-existing DSN name in the getConnection() call, say "MSV_TEST_X" ? Does it lead to the same error message? (In other words: Is the DSN and its contents entry actually read and used, or is the DSN not found somehow?)

And besides that: Is the tomcat server running as a service (and would require a system DSN in contrast to a user DSN)? Note: System DSNs are not shared between 32-bit and 64-bit ODBC, they have to be created for the according registry hive separately... - As you have listed the system DSNs with "dbdns -ls", is that the result of the 64-bit dbdsn.exe?

[Former Member]

Hi Volker. Yes, it seems to be the same.

DEBUG 17/03 13:48:26 [http-apr-8080-exec-9] SQLAnywhereAccessPoint: SQL Anywhere JDBC Driver Connection URL: jdbc:ianywhere:dsn=MSV_TEST_Central
DEBUG 17/03 13:48:26 [http-apr-8080-exec-9] SQLAnywhereAccessPoint: Connection 1: jdbc:ianywhere:dsn=MSV_TEST_Centraldbasql
DEBUG 17/03 13:48:26 [http-apr-8080-exec-9] SQLAnywhereAccessPoint: Connection 2: jdbc:ianywhere:dsn=MSV_TEST_Central:uid=dba:pwd=sql
ERROR 17/03 13:48:26 [http-apr-8080-exec-9] SQLAnywhereAccessPoint: java.sql.SQLException: No suitable driver found for jdbc:sqlanywhere:dsn=MSV_TEST_CentralXXXXX

[Former Member]

Also Tomcat 7 states this:

https://tomcat.apache.org/tomcat-7.0-doc/class-loader-howto.html

System — This class loader is normally initialized from the contents of the CLASSPATH environment variable. All such classes are visible to both Tomcat internal classes, and to web applications. However, the standard Tomcat startup scripts ($CATALINA_HOME/bin/catalina.sh or %CATALINA_HOME%bincatalina.bat) totally ignore the contents of the CLASSPATH environment variable itself, and instead build the System class loader from the following repositories:

Is that new on Tomcat 7? To ignore the CLASSPATH?

[VolkerBarth]

Then I would conclude the DSN is not really used. Does it exist as a 64-bit system DSN (listed with "%SQLANY12%\\bin64\\dbdsn -ls")?

What does happen when you omit the "DSN=..." and instead put in a normal connection string, say

conn = DriverManager.getConnection(
  "jdbc:ianywhere:UID=dba;PWD=sql;DBN=Central;DBF=C:\\Servitor Mobile\\Test\\Central\\db\\Central.db;ServerName=MSV_TEST_LT7868;START=dbsrv12.exe -x tcpip(HOST=10.44.118.6;PORT=6050);ASTOP=NO;INT=NO");

[VolkerBarth]

And just a further question: Is the database engine already running?

As you try to connect via TCP/IP, that does not allow to auto-start a database engine AFAIK...

Sorry, wrong statement, the TCPIP remark should not fit here as you do not include a LINKS connection parameter but a dbsrv12 -x command line option...

[Former Member]

DEBUG 17/03 14:06:04 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: SQL Anywhere JDBC Driver Connection URL: jdbc:ianywhere:dsn=MSV_TEST_Central
DEBUG 17/03 14:06:04 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: Connection 1: jdbc:ianywhere:dsn=MSV_TEST_Centraldbasql
DEBUG 17/03 14:06:04 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: Connection 2: jdbc:ianywhere:dsn=MSV_TEST_Central:uid=dba:pwd=sql
ERROR 17/03 14:06:04 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: java.sql.SQLException: No suitable driver found for jdbc:ianywhere:UID=dba;PWD=sql;DBN=Central;DBF=C:/Servitor Mobile/Test/Central/db/Central.db;ServerName=MSV_TEST_LT7868;START=dbsrv12.exe -x tcpip(HOST=10.44.118.6;PORT=6050);ASTOP=NO;INT=NO

[Former Member]

I did a dbdsn in the Bin64 directory and it shows.

In SQL Anywhere 12 everything we do is 64-bit.

[Former Member]

Oops - I need to retest and put sqlanywhere back in.

[Former Member]

DEBUG 17/03 14:08:37 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: SQL Anywhere JDBC Driver Connection URL: jdbc:ianywhere:dsn=MSV_TEST_Central
DEBUG 17/03 14:08:37 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: Connection 1: jdbc:ianywhere:dsn=MSV_TEST_Centraldbasql
DEBUG 17/03 14:08:37 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: Connection 2: jdbc:ianywhere:dsn=MSV_TEST_Central:uid=dba:pwd=sql
ERROR 17/03 14:08:37 [http-apr-8080-exec-10] SQLAnywhereAccessPoint: java.sql.SQLException: No suitable driver found for jdbc:sqlanywhere:UID=dba;PWD=sql;DBN=Central;DBF=C:/Servitor Mobile/Test/Central/db/Central.db;ServerName=MSV_TEST_LT7868;START=dbsrv12.exe -x tcpip(HOST=10.44.118.6;PORT=6050);ASTOP=NO;INT=NO

[Former Member]

Yes, the database is running.

I just want to double-check that anyone using SQL Anywhere sets up Tomcat as I have above. Basically, install all the software and put the various libraries in lib.

You can also put into WEB-INFlib of your own web app I believe.

However, the SQL Anywhere 12 documentation states to put the library in the CLASSPATH. I think it's just not picking up the library and possibly Tomcat ignores CLASSPATH anyway. But I can't find anything else in the documentation.

[chris_keating]

Have you tried copying the SQLAnywhere JDBC jar files into the $CATALINA_HOME/lib directory?

[Former Member]

Hi Chris,

Yes, that's where I put them first. Then when not working, I looked into WEB_INFlib of application.

Also looked at modifying catalina.properties: shared.loader=$CATALINA_HOME/shared/lib and creating a new directory for a test.

But I can't get any of those working.

I am working on the assumption that the error message means that it hasn't loaded/found the library.

Thanks.

[VolkerBarth]

FWIW, we do use Tomcat (however, not Tomcat 7) with SQL Anywhere (however, with JConnect), and we've put the jodbc.jar in the WEB-INF\\lib path. That works apparently. I can' tell whether this is the recommended approach, that's way beyond my expertise...

[Former Member]

Thanks Volker. Our original application was running on Tomcat 6 and Java J2SE 6. However, at some customer sites, it started failing. I think it's because they upgraded the version of Java. Rather than go into the ins and outs of it, I decided to upgrade all components to the latest and to 64-bit and test with the latest EBF from SQL Anywhere. I am no expert in the Tomcat/java world. I was hoping there were others out there that have upgraded also and know what the solution is. Thanks.

[VolkerBarth]

@Shao: Though I am no Tomcat expert, either, I think this is more a Tomcat/JDBC issue than related to SQL Anywhere. A quick search on Stack Overflow on "JDBC Tomcat WEB/Inf 7 not found" lists a bunch of according questions with different JDBC drivers. Possibly there is one answer that can help you further.

(That doesn't mean your question will stay unanswered here - it's just me who has no further suggestions...)

[chris_keating · Accepted solution]

There appears to be a bug in Tomcat which requires even JDBC4 drivers to need to register the driver. There is a similar discussion on this topic involving a mySQL JDBC4 driver.

Adding the following prior to the connection attempt should workaround the issue:

DriverManager.registerDriver( (Driver)
    Class.forName(
    "sybase.jdbc4.sqlanywhere.IDriver").newInstance()
);

[Former Member · Accepted solution]

Hi Chris, many thanks for that reply. I'll get that added and give it a go and report back! 🙂 Cheers, Shao

[Former Member · Accepted solution]

Hi Chris. Thanks for that. I can confirm that that works. I'll do a bit more testing, but it's looking good! 🙂 Cheers, Shao

[Former Member · Accepted solution]

Note that the driver is self registering so the Class.forName() should be sufficient without using registerDriver.

[Former Member]

Your URL header is incorrect. For sajdbc4.jar you need to use jdbc:sqlanywhere not jdbc:ianywhere. During the entire exchange above, Breck in particular has pointed to the sqlanywhere versus ianywhere distinction but all of you debug messages still say ianywhere. Please make sure you change the URL header to jdbc:sqlanywhere and properly rebuild your application.

[Former Member]

Hi Khamis. Sorry - a lot of the debug messages showing the connections get the driverConnectionURL variable which gets retrieved from the database which is wrong. But the code does use jdbc:sqlanywhere (as shown in the error) as I override it. I'll get it fixed in the database - I had changed it originally from it's original value of jdbc:odbc as from the Sybase docs it could be confused with something else.