tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Randall Svancara" <rsvanc...@adaweb.net>
Subject RE: Speed issues with SQL Server 2000 and JTDS
Date Mon, 03 Jan 2005 14:19:39 GMT
I have been using JTDS with SQL Server 2000 in conjunction with Tomcat
without any problems.  Perhaps if you post some your database connection
code, someone could provide you with assistance.  You might also try
posting to the JTDS Mailing list.  Are you using Database Connection
Pooling (DBCP)??

I am including an example the code I use to access a stored procedure on
SQL Server 2000 using DBCP.  

/*  Here are the things I import */
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.*;
import javax.sql.*;


Public class SomeClass{

	/**
 	 * A public class that returns an Applicant object
	 * @return the applicant as applicant
	 */
	public Applicant SomeApplicantMethod(){
        Applicant app = new Applicant();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rst = null;
        
        try{
            Context ctx = new InitialContext();     /* Declare initial
context */
            if(ctx == null ){
                logger.error("Error creating new context for some
reason");
                throw new Exception("No context");
            }
            /* Throw an exception if it is null */
            DataSource ds =
(DataSource)ctx.lookup("java:comp/env/jdbc/summitexec");
            
            conn = ds.getConnection();
            
            if(conn != null)  {
                
                stmt = conn.createStatement();
                
                rst = stmt.executeQuery("sp_SelectApplicant " +
canidateid);
                
                while(rst.next()){
			
				// Add result set to applicant object,
NOT SHOWN HERE!!!

                }
                

		    //Make sure you close everything, else you end up
with object leaks....
                if(stmt != null){
                    stmt.close();
                }
                
                if(rst != null){
                    rst.close();
                }
                
                if(conn != null){
                    conn.close();
                }
            }
        }catch(Exception E){
            logger.error("EXCEPTION ERROR Getting Applicant: " +
E.toString());
        } finally{
            
            // Close out of any open connections if they exist, this is
important
            // in order to release resources for connection pooling
            try{
                if(stmt != null){
                    stmt.close();
                    stmt=null;
                }
            }catch(SQLException E){}
            
            try{
                if(rst != null) {
                    rst.close();
                    rst = null;
                }
            }catch(SQLException E){}
            
            try{
                if(conn != null) {
                    conn.close();
                    conn = null;
                }
            }catch(SQLException E){}
        }		

	}
}


This is the quirky part about Tomcat, in version 5.0 or ealier, I have
to use this xml code in my webapp context file for DBCP.

	<Resource name="jdbc/summitexec" auth="Container"
type="javax.sql.DataSource" />
	<ResourceParams name="jdbc/summitexec">
    		<parameter>
        		<name>factory</name>
 
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    		</parameter>
		<parameter>
			<name>url</name>
	
<value>jdbc:jtds:sqlserver://192.168.0.2:1433/summitexec;User=someuser;P
assword=somepassword</value>
		</parameter>
		<parameter>
			<name>driverClassName</name>
			<value>net.sourceforge.jtds.jdbc.Driver</value>
		</parameter>    
   		<parameter>
			<name>user</name>
			<value>someuser</value>
		</parameter>
		<parameter>
			<name>password</name>
			<value>somepassword</value>
		</parameter>
	</ResourceParams>

In 5.5 I have to use this xml for DBCP.  If someone could provide
details why this is, I would appreciate it.

        <Resource name="jdbc/summitexec" 
                  auth="Container"
                  type="javax.sql.DataSource" 
                  driverClassName="net.sourceforge.jtds.jdbc.Driver"
 
url="jdbc:jtds:sqlserver://192.168.0.3:1433/summitexec"
                  username="someuser" 
                  password="somepassword" 
                  maxActive="20" 
                  maxIdle="10" 
                  maxWait="10"/> 

Thanks,

Randall

-----Original Message-----
From: Charles P. Killmer [mailto:charlesk@netgaintechnology.com] 
Sent: Sunday, January 02, 2005 2:13 PM
To: Tomcat Users List
Subject: Speed issues with SQL Server 2000 and JTDS

I bought the Core Servlets and Java Server Pages and read it over the
weekend.  Happy New Year to me.  I did get out to a few parties though.
;)  I am having trouble getting JTDS to return results quickly. 
 
Has anyone got any example code for how to properly query a SQL Server
2000 database?  The code I write needs to work with both SQL Server 2000
and SQL Server 7.  In creating the connection, I am specifying
TYPE_SCROLL_INSENSITIVE and TYPE_CONCUR_READ_ONLY.  I tried not
specifying anything and got errors about not being able to scroll the
results.  Is the only solution to this, use FORWARD_ONLY and buffer the
contents myself?  I hoping there is a better way.
 
Thank you
Charles Killmer
 

---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org


Mime
View raw message