JDBC - MS_SQL Server Interface
Software installation:
- 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:
--Prabakar