SQLJ Tool Plug-in for WebSphere Studio Application Developer

This article describes the SQLJ Tool plug-in for WebSphere Studio Application Developer. It is similar to the SQLJ Tool in VisualAge for Java, and lets you compile SQLJ files with the DB2 Universal Database SQLJ compiler.

Jane Fung, Senior IT Specialist, IBM Canada Toronto Lab

Jane Fung is a senior IT specialist at IBM Canada Ltd. She has technical sales responsibility on WebSphere Service Registry and Repository, WebSphere Business Services Fabric, and WebSphere TX Extender. Previously, she was part of the WebSphere Integration Developer development team responsible for developing the Business Process Executable Language (BPEL) and Business Rules debuggers. Jane received a bachelor's degree in electrical engineering from the University of Waterloo in year 2000. She has extensive publishing experience, including numerous developerWorks articles. Jane was the lead author of An Introduction to Rational Application Developer: A Guided Tour, an IBM Press book.



Barry Searle, WebSphere Studio Application Developer Migration Tech Lead, IBM Canada Toronto Lab

Barry Searle is the Migration Team Leader for WebSphere Studio Application Developer. He is a Professional Engineer and has worked at the IBM Canada Lab for over ten years on various application development tools. Prior to that, he had many years of industry experience developing command and control systems and leading complex communications development projects. You can reach Barry at searle@ca.ibm.com.



29 October 2002

© Copyright International Business Machines Corporation 2002. All rights reserved.

Introduction

Do you miss the SQLJ Tool in VisualAge® for JavaTM? This article presents an SQLJ Tool plug-in for WebSphere® Studio Application Developer that offers a number of similar features, and lets you compile SQLJ files with the DB2 Universal Database® SQLJ compiler.

Important: The attached, downloadable SQLJ Tool plug-in is not part of WebSphere Studio Application Developer and is not officially supported. It is provided as an example of the extensible nature of the product. Read the license agreement before downloading it, and use it at your own risk.


Installation

The SQLJ Tool plug-in uses the sqlj.exe executable file that comes with your database. sqlj.exe must be functioning in order for the plug-in to work properly.

Prerequisites

  • WebSphere Studio Application Developer V4.03 or V5 (the screen captures below are from V5 Early Availability)
  • sqlj.exe in DB2 UDB must be functioning
  • Windows® platform only

Installation steps

  1. Download com.ibm.sample.sqljtools.zip.
  2. Unzip the file into the /plugins/ directory.
  3. Make sure that sqlj.zip, runtime.zip, and db2java.zip are in the Windows classpath. These zip files are located in the \sqllib\java directory. Also, make sure that \sqllib\bin is in the Windows path.
  4. Make sure javac and java are in the Windows path. For example, if your jdk directory is c:\jdk, then c:\jdk\bin should be in the Windows path.
  5. Start WebSphere Studio Application Developer.
  6. Select Windows => Preference.
  7. Select SQLJ Tools.
  8. Enter the SQLJ executable path in the SQLJ Executable Path text box. The default for DB2 UDB for Windows is assumed to be sqlj.exe.
Figure 1. Preferences for the SQLJ Tool plug-in

Usage scenarios

1. Translating a simple SQLJ file

The SQLJ Tool plug-in uses the SQLJ compiler included with DB2 UDB. It provides function to translate the SQLJ file in the WebSphere Studio Application Developer workbench. If you encounter errors using this plug-in, see Troubleshooting below.

Example:

  1. Create a Java Project named Test and then click Finish.
  2. Download the App.sqlj file.
  3. Import the App.sqlj file into the Test project.
    1. Select the Test project.
    2. Select File => Import.
    3. Select File System and click Next.
    4. Select the directory where the SQLJ file is located.
    5. Check App.sqlj and then click Finish.
  4. Right click on App.sqlj and select SQLJ Tools => Translate:
    Figure 2. Translating an SQLJ file
  5. You will see a dialog box as shown below. Click OK.
    Figure 3. SQLJ translate dialog
  6. After the translation is done, App.java, App.class, and App_SJProfile0.ser are generated. You can see the .class and .ser files in the Navigator view.
  7. To eliminate errors in the generated Java file, include sqlj.zip in the Java build path:
    1. Right-click on the java project and select Properties
    2. Select Java build path and click on the Libraries tab.
    3. Click on Add External Jar and select sqlj.zip. For example, for DB2 UDB, sqlj.zip is usually located in the directory /sqllib/java/.

2. Using a package name

