I would appreciate your opinions regarding odbc and jdbc, which of these do you rather use together with SQL Anywhere?
Have any of you noticed any differences between jdbc and odbc regarding efficiency/performance?
Would you say that one of them is more relable or error prone?
I would like to know both your good and bad experiences regarding odbc and jdbc together with SQL Anywhere
asked 21 Oct '13, 10:48
Your question is a good one; but it requires a bit of clarification. The first clarification I would suggest is to be more explicit regarding what you mean by "JDBC". Do you mean jConnect or the SQL Anywhere JDBC Driver? Both JDBC drivers can be used with SQL Anywhere; however, they are very different drivers and can make a huge difference based on the needs of the application.
Second, you need to acknowledge that ODBC involves writing your application in C/C++ while JDBC entails writing your application in JAVA. The two languages, although similar in many ways, vary significantly in other fundamental ways which have nothing to do with SQL Anywhere or any other DB system.
answered 21 Oct '13, 11:02
Danger Will Robinson!
By default, a new connection will call the sp_login_environment procedure:
ALTER PROCEDURE "dbo"."sp_login_environment"() begin if "connection_property"('CommProtocol') = 'TDS' then call "dbo"."sp_tsql_environment"() end if end
You can change that default via the login_procedure option but let's assume you don't (almost nobody does)...
The CommProtocol connection property is described thusly: "TDS for Sybase Open Client and jConnect connections, HTTP for HTTP connections, HTTPS for HTTPS connections, and CmdSeq for ODBC, embedded SQL, OLE DB, ADO.NET, and SQL Anywhere JDBC driver connections."
Open Client and jConnect (but ODBC or SQL Anywhere JDBC connections) then call one of the MOST EVIL CREATIONS of all time, the sp_tsql_environment() procedure:
ALTER PROCEDURE "dbo"."sp_tsql_environment"() begin if "db_property"('IQStore') = 'Off' then -- SQL Anywhere datastore set temporary option "close_on_endtrans" = 'OFF' end if; set temporary option "ansinull" = 'OFF'; set temporary option "tsql_variables" = 'ON'; set temporary option "ansi_blanks" = 'ON'; set temporary option "chained" = 'OFF'; set temporary option "quoted_identifier" = 'OFF'; set temporary option "allow_nulls_by_default" = 'OFF'; set temporary option "on_tsql_error" = 'CONTINUE'; set temporary option "isolation_level" = '1'; set temporary option "date_format" = 'YYYY-MM-DD'; set temporary option "timestamp_format" = 'YYYY-MM-DD HH:NN:SS.SSS'; set temporary option "time_format" = 'HH:NN:SS.SSS'; set temporary option "date_order" = 'MDY'; set temporary option "escape_character" = 'OFF' end
The BIG EVIL NEWS is this line of code...
set temporary option "chained" = 'OFF';
...which basically turns server-side auto-commit ON, something no modern database developer wants.
The other option settings are mostly crap, turning chained off is the big one.
So... if you use jConnect, beware!
For accessing sql anywhere databases from java you have basically two options:
The ODBC based drivers are to be preffered in any terms of performance, compatibility etc.
The advantage the jConnect has is the following: You just have to deploy the jconnect.jar file and you can connect to the database, no version hassle etc.
When you use the sajdbc.jar or jodbc.jar, then you also have to deploy the native libraries in the very same version/ebf as referenced in the jar file.
They don't have to match the server SA version, but the client library where the .jar is running must match the jar version. Otherwise you will receive a version error message and your application can't connect to the database at all.
We have used jConnect for a long time, but have switched over slowly project per project to the odbc based drivers with very good results.
answered 22 Oct '13, 06:39