Posts Tagged Adventure Works

Connecting Java EE RESTful Web Services to Microsoft SQL Server Using NetBeans and GlassFish

Connecting Java EE RESTful web services, hosted on GlassFish, to Microsoft SQL Server – a high level overview. Demonstrate the creation of a Web Application project in NetBeans, including a SQL Server data source, entity classes from a SQL database, and RESTful web services. Show how to test and deploy the project to GlassFish.

Introduction

In a previous post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, I demonstrated how Microsoft’s JDBC Driver 4.0 can connect Java-based RESTful web services to Microsoft SQL Server. In a more recent post, Calling Microsoft SQL Server Stored Procedures from a Java Application Using JDBC, I demonstrated the use of JDBC to call stored procedures from a Java application. In this post, I am going to offer a high-level, end-to-end overview on how to create and connect Java EE RESTful web services, hosted on GlassFish, to SQL Server. The goals of post are:

  • Demonstrate the use of Microsoft’s JDBC Driver 4.0 to connect a Java-based application to SQL Server 2008 R2;
  • Demonstrate the use of NetBeans to:
    • Create a SQL Server Data Source;
    • Create entity classes from the SQL Server database using the SQL Server Data Source;
    • Create RESTful web services using JAX-RS, which communicate with database, through the entity classes;
    • Compile and deploy the data source, entities, and services to GlassFish;
  • Test the RESTful Web Services locally in NetBeans, and once deployed, in GlassFish.

Setting up the Post’s Example

To demonstrate the example in this post, I have the follow applications installed, configured, and running in my development environment:

If you have any questions about installing and configuring the Adventure Works database, please refer to my post, Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3. The post takes you through creating a SQL Server 2008 R2 instance (entitled ‘Development’), installing the Adventure Works database, and creating a database user (‘aw-dev’). Also, refer to my earlier post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, for directions on installing the Microsoft JDBC driver in the lib directory of GlassFish. This is necessary before following along with this demonstration.

Note, since I have NetBeans, SQL Server, and GlassFish all installed on a single computer, the URLs in several of the screen-grabs switch between ‘localhost’ and my computer’s actual name. Both references are interchangeable.

The Demonstration

Here are the high-level steps I will walk-through in this post:

  1. Confirm the SQL Server instance, database, and user are functioning properly;
  2. Create a new Web Application project in NetBeans;
  3. Create the SQL Server data source in the project;
  4. Create entity classes from the SQL Server database;
  5. Create RESTful web services using the entity classes;
  6. Test the web services locally in NetBeans;
  7. Build and deploy the project to GlassFish;
  8. Test the web services on GlassFish.

SQL Server

Using Microsoft’s SQL Server Management Studio, Quest’s Toad for SQL, or similar IDE, confirm the ‘Development’ instance (or whatever you have named your instance) of SQL Server 2008 R2 is running. Confirm the Adventure Works database is installed in that instance. Lastly, confirm the ‘aw_dev’ user can connect to the Adventure Works database and view and interact with all the database objects. Confirming these items will elevate many problems you might otherwise encounter when creating the data source, next.

SQL Server 2008 R2 AdventureWorks Database

View of the SQL Server Instance from SSMS. Note the : 1) Server, Instance, User, 2) Database, and 3) Schema

Create New Web Application Project

Create a new Java Web Application project in NetBeans. Name the project whatever you would like; I named mine ‘JdbcSqlWebSrvTest’. Select the correct GlassFish server instance and GlassFish domain where the project will be deployed. I am deploying my project to the default ‘domain1’ domain.

Creating a New Java Web Application Project in NetBeans - 02

Choose the Web Application Project-Type in NetBeans

Creating a New Java Web Application Project in NetBeans - 03

Provide a Project Name and Location

Creating a New Java Web Application Project in NetBeans - 04

Select the Target GlassFish Server and Domain Where the Project Will be Installed

Creating a New Java Web Application Project in NetBeans - 05

Include any Frameworks You Will Use with the Project

Creating a New Java Web Application Project in NetBeans - 06

View of the New Web Application Project in NetBeans

Create SQL Server Data Source and Entity Classes from the Database

Right-click on the project again and select ‘New’ -> ‘Other…’. From the list of Categories, select ‘Persistence’. From the list of Persistence choices, choose ‘Entity Classes from Database’. Click Next.

Creating New Entity Classes from a Database - 01

Choose ‘Entity Classes from Database’

Before we can choose which database tables we want from the Adventure Works database to create entity classes, we must create a connection to the database – a SQL Server Data Source. Click on the Data Source drop down and select ‘New Data Source…’. Give a Java Naming and Directory Interface (JNDI) name for the data source. I called mine ‘AdventureWorks’. Click on the ‘Database Connection’ dropdown, select ‘New Database Connection…’.

Creating New Entity Classes from a Database - 02

Database Tables View Before Data Source is Created

Creating a New Datasource for Entity Classes from a Database - 01

Name the Data Source

This starts the ‘New Connection Wizard’. The first screen, ‘Locate Driver’, is where we point the will instruct NetBeans to use the Microsoft JDBC Driver 4.0 for SQL Server Driver. Locate the sqljdbc4.jar file.

Creating a New Datasource for Entity Classes from a Database - 02

Locate the Microsoft JDBC Driver 4.0 for SQL Server Driver .jar File

On the next screen, ‘Customize the Connection’, input the required SQL Server information. The host is the machine your instance of SQL Server is installed on, such as ‘localhost’. The instance is the name of the SQL Server instance in which the Adventure Works database is installed, such as ‘Development’. Once you complete the form, click ‘Test Connection’. If it doesn’t succeed, check your settings, again.

Creating a New Datasource for Entity Classes from a Database - 03

Provide the SQL Server Adventure Works Database Connection Information

As I mentioned in an earlier post, the SQL Server Data Source forces you to select a single database schema. On the ‘Choose Database Schema’ screen, select the ‘HumanResources’ schema. The database tables you will be able to reference from you entity classes are limited to just this schema, when using this data source. To reference other schemas, you will need to create more data sources.

