[Excerpt] 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... Here, the user has logged on, but cannot access the data table. The reason for this problem is that the sa user is a system user, although it can log on to the database, however, the test database does not have the access permission of this user. Therefore, we create a new user share for this database. The creation process is as follows: Select a unique user in the test database ---> create user --> name selection (this step has two key points: Select SQL authentication for identity authentication, and select test for the default database)-> Create a new share, change the program, change the user login name and password, and run the program again: Driver is OK Conection is OK 1001 1002 1003 1004 1005 1006 Press any key to continue... This successful test In fact, it took me one night to solve these small problems, which is a waste of time. Therefore, I hope that my friends who encounter similar problems will not repeat the same mistakes. I would like to remind you to check online when you encounter problems, ask more questions in the Forum so that you will learn more, saving time and improving efficiency. Conclusion: the Key to the integration of Sqlserve and JDBC involves the sp3 patch (port opening) and user issues. After solving these two problems, the remaining problems are the SQL server operation problems, another point is to set the Sqlserver group during remote operations, and change the authentication to Sqlserve authentication in security.