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

Wednesday Jul 16, 2008

Installing Sun Java Mobile Enterprise Platform 1.0 (MEP)

I'm on the QA team for MEP and have installed MEP more times than I can count. From my perspective, installing MEP is easy. I'd like to make it easy for you.

The MEP bundle comes with the open-source Application Server (Glassfish 9.1 UR2) and an ant-based installer that supports Solaris, Linux, or Windows. Ant isn't even required on your system, the ant included with GF is used.

The installer is currently CLI based (GUI installer coming in December) but it is simple and does almost everything for you. It installs GF first, then the installer deletes the default appserver domain and replaces it with a new "mep" domain with all the MEP software in it. You just have to respond to the prompts and be a little patient, while all the deployment and configuration processing is being done.

To begin, get the bundle for your platform from the download site. The Solaris bundle is biggest because it contains the binaries for both Solaris Sparc and Solaris x86. Unzip the bundle, go into the top-level directory, and you'll see the three platform install scripts:,, and install-windows.bat. Basically, you execute the appropriate script and answer the prompts.

Before doing the install, make sure your system has:

  • At least 1GB of disk space
  • JDK 6 installed and added to your (root) path
  • JAVA_HOME environment variable set
  • Either MySQL or Oracle installed for use by MEP as the Sync database

For your first MEP installation, I recommend MySQL. With MySQL, the MEP installer is able to create the users and databases required by MEP; with Oracle, you have to do that work yourself. Also, Oracle uses so much system resources it is usually installed on a different system, whereas, MySQL is fine on the same system with MEP.

You can install MEP on a single system (single tier) or across two systems (two tiers). The install script asks you whether you want to do a single tier, tier1, or tier2 install. If you choose to do a two-tier install, you have to do the install twice on different systems, selecting "tier1" on the first system and "tier2" on the second system. For your first MEP installation, I suggest a single-tier.

Initially, the MEP install script does a "-console" (text based) install of GF. Just accept the license, specify the installation directory, and make sure you point to JDK 6 and not 5 (GF can be installed with JDK 5, but MEP 1.0 requires JDK 6).

Here's some tips on installing GF. The password for GF is for the GF Admin Console, not the MEP Admin Console, whose password is "syncpass". Accept the default ports 8080, 8181, and 4848. If you have good reason and specify different ports, make note of them for later reference. If you see other port numbers, it means the default ports are in use. That usually means another app server is running. If that is not intended, you should stop it, so the default easy to remember ports are freed up. When asked if you want to upgrade a previously installed version of the appserver, say NO. The reason GF is bundled with MEP is to provide you with a known quantity on which MEP has been thoroughly tested.

At the end of the GF install, it asks you to press Return to exit the installation program. It means the GF installation. Right after you press Return, the real MEP install begins. Pay attention to the message about the MEP install type. If you thought you were doing a single tier install and it says you are doing a "tier1" or "tier2" install, Ctrl-C out. On windows, the installer has a bad short-term memory and asks you where it installed GF; on Solaris and Linux, you aren't bothered.

Once you get into the MEP phase, try not to make a mistake, you can't back up, you could continue to the end and attempt to correct the problem after, but you'd have to know what you are doing. A better solution is to break out of the install, stop GF (asadmin stop-domain), delete the MEP domain (asadmin delete-domain mepdomain), and then rerun the MEP portion of the install by executing the command

gfdir/lib/ant/bin/ant -DAS_HOME=gfdir -emacs install

The last argument "install" is for a single tier install; you'd substitute "tier1" or "tier2" accordingly.

After confirming the install type, you get another license to accept: this one is for MEP itself. Accept it. When asked, agree to delete the default domain "domain1". It's only a loose end, left because the GF and MEP installs aren't totally integrated.

A most important step is providing your database information. For your first MEP install with MySQL, accept the defaults for host, port 3306, and driver. Then enter the userid and password, you use with the "mysql" command.

The final input is for the GF domain used by MEP. Accept the domain name passed through, but be careful about the ports. The defaults shown are the standard ports: 8080, 8181, and 4848. If you did not use the default ports when installing GF, you will need to specify the port numbers you used.

Now you sit back, while the MEP software is installed and deployed on GF. You'll see GF stopped and started a number of times. At the very end, the install script does a quick test to see if the MEP splash page (i.e., http://localhost:8080/sync) is accessible. You should take it one step further before declaring victory. Browse to the MEP Admin Console (i.e., http://localhost:8080/sync/admin), login with username "admin" and password "syncpass", and REGISTER THE PRODUCT. Thanks!

I'll follow up with blogs on the two-tier install and post-install configuration required to access Siebel.




« July 2016