Creating a New Datasource for Entity Classes from a Database - 04

Select the ‘HumanResources’ Database Schema

Creating a New Datasource for Entity Classes from a Database - 05

New Data Source Complete with JNDI Name and Database Connection Created

Back in the ‘New Entity Classes from Database’ window, you will now have the ‘AdventureWorks’ data source selected as the Data Source. After a few seconds of processing, all ‘Available Tables’ within the ‘HumanResources’ schema are displayed. Choose the four tables shown in the screen-grab, below. Actually, two are database tables and two are virtual tables, called database ‘views’. We will need to do an extra step later on, to use the two entity classes built from the database views.

Creating New Entity Classes from a Database - 03A

Retrieving all the ‘HumanResources’ Schema Database Tables and Views

Creating New Entity Classes from a Database - 03B

All the ‘HumanResources’ Schema Database Tables and Views Available

Creating New Entity Classes from a Database - 04

Selecting the Database Views Throws a Warning Regarding no Entity IDs (Primary Keys)

On the next screen, ‘Entity Classes’, in the ‘New Entity Classes from Database’ window, give a package name to place the individual entity classes into. I chose to call mine ‘entityclasses’.

Creating New Entity Classes from a Database - 05

Provide the Name of the Package Where the Entity Classes Will be Created

On the next screen, ‘Mapping Options’, choose ‘Fully Qualified Database Table Names’. Without this option selected, I have had problems trying to make the RESTful web services function properly. This is also the reason I chose to create the entity classes first, and then create the RESTful web services, separately. NetBeans has an option that combines these two tasks into a single step, by choosing ‘RESTful Web Services from Database’. However, the ‘Fully Qualified Database Table Names’ option is not available on the equivalent screen, using that process (at least in my version of NetBeans 7.2). I prefer the two-step approach.

Creating New Entity Classes from a Database - 06

Select the ‘Fully Qualified Database Table Names’ Option

Click finished. You have successfully created the SQL Server data source and entity classes.

The data source we created, which will be deployed to GlassFish, is referred to as a JDBC Resource and a JDBC Connection Pool. This JDBC information is stored in the ‘glassfish-resources.xml’ file by NetBeans.

New Glassfish JDBC Connection Pool and Resource - Source (glassfish-resources.xml)

New GlassFish JDBC Connection Pool and JDBC Resource Contained in the glassfish-resources.xml File

The JPA Persistence Unit is found in the ‘persistence.xml’ file in the ‘Configuration Files’ folder. This file describes the Persistence Unit (PU). The PU serves to register the project’s four persistable entity classes, which are referred to by JPA as managed classes.

JPA Configuration - Design View (persistence.xml)

persistence.xml Design View

JPA Configuration - Source (persistence.xml)

persistence.xml Source View

RESTful Web Services from Entity Classes

Now that have a SQL Server Data Source and our entity classes, we will create the RESTful web services. Right-click on the project and select ‘New’ -> ‘Other…’ -> ‘Persistence’ -> ‘RESTful Web Services from ‘Entity Classes’. You will see a list of four entity classes we just created, from which to choose. Add all four entity classes.

Creating New RESTful Web Service from Entity Classes - 01

Creating the New RESTful Web Service from Four Entity Classes

Creating New RESTful Web Service from Entity Classes - 02

Add the Four Entity Classes

On the next screen, give a name for Resource Package to store the service classes in; I called mine ‘service’. That’s it; you now have four RESTful web services and the corresponding Enterprise Beans and Façade service classes. The service class sits between the RESTful web service and the entity class.

Creating New RESTful Web Service from Entity Classes - 03

Provide a Package Location to Place the RESTful Web Service Classes Into

Click finished. You have successfully created the RESTful web services.

Web Application Project with Entities and Services Added - 01

View of Web Application Project with Entity Classes and RESTful Web Services Added

Web Application Project with Entities and Services Added - 02

View of Web Application Project with Entity Classes and RESTful Web Services Added

Adding a Primary Key to Entity Classes

If you recall, I mentioned a problem with the two entity classes we created from the database views. To avoid an error when you build and deploy your project to GlassFish, we need to make a small change to the VEmployee.java and VEmployeeDepartment.java entity classes. Entity classes need a unique identifier, a primary key (or, Entity ID) identified. Since these two entity classes are built from database views, as opposed to database tables, they lack a primary key. To fix this, annotate the ‘businessEntityID’ field with ‘@Id’ in each class. This indicates that ‘businessEntityID’ is the primary key (Entity ID) for this class. The field, ‘businessEntityID’, must contain unique values, for this to work properly. NetBeans will make the suggested correction for you, if you allow it.

Fix Entity Id Error in View Entity Classes - 01

NetBeans Highlights the Entity Id Error in Two View-Based Entity Classes

Fix Entity Id Error in View Entity Classes - 02

Select the ‘businessEntityID’ Field as the Primary Key

Fix Entity Id Error in View Entity Classes - 03

View of Entity Class with ‘@Id’ Annotation Added

Test RESTful Web Services Locally in NetBeans

NetBeans provides an easy way to test the RESTful web services, locally. Right-click on the ‘RESTful Web Services’ project folder within the main project, and select ‘Test RESTful Web Services’. Select the first option, ‘Locally Generated Test Client’, in the ‘Configure REST Test Client’ pop-up window.

Test RESTful Web Service in NetBeans - 01

Choose the Local Test Client Option

NetBeans will open a web browser window and displays the RESTful URI (Universal Resource Identifier) for the services in a tree structure. There are four primary URIs, corresponding to the four services. Each primary URI has additional child URIs grouped under them. Are they URIs or URLs? I found this excellent post that does a very good job explaining the difference between the URL (how to get there) and the URI (the resource), which is part of the URL.

Test RESTful Web Service in NetBeans - 02

You May Get an ActiveX Warning When Using IE to Test the RESTful Web Services

Test RESTful Web Service in NetBeans - 03

