Experience in connecting to SQL Server 2000 using JDBC in Java

The JDBC basics chapter in Java over the past few days describes how to use the JDBC-ODBC bridge to connect JBuilder to the sqlserver database. There is basically no problem, however, many people asked how to use the SQL Server Driver to perform operations on the SQL Server database. Therefore, I summarized my experience and post on other forums. The specific steps are as follows:

1. Install sqlserver2000
Install patch SP3 for sqlserver2000
Install the sqlserver2000 for SP3 driver
(Patch SP3 first, and then install the driver for SP3. When installing the patch, install the two verification methods for the sake of security. I install Windows verification first, install SQL server again)

Open JBuilder
2. tools_configure_libraries new sqlserver. Add the Three jar files in the Lib folder under the driver installation directory to the library paths.
3. Project -- default projectproperties add step 1st new sqlserver to the required libaraes page on the paths page
4. Tools -- Enterprise setup (jb9)
Enterprise -- Enterprise setup (jb2005)
In the pop-up dialog box, on the database driver page, add SQL Server in Step 4, OK
5. Restart JBuilder.
6. Tools -- Database Pilot
Select View -- option from the menu in the pop-up window.
In the displayed dialog box, select the driver page and click Add.
Enter com. Microsoft. JDBC. sqlserver. sqlserverdriver in the driver class text box and click OK.

7. Set the driver class named com. Microsoft. JDBC. sqlserver. sqlserverdriver.
The driver is com. Microsoft. JDBC. sqlserver. sqlserverdriver.
The URL is JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = database name (e.g.: cardsystem)

FAQ analysis-1:
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 it is successfully upgraded to SP3, this problem can be solved. In addition, the database must use the windows and database hybrid identity authentication method.

FAQ analysis-2:
When installing the driver for sqlserver2000 for SP3, a dialog box is displayed, prompting you to install a program that has created a pending file on the computer"

Cause: I installed SQL server before and deleted it later. Now, "a previously installed program has created a suspended file on the installation computer. You must restart the computer before running the installer. Unable to proceed.

The solution is as follows:
1) Add/delete programs to completely delete SQL Server.
2) Delete the SQL Server Directory that has not been deleted.
3) Open the Registry Editor, find the pendingfilerenameoperations project in HKEY_LOCAL_MACHINE/system/CurrentControlSet/control/session manager, and delete it. In this way, you can clear the temporary installation project.
4) Delete the SQL Server-related keys in the registry.
In fact, it is estimated that only step 1 can be done, so that you can clear the installation of temporary projects. I did this by leaving for 1, 2, 4, and 3. Therefore, estimation 3 is the most critical.

When SQL Server Driver is used to operate SQL Server databases, the general code is as follows:
Connection con = NULL;
Preparestatement pstmt = NULL;
Try {
Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver ");
Con = drivermanager. getconnection ("JDBC: Microsoft: sqlserver // localhost: 1433; databasename = pubs", "sa", "sa ");
Pstmt = con. preparedstatment ("insert ....");
//......
} Catch (classnotfoundexception e ){
E. printstacktrace ();
} Catch (sqlexception e ){
E. printstacktrace ();
} Finally {
Try {
If (pstmt! = NULL) pstmt. Close ();
If (con! = NULL) con. Close ();
} Catch (exception e ){
E. printstacktrace ();
}
}

If it is sqlserver2005, change it:
Class. forname ("com. Microsoft. sqlserver. JDBC. sqlserverdriver ");
Con = drivermanager. getconnection ("JDBC: SQL Server // localhost: 1433; databasename = pubs", "sa", "sa ");
 

> JAVA. SQL. sqlexception: [Microsoft] [sqlserver 2000 driver for JDBC] Error establishing socket.
It is estimated that you have a problem with the port settings for connecting to SQL Server. You can check the port to see if it is 1433.

Solution: [Microsoft] [sqlserver 2000 driver for JDBC] Error establishing socket

If such an error occurs, it can be said that it is not a program error. Most people write JDBC: Microsoft: sqlserver: // localhost: 1433. This is correct, many of them are caused by port problems. Check the port as follows:

1) Open "Enterprise Manager", open the console root directory> SQL Server group> Database
2) Right-click the database and choose Properties"
3) Select the "General" tab and click "network configuration". If no "TCP/IP protocol" is enabled, add it
4) Select "TCP/IP protocol" and click "properties" to check whether the port number is 1433.
5) If the port number is 1433, change it to another port number, modify the JDBC connection statement, and change the port number to the newly enabled port number, such as JDBC: Microsoft: sqlserver: // SERVER_NAME: 1400 (assuming the new port number is 1400)