Micro Focus Enterprise Developer 2.3 Update 2 for Eclipse > Welcome > Getting Started > Web Services Tutorials > Tutorial: Reusing Existing SQL Programs in a Java Environment > Create a SQL Server Database and Connection

Create a SQL Server Database and Connection

Create a SQL Server database, define a database connection to the database, and load the database using a provided SQL script.

Create the PUBS SQL Server Database

Using SQL Server Management Studio, create a new SQL Server database, PUBS, on your local default instance of SQL Server, and configure it to use SQL Server authentication by providing a user ID and password. See your SQL Server documentation for instructions.

Start Enterprise Developer

If you have closed Enterprise Developer since completing Tutorial: Reusing Existing COBOL Programs in a Java Environment, open it again; then open the Java EE perspective and the Project Explorer.

Define a SQL Server Connection

Using the Data Source Explorer in Eclipse, create a JDBC connection to the SQL Server PUBS database.

From the Main Menu, select Window > Show View > Other > Data Management > Data Source Explorer.
In the Data Source Explorer, right-click Database Connections; then select New from the context menu.
Select the SQL Server profile type from the list; then click Next.
Click New Driver Definition .
On the Name/Type tab, select the Microsoft SQL Server 2008 JDBC Driver template.
In the Driver name field, type a unique name to identify the connection.
On the JAR List tab, click Add JAR/Zip.
Browse to the directory where the sqljdbc4.jar is installed; then double-click the file to select it.
This places the full path and file onto the Driver files list.
If the Driver files list contains an sqljdbc.jar file, select the file; then click Remove JAR/Zip.

On the Properties tab complete the properties specifications as follows:

Property	Value
Connection URL	`jdbc:sqlserver://localhost:1433;databaseName=PUBS`
Database Name	`PUBS`
Driver Class	`com.microsoft.sqlserver.jdbc.SQLServerDriver`
Password	`SQLServerPassword`
User ID	`SQLServerUserID`

Where SQLServerUserID and SQLServerPassword are the user ID and password you supplied for authentication when creating the PUBS SQL Server database.

Click OK.
This returns you to the New Connection Profile dialog box.
Check Save password.
Click Test Connection to verify the connection.
When the connection succeeds, clear the message box; then click Finish to save the connection and close the New Connection Profile dialog box.

Add an SQL Script

We provide an SQL script for you to run against the PUBS database. This script creates the required table and loads it with data. Because you run the script from the Project Explorer, you must first add it to the project.

In the Project Explorer, expand CobolBook.
Right-click src; then select New > Other.
Expand General; then select File.
Click Next.
In the File name field, type SetupSQLBooks.sql; then click Finish.
This opens the empty SetupSQLBooks.sql file in the file editor.

Copy the following SQL script and paste it into the SetupSQLBooks.sql file:

USE PUBS;
GO

CREATE TABLE BOOKS (
	TITLE		VARCHAR(50) NOT NULL,
	TYPE		VARCHAR(20) NOT NULL,
	AUTHOR		VARCHAR(50) NOT NULL,
	STOCKNO 		CHAR(4) PRIMARY KEY,
	ISBN    		DECIMAL (13, 0) NOT NULL, 
	RETAIL		DECIMAL(4,2) NOT NULL,
    	ONHAND		INT NOT NULL,
    	SOLD		INT NOT NULL
)

INSERT INTO BOOKS VALUES(
	'OLIVER TWIST',
	'CHARLES DICKENS',
	'CLASSIC',
	'1111',
	9780140620467,
	5.00,
	10,
	30
)

INSERT INTO BOOKS VALUES(
	'A GAME OF THRONES',
	'GEORGE R. R. MARTIN',
	'FANTASY',
	'1112',
	7428545,
	3.86,
	17,
	75
)

INSERT INTO BOOKS VALUES(
	'A CLASH OF KINGS',
	'GEORGE R. R. MARTIN',
	'FANTASY',
	'1113',
	7447833,
	6.49,
	17,
	75
)

INSERT INTO BOOKS VALUES(
	'THE DAY OF THE JACKAL',
	'FREDERICK FORSYTH',
	'ADVENTURE',
	'1114',
	99552710,
	2.00,
	26,
	75
)

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE PHILOSOPHER''S STONE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1116',
	747558191,
	5.24,
	48,
	100
)

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE CHAMBER OF SECRETS',
	'J. K. ROWLING',
	'ADVENTURE',
	'1117',
	747562180,
	5.24,
	44,
	100
)

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE PRISONER OF AZKABAN',
	'J. K. ROWLING',
	'ADVENTURE',
	'1118',
	747573760,
	5.24,
	49,
	100
)

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE GOBLET OF FIRE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1119',
	747582386,
	6.74,
	44,
	100
)

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE ORDER OF THE PHOENIX',
	'J. K. ROWLING',
	'ADVENTURE',
	'1120',
	747591261,
	6.74,
	37,
	100
)

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE HALF-BLOOD PRINCE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1121',
	747598460,
	6.74,
	41,
	100
)

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE DEATHLY HALLOWS',
	'J. K. ROWLING',
	'ADVENTURE',
	'1122',
	1408810298,
	6.74,
	40,
	100
)

INSERT INTO BOOKS VALUES(
	'THE FELLOWSHIP OF THE RING',
	'J. R. R. TOLKIEN',
	'FANTASY',
	'1123',
	7123825,
	4.95,
	23,
	100
)

INSERT INTO BOOKS VALUES(
	'THE TWO TOWERS',
	'J. R. R. TOLKIEN',
	'FANTASY',
	'1124',
	261102362,
	7.99,
	28,
	100
)

INSERT INTO BOOKS VALUES(
	'LORD OF THE FLIES',
	'WILLIAM GOLDING',
	'SCARY',
	'2222',
	9780571191475,
	4.50,
	30,
	100
)

INSERT INTO BOOKS VALUES(
	'CATCH 22',
	'JOSEPH HELLER',
	'WAR',
	'3333',
	9780099477310,
	6.50,
	50,
	200
)

INSERT INTO BOOKS VALUES(
	'THE HITCHHIKER''S GUIDE TO THE GALAXY',
	'DOUGLAS ADAMS',
	'COMEDY',
	'4444',
	9780345453747,
	6.73,
	100,
	360
)

INSERT INTO BOOKS VALUES(
	'TO KILL A MOCKINGBIRD',
	'HARPER LEE',
	'CLASSIC',
	'5555',
	9780099466734,
	4.87,
	50,
	75
)

SELECT * FROM BOOKS

Click File > Save to save the SQL script.

Execute the SQL Script

In the Eclipse file editor window that contains the SetupSQLBooks.sql file, use the drop-down list at the top to set the Type to SQL Server_2008.
Select the name of your SQL Server connection from the Name drop-down list.
Right-click on any blank area in the body of the script; then select Execute All from the context menu.