Increase productivity in Java database development with new IBM pureQuery tools, Part 2: Detect and fix SQL problems inside Java program

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Sonali Surange (ssurange@us.ibm.com), pureQuery Tools Lead, IBM　
Becky Nin (nin@us.ibm.com), Advisory Software Engineer, IBM

20 Sep 2007

With IBM® pureQuery technology and tools, detect various problems as you code SQL inside your Java program. Boost productivity developing database applications under different scenarios during a product development and deployment cycle, and customize SQL validation to your needs.

Introduction

IBM pureQuery technology and tools greatly simplify the task of developing Java applications that contain SQL. pureQuery provides deep integration between Java language and SQL within the Eclipse environment. pureQuery tools allow developers to seamlessly work with SQL in the Java editor using various productivity features such as SQL validation, SQL content assist, SQL execution, show tables and columns in Database Explorer, and SQL templates.

Refer to Part 1 of this series for an overview of pureQuery tools.

This article focuses on SQL validation inside Java.

Additionally, this article uses the following features from pureQuery tools:

• Generate inline style of pureQuery code
• SQL content assist to fix invalid queries
• Generate DDL to create a table

This article also uses the following IBM DB2® Developer Workbench tools:

• Alter table
• Database Explorer
• Data connection persistence options
• SQL / XQuery Editor

Platform support

IBM databases -– IBM DB2® for Linux®, UNIX® and Windows®, zSeries®, and iSeries®, as well as IBM Informix® Dynamic Server

Eclipse environment

• Developer Workbench 9.5 open beta
• Future upcoming support for shell sharing with IBM Rational® suite of products

Prerequisites

DB2 SAMPLE database

Back to top

IBM pureQuery

pureQuery is a new, high-performance Java data access platform focused on simplifying the tasks of developing and managing applications that access data.

The benefits of using pureQuery extend throughout the development, deployment, management, and governance stages of the application life cycle.

pureQuery provides access to data in databases and in-memory Java objects with its tools, APIs, and runtime environment.

Back to top

IBM pureQuery Eclipse Tools

pureQuery Eclipse-based tools available with in IBM Viper Developer V9.5 allow you to:

• Code SQL with Java in the Java editor and be equally productive in both
• Never leave your Java editor and build error free Java and SQL
• Auto-generate simple data access layer with much less code than JDBC
• Easily improve your application performance with static SQL and no extra work
• Use SQL to access database and in-memory collections

Back to top

Why pureQuery SQL validation in Java language

Today, Eclipse's Java development environment offers little support for developers who need to code SQL statements into database applications. The experience of writing Java code is far better than the experience of writing SQL code.

The Java editor reports errors and identifies inaccuracies in Java syntax as you type.

You can see the details of the problem by hovering the cursor over the problem or the problem marker, as shown in Figure 2:

If you ignore problems and continue coding, the Problems view lists them when you save the file or when you build the project, depending on whether or not the project is set to build automatically. In the Problems view, you can double-click on a problem to see the corresponding code in the Java editor.

You can fix problems with Eclipse's Java quick-fix support or content assist.

Java application developers working in Eclipse have been benefiting from this integration for a long time, and coding and identifying Java problems in this fashion is almost second nature.

Drawbacks of existing solutions

Today, coding SQL into Java applications is not as easy. The Java editor cannot detect errors in SQL syntax, nor can it tell you that SQL statements refer to database objects that do not exist. In fact, when the application coding is completed, there is no indication that the SQL is valid or invalid until the application is executed. At that stage, the execution of the SQL fails and the program must catch any exceptions to determine the reason for the failure.

There are two methods that you can use to find the SQL problems in your applications:

1. Handle SQL exceptions in your code and run your applications to find out whether the SQL is valid or invalid. If there is an error in an SQL statement, fix it and possibly rerun the application multiple times to catch and fix additional SQL errors
2. Use JDBC API prepare statements as a way to validate your SQL. The drawback of this solution is that it can identify only the first problem in your SQL. You have to repeat the fix and retry cycle to perfect the SQL

