Summary of JSP connection to SQL SERVER

Due to system requirements, I recently moved the system from MySQL to SQL 2000 during the development process. I encountered many problems and it took me a lot of time. Now I will show you my experience so that you can avoid detours and save time.

The first is the installation of the SQL 2000 database. Here I will mainly talk about compatibility between SQL 2000 and the operating system: SQL 2000 has a total of seven different versions, meet the needs of users of different levels.

I tried it. In the XP system, only the "Personal Development edition" can be installed normally without errors. Therefore, you should pay attention to it during installation, refer to the relevant instructions for the configuration during installation.

The following describes how to connect to the SQL 2000 database. First, download the JDBC driver. You 'd better download it from the official Microsoft website, then put the three downloaded JAR packages under your WEB application's WEB-INF/lib. Next, write a program for testing:


/*************************************** ********
/*
/* DBTest. java
/*
/*************************************** *****/

Import java. SQL .*;

Public class DBTest
{

Connection con;
Statement sta;
ResultSet rs;

String driver;
String url;
String user;
String pwd;
Public DBTest ()
{
Driver = "com. microsoft. jdbc.
Sqlserver. SQLServerDriver ";;
Url = "jdbc: microsoft: sqlserver:
// Localhost: 1433; DatabaseName = test ";
// Test indicates the database name.
User = "sa ";
Pwd = "sa ";
// Change your user and password
Init ();
}
Public void init ()
{
Try {
Class. forName (driver );
System. out. println ("driver is OK ");
Con = DriverManager.
GetConnection (url, user, pwd );
System. out. println ("conection is OK ");
Sta = con. createStatement ();
Rs = sta.exe cuteQuery
("Select * from room ");
While (rs. next ())
System. out. println
(Rs. getInt ("roomNum "));
} Catch (Exception e)
{
E. printStackTrace ();
}
}

Public static void main (String args [])
// Replace []
{
New DBTest ();
}
}

The above code should be correct. Let's take a look at it first. If the sqlser server is not upgraded to sp3 (when using jdbc, if the system is xp or 2003, it is necessary to upgrade sqlserver to sp3 and go up everywhere), let's look at the running results:

Driver is OK
Java. SQL. SQLException: [Microsoft]
[SQLServer 2000 Driver for JDBC]
Error establis
Hing socket.
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.
TDSConnection. <init> (Unknown Source)

At com. microsoft. jdbc. sqlserver.
SQLServerImplConnection. open (Unknown Sou
Rce)
At com. microsoft. jdbc. base.
BaseConnection. getNewImplConnection (Unknown S
Ource)
At com. microsoft. jdbc. base.
BaseConnection. open (Unknown Source)
At com. microsoft. jdbc. base.
BaseDriver. connect (Unknown Source)
At java. SQL. DriverManager.
GetConnection (DriverManager. java: 523)
At java. SQL. DriverManager.
GetConnection (DriverManager. java: 171)
At DbTest. init (DbTest. java: 32)
At DbTest. <init> (DbTest. java: 25)
At DbTest. main (DbTest. java: 46)
Press any key to continue...

The main cause of the above error is that the default database server port 1433 is not enabled and cannot be connected directly. If you upgrade to sp3, this problem can be solved. Let's take a look at the program running results after the upgrade:

Driver is OK
Conection is OK
Java. SQL. SQLException:
[Microsoft] [SQLServer 2000 Driver for JDBC]
[SQLServer] pair
The image name 'room 'is invalid.
At com. microsoft. jdbc. base.
BaseExceptions. createException (Unknown Source
)
At com. microsoft. jdbc. base.
BaseExceptions. getException (Unknown Source)
At com. microsoft. jdbc. sqlserver. tds.
TDSRequest. processErrorToken (Unknown
Source)
At com. microsoft. jdbc. sqlserver. tds.
TDSRequest. processReplyToken (Unknown
Source)
At com. microsoft. jdbc. sqlserver. tds.
TDSExecuteRequest. processReplyToken (
Unknown Source)
At com. microsoft. jdbc. sqlserver. tds.
TDSRequest. processReply (Unknown Sour
Ce)
At com. microsoft. jdbc. sqlserver.
SQLServerImplStatement. getNextResultType
(Unknown Source)
At com. microsoft. jdbc. base.
BaseStatement. commonTransitionToState
(Unknown
Source)
At com. microsoft. jdbc. base.
BaseStatement. postImplExecu
Te (Unknown Source)

At com. microsoft. jdbc. base.
BaseStatement. commonExecute (Unknown Source)
At com. microsoft. jdbc. base.
BaseStatement.exe cuteQueryInternal
(Unknown So
Urce)
At com. microsoft. jdbc. base.
BaseStatement.exe cuteQuery (Unknown Source)
At DbTest. init (DbTest. java: 35)
At DbTest. <init> (DbTest. java: 25)
At DbTest. main (DbTest. java: 46)
Press any key to continue... <