AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  SQLServer 2000 Driver for JDBC behaviour on SQL Server resta          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message gp Guest Posted: Tue Feb 22, 2005 11:29 pm    Post subject: SQLServer 2000 Driver for JDBC behaviour on SQL Server resta 1) My java code properly connects to the SQL Server DB via the SQL Server 2000 JDBC driver. 2) During one of our stress tests, we restarted the SQL Server to see how the code handles it. 3) My code doesn't really recognize that the SQL server is up and my calls to DB fail throwing the following exception (Note: I have tried this on Oracle 9i and it works properly. i.e my code is able to read from Oracle DB on db restart.) Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Broken pipe at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.submitRequest(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.openCursor(Unknown Source) at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.execute(Unknown Source) Any help would be appreciated. Back to top joeNOSPAM@BEA.com Guest Posted: Wed Feb 23, 2005 5:06 am    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r I would like to see how you made an oracle jdbc connection that passed this test. The exception you get is a common one, when the driver finds that the socket it had been using for it's connection to the DBMS is dead during a user call to a JDBC method. You should program to deal with this sort of failure if your DBMS may go away sometimes. Drivers typically do *not* transparently reconnect when a DBMS goes down and comes back up. Some drivers and DBMSes do have failover capability but this is never guaranteed to be transparent because the context of the connection cannot be guaranteed to be retained across the failover. Typically for a complicated JDBC environment even the failover drivers require the moral equivalent of making a new connection. Joe Weinstein at BEA Back to top Luke Webber Guest Posted: Wed Feb 23, 2005 5:36 am    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r [email]joeNOSPAM (AT) BEA (DOT) com[/email] wrote: Quote: I would like to see how you made an oracle jdbc connection that passed this test. I expect he used the ConnectionRetry and ConnectionDelay options in the connection URL. I'm not sure if the SQL Server driver has an equivalent. Luke Back to top Alin Sinpalean Guest Posted: Wed Feb 23, 2005 3:39 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Luke Webber wrote: Quote: I expect he used the ConnectionRetry and ConnectionDelay options in the connection URL. I'm not sure if the SQL Server driver has an equivalent. Those parameters instruct the (DataDirect) driver how many times to retry establishing a connection and how much to wait between succesive retries. So unless you actually request a new connection, those parameters have no effect whatsoever. And the OP implied that he managed to use the same connection after restarting Oracle. Which, I have to agree with Joe, is impossible. Alin. Back to top gp Guest Posted: Wed Feb 23, 2005 8:12 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Thanks for the responses. I agree with what Alin says. I have not used the same connection after restarting Oracle DB, but instead used a new Connection. My connection pool is managed my BitMechanic code. In the creation of Connection Object: if (connection is not valid) then use ConnectionRetry and ConnectionDelay to make another call until you get a Connection. (Note: I don't have to have this above line on Windows, as my JDBC driver gets a new connection from the pool if the connection is null in the call to createStatement()) This way my connection.createStatement() would not throw any Broken Pipe Exception. Back to top Luke Webber Guest Posted: Wed Feb 23, 2005 8:59 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Alin Sinpalean wrote: Quote: Luke Webber wrote: I expect he used the ConnectionRetry and ConnectionDelay options in the connection URL. I'm not sure if the SQL Server driver has an equivalent. Those parameters instruct the (DataDirect) driver how many times to retry establishing a connection and how much to wait between succesive retries. So unless you actually request a new connection, those parameters have no effect whatsoever. And the OP implied that he managed to use the same connection after restarting Oracle. Which, I have to agree with Joe, is impossible. Really? Then that's an unfortunate lack. One thing I like about the MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't have that, it's a big plus for MySQL. Luke Back to top Alin Sinpalean Guest Posted: Thu Feb 24, 2005 7:27 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Luke Webber wrote: Quote: Really? Then that's an unfortunate lack. One thing I like about the MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't have that, it's a big plus for MySQL. I wouldn't necessarily put it that way. Think about what happens if you start a transaction on a connection, then after you do some work, the connection fails; it then auto reconnects, you do some more work while not knowing anything about what just happened; then you commit your work, which actually commits only the second half; your DB is now in an inconsistent state. I think it's not worth it. Alin. Back to top Luke Webber Guest Posted: Thu Feb 24, 2005 9:23 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Alin Sinpalean wrote: Quote: Luke Webber wrote: Really? Then that's an unfortunate lack. One thing I like about the MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't have that, it's a big plus for MySQL. I wouldn't necessarily put it that way. Think about what happens if you start a transaction on a connection, then after you do some work, the connection fails; it then auto reconnects, you do some more work while not knowing anything about what just happened; then you commit your work, which actually commits only the second half; your DB is now in an inconsistent state. I think it's not worth it. I don't think it would work that way, but it would be interesting to know. I wonder if Mark Matthews is following this thread? IME the autoReconnect feature is especially useful if you have something like a web app, which stays up for long periods of time. You really don't want to have to code around the need to reconnect every time somebody brings the database server down, even though your app probably weren't accessing the serrver when it failed. I'm fairly certain that, in the event that you are accessing the server when it goes down, you'll get an error, which is only fair. But if you're between operations, your connection will reestablish itself when the server comes back up. How many apps keep transactions open for more than the briefest instant, anyway? Sound like a Bad Idea to me. Luke Back to top Mark Matthews Guest Posted: Thu Feb 24, 2005 10:11 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Alin Sinpalean wrote: Quote: Luke Webber wrote: Really? Then that's an unfortunate lack. One thing I like about the MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't have that, it's a big plus for MySQL. I wouldn't necessarily put it that way. Think about what happens if you start a transaction on a connection, then after you do some work, the connection fails; it then auto reconnects, you do some more work while not knowing anything about what just happened; then you commit your work, which actually commits only the second half; your DB is now in an inconsistent state. I think it's not worth it. Alin. Alin, Autoreconnect only works if autoReconnect is true, and even still, you still get an exception, you just need to re-try the operation. However, we're planning on deprecating the feature, and requiring applications to acquire a new connection to retry their operation on. As applications get more-and-more stateful with the way they deal with JDBC connections and other session state, it's the only way to ensure proper operation. The real issue is that the original JDBC specification never specified how long a connection should remain alive, so many developers assumed 'forever' while most vendors assumed 'as long as possible', which is a mismatch of expectations, to say the least. _Robust_ transactional JDBC applications are coded to expect the JDBC connection to go south at any moment, and recover accordingly, which means sometimes you can retry the operation, other times you punt the exception back to the next layer up (and eventually to the user). On the other hand, it's a lot of overhead to do that if all you're doing is throwing up read-only pages of data. -Mark Back to top Alin Sinpalean Guest Posted: Fri Feb 25, 2005 12:59 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Mark, Thanks for the clarification. It's clearly obvious that a connection won't be able to stay up "forever"; not taking this into account is definitely bad coding. If an exception is thrown when the connection fails and is then reestablished, this doesn't really help a lot; the operation will probably be aborted anyway. All server-side applications have some sort of connection pool, usually DBCP. It's the connection pool's job to ensure the connections are still alive and to dump them and create new ones if not. If a connection pool can't do that then it's not much more than a list of objects. Luke, If the error occurs "between" two operations it will actually occur while the second operation executes and will probably cancel the operation. Having a decent connection pool behind it all will lead to the same result: any subsequent operation will get a new connection. Alin. Back to top Mark Matthews Guest Posted: Fri Feb 25, 2005 3:20 pm    Post subject: Re: SQLServer 2000 Driver for JDBC behaviour on SQL Server r Alin Sinpalean wrote: Quote: Mark, Thanks for the clarification. It's clearly obvious that a connection won't be able to stay up "forever"; not taking this into account is definitely bad coding. If an exception is thrown when the connection fails and is then reestablished, this doesn't really help a lot; the operation will probably be aborted anyway. All server-side applications have some sort of connection pool, usually DBCP. It's the connection pool's job to ensure the connections are still alive and to dump them and create new ones if not. If a connection pool can't do that then it's not much more than a list of objects. Alin, DBCP isn't all that robust in my experience, and the documentation Apache has put up unfortunately isn't real good and providing a decent default installation either, for example, here's what they say to do for MySQL: <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/javatest?autoReconnect=true"/> Obviously, you don't want to use 'autoReconnect' with a connection pool, 100 connections is probably way too high for most applications, and they don't test idle connections by default by adding testWhileIdle="true" and validationQuery="SELECT 1". I've filed a bug on this at least once, maybe I'll just post to the tomcat mailing list and see if that gets any traction. DBCP also doesn't have (that I know of), any way to set a 'max idle time' of a connection, which is also very handy, especially if you set it to something less than 'wait_timeout' on the MySQL server...I use C3P0 a lot, and it has such a feature, as do many commercial appserver pools. Because connection setup is so quick in MySQL, I usually recommend a dynamic-sized pool, with some maximum cap (determined by how beefy your MySQL server is), and with low max-idle-time, on the order of minutes. If the system is under load, the connections don't go idle, when the load lightens up, idle connections are tossed. The only penalty you pay is a little extra time to respond to a load spike. In any case, _no_ connection pool will save you from having a connection die from the time you retrieve it from the pool until the time you return it, so of course, defensive coding is the rule! -Mark Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.