View of all the RESTful Web Services Universal Resource Identifiers (URIs)

Click on the ‘entityclasses.employee’ URI. Choose the HTTP ‘GET()’ request method from the drop-down and click the ‘Test’ button. The service should return a status of ‘200 (OK)’, along with xml output containing information on all the Adventure Works employees. Congratulation, the RESTful web services have just returned data to your browser from the SQL Server Adventure Works database, using the entity classes and data source you created.

Test RESTful Web Service in NetBeans - 04

All Employees Being Successfully Retrieved from the Adventure Works Database

Click on the other URIs to familiarize yourself with the various default resources. Test the employee ‘from/to’ URI by inputting two parameters, test the ‘count’ URI, and try changing the MIME type where applicable from XML to JSON and observe the results.

Test RESTful Web Service in NetBeans - 05

A Single Employee Being Successfully Retrieved from the Adventure Works Database Using Input Parameter

Test RESTful Web Service in NetBeans - 06

Count of All Employees Being Successfully Retrieved from the Adventure Works Database

WADL

Note the link in the upper right corner of the above screens, labeled WADL: ‘http://[your_server_path]/JdbcSqlWebSrvTest/webresources/application.wadl’

The WADL (Web Application Description Language) file is the machine-readable XML description of the RESTful web service(s). The WADL file is to RESTful web services, as the WSDL (Web Service Definition Language) file is to non-RESTful, SOA-/SOAP-oriented web services. The WADL provides all the information you need to understand to the various RESTful web service’s resources, and how to call them using their URIs. According to Wikipedia, in the WADL file, ‘the service is described using a set of resource elements. Each resource has param elements to describe the inputs, and method elements which describe the request and response of a resource. The request element specifies how to represent the input, what types are required and any specific HTTP headers that are required. The response describes the representation of the service’s response, as well as any fault information, to deal with errors.’ You can download the WADL file (application.wadl), and review it in an XML-friendly viewer such as Notepad++.

View of WADL - application.wadl

View of the RESTful Web Services’ WADL File – application.wadl

Deploy Project to GlassFish

Now that the RESTful web services are working properly from within NetBeans, we can deploy them to GlassFish. To deploy the project to GlassFish, right-click on the main project icon in the Projects tab and select ‘Clean and Build’. Once the project builds successfully, right-click again and select ‘Deploy’. This will instruct Apache Ant to deploy the project as a .war file to GlassFish, using the project’s default Ant deploy task. The SQL Server data source will also be installed into GlassFish.

Once the deployment is complete, switch to GlassFish and refresh the home page if necessary. Under the ‘Applications’ item on the left-hand navigation menu, you should see a new application with the same name as your project, ‘JdbcSqlWebSrvTest’.

GlassFish 01 - Prior to Deploying New Application

Default View of GlassFish Domain Prior to Deploying the New Web Application

GlassFish 02 - New Application Deployed to GlassFish

New Web Application and JDBC Resource and Pool Deployed Successfully to GlassFish

Also, under the ‘JDBC’ -> ‘JDBC Resources’ item, you should see a resource with the same name as the data source you created in NetBeans, ‘AdventureWorks’. Under the ‘JDBC’ -> ‘JDBC Connection Pools’, you should see a pool entitled ‘microsoft_sql_AdventureWorks_aw_devPool’. The JDBC Resource, ‘AdventureWorks’, is linked to this pool. The pool is a ‘javax.sql.DataSource’ resource type, which references the ‘com.microsoft.sqlserver.jdbc.SQLServerDataSource’. This data source is identical to the data source you built in NetBeans.

GlassFish 03 - New Datasource (Resource) Deployed to GlassFish

New JDBC Resource Successfully Deployed to GlassFish

GlassFish 04 - New JDBC Connection Pool

New JDBC Connection Pool Successfully Deployed to GlassFish

Test Web Services on GlassFish

To test the RESTful web services from GlassFish, begin by clicking on the ‘JdbcSqlWebSrvTest’ application, under ‘Applications’ menu item. On the Applications page, click on the ‘Launch’ action link. GlassFish open a new web browser window, and presents you with two ‘Web Application Links’, one link is HTTP and the other, HTTPS. Click on the HTTP link. This should display the default index.jsp page’s ‘Hello World!’ message.

Test RESTful Web Service in GlassFish - 01

Default Response from Application

To call the service, append the current URL to match the resource URIs you used when testing the services in NetBeans. For example, to display all the employees again like you did in NetBeans, append the current URL, http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/, to include the following:

http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee

This URI should return the same xml content you observed when testing this same URI locally in NetBeans.

Test RESTful Web Service in GlassFish - 02

All Employees Being Successfully Retrieved from the Adventure Works Database

As another test, append the URI to also include the Id of a single employee, as follows:

http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee/2

This should cut the amount of data returned from the Adventure Works database to a single employee record.

Test RESTful Web Service in GlassFish - 03

A Single Employee Being Successfully Retrieved from the Adventure Works Database Using a Parameter

One last test, remove the number two from the URI and add the word ‘count’, as follows:

http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee/count

This time, you should see a single integer returned to the browser, representing the count of all employees in the database’s employee table.

Test RESTful Web Service in GlassFish - 04

Count of All Employees Being Successfully Retrieved from the Adventure Works Database

Conclusion

Congratulations, the Java EE RESTful web services have been successfully deployed to GlassFish. The services are connecting to Adventure Works SQL Server database, through the entity classes and data source, and returning data to your web browser! Next step is to create a RESTful web services client application, to display the data returned by the services and/or to perform CRUD operations on the database.

, , , , , , , , , , , , , , , , , , , ,

15 Comments

Calling Microsoft SQL Server Stored Procedures from a Java Application Using JDBC

Demonstrate the use of the JDBC to call stored procedures from a Microsoft SQL Server database and return data to a Java-based console application.

Console Output of Demonstration

Update 07/10/2015: All source code now on GitHub
Update 09/08/2020: Major source code update on GitHub:
MS SQL 2017, JDBC 8.4, Java 11, Gradle

