WebSphere Commerce Development Environment
WebSphere Commerce developer environment comes with a Rational Application Developer, a WebSphere Application Server runtime with an embedded web server and an Apache Derby database. While options are available to switch your database to DB2 or other RDBMS, if you go with default as a developer, you would like to access the database using a client with some GUI capabilities. So what are our options:
- Go with what is available in the product – you either have your server running and access the DB using the in-built web interface or use the interactive SQL scripting tool “ij” for offline access; neither of which is particularly attractive for all uses. For instance, browsing available table and their schema information, accessing history of previous commands executed, exporting results of queries are all cumbersome activities. And on top of this, current setup of Apache Derby allows only one active connection at any time – so if you are connected through ij, your server cannot even start and if your server is running ij will not be able to connect.
- Switch to a full feature DBMS which has proprietary or open source tools available. For example, DB2 comes with a control center that allows you to connect and browse multiple local and remote database schema. You may also use RAD’s “Database Development” perspective to create and manage connections to local and remote databases.
- Setup Rational Application Developer to connect and browse your derby database
- Setup a custom tool (such as SQuirreL) to connect and browse your derby database.
In this blog I shall cover the last two options – setting up your Rational Application developer to connect and browse the included Apache Derby database and later the same using SQuirreL .
Steps for connecting to Apache Derby using Rational Application Developer.
Launch your WebSphere Commerce toolkit
Click on Window > Open Perspective > Other. In the resulting pop-up, select “Database Development” and click OK
This opens the database development perspective with a data source explorer on the left side.
Right click on Database Connections and choose New.
In the Connections Parameters window, Choose Derby in the “Select a database manager” section, type in the following information in the “properties” section:
For e.g. C:\WCDE80\db\mall and jdbc:derby:C:\WCDE80\db\mall respectively.
(Note: Username is not needed for the default database packaged with WSC toolkit, so this string can be anything).
Next, in the JDBC driver section, choose “Derby 10.0 – Other Driver default” from the drop down. Then click on the button with three dots on the right to open the “Specify Jar List” window. Here, if the list is empty click on “Add jar/zip” or if there is a jar already present, then choose the jar and click on “Edit jar/zip” button. Then browse and choose “derby.jar” from your <TOOLKIT>\lib\ folder. Tip: You may choose any of the embedded driver options available since it is really the JAR that is configured against that driver that is important. Choosing the right version of jar is important as the tool will not allow connecting to databases created with newer version of Apache with older version of drivers. Hence choosing the driver that is present within the toolkit is important to keep the version same.
Click OK to come back to the connection parameters window. Here use “Test Connection” to test the connection – this should be successful. If not check the URL and make sure it is connectable from command line using ij interface.
Click Finish. Now you should see the connection in the “Data Source Explorer” section on the left. You can right click and choose connect to connect and browse the schema. You can also right click on the connection and choose “New SQL Script” to type in your own SQLs and choose and execute them. You can keep these handy script files around to use when you need. You can also extract and load data from one database to another (works best if done table by table) – so if you need to get data for specific tables, this is handy to have. And, if at all you need to develop new access beans, they are just a right click away too.
Steps for connecting to Apache Derby using SQuirreL .
This one is mostly following the steps documented in Using SQuirreL SQL Client with Derby
Only thing we need to make sure is choose the right version of derby.jar in the Extra Class Path of the “Apache Embedded Driver” configuration:
A note here: Do just the embedded driver setup. That works with about 10 minutes of effort. Setting up the client driver requires a Derby Network Server and make some involved changes to JDBC connection setup in our toolkit. That is also the prerequisite to allow multiple connections to the database. That is not covered in this blog.
There you go folks – a basic “how to” blog for a change. This was triggered because a new member of my team asked “how to I connect to our project’s DB in local?” and was not convinced with the options available out of the box.