• Post Reply Bookmark Topic Watch Topic
  • New Topic

must change method of connecting to MS SQL SERVER from servlet

 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I have an Android application which connects to Servlet and this one connects to Ms-Sql Server.
The current method is :
declare a datasource in Administrative tools (ODBC)

and in the sevlet using:



It works fine for one connection, but now I have a lot of connections (con29, con30...),
and the application gets stuck when one connection ask to be active when other one is still open.

If someone has the answer for solving this, I'll appreciate this a lot. Thank you.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65530
108
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you need multiple connections?
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Bear, The user has a button which activates proc 1 (proc1 is a stored procedure in SQL Server)
Proc1 1 does something and enters to a loop of 10 minutes in SQL (as it should).

The user can click on another button which activates proc2 (proc2 is another stored procedure in SQL Server).
I would like proc2 to kill proc1 and to do something else.

Therefore proc2 need to use another connection, than the connection which proc1 using.


 
Claude Moore
Ranch Hand
Posts: 832
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No matter why you need multiple connections, I suggest you to have a look at Datasource and connection pooling mechanism. They are far better than manually opening connection via driver manager -at least in a java ee context.
More important, you are using JdbcOdbc driver. If I am not wrong, its support has been dropped in java 8. You should use Jdbc driver provided by Microsoft (which is more efficient, by the way)
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Claude, I went with your advice about the pooling, and did the next (I hope this is what you ment):



Everything is compiled, but at RunTime I get for the last line (When I use getConnection() from the datasource) the Exception:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I read that this kind of error can happen for 64 bit operation system, but mine is 32.
I can not understand why do I get this error...
 
Claude Moore
Ranch Hand
Posts: 832
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would get rid of JdbcOdbc driver as first thing to do. Download from Microsoft web site JDBC driver specific for your Sql Server version,and use it instead of JdbcOdbc driver. You should be able to find a few examples about using it. Moreover, I would not define manually my datasource. I would configurate it in my servlet container, and then I would execute a lookup via JNDI, in a similar way like the one you followed:



I'm sorry but I have no idea about the error you got.
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Claude, It's obviously you know about this (much) more than I do, so thank you for your advices. I must tell you that few months ago when I tried few ways to connect to
Sql Server through the servlet I had really lot of attempts, and the JdbcOdbc way was the only one which did the work, so I'm little bit afraid to go and try again all this mess.
Inspite of that, I think I don't have any choice and I need to do what you wrote.
A little question about this sentence :
Moreover, I would not define manually my datasource. I would configurate it in my servlet container, and then I would execute a lookup via JNDI