Both of these methods require a live database connection, although database systems are often not available during application design time. Moreover, if you have access only to a remote database, you might have trouble with intermittent or slow access to your network.

The Java development experience, as apposed to the SQL development experience, using the Eclipe's Java editor offer vastly different developer experiences. The Java development experience is very optimal and productive, whereas the experience coding SQL is very primitive and non-productive.

pureQuery tools solve the above problem by integrating several SQL editing features inside Eclipse's Java editor.

Highlights of pureQuery SQL validation

pureQuery tools integrate directly into the Java editor and alert you to problems in your SQL statements as you type. These tools:

• Detect errors in database schemas, tables, columns, and other such database artifacts from the SQL. This feature is often referred to as semantic validation
• Detect whether SQL keywords and their location in the SQL are valid. This feature is often referred to as syntactic validation
• Identify most errors in the SQL statement, not just the first one. This feature boosts your productivity, allowing you to fix all SQL problems in one shot. For example, if two columns are spelled incorrectly in a statement, pureQuery detects both errors
  
  Note:If your table is misspelled, then column errors are not detected; if your schema is misspelled, then table and column errors are not detected; and so forth. If the SQL syntax has problems, then the semantic errors are not detected.
• Describe the details of SQL errors in two places: in your code and in the Problems view. To see an explanation of an error, hover your cursor over it in the code or double-click on the error in the Problems view. To diagnose the error, you do not have to note its SQL code and then hunt through a troubleshooting manual
• Highlight SQL errors in the code according to your preferences
• Detect errors in SQL statements that span multiple lines in your Java strings
• Let you work with a disconnected, saved model for the database connection
• Let you filter out database objects in the Database Explorer so you see only the objects that you are interested in
• Let you validate your Java application against different database connections such as development, test, production, or different schemas
• Let you switch off SQL validation
• Let you treat SQL errors as warnings

Follow the steps in the next section to try out these features from pureQuery tools:

Back to top

pureQuery SQL validation in action

Reap the benefits of pureQuery SQL validation during any development or deployment stage of your application. Boost productivity at every stage and in every scenario.

Below are some scenarios that describe the benefits of pureQuery validation and SQL editing features:

10 unique scenarios to boost application development productivity

1. Want to build a pureQuery application and detect errors in your database schemas, tables, or columns as your create or customize SQL?
   Use the scenario to detect and fix SQL as you type for semantic validation.
   • Build a pureQuery application, and walk through each of the SQL validation feature in this scenario. Use pureQuery SQL content assist to fix issues.
   
   
2. Want to detect errors in SQL keywords as you create or customize SQL?
   Use the scenario to detect and fix SQL as you type for syntactic validation.
   
   
3. Want to switch from development to test or production databases and schemas, and continue to get instant SQL validation?
   Use the scenario to switch the database for SQL validation.
   • Use the pureQuery Generate DDL feature to get a proposal for a script to create a table from pureQuery beans. Use use the new SQL / XQuery editor to execute the generated SQL.
   
   
4. Want to ignore SQL errors in a pureQuery application while the database schema is under construction?
   Use the scenario to customize SQL validation severity.
   
   
5. Need to rename a table or column and get instant SQL validation from this impact in your Java application?
   Use the scenario to detect the impact of database changes in the Java application.
   • Use the alter table functionality from Database Explorer.
   
   
6. Do not wish to stop error-free SQL development till the database server becomes available again? Do not wish to get affected by changes made to the database by other team members?
   Use the scenario for disconnected and productive, error-free SQL development in Java applications.
   
   
7. Want to get automatic SQL validation every time you open the IDE and not have to re-type the password when working with live connections?
   Use the scenario to save password in Database Explorer.
   
8. Want to see SQL errors in your application in a report format?
   Use the scenario to generate an SQL problem report.
   
   
9. Don't like the default error display options for Java programming and, therefore, for SQL errors?
   Use the scenario for customizations to the error display options.
   
   
10. Love pureQuery SQL validation, and want to use it in all your existing programs?
    Use the scenario to extend pureQuery SQL validation.
    
    