Introduction

Enterprise software solutions often combine multiple technology platforms. Accessing an Oracle database via a Microsoft .NET application, or accessing Microsoft’s SQL Server from a Java-based application is common practice. In an earlier post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, I discussed the use of the Microsoft JDBC Driver 4.0 for SQL Server to connect a Java-based RESTful web service application to a Microsoft SQL Server database. In this post, I will demonstrate the use of JDBC (Java Database Connectivity) to call stored procedures from a Microsoft SQL Server 2008 R2 database and return data to a Java-based console application. The objectives of this post include:

  • Demonstrate the differences between using static SQL statements and stored procedures to return data
  • Demonstrate the use of three types of JDBC statements to return data: Statement, PreparedStatement, and CallableStatement
  • Demonstrate how to call stored procedures with input and output parameters
  • Demonstrate how to return single values and a result set from a database using stored procedures

Why Stored Procedure?

To access data, many enterprise software organizations require their developers to call stored procedures within their code as opposed to executing static SQL statements against the database. There are several common reasons stored procedures are preferred:

  • Optimization – Stored procedures are often written by DBAs or developers who specialize in database development. They understand the best way to construct queries for optimal performance and minimal load on the database server. Think of it as a developer using an API to interact with the database.
  • Safety and Security – Stored procedures are considered safer and more secure than static SQL statements. The stored procedure provides tight control over the content of the queries, preventing malicious or unintentionally destructive code from being executed against the database.
  • Error Handling – Stored procedures can contain logic for handling errors before they bubble up to the application layer and possibly to the end-user.

Adventure Works 2008 Database

For brevity, I will use an existing SQL Server instance and database I’ve already created for recent series of blog posts. The first post in that series, Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3, covers the creation of a SQL Server 2008 R2 instance and installation of Microsoft’s Adventure Works database. The database comes prepopulated with plenty of data for demonstration purposes. In addition to the SQL Server instance and database, I will also use the ‘aw_dev’ user account created in that post. However, for this post, we will need to add additional permission to call or ‘Execute’ the database queries.

Add Execute Permission to User

Lastly, I have added four stored procedures to the Adventure Works database to use in this post. In order to follow along, you will need to install these stored procedures. The SQL install scripts are included in the downloadable code. See the SQL statements below.

View of New Stored Procedures in Database
View of New Stored Procedures in Database

Data Sources, Connections, and Properties

After adding the Microsoft JDBC Driver 4.0 for SQL Server to the project, we create a SQL Server data source (com.microsoft.sqlserver.jdbc.SQLServerDataSource) and database connection (java.sql.Connection). There are several patterns for creating and working with data sources and connections. This post does not necessarily focus on the best practices for creating or using either. In this example, the application instantiates a connection class (SqlConnection.java), which in turn instantiates the java.sql.Connection and com.microsoft.sqlserver.jdbc.SQLServerDataSource objects. The data source’s properties are supplied from an instance of a singleton class (ProjectProperties.java). This properties class instantiates the java.util.Properties class and reads values from an XML property file (properties.xml). On startup, the application creates the database connection, calls each of the example methods, and then closes the connection.

New Java Project in NetBeans
New Java Project in NetBeans
Add JDBC Driver 4.0 Jar File
Add JDBC Driver 4.0 Jar File

Examples

For each example, I will show the stored procedure, if applicable, followed by the Java method that calls the procedure or executes the static SQL statement. I have left out all the data source and connection code in the article. Again, a complete copy of all the code for this article is available on GitHub. The GitHub project contains the complete NetBeans project, including some basic JUnit tests, in a zipped package. A second zipped package contains the SQL scripts necessary to create the four stored procedures used in the examples.

Example 1: A Simple Statement