The SQLJ Tool plug-in lets you translate and compile the sqlj file to a package folder. In the Translate SQLJ File dialog, fill in a package name. The tool will create the package folder if it does not already exist. A copy of the sqlj file will also be copied to the package for translation.

Example:

  1. Right-click on App.sqlj and select SQLJ Tools => Translate. You will see a dialog box as shown below.
  2. Enter com.ibm.example for the Package name and click OK.
Figure 4. Translate SQLJ File dialog: Enter package name

3. SQLJ options

The tool lets you set SQLJ options for the translation. To see the available options:

  1. Right-click on App.sqlj and select SQLJ Tools => Translate.
  2. In the SQLJ Options field, enter help. Click OK.
  3. The tool will not translate the file, but will instead display the available help options. The display options may vary depending on your version of DB2.
Figure 5. SQLJ Tool Finish dialog: Help option

To see the translation status, use the -status option, which is the default in the Preferences.

Example:

  1. Right-click on App.sqlj and select SQLJ Tools => Translate.
  2. In the SQLJ Options field, enter -status if it is not already there. Click OK. The status will be displayed in the SQLJ Tool Finish dialog.
Figure 6. SQLJ Tool Finish dialog: Status option

You may want to use certain options for all SQLJ files. You can set these options in the SQLJ Tools Preferences.

4. SQLJ files with errors

The SQLJ translator often reports translation errors, which are displayed in the Finish dialog. They are also logged in the .log file, which you can view in the PDE Error Log view.

Example:

  1. Download Error.sqlj and import it into the Test Project
  2. Right click on Error.sqlj and select SQLJ Tools => Translate. After the translation is done, the error messages are displayed in the SQLJ Tool Finish dialog box:
    Figure 7. SQLJ Tool Finish dialog: error message


    The error messages are also logged in a the file SQLJtranslation.log, which is overwritten on each translation. This log file is created in the project in which the SQLJ translation is done.
  3. Open SQLJtranslation.log with a text editor. Here is what it should look like:
Fri Sep 20 21:45:11 EDT 2002 
==================== 
Translating: Error.sqlj 
==================== 
[Translating 1 files.] 
[Reading file Error] 
Error.sqlj:17.12: Error: Missing semicolon. 
Error.sqlj:17.21: Error: Missing semicolon. 
Error.sqlj:17.29: Error: Unexpected token ']' in Java statement. 
Error.sqlj:28.14: Error: Missing semicolon. 
Error.sqlj:28.39: Error: Missing semicolon. 
Error.sqlj:28.54: Error: Missing semicolon. 
Error.sqlj:28.57: Error: Missing semicolon. 
Error.sqlj:28.60: Error: Missing semicolon. 
Error.sqlj:28.60: Error: Unbalanced curly braces. 
Error.sqlj:35.3: Error: Unbalanced curly braces. 
Error.sqlj:38.3: Error: Java Parsing. Encountered: finally 
Expected: <eof> ; "abstract" ...; "public" ...; "interface" ...; "#sql" ...; ";" ...; 
Total 11 errors. 
</eof>

5. SQLJ files with dependencies

Many SQLJ files are dependent on other SQLJ files. The tool supports references to existing translated SQLJ files.

Example:

  1. Download UpdIter.sqlj and UpdateByName.sqlj.
  2. UpdateByName.sqlj has references to UpdIter.sqlj. Therefore, translation needs to be done first on UpdIter.sqlj. If UpdIter.sqlj is not translated before UpdateByName.sqlj, you will receive the following errors:
Fri Sep 20 22:00:37 EDT 2002 
==================== 
Translating: UpdateByName.sqlj 
==================== 
[Translating 1 files.] 
[Reading file UpdateByName] 
[Translating file UpdateByName] 
UpdateByName.sqlj:14.6-14.75: Error: Left hand side of assignment does 
not have a Java type. 
UpdateByName.sqlj:19.8-19.79: Error: Invalid Java type for host item #1. 
UpdateByName.sqlj:19.8-19.79: Error: Invalid Java type in cursor for 
WHERE CURRENT OF 
Total 3 errors.

If you encounter the following error after you translated UpdIter before UpdateByName, you must have forgotten to add sqlj.zip to your Java build path.

Fri Sep 20 21:56:32 EDT 2002 
==================== 
Translating: UpdateByName.sqlj 
==================== 
[Translating 1 files.] 
[Reading file UpdateByName] 
[Translating file UpdateByName] 
UpdateByName.sqlj:14.6-14.75: Error: Return type incompatible with 
SELECT statement: UpdIter is not an iterator type. 
UpdateByName.sqlj:19.8-19.79: Error: Cursor for WHERE CURRENT OF 
has illegal type UpdIter. It must implement ForUpdate. 
Total 2 errors.

