SQL Anywhere database - connecting to sybase with JDBC drivers

Author

connecting to sybase with JDBC drivers

David Gunzburger

2005-08-24, 9:23 am

I have a Java application that connects to SQL Anywhere 9.0.2.3044
database.

I've tried 3 different database drivers to connect to the database,
JConnect (5.5,6.0), iAnywhere JDBC, and sun jdbc odbc.

Both the Jconnect and sun jdbc drivers run correctly. But, since the
database contains Unicode (UTF-8) data, the sun jdbc driver does not
correctly disply the data.

This leaves me with the option of using JConnect or iAnywhere JDBC
driver. From what I've read, everybody tends to recommend the iAnywhere
driver for many reasons (up to 4x performance sounds very nice). I
cannot get the iAnywhere drivers to work correctly though.

Looking at the logs, it seems to be failing with executing prepared
statements. Is there a known issue or limitation concerning prepared
statements?

I'm connecting to the database with the following connection string:

Class.forName("ianywhere.ml.jdbcodbc.IDriver");
con = DriverManager.getConnection(" jdbc:odbc:dsn=sybase
DB");

Thanks,
-Dave

Greg Fenton

2005-08-25, 3:31 am

David Gunzburger wrote:
>
> Looking at the logs, it seems to be failing with executing prepared
> statements. Is there a known issue or limitation concerning prepared
> statements?
>

Can you show us the code and the stack trace associated with this failure?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

David Gunzburger

2005-08-25, 9:23 am

The apache stdout log shows:
> java.sql.SQLException: Not implemented
> at ianywhere.ml.jdbcodbc.IStatement. executeBatch(IStatem
ent.java:164)
> at WebSurvey.DataStore.DataManager. writeAnswer(DataMana
ger.java:100)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at org.apache.catalina.core. ApplicationFilterCha
in. internalDoFilter(App
licationFilterChain.java:252)
> at org.apache.catalina.core. ApplicationFilterCha
in. doFilter(Application
FilterChain.java:173)
> at org.apache.catalina.core. StandardWrapperValve
. invoke(StandardWrapp
erValve.java:213)
> at org.apache.catalina.core. StandardContextValve
. invoke(StandardConte
xtValve.java:178)
> at org.apache.catalina.core.StandardHostValve. invoke(StandardHostV
alve.java:126)
> at org.apache.catalina.valves.ErrorReportValve. invoke(ErrorReportVa
lve.java:105)
> at org.apache.catalina.core.StandardEngineValve. invoke(StandardEngin
eValve.java:107)
> at org.apache.catalina.connector.CoyoteAdapter. service(CoyoteAdapte
r.java:148)
> at org.apache.coyote.http11.Http11Processor. process(Http11Proces
sor.java:856)
> at org.apache.coyote.http11. Http11Protocol$Http1
1ConnectionHandler. processConnection(Ht
tp11Protocol.java:744)
> at org.apache.tomcat.util.net.PoolTcpEndpoint. processSocket(PoolTc
pEndpoint.java:527)
> at org.apache.tomcat.util.net. LeaderFollowerWorker
Thread. runIt(LeaderFollower
WorkerThread.java:80)
> at org.apache.tomcat.util.threads. ThreadPool$ControlRu
nnable.run(ThreadPool.java:684)
> at java.lang.Thread.run(Unknown Source)

The java code looks similar to what follows:
//--------------
sql = "INSERT INTO xxx (a,b,c) VALUES (?,?,?)"
con. setAutoCommit(false)
;
PreparedStatement stmt = con. prepareStatement(sql
);

stmt.setString(1,value);
stmt.setInt(2,value);
stmt.setInt(3,value);

int [] Counts = stmt.executeBatch(); //Line 100 in writeAnswer()
//---------------

Hope this maybe can shed some light. Also, just to give a bit more
info, we setup the database to start up as a service when the computer
starts up.

thanks,
-dave

Greg Fenton wrote:
> David Gunzburger wrote:
>
>
> Can you show us the code and the stack trace associated with this failure?
>
> greg.fenton

Greg Fenton

2005-08-26, 3:23 am

David Gunzburger wrote:[color=darkred
]
> The apache stdout log shows:
>

Hmmm....well, I cannot duplicate the problem with a test script I mocked
up. Can you try the following and see if you still get the "not
implemented" exception??

To build, simply run "build.sh".
To run, simply run "run.sh"

If you are testing on MS-Windows, rename the files to ".bat" and replace
"$ASANY9" in the scripts with "%ASANY9%".

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

David Gunzburger

2005-08-29, 11:23 am

Greg,
I went ahead and ran the scripts on a WinXP environment. Here is the output:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\test>build

C:\test>javac -classpath ".;C:\Program Files\Sybase\SQL Anywhere 9/java/jodbc.jar" TestBatch.java

C:\test>run

C:\test>java -cp ".;C:\Program Files\Sybase\SQL Anywhere 9/java/jodbc.jar" TestB
atch "C:\Program Files\Sybase\SQL Anywhere 9/asademo.db"

Successfully connected!
java.sql.SQLException: Not implemented
at ianywhere.ml.jdbcodbc.IStatement. executeBatch(IStatem
ent.java:164)
at TestBatch.main(TestBatch.java:63)

URL is & #91;jdbc:odbc:driver
=Adaptive Server Anywhere 9. 0;uid=DBA;pwd=SQL;db
f=C:\Pro
gram Files\Sybase\SQL Anywhere 9/asademo.db]