Before jumping into stored procedures, we’ll start with a simple static SQL statement. This example’s method uses the java.sql.Statement class. According to the Oracle documentation, the Statement object is used for executing a static SQL statement and returning the results it produces. This SQL statement calculates the average weight of all products in the Adventure Works database that contain a weight. It returns a solitary double value. This example demonstrates one of the simplest methods for returning data from SQL Server.

    /**
     * Statement example, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightST() {
        double averageWeight = 0;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT ROUND(AVG([Weight]), 2)";
            sql += " FROM Production.Product";
            sql += " WHERE ([Weight] > 0)";
            sql += " AND (WeightUnitMeasureCode = 'LB')";
            stmt = connection.getConnection().createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                averageWeight = rs.getDouble(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 2: The Prepared Statement

Next, we’ll execute almost the same static SQL statement as Example 1. The only change is the addition of  ‘AS averageWeight’. This allows us to parse the results by column name, making the code easier to understand as opposed to using the numeric index of the column as in Example 1. Instead of using the java.sql.Statement class, we use the java.sql.PreparedStatement class. According to Oracle’s documentation, a SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

    /**
     * PreparedStatement example, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightPS() {
        double averageWeight = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT ROUND(AVG([Weight]), 2) AS averageWeight FROM";
            sql += " Production.Product";
            sql += " WHERE ([Weight] > 0)";
            sql += " AND (WeightUnitMeasureCode = 'LB')";
            pstmt = connection.getConnection().prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                averageWeight = rs.getDouble("averageWeight");
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 3: The Callable Statement

In this example, the average product weight query has been moved into a stored procedure. The procedure is identical in functionality to the static statement in the first two examples. To call the stored procedure, we use the java.sql.CallableStatement class. Again according to Oracle’s documentation, the CallableStatement extends PreparedStatement. It is the interface used to execute SQL stored procedures. The CallableStatement accepts both input and output parameters, however, this simple example does not use either. Like the previous two examples, the procedure returns a single double number value.

CREATE PROCEDURE [dbo].[uspGetAverageProductWeight]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ROUND(AVG([Weight]), 2)
    FROM Production.Product
    WHERE ([Weight] > 0) AND (WeightUnitMeasureCode = 'LB')
END
GO

    /**
     * CallableStatement, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightCS() {
        CallableStatement cstmt = null;
        double averageWeight = 0;
        ResultSet rs = null;
        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call uspGetAverageProductWeight}");
            cstmt.execute();
            rs = cstmt.getResultSet();
            if (rs.next()) {
                averageWeight = rs.getDouble(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 4: Calling a Stored Procedure with an Output Parameter

In this example, we use almost the same stored procedure as in Example 3. The only difference is the inclusion of an output parameter. This time, instead of returning a result set with a value in a single unnamed column, the column has a name – ‘averageWeight’. We can now call that column by name when retrieving the value.

The stored procedure patterns found in Examples 3 and 4 are both commonly used. One procedure uses an output parameter and one not, both return the same value(s). You can use the CallableStatement to for either type.

CREATE PROCEDURE [dbo].[uspGetAverageProductWeightOUT]
    @averageWeight DECIMAL(8,2) OUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @averageWeight = ROUND(AVG([Weight]), 2)
    FROM Production.Product
    WHERE ([Weight] > 0) AND (WeightUnitMeasureCode = 'LB')
END
GO

    /**
     * CallableStatement example, (1) output parameter, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightOutCS() {
        CallableStatement cstmt = null;
        double averageWeight = 0;

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call dbo.uspGetAverageProductWeightOUT(?)}");
            cstmt.registerOutParameter("averageWeight", java.sql.Types.DECIMAL);
            cstmt.execute();
            averageWeight = cstmt.getDouble("averageWeight");
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 5: Calling a Stored Procedure with an Input Parameter

Enough calculating the average weight of products. Let’s move onto another stored procedure. In this new example, the procedure returns a result set (java.sql.ResultSet) of employees whose last name starts with a particular sequence of character (e.g., ‘M’ or ‘Sta’).  The sequence of characters is passed as an input parameter to the stored procedure using the CallableStatement, again.

The method making the call iterates through the rows of the result set returned by the stored procedure, concatenating multiple columns to form the employee’s full name as a string. Each full name string is then added to an ordered collection of strings, a List<String> object. The List instance is returned by the method. You will notice this procedure takes a little longer to run because of the use of the ‘LIKE’ operator. The database server has to perform pattern matching on each last name value in the table to determine the result set.

CREATE PROCEDURE [HumanResources].[uspGetEmployeesByLastName]
    @lastNameStartsWith VARCHAR(20) = 'A'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Title, FirstName, MiddleName, LastName, Suffix
    FROM Person.Person
    WHERE (PersonType = 'EM') AND (LastName LIKE @lastNameStartsWith + '%')
END
GO

    /**
     * CallableStatement example, (1) input parameter, db returns ResultSet
     *
     * @param lastNameStartsWith
     * @return List<String> of employee names
     */
    public List<String> getEmployeesByLastNameCS(String lastNameStartsWith) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List<String> employeeFullName = new ArrayList<>();

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call HumanResources.uspGetEmployeesByLastName(?)}",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            cstmt.setString("lastNameStartsWith", lastNameStartsWith);
            boolean results = cstmt.execute();
            int rowsAffected = 0;

            // Protects against lack of SET NOCOUNT in stored prodedure
            while (results || rowsAffected != -1) {
                if (results) {
                    rs = cstmt.getResultSet();
                    break;
                } else {
                    rowsAffected = cstmt.getUpdateCount();
                }
                results = cstmt.getMoreResults();
            }
            while (rs.next()) {
                employeeFullName.add(
                        rs.getString("LastName") + ", "
                        + rs.getString("FirstName") + " "
                        + rs.getString("MiddleName"));
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return employeeFullName;
    }

Example 6: Converting a Result Set to Ordered Collection of Objects

In this last example, we pass two input parameters, product color and product size, to a slightly more complex stored procedure. The stored procedure returns a result set containing several columns of product information. This time, the example’s method iterates through the result set returned by the procedure and constructs an ordered collection of products, a List<Product> object. The Product objects in the list are instances of the Product.java POJO class. The method converts each column/row value (key/value pair) into a Product property (i.e. Product.SizeProduct.Model). This is a common pattern for persisting data from a result set in the application. The  Product.java class is part of the project code you can download.

CREATE PROCEDURE [Production].[uspGetProductsByColorAndSize]
    @productColor VARCHAR(20),
    @productSize INTEGER
AS
BEGIN
    SET NOCOUNT ON;

    SELECT p.Name AS [Product], p.ProductNumber, p.Color, p.Size, m.Name AS [Model]
    FROM Production.ProductModel AS m INNER JOIN
        Production.Product AS p ON m.ProductModelID = p.ProductModelID
    WHERE (p.Color = @productColor) AND (p.Size = @productSize)
    ORDER BY [Model], [Product]
END
GO

    /**
     * CallableStatement example, (2) input parameters, db returns ResultSet
     *
     * @param color
     * @param size
     * @return List<Product> of Product objects
     */
    public List<Product> getProductsByColorAndSizeCS(String color, String size) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List<Product> productList = new ArrayList<>();

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call Production.uspGetProductsByColorAndSize(?, ?)}",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            cstmt.setString("productColor", color);
            cstmt.setString("productSize", size);
            boolean results = cstmt.execute();
            int rowsAffected = 0;

            // Protects against lack of SET NOCOUNT in stored prodedure
            while (results || rowsAffected != -1) {
                if (results) {
                    rs = cstmt.getResultSet();
                    break;
                } else {
                    rowsAffected = cstmt.getUpdateCount();
                }
                results = cstmt.getMoreResults();
            }

            while (rs.next()) {
                Product product = new Product(
                        rs.getString("Product"),
                        rs.getString("ProductNumber"),
                        rs.getString("Color"),
                        rs.getString("Size"),
                        rs.getString("Model"));
                productList.add(product);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return productList;
    }

Schema Reference