6. Source/bin directory support

Many users would use source/bin directories for most of their projects. The tool supports most source/bin translation situations -- the exceptions will be listed in the examples. Translation should be done only in the source directory.

Example 1. Independent SQLJ file

  1. Create a new project with a source and a bin directory.
  2. Copy all the SQLJ files from the Test project to this new project.
  3. Translate App.sqlj. The App.class file is generated in the source directory. You may get around this problem by using the -compile=false option.

Example 2. SQLJ file with dependencies

UpdateByName.sqlj has references to the UpdIter.class file, which must therefore be placed in the source directory so that the SQLJ translator can find it. If the -compile=false option is used, UpdateByName.sqlj will probably not be translated successfully. Therefore, you should not use -compile=false in this case.

7. Multiple SQLJ selections

Sometimes you may want to translate a set of SQLJ files. You can select and translate multiple files in the same project. Each file is translated in the order of selection. For example, if you need to have UpdIter translated before UpdateByName, select UpdIter first in the multiple selections. When a package name is entered in the dialog, all files in it will be translated and placed under the package name.

8. A simple SQLJ editor

Try double-clicking on one of the SQLJ files. A simple SQLJ editor opens up, with a few improvements in keyword coloring compared to the text editor. Sophisticated features such as code assist and formatting are not implemented.


Troubleshooting

1. Incorrect SQLJ executable path error message

If the sqlj executable path is not set correctly when invoking a translation, you will see the following error message:

Figure 8. Error dialog

2. Translation seems to be frozen

If the translation seems to be frozen on the Command Prompt window, just close the window. This behavior is caused by the SQLJ compiler from DB2 UDB "freezing" during the translation.

3. Translate SQLJ file dialog not showing up

If you tried to translate on an SQLJ file in a bin directory, the SQLJ File dialog may not show up. You should perform translation only in a source directory.

4. NoClassDefFound error

If you receive a NoClassDefFound: sqlj/tools/sqlj error when attempting a translation, make sure the sqlj.zip, runtime.zip and db2java.zip files are in the Windows classpath. These ZIP files are located in the \sqllib\java directory. After you modify the classpath, you may need to restart the workbench to pick up the changes.

5. Error in sqlj: path, or filename java not found

If you receive the error Error in sqlj: path or file name "java" not found, then your DB2 SQLJ compiler is not fully set up. Make sure javac and java are in the path. For example, if your jdk directory is c:\jdk, then c:\jdk\bin should be in the Windows classpath. To check if this problem is resolved, open a command prompt and issue the command java. If the command prompt does not have a File not found error, the problem is solved.

6. CreateProcess error in Java compilation

If you receive the error Error in Java compilation: CreateProcess: javac -J-Djavac.pipe.output=true App.java error=2, then your DB2 SQLJ compiler is not fully set up. Make sure javac and java are in the path. For example, if your jdk directory is c:\jdk, then c:\jdk\bin should be in the Windows classpath. To check if this problem is resolved, open a command prompt and issue the command javac. If the command prompt does not have a File not found error, the problem is solved.

If you only have the JRE\bin directory in the path, this problem will also occur, because the JRE\bin directory does not have javac. The SQLJ compiler requires javac at the end of the translation process to compile the generated Java code. This error message will disappear if you use the -compile=false option. Except for the fact that the Java files are not compiled, this error should not affect the normal process of SQLJ translation.

7. Still having problems?

If you are not sure if you have a tool problem or a database setup problem, perform an SQLJ translation in a DB2 command prompt. If the same problem occurs in the DB2 command prompt, then the problem is not in the SQLJ Tool. For example: sqlj.exe App.sqlj -status


Conclusion

After going through the tutorial, you probably have some suggestions and ideas on how to improve the SQLJ Tool plug-in. Feel free to send them to me at (jcyfung@ca.ibm.com). I hope you've enjoyed the tool and this article.


Downloads

DescriptionNameSize
Code samplesqljfiles.zip  ( HTTP | FTP )0.1MB
Code samplecom.ibm.sample.sqljtools.zip  ( HTTP | FTP )0.1MB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere, Information Management
ArticleID=13234
ArticleTitle=SQLJ Tool Plug-in for WebSphere Studio Application Developer
publish-date=10292002