Over the past several years, I have found your responses to jdbc
usage/driver related issues to be extremely helpful. I am sure that
you're very busy so I will make my question as brief as possible - the
reason I didn't post this to a newsgroup is that I don't want to hear
any more hype or links to benchmarks (since the vast majority of the
benchmarks don't test concurent/mixed-transaction environments). (I am
also posting a modified version of this message to some newsgroups, but
based on past responses - don't have too much hope that these will lead
to a resolution.) If the only way to answer this question would be to
have a phone conversation, my company would be more than happy to pay a
consulting-service fee for your time.
My situation:
My company has a enterprise level web-app that targets SQL Server. We
don't use J2EE - so we stick to a simple to administer web containers
(JRun,Tomcat). Recently, we have been getting worse and worse
performance from our jdbc driver (we use the free MS jdbc driver) -
things like strange transaction resource handling, chopy overall
performance, etc.. I have spent significant time tweaking it (following
various advice - many times yours - on newgroups... I can go into
details, but I don't want to take up your time). As the project's
architect, I need to do something about this problem - but various
high-level tunnings that I have done to the way we use connections with
this driver haven't significantly improved perfomance under normal
everyday load. (Our queries often span tables with millions of records,
and are relatively dynamic. We use a seperate pool of autocommit-off
connections for writes, and autocommit-on connecions for reads)
So, then, my question - in your expert opinion - what is the BEST
driver from SQL Server 2000 given the following needs:
Things I need it to have are solid jdbc 1.0, solid transaction
handling, decent concurrent load handling, stable implementation of
resource handling (i.e. auto closing result sets when parent statements
close, closing of result sets & statements when connections close) and
support for multiple open statements/result sets per connection.
Things I do not have a direct need for:
Connection Pooling (I keep my own pool of open connections), DataSource
support, distributed transactions or 2-phase commit support, RowSet
variants, jdbc 3.0 autokey generation, J2EE compliance. We do make use
of updatable result sets - but I don't care if it isn't supported since
I can still use MS driver for those, as they are mainly done it batch
jobs).
Things that are nice:
It would also be good to have a driver that can handle prep statement
caching on a driver level (vs connection level) although this isn't a
necessity, and if it is available - it would need to have a
configurable caching strategy (or have a way to be turned off :)
I very much appreciate your help in advance
Thanks
Gary Bogachkov
System Architect
Stericycle Direct Return
I have read similar posts to this, but I am still having problems.
I am trying to use connection pooling to connect to a local SQL Server 2005 database. I am running my application using MyEclipse Enterprise Workbench. I have verified that sqljdbc.jar resides in "WebRoot/WEB-INF/lib/"
Hi at all, i'm new here, your communitiy seems too much preparated. My question is simple... i must to connect via jdbc to a ms sql server 2000 database, but i'm in doubt to select which jdbc driver to use. your suggest over microsoft'drivers? i found on the internet the open source drivers jdts...what about them?
Hi,Could some please tell me whether Microsoft provides Type 2 and Type 4jdbc driver ? For Type 4 MS providescom.microsoft.jdbc.sqlserver.SQLServerDriver driver. What is thecofiguration required for type 2 driver and what driver class filesrequired ?Its very urgent to me please reply.Ajay
Dear All,I developed an web application which use MS SQL Server 2000. Iencounter the following SQLException "[Microsoft][SQLServer 2000Driver for JDBC]Error setting up static cursor cache". Did anyoneencounter this problem before? What does this exception mean and howto solve this problem?[Remarks: The web application work properly on development machine andthis machine but encounter this exception when move to anothermachine, so is this exception related to the machine?]Thank you for your attention.Yours faithfully,Benny
I'm trying to figure out if the SQL Server 2005 JDBC driver supports SSL at all. I've downloaded the driver documentation, looked at more web pages than I care to count, and even went as far as to take a stab at guessing at different driver property names in the hopes I might discover some undocumented feature. Each time I try, I come up dry.
The following exception shows up in my JBoss log:
2007-01-03 12:13:36,812 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The SQL Server login requires an SSL connection.)
I know I have things working properly on the SQL Server side, as both osql and Query Analyzer work fine, and packet sniffing yields no plain text information. I've also tried swapping in the open source JTDS driver, and once again, sniffing shows that the data between the two machines in encrypted.
Does anyone know if I'm just missing something completely obvious, or can someone state with absolute certainty that the driver does not support SSL? Any ideas on how to find out if there are plans to support this feature and what the timeline might be?
Do we have to use SQL server 2005 JDBC driver to connect the sql server 2005? I got sql server 2000 driver for JDBC, it works fine to connect the database. But when I use sql server migration assistant for oracle, i got an error saying: [sql server 2000 driver for JDBC] Error establishing socket. Do I have to download SQL server 2005 driver for JDBC or I miss something else?
Can any version of the SQL Server 2000 JDBC driver connect to SQL Server 2005 or do I have to install the new JDBC version 1.1 to connect to my 2005 instance?
I have one query which executes properly with mssql 2000 jdbc driver but fails with the 2005 driver.
The query creates a table, using insert exec method tries to get the data in the created table. But, executing this statement fails producing exception com.microsoft.sqlserver.jdbc.SQLServerException: The statement did no t return a result set.
the command is as: insert into #temp exec ('dbcc inputbuffer (' + @spid + ')')
I tried executing only the dbcc command on the db with query analyzer, it returns the results correctly, even with the same driver if I execute the dbcc query it runs fine and returns the resultset. But, only when I use it with insert exec it fails.
Does anyone know why this error occurs and what can be work-around.
Tengo instalado Microsoft Windows 2003 Server Standard Edition en un servidor IBM xSeries 220, estoy tratando de instalar IBM Director 5.10 y no ha sido posible hacer que se conecte con la base de datos.
Esto ocurre cuando intento configurar el Servidor SQL en la fase final de la instalación de IBM Director.
Gracias por su ayuda, ¿puede alguien darme alguna sugerencia?
We have a problem with SQL Server 2005 v1.2 JDBC driver, where the application is failing with java.lang.OutOfMemoryError. We have a database of around 50 milion rows and our application has to query that database and operate on the large dataset. However, we are not facing this problem with databases having 1 million rows.
Database is running fine even after the java.lang.OutOfMemoryError in the application side. However, the application is not responding after this error.
We are facing this problem with SQL Server 2005 Standard Edition as well as Enterprise Edition. Any help or suggestions will be highly appreciated.
Recently I downloaded version 1.2 release of the Microsoft SQL Server 2005 JDBC Driver. Thanks a ton to Microsoft for providing many new features such as Adaptive Buffering , SSL Encryption , Tightly Coupled XA Transactions etc.
I am mainly concerned about SSL part of it and would be grateful if you can solve my queries or forward it to app- ropriate forum.
1 ) I tried connecting to SSL enabled SQL Server 2005 through a sample program provided by Microsoft along with this package ( Version 1.2 ) and I succeeded whereas in previous versions I use to get an exception as "The SQL Server login requires an SSL connection." My connection string is as follows "jdbcqlserver://Ip adress of dataserver machine:1433;databaseName=master;user=sa;password=sapassword" .
Ideally we should only get an SSL connection when we request it else an error message should be thrown informing the user that dataserver requires an SSL connection. This would help in choosing between type of communication a user needs with dataserver.
The scenario now is like forcing a user to have SSL communication if dataserver forces it. A flag may be added as ssl should be mandatory or auto .
2 ) Current driver 1.2 is not capable of communicating with MS SQL Server 2000 both in case of SSL and Non - SSL cases , The error which we receive in both cases is :
Oct 18, 2007 4:57:17 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin WARNING: ConnectionID:1 TransactionID:0x0000000000000000 Prelogin response packet not marked as EOM The TDS protocol stream is not valid.
To communicate with both MS SQL Server 2000 and 2005 a user will have to maintain both the drivers which may increase complexity of the application , like loading both driver in a single process , etc etc . . .
If version 1.2 id built on top of Sql server 2000 JDBC drive then it should be able to handle following 4 cases :
i ) SQL Server 2005 - SSL , ii ) SQL Server 2005 - Non - SSL , iii ) SQL Server 2000 - SSL , iv ) SQL Server 2000 - Non - SSL .
If this single driver handles all above 4 cases , life of a developer and application developed by him/her would become less complex.
3 ) Last but not the least , the SQL Server 2000 JDBC driver connects with both kind of SQL Server 2000 instances i.e. SSL and Non - SSL without any certificate of flags specified. I was able to establish non - SSL connection with SQL Server 2000 using two third party JDBC drivers which I think is not appropriate behaviour.
4 ) I have also noticed that when SQL Server 2005 is running in Non - SSL mode i.e. "ForecEncryption=No" and our Client application requests SSL communication , we are not warned about server is not runnning in SSL mode , could you please explain this ?
Kindly correct me I am wrong anywhere as I am just a beginner and don't have much knowledge on this subject .
I have a J2EE based web application. There is a requirement where user triggers a process to query a database, and the application needs to save the results to an SDF file (Microsoft SQL Server CE 2.0 / Microsoft SQL Server Compact Edition) database. I need JDBC driver for it, but could not find it anywhere. Please help. Also, if there is any alternate way to do this, please let me know.
Recently, I found a bug in Microsoft's official SQL Server2000 JDBC driver. It seemed to lie in the Statement's setDate() method. Maybe they simply assumed all date are represented in 12-hours format. The test code is below:
public void testMisc() throws Exception { Connection conn = TransactionManager.getConnection(); String sql = "select count(salescheck0_.ID) as x0_0_ " + "from T_CHK_OUT_BILL_TEST salescheck0_ " + "where (salescheck0_.EX_TIME>?)" + "and(salescheck0_.EX_TIME<?)"; PreparedStatement stmt = conn.prepareCall(sql); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date timefrom = sdf.parse("2004-04-02 00:00:00.000"); Date timeto = sdf.parse("2004-04-02 23:00:00.000");
Given the code and data, the expected output ought to be "count = 19" But it's "count = 19" instead. However, if you set date by using alternative method like the commented statements, you can get the correct result.
Does anybody know if there exists any patch? Could we expect Mircosoft to fix this bug? If so, where could I submit this bug report?
I'm encountering a problem that seems to be due to the StreamError class not being serializable. This was raised on the JDBC Driver Team blog by Jonas on 10/24/2007. Jimmy Wu notes on 10/29/2007 that it is under investigation. Any progress?
We are in the process of configuring a j2ee application with SQL Server 2005. We have downloaded the new version of SQL Server 2005 Java Database Connectivity (JDBC) Driver 1.0.
We are having some issues in getting it to work using the XA datasource. Description of the error is as follows:
javax.transaction.xa.XAException: java.sql.SQLException: Failed to create the XA control connection. Error: Could not find stored procedure 'master..xp_sqljdbc_xa_init'.. at com.microsoft.sqlserver.jdbc.SQLServerXAResource.start(Unknown Source)
Has anyone else has encountered this and possibly found a solution.
I'm having a heck of a time trying to access a SQL server database using Saxon's SQL extensions with the Microsoft SQL server JDBC driver. The Saxon XSL processor has implemented some SQL extensions that allow you to access a SQL database through a JDBC driver. I have Visual Studio 2005 professional with SQL server express installed on my workstation. The SQL server express installation is installed at the default 1433 port on my workstation, it has been setup to accept connections over TCP and has been configured to accept Windows authentication and SQL authentication. In addition, I have downloaded and installed Microsoft's JDBC driver for SQL server.
After installing the JDBC driver I added the JDBC sqljdbc.jar to my Java CLASSPATH. Then I built a simple Saxon XSL that made use of the SQL extensions:
When I run the Java version of Saxon it issues a JDBC connection failure:
SXSQ0003: JDBC Connection Failure: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect Transformation failed: Run-time errors were reported
After some discussion on the Saxon listserv, I hacked a sample in the Microsoft JDBC documentation to see whether a standard Java program could use the JDBC driver and access the database:
Code Snippet import java.sql.*; public class connectURL { public static void main(String[] args) { // Create a variable for the connection string. String connectionUrl = "" + "jdbc:sqlserver://localhost:1443" + ";databaseName=prototype"; // Declare the JDBC objects. Connection con = null; Statement stmt = null; ResultSet rs = null; try { // Establish the connection. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager.getConnection(connectionUrl, args[0], args[1]); // Create and execute an SQL statement that returns some data. String SQL = "SELECT * FROM dbo.Employees"; stmt = con.createStatement(); rs = stmt.executeQuery(SQL); // Iterate through the data in the result set and display it. while (rs.next()) { System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4)); } } // Handle any errors that may have occurred. catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) try { rs.close(); } catch(Exception e) {} if (stmt != null) try { stmt.close(); } catch(Exception e) {} if (con != null) try { con.close(); } catch(Exception e) {} } } }
This Java application accesses SQL server express and prints out the list of employees from the dbo.Employees table in the database:
Code Snippet -- Create the table definition. CREATE TABLE dbo.Employees ( empid INT NOT NULL PRIMARY KEY, mgrid INT NULL REFERENCES dbo.Employees, empname VARCHAR(25) NOT NULL, salary MONEY NOT NULL, CHECK (empid <> mgrid) ); GO
-- Insert test data. INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 1, NULL, N'David' , $10000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 2, 1, N'Eitan', $7000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 3, 1, N'Ina', $7500.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 4, 2, N'Seraph', $5000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 5, 2, N'Jiru', $5500.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 6, 2, N'Steve', $4500.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 7, 3, N'Aaron', $5000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 8, 5, N'Lilach', $3500.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 9, 7, N'Rita', $3000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 10, 5, N'Sean', $3000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 11, 7, N'Gabriel', $3000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 12, 9, N'Emilia', $2000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 13, 9, N'Michael', $2000.00 ); GO INSERT INTO dbo.Employees( empid, mgrid, empname, salary ) VALUES( 14, 9, N'Didi', $1500.00 ); GO
-- Create unique index. CREATE UNIQUE INDEX idxManagerEmployee ON dbo.Employees( mgrid, empid ); GO
Internally, Saxon uses almost the exact same code, e.g., three argument DriverManager.getConnection, but the call fails. Michael Kay, has indicated that this issue isn't related to Saxon since the error message coming from Saxon indicates that Saxon is finding the JDBC driver and issuing the connection request, but the JDBC driver is failing.
Has anyone tried to use Saxon's SQL extensions with Microsoft's JDBC driver? If you have could you indicate how you have things setup to make it work? If anyone from Microsoft's JDBC driver team is listening, would you be will to help recreate this issue or help trying to diagnose the issue?
I'm using JDBC driver for Server 2005 (version 1.1.1501) to access Server 2000. I have no problems reading my data, but every update returns "Cursor is READ ONLY".
If I use driver for Server 2000 I have no problems. And I have no problems running the same program with 2005 driver against SQL Server Express.
I'm trying to configure JBoss application server to use SQLServer 2005 as database with XA transactions. I can start a new transaction, access SQLServer from within the transaction, but when committing, there is the following XAException from SQLServer: 16:04:20,926 INFO [STDOUT] javax.transaction.xa.XAException: java.sql.SQLException: COMMIT: Status:0 msg:*** SQLJDBC_XA DTC_ERROR Context: xa_commit, state=1, StatusCode:-4 (0xFFFFFFFC) ***16:04:20,926 INFO [STDOUT] at com.microsoft.sqlserver.jdbc.SQLServerXAResource.commit(Unknown Source)16:04:20,941 INFO [STDOUT] at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.commit(XAManagedConnection.java:176)16:04:20,941 INFO [STDOUT] at com.arjuna.ats.internal.jta.resources.arjunacore.XAResourceRecord.topLevelOnePhaseCommit(XAResourceRecord.java:641)16:04:20,941 INFO [STDOUT] at com.arjuna.ats.arjuna.coordinator.BasicAction.onePhaseCommit(BasicAction.java:2619)16:04:20,941 INFO [STDOUT] at com.arjuna.ats.arjuna.coordinator.BasicAction.End(BasicAction.java:1779) [...]
The system is running on Windows Server 2003 64bit with SQLServer 2005 64bit, and JBoss running on JDK 1.5.0 (32bit) Can anyone tell me what this message indicates?
We have a customer in Japan who's using our tool to connect to their SQL Server. Our application runs on a Linux machine and uses the MSSQL JDBC driver for connectivity.
We're getting errors back that are not-decipherable because, I presume, they're in Japanese, and then our logs don't log them in Unicode, so they get flattened out as question marks. The errors come out looking something like this:
Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]???? 'MSSQL' ???????????????????????????????
SQL State: HY000
SQL Code: 4060
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]???? 'sa' ??????????????
SQL State: 28000
SQL Code: 18456
[Microsoft][SQLServer 2000 Driver for JDBC]An error occured while attempting to log onto the database.
SQL State: 08001
SQL Code: 0
I think we could troubleshoot the problem if we could only read the errors coming back. I know in the ODBC world there are options like "Change the language of SQL Server system messages" and "Perform transaltion for character data" in the ODBC control panel. I couldn't find any similar options available in the JDBC driver however. Do they exist?
Even if they don't, just scanning the messages makes it pretty clear that there's an authentication/permissions issue at work here. We have another app using the same credentials that works fine though - so if there are any known issues about using JDBC to authenticate in a multi-lingual environment, that would be helpful too. Our tcpdumps seem to suggest that the JDBC driver is using multi-byte strings when communicating with the server, however the "working" application is using single-byte encoding. Could that be why we're not authenticating? How is that controlled? Note that at this point I'm just talking about authentication, not the codepages/encodings of the data in the database - right now I'm just worried about logging in.
Hi, We are looking for a way to create the jdbc connection with Microsoft jdbc driver on a TomCat server ? Meaning the exact information to enter within the server.xml file or within the Tomcat web server administration tool - data source GUI.
I am using SQL server 2005 stored procedures being called from my java application using the CallableStatement. As long as my stored procedure is a simple and direct Select statement things are moving nicely. But my stored procedures are a little bit more complicated and this causes problems for me to parse the data in a ResultSet. a sample stored procedure: Create procedure sp_Get
//this also returns false? boolean moreResults = cmd.getMoreResults();
The strange part of this is that when you execute this query in the SQL Server Managent Studio it returns 1 result set. Why is my java code not able to see this result?
I have some code that should execute multiple statements and then commit. I am using the Microsoft SQL Server 2005 JDBC Driver 1.1.
The statements only commit ifI close the connection to the SQL Server 2005 instance. I've looked at the example given at http://msdn2.microsoft.com/en-us/library/ms378931.aspx and it doesn't require closing the connection in order to commit.
Example:
public void doCall(Connection con) { try { /*Turn off AutoCommit.*/ con.setAutoCommit(false);
