Error connecting remotely using sajdbc4.jar

[Former Member]

I have installed a new instance of SQL Anywhere on Windows 7-64 (Developer version, 12.1.3457) and am trying to connect a remote Quantum DB (also Windows 7-64) under Eclipse using the new sajdbc4.jar. I am getting a hang whenever I try and connect. At first I was getting an error that dbjdbc12.dll could not be found in the system path, so I copied dbjdbc12.dll directly to my Windows/System32 folder.

Now whenever I try and establish the connection, it just hangs, Eclipse crashes. The only thing I can think of is that there seems to be some problem loading the dll. I have tried regsvr32 on the dll, no effect. Rebooted the machine, no difference, etc. etc. I have tried using the following connection URLs:

jdbc:sqlanywhere:DBN=xxxxx;ServerName=xxxxx;LINKS=tcpip(host=xxxxxxxx;port=2638)
jdbc:sqlanywhere:SERVER=xxxxxx;DBN=xxxxx;host=adv76408;port=2638)

...and several other variations of the above. No luck with any of it.

I have confirmed the server is actually listening, as I am able to use the jConnect jconn3 driver to establish the connection, but so far have had no luck whatsoever in getting the connection even "out of the box" using the sajdbc4 connection method, which I really would like to use.

Note, I also tried running Dependency Walker on the dll and came up with a dependency not found for IEShim.dll, which I located and also placed in the system path, no help there either.

Anyone out there have any experience with this or can give me any clues where I might be going wrong? In the end, we have a java application that makes connections to various different RDBMS targets, and would like to use a different driver for ASE and SQL Anywhere for the connections, so I really do not want to go back to using jconn3 or jconn4 for the connections.

Oh, for more info, I am using Java 1.6 update 20 (and have tried other update versions as well with no difference).

Thanks in advance!

[jeff_albion]

I have just tested a new install of QuantumDB ( http://quantum.sourceforge.net/ ), with the latest GEF ( http://www.eclipse.org/gef/ ), in Eclipse Juno SR1 ( 20120920-0800 ) against SA 12.0.1.3867 and sajdbc4.jar, against the 'demo' database and was able to connect without issue (and returned the sidebar of the schema successfully).

If you add "-z -o console.txt" to the SQL Anywhere database server start-up options, do you ever see a connect request from Eclipse in your set up?

[Former Member]

Thank you, Jeff. Did you try this with your database remote to the Eclipse? I tried the sample java program that I got from http://dcx.sybase.com/1200/en/dbprogramming/connecting-establishing-jdbc.html.

What is interesting is that the program runs locally on the machine where the database is installed, but when the connection is attempted remotely, it hangs as soon as java calls the dll, and it looks like java is looping (uses 12.5% of CPU in an 8-core machine).

The remote database does not see an incoming connection.

[jeff_albion]

Yes, I had tried my initial attempt locally but also did not see a problem when I just went back now and tried a remote server over the network.

Are you sure that you can reach the database server on the IP/Port, over a TCP connection? Is there a firewall in the way, perhaps?

Can you try 'Interactive SQL' just to verify that you can successfully log in to the server with those credentials outside of Eclipse?

[Former Member]

I have no problem reaching the database using another method. I verified in Ecplise I could reach it remotely using jconn3 and jconn4 from jConnect, so there is no firewall problem.

I did, however, just download and install EBF 3867, as I saw a lot of jdbc issues addressed in this EBF. I copied over the new dbjdbc12.dll and sajdbc4.jar files to the remote computer and tried the test program again. It gets further :-), now I see:

Unexpected exception : java.sql.SQLException: Invalid ODBC handle, sqlstate = HY000

...so this brings me to the most basic point:

Assuming I want to make a remote connection to the demo database using sajdbc4, what exactly should my connection string look like? I am wondering if my problem now is not just that my connection string has something the driver does not like.

[jeff_albion]

