|
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 |
|
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. Well I meant both of them, perhaps I should have stated that also. What kind of needs are you referring to? What I have heard is that jConnect should not be as reliable as SQL Anywhere JDBC Driver, but I have no experience of both of these so I can not tell if this is a correct statement. I thought you could use java and odbc together, my misstake then.
(22 Oct '13, 03:35)
M G
Replies hidden
1
The SQL Anywhere JDBC driver is usually the recommended JDBC driver when connecting to SQL Anywhere; however, jConnect does have certain benefits. For example, as pointed out by the answer below, jConnect is easier to deploy. Also, jConnect is pure JAVA so if you are using a JAVA VM that does not support JNI, jConnect may be your only option. In addition, if your application needs to connect to both ASE and SQL Anywhere, then jConnect might be your better bet since it will force SQL Anywhere to behave more like ASE. In terms of performance, the SQL Anywhere JDBC Driver outperforms jConnect in a large number of scenarios when connected to SQL Anywhere; however, we have observed certain situations where jConnect will perform better than the SQL Anywhere JDBC Driver. In general though, we always recommend that you start with the SQL Anywhere JDBC Driver if possible and then consider jConnect if you run into issues or if your application has other needs (like the ones I just mentioned.) As far as C/C++ versus JAVA goes. Pure ODBC applications in general are written in C/C++. If you write your application in JAVA and want to use the SQL Anywhere ODBC Driver under the covers, then you would need to use a JDBC-ODBC bridge. It should be noted that although the SQL Anywhere JDBC Driver (sajdbc.jar and sajdbc4.jar) is not pure JAVA, it is nevertheless a Type-2 JAVA and not to be considered a JDBC-ODBC bridge. On the other hand, the old deprecated iAnywhere JDBC Driver (jodbc.jar and jodbc4.jar) is a Type-1 driver and can be considered a JDBC-ODBC bridge. Hence, the SQL Anywhere JDBC Driver does not require the SQL Anywhere ODBC Driver and will not attempt to load it while the old iAnywhere JDBC Driver does require the SQL Anywhere ODBC Driver and must load it in order to successfully connect to SQL Anywhere.
(22 Oct '13, 13:27)
Karim Khamis
|
|
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. |