C:\test>

Looks like I'm getting a similar error message. Let me know if you need additional information.
Thanks,
-Dave

Greg Fenton wrote:
> David Gunzburger wrote:
>
>
>
>
> Hmmm....well, I cannot duplicate the problem with a test script I mocked
> up. Can you try the following and see if you still get the "not
> implemented" exception??
>
> To build, simply run "build.sh".
> To run, simply run "run.sh"
>
> If you are testing on MS-Windows, rename the files to ".bat" and replace
> "$ASANY9" in the scripts with "%ASANY9%".
>
> greg.fenton
>
>
> ------------------------------------------------------------------------
>
> javac -classpath ".;$ASANY9/java/jodbc.jar" TestBatch.java
>
>
> ------------------------------------------------------------------------
>
> java -cp ".;$ASANY9/java/jodbc.jar" TestBatch $ASANY9/asademo.db

Greg Fenton

2005-08-29, 11:23 am

David Gunzburger wrote:
>
> Successfully connected!
> java.sql.SQLException: Not implemented
> at
> ianywhere.ml.jdbcodbc.IStatement. executeBatch(IStatem
ent.java:164)
> at TestBatch.main(TestBatch.java:63)
>

Can you try a newer EBF? I'm running 9.0.2.3137.

From the README of 3137, I see:

============(Build #3119 - Engineering Case #387869)============
====

When executing a batch using the PreparedStatement object, it is proper
practice to use the PreparedStatement.executeUpdate() method to execute
the batch and retrieve the single update count associated with it.
However, applications sometimes (incorrectly) use the
preparedStatement.executeBatch() method instead. The iAnywhere JDBC
driver has now been modified to support the
preparedStatement.executeBatch() method in this manner.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

David Gunzburger

2005-08-30, 11:25 am

Thanks! Upgrading to a newer EBF appeared to fix the problem.

Would you recommend using EBF 3137 or 3169? I downloaded the 3169 EBF a couple
weeks ago, but it no longer on the Sybase website for download. This is for a
Windows x86 machine.

Thanks again,
-Dave

Greg Fenton wrote:
> David Gunzburger wrote:
>
>
> Can you try a newer EBF? I'm running 9.0.2.3137.
>
> From the README of 3137, I see:
>
> ============(Build #3119 - Engineering Case #387869)============
====
>
> When executing a batch using the PreparedStatement object, it is proper
> practice to use the PreparedStatement.executeUpdate() method to execute
> the batch and retrieve the single update count associated with it.
> However, applications sometimes (incorrectly) use the
> preparedStatement.executeBatch() method instead. The iAnywhere JDBC
> driver has now been modified to support the
> preparedStatement.executeBatch() method in this manner.
>
>
> Hope this helps,
> greg.fenton

Greg Fenton

2005-08-30, 11:25 am

David Gunzburger wrote:
> Would you recommend using EBF 3137 or 3169? I downloaded the 3169 EBF a
> couple weeks ago, but it no longer on the Sybase website for download.
> This is for a Windows x86 machine.
>

3169 has been pulled, so I certainly would not suggest using it if
you've got it. I've been using 3137 for quite some time and have not
had any issues with it myself. There is a new EBF coming down the pipe
to replace 3169, but there is not ETA for it.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links