I did, however, just download and install EBF 3867, as I saw a lot of jdbc issues addressed in this EBF. I copied over the new dbjdbc12.dll and sajdbc4.jar files to the remote computer and tried the test program again.

Which dbjdbc12.dll file did you actually copy? The \\bin64 version or the \\bin32 version? Also, which bitness of Eclipse are you running - 32-bit or 64-bit? (this also implies which version of the JRE you are running to make this combination compatible).

[Former Member]

I copied over the 64 bit version of the dll from bin64 (bin32 does not even have one), and am using 64 bit JRE and 64 bit Eclipse. But Eclipse seems not to be the problem as I also cannot get a sample java program (used the sample JDBCConnect.java found in UsersPublicDocumentsSQL Anywhere 12SamplesSQLAnywhereJDBC as my staring point) to establish a remote connection. It DOES work locally, but not remotely.

Can you provide a sample remote connection string? It now appears to me, since using the dll from the patch, when running the sample program in a debugger, it fails at the point it reaches the connection string and never tries the connection. To try to connect to the demo12 database I am using:

jdbc:sqlanywhere:uid=DBA;pwd=sql;eng=demo12;LINKS=tcpip(host=adv76408;port=2638)

I have also tried it with a database I created called Adabas as follows:

jdbc:sqlanywhere:uid=DBA;pwd=sql;DBN=Adabas;ServerName=Adabas;LINKS=tcpip(host=adv76408;port=2638)

jdbc:sqlanywhere:uid=DBA;pwd=sql;DBN=Adabas;ServerName=Adabas;host=adv76408;port=2638

jdbc:sqlanywhere:uid=DBA;pwd=sql;DBN=Adabas;SERVER=Adabas;host=adv76408;port=2638

None of these work. I only bring up either the demo12 database or my database at any one time as network server, so they are listening on 2638 when started. I can confirm the port is listening and the target reachable by other means (used jconn3 and jconn4 drivers from Sybase jConnect). But I cannot get even the java sample I referenced above to connect. I have tried running the java program from several different machines, no luck.

[jeff_albion]

I also cannot get a sample java program (used the sample JDBCConnect.java found in Users\\Public\\Documents\\SQL Anywhere 12\\Samples\\SQLAnywhere\\JDBC as my staring point) to establish a remote connection. It DOES work locally, but not remotely.

This sounds odd - does this mean you only see the 'Invalid ODBC Handle' error message when you try adding LINKS=tcpip() to the connection strings? Or it just 'hangs' and never connects in this scenario now?

Your sample connection strings look okay - I used the first format you posted in my test.

[jeff_albion · Accepted solution]

After some playing with the DLL files, I have now figured this situation out... not on my production laptop. 🙂

The reason both yourself and Jonathan are seeing the "Invalid ODBC Handle" message in the 'deployed' JDBC scenario is because while the JDBC JAR driver can find the 'dbjdbc12.dll' native library in the path, the native driver cannot locate the additional support DLLs that go along with this file, that are mentioned in the documentation. Specifically, the dblg[xx]12.dll file is missing - but dbicu12.dll, and dbicudt12.dll are also required for the proper operation of the driver.

It should be mentioned that if you are looking at these DLLs in a tool like 'Dependency Walker', you won't see the explicit static links to notice that these are required - they are dynamically loaded at runtime.

Once all native libraries were present, I was able to connect to servers without issue, on the latest JDBC build - I didn't go back and try earlier versions, but I assume all of these situations were just improper deployments of the native DLLs.

---

I will see if we can improve the error message we throw in this instance to make it more obvious that we are missing the language file DLL rather than 'Invalid ODBC Handle'. Thank you for the bug report.

[Former Member · Accepted solution]

Thank you for the answer. I have to admit having trouble finding this in the doc, but I was "under the gun" when working on this. Also sorry for the late response, but I seem to not have received an email that the post was answered.

Thank you for investigating and for the solution.

[Former Member]

