Avoka Blog: Adobe LiveCycle

August 19, 2008

Using the LiveCycle SQL (JDBC) component – Part I

Filed under: Designing Processes, LiveCycle — htreisman @ 1:51 pm

Overview

The LiveCycle JDBC/SQL component is probably the most useful component in the Foundation category. You can use it to query a database, which can help pre-populate forms, make routing decisions, and more. It’s a very powerful component, but it can get a bit complicated, particularly when you want to pass process variables to your query. In this blog, we investigate the JDBC component in detail.

Datasource name

The datasource name is a way of getting the application server to do all the hard work of connecting to a database. Basically, you configure a pool of data connections to a particular database by configuring the application server. The application server will connect to the database on your behalf, using the connection information you provide, and make the connections available for your use. This is done differently in different application servers. Check out the LiveCycle installation guide or application server documentation for instructions on how to create connection pools.

All you need to provide is the name of the datasource. In most cases, you will be using an “in memory” connection pool, which means that you will need a “java:/” prefix to your pool name.

For example, to connect to the LiveCycle database itself (which we will use in these examples), use:

java:/IDP_DS

Simple query

A simple query is shown below:

Passing a process variable as a parameter – “Embedded Xpath”

If you want to pass a process variable as a parameter to your query, you start by creating a variable, such as “begins_with”, of type String. You can use the “Xpath…” button to replace the “D” with the process variable, and insert it into the query. Your sql query will look like this:

select oid,id from tb_sc_service_category
where id like '{$ /process_data/@begins_with $}%'

Note that:

  • the single quotes are still there, indicating to the SQL engine that we’re using a string
  • the percent symbol is still there, indicating a SQL wild-card
  • the variable name has been “wrapped” in {$ … $}. This indicates to the process engine when the process runs, it should replace the {$…$} with the actual value of that variable.

So if you run this process, and set the variable begins_with = “D”, you should get exactly the same results.

However, if you click the Test button, you won’t get any data. This is because when you click the Test button, the process engine is not running, so the {$…$} wrapping is not replaced by anything, and you’re trying to locate a data that doesn’t exist.

This gets even worse if you use non-string variables, such as:

select oid,id from tb_sc_service_category
where oid ={$ /process_data/@some_id $}

In this case, you will actually get a SQL error:

Exception: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server
version for the right syntax to use near '' at line 2.

This is because the snippet of text “{$ /process_data/@some_id $}” doesn’t look anything like the integer that the SQL engine is expecting. When the process actually runs, this text will be substituted with the actual value of the some_id variable, such as “1”, and this would work fine – it’s only during testing that you have a problem.

Things would also not if you were trying to find a category called “Bob’s stuff”. After variable replacement, you would get a SQL statement that looks like this:

select oid,id from tb_sc_service_category
where id like 'Bob's stuff%'

You now have three quote characters in your SQL, and the SQL processor will get confused.

The way to fix all these problems is to use a Parameterized Query.

Passing a process variable as a parameter – “Parameterized”

With a parameterized query, rather than embedding the parameter directly into the query, we use a “?” to indicate the parameter. We can then provide both a test value and a runtime-variable to be used when the query is executed. This is shown below:

Note that:

  • We check the box that says “Use Parameterized Query”
  • We added a single row to the table because we have a single ? in our query. You must have the same number of rows as ?’s.
  • We can specify a test value to be used for testing the query, and a variable name for when the query actually runs in the process.
  • The wild-card character % must be embedded in the test data and the variable value.
  • We no longer need the quote characters in our SQL query, because we’re explicitly setting the type of the parameter to a string. We also don’t include quotes in our test data or variable value. This eliminates the problems related to having quotes in your parameter value.

Avoka’s Added Value

Avoka provides an added value component that makes SQL queries easier and more powerful.

This component is shown below:

Some of the additional features in Avoka’s component include:

  • Browsing and insertion of tables and column names into your query (shown above)
  • Additional output data formats, including XML, comma separated values, a CSV file, process variables of type “List”, and insertion of data directly into XFAForm variables.

You can see more examples of usage here:

http://avoka.dnsalias.com/confluence/display/Public/Retrieving+data+from+a+database+and+populating+a+form+with+the+data

http://avoka.dnsalias.com/confluence/display/Public/Retrieving+data+from+a+database+and+saving+as+concatenated+strings+or+lists+in+process+variables

http://avoka.dnsalias.com/confluence/display/Public/Retrieving+data+from+a+database+and+saving+as+CSV+file

You can download a trial version here:

Download trial

Summary

Using parameterized queries simplifies and enhances your ability to define and test your SQL queries. We generally recommend that you use a parameterized query rather than embedding the variable directly into the string.

Advertisements

2 Comments »

  1. this is an extremely good and helpful article

    Comment by kelevra — August 26, 2008 @ 2:24 am

  2. Thanks for posting this topic. It is so helpful. Are you going to post part II soon? I can’t wait to see it posted.

    Thanks again.
    HD

    Comment by H Dao — December 16, 2008 @ 12:31 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: