Friday Jun 12, 2009

Using SQL Server with MEP MusicDB Sample

This entry describes how to take the SGMP (MEP) 1.1 MusicDb sample and get it working with Microsoft SQL Server. The MusicDb sample uses a JDBC database as its "backend". In theory, you should be able to use any JDBC database, but there are some important steps you need to perform to be successful.

Note, I'm only showing you how to change the backend database, not the gateway (sync) database used by MEP. For release 1.1, the installer supports MySQL or Oracle for the gateway database. While it should be possible to use Microsoft SQL Server for both the gateway and backend databases, this blog only covers switching the backend to SQL Server.

The basic procedure is:

  1. Set up the musicdb database and album table in SQL Server

  2. Configure GlassFish to have a JDBC connection pool for SQL Server

  3. Configure the jdbc/musicdb JDBC Resource to use the SQL Server connection pool

  4. Create a MEP user associated with the MusicDb connector

How To Set Up the musicdb Database and album Table in SQL Server

For the default database, MySQL, the installer creates the musicdb database, album table, and musicdbuser user automatically. You have to set up the equivalent if you want to use a different database.

Initially, I used MS SQL Sever 2005 Server Management Studio with SQL Server 2005, but ran into a problem because it doesn't support the DATE data type -- only DATETIME. So I suggest using SQL Server 2008 or later, which does support DATE.

  1. Create a musicdbuser user with password musicdbpass

  2. Create a new database named musicdb owned by musicdbuser

  3. Add a table named album (actually labeled dbo.album) to the musicdb database with the following columns

          name (varchar(30), primary key, not null)
          artist (varchar(30), not null)
          date_published (date, not null)
          rating (int, not null)
          username (varchar(30), not null)
  4. With MS SQLServer Configuration Manager, enable TCP/IP on port 1433 for the static IP address of the machine hosting SQL Server (also, if localhost)

  5. Restart the SQL Server service

Configure GlassFish to have a JDBC Connection Pool for SQL Server and Configure the jdbc/musicdb JDBC Resource to Use the SQL Server Connection Pool

  1. Download Microsoft's 2.0 Driver for SQL Server 2008

  2. Copy the driver sqljdbc4.jar to <GlassFish_HOME>/domains/mep/lib/ext

  3. Restart GlassFish

  4. Login to GlassFish Admin Console (typically localhost:4848)

  5. In left pane tree, select Resources node -> JDBC -> Connection Pool

  6. Click New and enter the following
    name: musicdb_jdbc_pool_sqlserver
    resource type: javax.sql.DataSource
    datasource class:

  7. Select Resources node -> JDBC -> Resource -> jdbc/musicdb
    Note: The MusicDb sample expects this name. If you want to change it, you would have to change the sample code.

  8. Change the name of the JDBC connection pool used by this resource from musicdb_jdbc_pool_mysql to the one you just created for SQL Server, i.e., musicdb_jdbc_pool_sqlserver

  9. Select the newly created musicdb_jdbc_pool_sqlserver node and click Additional Properties then add
    user: musicdbuser
    password: musicdbpass
    serverName: localhost
    portNumber: 1433
    databaseName: musicdb

  10. Click General tab and click Ping. Get Success!

Create a MEP User Associated with the MusicDb Connector

You don't have to do anything else to use the installed ECBO connector or any JAX-RS connectors you have added. The MusicAlbumProvider class has

public static final String MUSICDB_JNDI_DATASOURCE = "jdbc/musicdb";

So all you have to do is switch the JDBC connection pool used by the jdbc/musicdb JDBC Resource node. The Resources -> Connectors -> Connection Pools and Connection Resources for either ECBO or JAX-RS connectors now automatically connect to SQL Server rather than MySQL.

The only other thing you should do before doing syncs to the album table on SQL Server is to create a new user for your ECBO or JAX-RS MusicDb connector. When you do that, the user is validated (the login is checked) against the JDBC data base in effect. In effect, successful user creation is a simple test that your set-up for SQL Server is good.

  1. Login to MEP Admin Console

  2. Click Users tab

  3. Click Create User tab

  4. Fill in the fields as desired (hint: set user to musicdbuser)

  5. For ECBO select the MusicDb connector that comes preinstalled

The quickest way to get up and running with a different database such as SQL Server is to use the presinstalled/configured ECBO MusicDb connector. But if you want to try a musicdb JAX-RS connector, you would have to add the Resources -> Connectors -> Connection Pool and Connection Resource for a JAX-RS connectors in the GF Admin Console, then create a new Connector in the MEP Admin Console before creating a musicdb user associated with that connector. Detailed steps are documented in the Connector User Guide




« August 2016