Is this suggestion for how to use the JDBC driver (the new way I'm going to try) , or for the current JdbcOdbc driver (my current way)?
 
Tim Holloway
Bartender
Posts: 18415
58
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Regardless of which JDBC driver you use, a Connection pool should not be coded into your webapp. The J(2)EE standards allow for Connection Pools to be defined and constructed as the property of the appserver itself (thus potentially shareable by multiple webapps). Aside from sharability, this approach also means that you don't have to store sensitive information (such as userid/password) in application code, and you can swap out and/or tune drivers without modifying application code.

The ODBCJDBC bridge driver should NEVER be used with web applications, It wasn't designed for multiple concurrent users. Besides, you have better options for SQL Server. Microsoft makes one available from their web site, but there is also one on sourceforge.net (jtds, I think it is) and there should be a few others of various flavors too. Some are open-source, some are closed-source, some are free and some you pay for. Use whichever one you like, but stay away from the ODBC bridge!
 
Dave Tolls
Ranch Hand
Posts: 2284
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As a further note on the JDBC ODBC Bridge, it was only ever a temporary stop-gap while you were waiting for a proper driver from whoever your DB vendor was. And that was over 15 years ago.

Suffice to say it hasn't been needed (MS Access aside, and the less said about that the better) for the best part of 15 years.
 
Claude Moore
Ranch Hand
Posts: 832
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:As a further note on the JDBC ODBC Bridge, it was only ever a temporary stop-gap while you were waiting for a proper driver from whoever your DB vendor was. And that was over 15 years ago.

Suffice to say it hasn't been needed (MS Access aside, and the less said about that the better) for the best part of 15 years.


I fully agree. But a couple of times I found it useful to connect to some databases not providing specific jdbc drivers - of course I am not talking about any major vendor.
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim, Dave, and Claude, thank you very much for your responses.
I'll try and update
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all, Wanted to say I moved to JDBC driver by 3 steps:
1. Installed the sqljdbc4.jar
2. As Claude suggested I configurated the data in servlet container (I mean in ProjectName/WebContent/META-INF/context.xml):
<?xml version="1.0" encoding="UTF-8"?>

3.In the servlet code I got the connection this way:

It works.

The thing is that after reading about Pooling , I understand the idea, but I didnt realize what is the diference in the code.
I mean, I didn't see any diffrent code for pooling, than what I wrote above.

May you tell me what are the changes I should add for this Pooling goal?

 
Tim Holloway
Bartender
Posts: 18415
58
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The main difference here is that when you do "brute force" unpooled connections, you obtain the driver and get the connection directly in application code.

In JEE, if you use a Datasource, then the DataSource gets the connection and hands it off to you.

Actually, there's no requirement that a DataSource be a connection pool, it's just that connection pools are a more efficient way of handling lots of connection requests. That's because the pool is a pool of active connections. Meaning that you don't have to go to all the trouble of setting up a network connection to the database or initializing client and server-side objects to support it. That stuff was done when the connection was first created and will be there until the connection is either terminated or times out. And since creating and opening a connection is a fairly large amount of overhead, there are some significant performance advantages in being able to pass live connections around over creating them every time someone needs one.

The actual connection object that the application works with isn't really the driver connection, though. What the connection pool presents is a fa├žade object. It implements the methods in the Connection interface and MOSTLY just passes calls through to the "real" (driver) Connection. Except in one very important case: the pooled Connection's close() method doesn't actually terminate the Connection, it just returns it to the pool. So that some future user can obtain it without the cost of building a Connection from scratch.
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim, due to your explanation I feel I understand the pooling issue much better, the thing is that I still remained with
the problem of not knowing how to implement the pooling issue.

Today the way of getting a connection (after moving to JDBC driver instead of jdbc-odbc, thank to Claude, Dave and your advice ) is:
1. In the init() I create the data source by:

2 In the doPost(), I use the connection and close it by:

Now, how do I insert the Pooling issue? I mean which new object should I use in the Java code?
which new changes should I make in some xml files?
 
Tim Holloway
Bartender
Posts: 18415
58
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


From the application's point of view, whether or not a Connection comes from a pool is not known or important. The one and only place where the code differs is in how you get your Connection.

My log statements may be inaccurate. I haven't used Servlet's log method in years, since it's not available in non-servlet code the way loggers such as log4j are. Also, I probably left some loopholes that could under bad conditions throw improperly-handled Exceptions, especially NullPointer exceptions. But that's the basic shape of it.

Also the latest version of Java has an interesting new sort of try-catch construct that works well when constructing failure-prone objects, but I didn't use that for backwards-compatibility reasons here.
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wow. You surprised me. Do you say I don't need to change any XML file as web.xml and things like these?
and no new object is needed like ComboPooledDataSource??

Nothing else is needed for Pooling, and my servlet is using now Pooling?
 
Tim Holloway
Bartender
Posts: 18415
58
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Datasource (pool) itself is created and owned by the webapp server. Your webpp uses JNDI as a service locator to find it, then uses the DataSource API to obtain connections. That's all there is to it. Nothing special in web.xml or anything like that.
 
Eli Ean
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Got it. Thank you Tim. Appreciate your help.
I guess there are some people in the world who can learn something from this conversation.
Problem solved.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!