The latest release of SQLAnywhere - 12.0.1.3853 - contains a bug in the native code for the type-2 JDBC driver (sajdbc4.jar). The bug appears to be a native code linking problem. I get the same message: "Invalid ODBC handle" whenever I try to run an application from inside Eclipse. It's also new to this patch - as it just started happening right after I installed it.

I've attempted to trace the problem down, and it appears to be a problem where a 64 bit binary is linked to a 32 bit binary. I'm not certain that's the problem, though, as it seems to work fine from the command line. If I find anything more, I'll post it here.

Two potential workarounds. First, you can run the code from the command line. If you load the proper lib64 & lib32 directories in to your PATH, or run the sa_config.sh (from UNIX/Mac), you can run an executable Java or JAR file from the command line. Second, you can use an older sajdbc4.jar file and lib64 directory from a previous release. Just put them in a separate place, and point to them separately. I'm using both workarounds successfully from my machine.

[Former Member]

Thank you for the answer. Good to know I am not the only person that has hit this error. Interestingly enough though, I have tried to run an executable java sample program from the command line, and still get the same error. But on the "client" machine where I am running it, I only have copied over two files, sajdbc4.jar and dbjdbc12.dll (64 bit version -- seems the SQl Anywhere distro does not even have a 32 bit version in the lib32 directory). I am on Windows (for now), not UNIX, and am looking to advise any customers we have who may be using SQL Anywhere as a target database with an easy connection solution: tell them which jar files to use and provide sample example/default connection strings, etc.

It looks like this might be a dead end path as far as that is concerned, since the sajdbc4.jar is really a type 2 driver (name is misleading?) and requires the additional step of including dll's or UNIX library files in the libpath. Makes configuration potentially very nasty for them and we are looking for the easiest route. This definitely is not it.

Since I have determined along the way that the jconn3.jar and jconn4.jar files from jConnect work just fine to establish a connection to SQL Anywhere, we will most likely stay with that route and advise customers to use that option.

[jeff_albion]

I am not so sure this is a 'bug in the latest 12.0.1.3853 JDBC driver' - particularly since 12.0.1.3853 isn't a publicly available release: http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0 - 12.0.1.3851 is. 12.0.1.3867 was also more recently released.

Also since I cannot reproduce the same error message with the same software versions on my environment, we seem to have a fundamental disagreement about the behaviour and set up.

I will try installing Eclipse on a blank Windows 7 x64 VM, and will copy over the JARs manually like you are doing to see if I can reproduce the message in any combination of software (intentionally mixing 32-bit / 64-bit, etc.)

---

since the sajdbc4.jar is really a type 2 driver (name is misleading?)

No, since in previous versions there is also a 'sajdbc.jar', which is the JDBC 3.0 driver, where 'sajdbc4.jar' is a JDBC 4.0 driver - both are Type-2. The jConnect JARs are named similarly. (jconn3.jar / jconn4.jar).

Makes configuration potentially very nasty for them and we are looking for the easiest route. This definitely is not it.

Yes, the native library must be located on the system path / java.library.path, so if you're looking for a 'Java-only' (e.g. JDBC 'Type-4') solution, jConnect is the correct solution. However, we generally recommend the native ('Type-2') driver for performance reasons:

http://www.sybase.com/detail?id=1037304

http://sqlanywhere-forum.sap.com/questions/16254/javasqlsqlexception-jz0sj-metadata-accessor-informa...

[Former Member]

I will try installing Eclipse on a blank Windows 7 x64 VM, and will copy over the JARs manually like you are doing...

Jeff, just curious...did you ever try this?

[VolkerBarth]

@maestrox2: FYI you can "comment on a comment" (instead on the question/answer) by using the "Reply" button (the second-right one beneath each comment), thereby creating hierarchical comments... - just for information:)

[jeff_albion]

I did just now, and yes and I can now reproduce the error message on my installation. I am investigating further.