You first need to create a pureQuery application using pureQuery tools from the sample database:

Setup – Create pureQuery code

Follow the steps below:

1. Open the Java perspective. Add the Database Explorer view to the Java perspective -- navigate to Window > Show View, select Data, and double-click on Database Explorer.
2. Connect to the SAMPLE database in the Database Explorer
3. Create a Java project.
4. Call it pureQuerySQLPrj.
   
   Figure 4. Create a Java project
   
   
   
5. In the Database Explorer, right-click on the PROJECT table, and select Generate pureQuery code.
6. Ensure the following check boxes are checked:
   
   Figure 5. Generate pureQuery code
   
   
   
7. Select Finish.

pureQuerySQLPrj will now contain the following files:

• Project.java – Bean representing the PROJECT table
• ProjectInlineSample.java – Inline-method style program with suggestions for create, select, update, and delete statements

This article works with the SQL generated in the ProjectInlineSample.java.

In the next article in this series, you will use the method-style application development, supported by pureQuery.

1. Open ProjectInlineSample.java in the Java editor.
   
   Observe that the program contains pureQuery's API to use create, select, update, and delete statements.
   
   Observe, also, that all SQL in the program is highlighted with a light yellow box. pureQuery's SQL annotation feature allows distinguishing SQL in your Java program. You will work with the highlighted SQL in the rest of this article.
   
   
2. Ensure your project now has pureQuery support enabled:
   1. Right-click on pureQuerySQLPrj, and select Properties.
   2. Select pureQuery. Observe the SAMPLE connection associated with the project.
   3. Select Current schema setting and observe that your userID on the connection is used for the current schema setting.

This connection is used by pureQuery tools to provide SQL validation for the Java files in that project.

Detect and fix as you type for semantic validation

To demonstrate pureQuery’s SQL validation features, let's make some changes to the generated SQL.

1. In the first select statement that was generated, change the names of two columns to invalid values. (This example changes PROJNAME and PRENDATE to invalid values.)
   
   Observe that the Java language error notification (by default, the red squiggles) is shown under the incorrect column names.
   
   
2. Save the file, and observe the problem notification in the editor (by default, a red cross).
3. Open the Problems pane -- navigate to Window > Show View > Problems.
   
   Notice that all the invalid column names are listed along with other Java language errors in the Java program:
   
   Figure 6. Errors in multiple columns are displayed
   
   
   
   
4. To fix the column names, you can use pureQuery’s content assist feature:
   1. With your cursor before the column name, press Ctrl + Space ( or your default keystroke for content assist) to activate content assist.
      Notice that the list of columns for PROJECT table will be displayed. Select PROJNAME and PRSTDATE to fix the column name errors.
      
      
      Figure 7. Column content assist
      
      
      
      
5. In the first select statement that was generated, change the name of the table to an invalid value. (This example changes PROJECT table to PROJECT1.)
   
   Observe that the Java language error notification (by default, the red squiggles) is shown under the incorrect SQL; in this case, table name PROJECT1.
6. Save the file, and observe the problem notification in the editor (by default, a red cross).
7. Open the Problems pane by navigating to Window > Show View > Problems.
   
   Notice that the invalid table name is listed along with the other Java language errors in the Java program:
   
   Figure 8. Table error
   
   
   
   
8. To fix the table name, you can use pureQuery's content assist feature.
   1. With your cursor before the table name, press Ctrl + Space ( or your default keystroke for content assist) to activate content assist.
      
      Notice that the list of tables for the schema will be displayed. Select PROJECT to fix the table name.
      
      Figure 9. Table content assist
      
      
      
      
9. Now change the schema name to an invalid value and observe similar errors as above. Use content assist to fix the schema name.

What was achieved:

You observed pureQuery's unique ability to flag all errors in an SQL at one time, such as all column errors. This is a unique feature in comparison with any other mechanism to identify errors in SQL, such as using JDBC prepared statements.

You also observed that pureQuery can isolate errors spanning multiple lines and show error markers just like any other Java language error.

