JDBC - MS_SQL Server  Interface

• Install SQL Server 2000 Developer Edition

Choose "Server and Client Tools" option
Choose "Mixed Authentication (SQL Server and Windows) " option


• Install SQL Server Service Pack 4



• Install JDK 1.4.2 (click on "Download J2SE JDK" link) [ j2sdk-1_4_2_10-windows-i586-p.exe  54.67MB ]



• Install JDBC Driver

Execute "MS_JDBC_setup.exe" file


• If Eclipse is not already installed,

download it from http://www.eclipse.org  ["downloads" tab and click on Eclipse SDK 3.1.2, Windows (103 MB)]
and install it.

SQL Server Setup:

• Create two new databases:

Start -> All Programs -> Microsoft SQL Server ->Enterprise Manager
On the left panel, expand "Microsoft SQL Servers" and select & RightClick "SQL Server Group"
 "New SQL Server Registration" -> Next -> Add new Available (Local) server to Added servers -> Next
 "choose "Window account information", Next, Next, Finish, Close
Expand "SQL Server Group" and "(Local)"; then select "Databases""
Right click "Databases"  -> Select "New Database..." and create an empty "company" database
Similarly, create an empty "staff" database
Select "(Local)" server (below SQL Server Group), click Tools menu and select "SQL Server Configuration Properties..."
click "Security" tab, choose "SQL Server and Windows" authentication, OK

• Populate the two new databases:

Invoke Enterprise Manager and select "company" database
  From Tools menu click on "SQL Query Analyzer"
  Click on "Load SQL Script" icon (second icon on the icon bar)
  Browse and choose setupCompany.sql -> Open; Click "Execute Query" icon
At the top center of the window, select "staff" from the dropdown list of databases
  Click on "Load SQL Script" icon (second icon on the icon bar)
  and run setupStaff.sql script (note this script file creates tables and loads all data)
Close "SQL Query Analyzer" window

• Create a new user and set permissions:

Invoke Enterprise Manager, expand "SQL Server Group" and "(Local)""
Expand "Security" and right click on "Logins" and select "New Login..."
  General tab: Name as Application program username, Choose SQL Server Authentication,
      Password, company as default database
  Database Access tab: Enable company (top), Enable public, db_datareader, db_datawriter (bottom)
       Enable staff (top), Enable public, db_datareader, db_datawriter (bottom)
  OK

• Client Network Utility - Connection Registration:

Start -> All Programs -> Microsoft SQL Server ->Client Network Utility
  General tab: Enable only TCP/IP protocol
  Alias tab: Add an alias with servername as Local, server alias as meaningful label
    choose TCP/IP Library, enable Dynamically determine port, and OK

Eclipse Configuration:

• Download all four jar (Java Archive) files from jarFiles folder



• Follow the instructions on configuration steps

Testing Database Connectivity:

• To establish a connection between a Java database application program and

Microsoft SQL server, perform the following two steps:
  - register appropriate database driver (either JDBC or JDBC-ODBC)
  - connect to a database server with appropriate authentication


• Following two sample programs test database connectivity

   TestDB1.java   -- fixed username & password
   TestDB2.java   -- tests for any username & password

Testing Dynamic SQL queries:

• JDBC - MS_SQL can support dynamic queries but not embedded queries

since there is no precompiler that can compile embedded queries.


• Following three sample programs demonstrate dynamic queries and assume the default database is "company".

   TestDB3.java   -- fixed dynamic query string
   TestDB4.java   -- dynamic query string with an argument
   TestDB5.java   -- nested dynamic queries

Reference Links:

• JDBC Overview



• JDBC API



• JDBC Code Samples



• Using PreparedStatement



• PreparedStatement Class

 --Prabakar