You’ll notice in Example 4, I refer to the dbo schema ({call dbo.uspGetAverageProductWeightOUT(?)}). In Examples 3, I did not refer to the dbo schema. Since this stored procedure is part of the dbo schema and the dbo schema is also the default schema for this database, I did not have to reference it. However, according to Microsoft, it is always good practice to refer to database objects by a schema name and the object name, separated by a period. That includes the default schema. You must always refer to the schema if it is not the default schema.

Running the Examples

The application has a main method that runs all six examples. It will display the name of the method being called, the duration of time it took to retrieve the data, and the results returned by the method.

package com.articles.examples;

import java.util.List;

/**
 * Main class that calls all example methods
 *
 * @author Gary A. Stafford
 */
public class RunExamples {

    private static Examples examples = new Examples();
    private static ProcessTimer timer = new ProcessTimer();

    /**
     * @param args the command line arguments
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        System.out.println("");
        System.out.println("SQL SERVER STATEMENT EXAMPLES");
        System.out.println("======================================");

        // Statement example, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        double averageWeight = examples.getAverageProductWeightST();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightST");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // PreparedStatement example, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightPS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightPS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightCS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement example, (1) output parameter, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightOutCS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightOutCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement example, (1) input parameter, db returns ResultSet
        timer.setStartTime(System.nanoTime());
        String lastNameStartsWith = "Sa";
        List<String> employeeFullName =
                examples.getEmployeesByLastNameCS(lastNameStartsWith);
        timer.setEndTime(System.nanoTime());
        System.out.println("GetEmployeesByLastNameCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Last names starting with '"
                + lastNameStartsWith + "': " + employeeFullName.size());
        if (employeeFullName.size() > 0) {
            System.out.println("Last employee found: "
                    + employeeFullName.get(employeeFullName.size() - 1));
        } else {
            System.out.println("No employees found with last name starting with '"
                    + lastNameStartsWith + "'");
        }
        System.out.println("");

        // CallableStatement example, (2) input parameters, db returns ResultSet
        timer.setStartTime(System.nanoTime());
        String color = "Red";
        String size = "44";
        List<Product> productList =
                examples.getProductsByColorAndSizeCS(color, size);
        timer.setEndTime(System.nanoTime());
        System.out.println("GetProductsByColorAndSizeCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        if (productList.size() > 0) {
            System.out.println("Products found (color: '" + color
                    + "', size: '" + size + "'): " + productList.size());
            System.out.println("First product: "
                    + productList.get(0).getProduct()
                    + " (" + productList.get(0).getProductNumber() + ")");
        } else {
            System.out.println("No products found with color '" + color
                    + "' and size '" + size + "'");
        }
        System.out.println("");

        examples.closeConnection();
    }
}
Results with Plans Cached
Results of Running the Application

SQL Statement Performance

Although this post is not about SQL performance, I’ve added a timer feature (ProcessTimer.java class) to capture the duration of time each example takes to return data, measured in milliseconds. The  ProcessTimer.java class is part of the project code you can download. You will observe significant differences between the first four examples, even though they all basically perform the same function and return the same information. The time differences are a result of several factors, primarily pre-compilation of the SQL statements and SQL Server plan caching. The effects of these two factors are easily demonstrated by clearing the SQL Server plan cache (see SQL script below) and then running the application twice in a row. The second time, pre-compilation and plan caching should result in significantly faster times for the prepared statements and callable statements, in Examples 2–6. Differences of 2x, and up to 80x can be observed in the actual output from my machine, below.

DBCC FREEPROCCACHE;
GO

CHECKPOINT;
GO

DBCC DROPCLEANBUFFERS;
GO

C:\Users\gstaffor>java -jar
"C:\Users\gstaffor\Documents\NetBeansProjects\
JdbcStoredProcsExample\dist\JdbcStoredProcsExample.jar"

SQL SERVER STATEMENT EXAMPLES
======================================
GetAverageProductWeightST
Duration (ms): 402
Average product weight (lb): 13.48

GetAverageProductWeightPS
Duration (ms): 26
Average product weight (lb): 13.48

GetAverageProductWeightCS
Duration (ms): 35
Average product weight (lb): 13.48

GetAverageProductWeightOutCS
Duration (ms): 1620
Average product weight (lb): 13.48

GetEmployeesByLastNameCS
Duration (ms): 1108
Last names starting with 'Sa': 7
Last employee found: Sandberg, Mikael Q

GetProductsByColorAndSizeCS
Duration (ms): 56
Products found (color: 'Red', size: '44'): 7
First product: HL Road Frame - Red, 44 (FR-R92R-44)

C:\Users\gstaffor>java -jar
"C:\Users\gstaffor\Documents\NetBeansProjects\
JdbcStoredProcsExample\dist\JdbcStoredProcsExample.jar"

SQL SERVER STATEMENT EXAMPLES
======================================
GetAverageProductWeightST
Duration (ms): 57
Average product weight (lb): 13.48

GetAverageProductWeightPS
Duration (ms): 14
Average product weight (lb): 13.48

GetAverageProductWeightCS
Duration (ms): 8
Average product weight (lb): 13.48

GetAverageProductWeightOutCS
Duration (ms): 20
Average product weight (lb): 13.48

GetEmployeesByLastNameCS
Duration (ms): 42
Last names starting with 'Sa': 7
Last employee found: Sandberg, Mikael Q

GetProductsByColorAndSizeCS
Duration (ms): 8
Products found (color: 'Red', size: '44'): 7
First product: HL Road Frame - Red, 44 (FR-R92R-44)

Conclusion

This post has demonstrated several methods for querying and calling stored procedures from a SQL Server database using JDBC with the Microsoft JDBC Driver 4.0 for SQL Server. Although the examples are quite simple, the same patterns can be used with more complex stored procedures, with multiple input and output parameters, which not only select, but insert, update, and delete data.

As with any non-Oracle database driver, there are some limitations of the Microsoft JDBC Driver 4.0 for SQL Server you should be aware of by reading the documentation. However, for most tasks that require database interaction, the driver provides adequate functionality with SQL Server. The best place to learn more about using the driver is the help files that come with the driver. The path to the help files, relative to the root of the driver’s parent folder is [path to folder]\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\help\default.htm.

, , , , , , , , , , , , , , ,

13 Comments

Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3

Objectives of 3-Part Series:

Part I: Setting up the Example Database and Visual Studio Projects

  • Setup and configure a new instance of SQL Server 2008 R2
  • Setup and configure a copy of Microsoft’s Adventure Works database
  • Create and configure both a Visual Studio 2010 server project and Visual Studio 2010 database project
  • Test the project’s ability to deploy changes to the database

Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT

  • Convert the Adventure Works Visual Studio 2010 database and server projects to SSDT projects
  • Create a second Solution configuration and SSDT publish profile for an additional database environment
  • Test the converted database project’s ability to publish changes to multiple database environments

Part III: Automate the Building and Publishing of the SSDT Database Project Using Jenkins

  • Automate the build and delivery of a sql change script artifact, for any database environment, to a designated release location using a parameterized build.
  • Automate the build and publishing of the SSDT project’s changes directly to any database environment using a parameterized build.

Background

Microsoft’s Visual Studio 2010 (VS 2010) IDE has been available to developers since April, 2010. Microsoft’s SQL Server 2008 R2 (SQL 2008 R2) has also been available since April, 2010. If you are a modern software development shop or in-house corporate development group, using the .NET technology stack, you probably use VS 2010 and SQL 2008 R2. Moreover, odds are pretty good that you’ve implemented a Visual Studio 2010 Database Project SQL Server Project to support what Microsoft terms a Database Development Life Cycle (DDLC).

Now, along comes SSDT. Recently, along with the release of SQL Server 2012, Microsoft released SQL Server Data Tools (SSDT). Microsoft refers to SSDT as “an evolution of the existing Visual Studio Database project type.” According to Microsoft, SSDT offers an integrated environment within Visual Studio 2010 Professional SP1 or higher for developers to carry out all their database design work for any SQL Server platform (both on and off premise). The term ‘off premises’ refers to SSDT ‘s ability to handle development in the Cloud – SQL Azure. SSDT supports all current versions of SQL Server, including SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Azure.

SSDT offers many advantages over the VS 2010 database project type. SSDT provides a more database-centric user-experience, similar to SQL Server Management Studio (SSMS). Anyone who has used VS 2010 database project knows the Visual Studio experience offered a less sophisticated user-interface than SSMS or similar database IDE’s, like Toad for SQL. Installing Microsoft’s free SSDT component, after making sure you have SP1 installed for VS 2010 Professional or higher, you can easily convert you VS 2010 database projects to the new SSDT project type. The conversion offers a better development and deployment experience, and prepare you for an eventual move to SQL Server 2012.

Part I: Setting up the Example Database and Visual Studio Projects

Setting up the Example

To avoid learning SSDT with a copy of your client’s or company’s database, I suggest taking the same route I’ve taken in this post. To demonstrate how to convert from a VS 2010 database project to SSDT, I am using a copy of Microsoft’s Adventure Works 2008 database. Installed, the database only takes up about 180 MBs of space, but is well designed and has enough data to use as a good training tool it, as Microsoft intended. There are several version of the AdventureWorks2008 database available for download depending on your interests – OLTP, SSRS, Analysis Services, Azure, or SQL 2012. I chose to download the full database backup of the AdventureWorks2008R2 without filestream for this post.

Creating the SQL Server 2008 R2 Instance and Database

Before installing the database, I used the SQL Server Installation Center to install a new instance of SQL Server 2008 R2, which I named ‘Development’. This represents a development group’s database environment. Other environments in the software release life-cycle commonly include Testing, Staging, and Production. Each environment usually has its own servers with their own instances of SQL Server, with its own copy of the databases. Environments can also include web servers, application servers, firewalls, routers, and so forth.

After installing the Development instance, I logged into it as an Administrator and created a new empty database, which I named ‘AdventureWorks’. I then restored the downloaded backup copy of Adventure Works 2008 R2, which I downloaded, to the newly created Adventure Works database.

Creating the Database 01

Creating the New Database

Creating the Database 02

Creating the New Database

Creating the Database 03

Creating the Database: Restoring the Full Backup

Creating the Database 04

Creating the Database: Restoring the Full Backup

Creating the Database 05

Creating the Database: Restoring the Full Backup

You may note some differences between the configuration settings displayed below in the screen grabs and the default configuration of the Adventure Works database. This post is not intended to recommend configuration settings for your SQL Server databases or database projects. Every company is different in the way it configures its databases. The key is to make sure that the configuration settings in your database align with the equivalent configuration settings in the database project in Visual Studio 2010. If not, when you initially publish changes to the database from the database project, the project will script the differences and change the database to align to the project.

Creating the Database 06

Creating the Database: Database Properties

Creating the Server Login and Database User

Lastly in SSMS, I added a new login account to the Development SQL Server instance and a user to the Adventure Works database, named ‘aw_dev’. This user represents a developer who will interact with the database through the SSDT database project in VS 2010. For simplicity, I used SQL authentication for this user versus Windows authentication. I gave the user the minimal permissions necessary for this example. Depending on the types of interactions you have with the database, you may need to extend the rights of the user.

New Database Login 01

New Database Login

New Database Login 02

New Database Login

Two key, explicit permissions must be assigned for the user for SSDT to work properly. First is the ‘view any definition’ permission on the Development instance. Second is the ‘view definition’ permission on the Adventure Works database. These enable the SSDT project to perform a schema comparison to the Adventure Works database, explained later in the post. Lack of the view definition permission is one of the most common errors I’ve seen during deployments. They usually occur after adding a new database environment, database, database user, or continuous integration server.

New Database Login: ‘View any definition’ Permission

New Database Login: ‘View any definition’ Permission

New Database Login 03

New Database Login: ‘View definition’ Permission

Setting up Visual Studio Database Project

In VS 2010, I created a new SQL Server 2008 database project, named ‘AdventureWorks2008’. In the same Visual Studio Solution, I also created a new SQL Server 2008 server project, named ‘Development’. The database projects mirrors the schema of the Adventure Works database, and the server project mirrors the instance of SQL Server 2008 R2 on which the database is housed. The exact details of creating and configuring these two projects are too long for this post, but I have a set of screen grabs, hyperlinked below, to aid in creating these two projects. For the database project, I only showed the screens that are signficantly different then the server project screens to save some space.

Server Project

VS 2010 Server Project 01

VS 2010 Server Project: New Project SQL Server 2008 Wizard

VS 2010 Server Project 02

VS 2010 Server Project: New Project SQL Server 2008 Wizard

VS 2010 Server Project 03

VS 2010 Server Project: Project Properties

VS 2010 Server Project 04

VS 2010 Server Project: Set Database Options

VS 2010 Server Project 06

VS 2010 Server Project: Import Database Schema

VS 2010 Server Project 08

VS 2010 Server Project: Database Connection Properties

VS 2010 Server Project 09

VS 2010 Server Project: Configure Build and Deploy

Database Project

VS 2010 Database Project 01

VS 2010 Database Project: New Project SQL Server 2008 Wizard

VS 2010 Database Project 02

VS 2010 Database Project: Project Properties

VS 2010 Database Project 03

VS 2010 Database Project: Summary

Reference from Database Project to Server Project

After creating both projects, I created a reference (dependency) from the Adventure Works 2008 database project to the Development server project. The database project reference to the server project creates the same parent-child relationship that exists between the Development SQL Server instance and the Adventure Works database. Once both projects are created and the reference made, your Solution should look like the second screen grab, below.

Adding a Reference to the Database Project

Adding a Reference to the Database Project

Database Project Dependencies

Database Project Dependencies

Final View of VS 2010 Solution with both Projects and Reference

Final View of VS 2010 Solution with both Projects and Reference

Creating the Development Solution Configuration

Next, I created a new ‘Development’ Solution configuration. This configuration defines the build and deployment parameters for the database project when it targets the Development environment. Again, in a normal production environment, you would have several configurations, each targeting a specific database environment. In the context of this post, a database environment refers to a unique combination of  servers, server instances, databases, data, and users. For this first example we are only setting up one database environment, Development.

New Development Solution Configuration 01

New Development Solution Configuration

New Development Solution Configuration 02

New Development Solution Configuration

The configuration specifies the parameters specific to the Adventure Works database in the Development environment. The connection string containing the server, instance, and database names, user account, and connection parameters, are all specific to the Development environment. They are different in the other environments – Testing, Staging, and Production.

Testing the Development Configuration

Once the Development configuration was completed, I ran the ‘Rebuild’ command on the Solution, using the Development configuration, to make sure there are no errors or warnings. Next, with the Development configuration set to only create the deployment script, not to create and deploy the changes to the database, I ran the ‘Deploy’ command. This created a deployment script, entitled ‘AdventureWorks2008.sql’, in the ‘sql\Development’ folder of the AdventureWorks2008 database project.

Create a Deployment Script Only

Database Project Configuration Settings to Create a Deployment Script Only

Deployment Script Location in Project

Deployment Script Location in Project

Since I just created both the Adventure Works database and the database project, based on the database, there are no schema changes in the deployment script. You will see ‘filler’ code for error checking and so forth, but no real executable schema changes to the database are present at this point. If you do see initial changes included in the script, usually database configuration changes, I suggest modifying the settings of the database project and/or the database to align to one another. For example, you may see code in the script to change the database’s default cursor from global to local, or vice-versa. Or, you may also see code in the script to the databases recovery model from full to simple, or vice-versa. You should decide whether the project or the database is correct, and change the other one to match. If necessary, re-run the ‘Deploy’ command and re-check the deployment script. Optionally, you can always execute the script with the changes, thus changing the database to match the project, if the project settings are correct.

Testing Deployment

After successfully testing the development configuration and the deployment script, making any configuration changes necessary to the project and/or the database, I then tested the project’s ability to successfully execute a Deploy command against the database. I changed the Development configuration’s deploy action from ‘create a deployment script (.sql)’ to from ‘create a deployment script (.sql) and deploy to the database’. I then ran the ‘Deploy’ command again, and this time the script is created and executed against the database. Since I still had not made any changes to the project, there were no schema changes made to the database. I just tested the project’s ability to create and deploy to the database at this point. Most errors at this stage are insufficient database user permissions (see example, below).

Test Deployment to the Database

Test Deployment to the Database

View Any Definition Permission Error

View Any Definition Permission Error

Testing Changes to the Project

Finally, I tested the project’s ability to make changes to the database as part of the deployment. To do so, I created a simple post-deployment script that changes the first name of a single, existing employee. After adding the post-deployment script to the database project and adding the script’s path to the post-deployment script file, I again ran the ‘Deploy’ command again, still using the Development configuration. This time the deployment script contained the post-deployment script’s contents. When deployed, one record was affected, as indicated in VS 2010 Output tab. I verified the change was successful in the Adventure Works database table, using SSMS.

Test Changes Deployed to Database 02

Simple Post-Deployment Script

Test Changes Deployed to Database 01

Simple Post-Deployment Script

Test Changes Deployed to Database 03

Post-Deployment Script Location in Project

Test Changes Deployed to Database 04

One Row Affected by Deployment this Time

Before Post-Deployment Script was Ran

Before Post-Deployment Script was Deployed

After Post-Deployment Script was Ran

After Post-Deployment Script was Deployed

Conclusion

We now have a SQL Server 2008 R2 database instance representing a Development environment, and a copy of the Adventure Works database, being served from that instance. We have corresponding VS 2010 database and server projects. We also have a new Development Solution configuration, targeting the Development environment. Lastly, we tested the database project’s capability to successfully build and deploy a change to the database.

In Part II of this series, I will show how to convert the VS 2010 database and server projects to SSDT.

, , , , , , , , , , , , , , , , , , ,

5 Comments