Detect and fix SQL as you type for syntactic validation

1. Change the update keyword in one of the update statements generated in the SQL.
   
   Notice the same behavior as in the case of table name change, above.
   
   
2. Quick fixes are available to fix certain SQL keywords. You can use Java editor quick fix to fix the update keyword.
   1. Use content assist to fix the SQL keywords as shown in Figure 10:
      
      Figure 10. Update error and content assist
      
      
      

Note: A future release of pureQuery tools will provide pureQuery host-variable validation within SQL.

What was achieved:

You observed the ability to detect and fix syntactic errors in SQL statements.

Switch database for SQL validation

Typically, applications are developed on a development database, tested on a different database, and finally deployed on yet another database. Typically, the schema names for each of the databases are different. In this scenario, learn how you can switch databases for a pureQuery project and get your SQL validated without changing any code.

1. Create a test database on your DB2 server, and call it TESTDB, using the steps below:
   1. Run the following SQL statement in a DB2 command prompt:
      
      

      db2 create database prod

      
      
2. Create a connection to the TESTDB database in the database explorer.
3. Change the connection for your Java project to point to TESTDB:
   1. Right-click on pureQuerySQLPrj, and select Properties.
   2. Select pureQuery, and change the connection name to TESTDB:
      
      Figure 11. Change database for Java project
      
      
      
      
4. Now create the PROJECT table in the TESTDB database under a schema name TESTSCHEMA:
   1. Open the Project.java bean.
   2. Right-click on the bean, and select Generate DDL from the pureQuery Assist menu.
   3. Open the Console view by navigating to Window > Show View, then select Console.
   4. Copy the DDL listed. (This is a suggestion for creating a table that the Project bean would represent.)
      
      Figure 12. Create PROJECT table in the PROD database under a PRODSCHEMA
      
      
      
      
5. Run the DDL using the SQL / XQuery editor
   1. Open="" the SQL/XQuery editor by right clicking on the TESTDB database under the TESTDB connection in Database Explorer and selecting New SQL or XQuery Script.
   2. Give a name ProjectDDL and ensure the button for SQL Editor is selected.
   3. Copy the SQL script from the console view to ProjectDDL.sql in the SQL/ XQuery editor
   4. Be sure to change the schema name as shown:
      
      Listing 1. Run the DDL
      

      CREATE TABLE TESTSCHEMA.PROJECT ( DEPTNO VARCHAR(255) NOT NULL, MAJPROJ VARCHAR(255) NOT NULL, PRENDATE DATE NOT NULL, PROJNAME VARCHAR(255) NOT NULL, PROJNO VARCHAR(255) NOT NULL, PRSTAFF DECIMAL(9 , 2) NOT NULL, PRSTDATE DATE NOT NULL, RESPEMP VARCHAR(255) NOT NULL ) DATA CAPTURE NONE ; ALTER TABLE TESTSCHEMA.PROJECT ADD CONSTRAINT PROJECTPK PRIMARY KEY (PROJNO);

      
      
   5. Right click on the editor and select Run SQL. View the results of successful execution.
      
      
      Figure 13. Execute DDL script using SQL / XQuery editor
      
      
      
6. Refresh the schemas for TESTDB connection in the Database Explorer
7. Re-open ProjectInlineSample.java.
   
   Notice that the Java editor automatically detects that your original schema is invalid.
   
   Figure 14. Java program detecting that original schema is not valid anymore on the test or production database
   
   
   
   
8. Use content assist to fix the errors.

Note that in order to avoid code changes due to changes in schema names, you can also avoid qualifying the identifiers that belong to the current schema on the connection. Then you can simply change the current schema for your Java project to use the appropriate schema name, as shown below:

What was achieved:

You learned how you can switch between development and test databases, and still continue to get SQL validation for either of the databases. This is achieved by switching the database associated with the Java project.

Alternately, you can construct your application without qualifying your SQL with a schema and change the current schema on a Java project, when schema names change.

Customize SQL validation severity.

pureQuery tools allow the flexibility to treat SQL errors in a Java program as errors, noting them as warnings or allowing to even ignore them. Scenarios exist when database schemas are out of sync or under development, and such errors need to be treated as warnings.

To treat SQL validation errors as warnings, select the preference setting for severity level for SQL problems as "Warnings".

Alternately, if you are developing your SQL and the database schema does not exist yet, you can choose to switch off SQL validation. To switch off validation, select the preference setting for severity level for SQL problems as "Ignore".

You can decide to switch off the validation during certain application development cycle and switch it back on later on.

To set your preferences:

1. Select Window > Preferences > Data > pureQuery > Compiler Settings, as shown in Figure 16:
   
   Figure 16. Set SQL validation severity to ignore
   
   
   Changing the preference will trigger a workspace re-build. Alternately, you can choose to rebuild your specific projects.

Treating SQL errors as warnings shows the same warning notification as Java language warnings (by default,yellow squiggles), warning file and line marker (by default, yellow cross), and listing in the warnings section of the problems view.

What was achieved:

You also learned how you can switch off validation when the database is still under development. Alternately, you can treat SQL errors as warnings.

Detect the impact of database changes in Java application

1. Select the PROJECT table on the TESTDB connection under TESTSCHEMA, and select Alter.
2. Change two column names, as shown in Figure 17, then select Run DDL:
   
   Figure 17. Alter table to change column names
   
   
   
   
3. Re-open ProjectInlineSample.java, and notice that the impact of changing column names is now reflected in the Java program:
   
   Figure 18. All instances of column renames in database detected in Java program
   
   
   

What was achieved:

You saw how database changes can be detected in your Java application, making it easier to adjust your application to these changes at application development time.

Note: More complete impact analysis will be provided in future pureQuery tools releases.

Disconnected and productive error-free SQL development in Java

You can use pureQuery SQL validation with an online, live connection or one that has been saved offline to the file system.

If the connection is saved offline, pureQuery SQL validation will use the offline, saved database metadata for validation.

1. Right-click on the database explorer connection to the SAMPLE database, and select Save offline.
   
   All SQL validation will now use the saved database schema.

What was achieved:

You can isolate your development environment from changes in the database instance if it is shared by other developers or from the dependency on a live, available connection while you are designing your database application. This can provide a great productivity boost towards undisrupted, focused application development.

Save password in Database Explorer

Effect of data connection state on SQL validation:

• You can request that passwords for connections be persisted across Eclipse invocations. If passwords are persisted, you can also set the preference to automatically reconnect the connection on startup.

1. From Window > Preferences > Data, select Persistence Scope for the password information setting.
2. Check the box to automatically reconnect connections on startup.

Note: In the case your settings do not match the above, on Eclipse startup, your Java files containing SQL may not show the error markers for invalid SQL. Similarly, your SQL may not show as highlighted (or your preferred setting for SQL) in the Java program. A warning on the first line of the file will identify the reason for this behavior:

What was achieved:

You learned about password settings that avoid re-typing the password every time the IDE is opened.

Generate an SQL problem report

All SQL errors are detected and reported along with other Java language errors in the problems view. You can customize your problems view to list only errors or warnings, using standard eclipse features.

Either by selecting all errors in the problems pane, or by selecting only specific errors that you are interested in, you can copy the errors and paste them into a report file external to the eclipse environment.

                
Severity and description    Path    Resource    Location    Creation Time    Id
Table "PROJECT" does not contain column "DETNO".    
pureQueryValidationPrj/src/com/demo
ProjectInlineSample.java
line   6728

Table "PROJECT" does not contain column "PRTAFF".
pureQueryValidationPrj/src/com/demo
ProjectInlineSample.java
line6729

SQL errors detected in the Java program are persisted along with all other Java language errors. Alternately, to produce a report in a custom format, you can write a utility to scan the persisted errors in a project of interest and populate the custom report in a format of interest.

Customization options to change error display options

Eclipse allows you to change the default error / warning notifications (red squiggles, red marker for error; yellow squiggles and yellow alert for warnings) to a visual notification of your choice. Your preference settings are used to indicate SQL errors as well.

To customize error indication for Java language and SQL:

1. Navigate to Window > Preferences > General > Editors > Text Editors > Annotations, and select Errors.
2. Change the following preferences:
   • "Text as" to "Highlighted"
   • "Color" to light blue
   
   
   Figure 21. Customize error display
   
   
   
   
3. Rebuild your project.

Notice that all Java language and SQL errors now show as blue highlights:

What was achieved:

You looked at pureQuery's ability to re-use Java editor preferences provides a consistent look and feel to Java language and SQL in a Java program.

Extend pureQuery SQL validation to your program

pureQuery's SQL integration is available in the standard Java editor in Eclipse.

By default, the SQL editing functionality is enabled on strings that are used within pureQuery APIs. In addition, you can use it on any strings of your choice in your applications containing SQL.

Refer to the "SQL editor integration inside Java editor > Use pureQuery's editor integration in your program" section in Part 1 for details on enabling this feature for your Java application.

Back to top

Why 10x productivity improvement

This article has looked at pureQuery SQL validation features in action, and illustrated ways to customize and extend them to other non-pureQuery strings and APIs.

Let's now evaluate how this feature provides 10x productivity improvement to developers:

The steps to successfully build a Java application with database without pureQuery tools include:

1. Hand-code SQL in Java language. The SQL could have input parameters and could return result sets.
2. Hand-code an application to test the SQL..
3. Hand-code all parameter information to input to the SQL and to retrieve the results back.
4. Hand-code try-catch and display exceptions caught.
5. Execute the application.
6. In case the SQL was invalid, investigate the SQL exception thrown.
7. Identify cause of the failure by going through database manuals and searching for the error codes sent back in the exception.
8. Open an SQL editor or builder from the database vendor, and try to correct the SQL.
9. Paste the SQL back into the Java program.
10. Repeat steps 5-8 until you are successful.

A major downside, also, is that you must have a live connection to perform this test. It is impossible to test your SQL for validity without a live connection. This can be very restrictive.

Without pureQuery, there is no ability to find all the errors in all the SQL statements in your program at the same time.

Using pureQuery tools, you can build valid SQL in your Java application as you code. Improve productivity by reducing the edit-debug-test cycle into one step.

Back to top

Summary and a peek into the future

pureQuery tools greatly improve SQL productivity when developing Java database applications on IBM databases such as DB2 and IDS.

In the next article in this series, Part 3, learn in depth about pureQuery application development using the different pureQuery styles.

Then stay tuned for the following Part 4, focusing on pureQuery application lifecycle on the Informix database.

Back to top

Acknowledgements

Thanks to Brian Payton and Roger Roles for the Integrated Query and parser technologies.

Special thanks to Robert Heath and Michael Hsing for reviewing this article.

Resources

• "Increase productivity in Java database development with new IBM pureQuery tools, Part 1: Overview of pureQuery tools" (developerWorks, September 2007): Get you up to speed on using pureQuery tools. Develop Java applications using pureQuery -- create new apps, and use the tools to leverage existing assets.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Technology bookstore: Browse for books on these and other technical topics.

• IBM Viper Developer V9.5 open beta: IBM pureQuery and tools are available in the Viper Developer V9.5 open beta. Start reaping the benefits.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

		Sonali Surange leads IBM's pureQuery tools in the IBM's Data Server Tooling development organization at the IBM Silicon Valley Lab in San Jose, California. Prior to joining IBM's pureQuery, Sonali led the Visual Studio .Net tools effort for IBM Data Servers and IBM's SOA effort on .NET. Previously, Sonali led Informix SOA solutions and was lead developer on Object Relational mapping tools.

		Becky Nin is a member of IBM's pureQuery tools team in IBM Silicon Valley Lab in San Jose, California. Previously, Becky developed other Eclipse-based tooling such as the SQLJ support in RAD. Before working on Eclipse tooling, Becky was the lead developer for the Data Access Beans component of VisualAge for Java.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top