The SQL assembler is a specialized Java assembler class that you use with the Java adapter to provide a bridge to a SQL database management system. Using the SQL assembler, you can build simple create, read, update, and delete (CRUD) applications based on the Data Management Service without writing server-side code. You can directly specify the SQL statements that you want to execute when the user creates, updates, or deletes items. You can also specify a number of SELECT statements to retrieve data in different ways. This assembler is useful when your database data model is not very complex and you want to expose it to MXML without writing Java code. You configure the connection to the database and write SQL code in a Data Management Service destination definition. You are only required to include SQL code for operations that a Flex application calls. The SQL assembler does not support hierarchical (nested) destinations.

The SQL assembler class, flex.data.assemblers.SQLAssembler, extends the flex.data.assemblers.AbstractAssembler class and is included in the public LiveCycle Data Services ES Javadoc API documentation. When necessary, you can extend SQLAssembler to override methods. The resources directory the LiveCycle Data Services ES installation includes the source code for the SQLAssembler and HibernateAssembler classes.

Configuring a destination that uses the SQL assembler

Before using the SQL assembler from a Flex client, you configure a destination that specifies database connection information and SQL statements. The following example shows a complete destination definition that uses the SQL assembler; the lcds-samples web application included with LiveCycle Data Services ES uses this destination:

<destination id="sql-product">
    <adapter ref="java-dao"/>
    <properties>
        <use-transactions>true</use-transactions>
        <source>flex.data.assemblers.SQLAssembler</source>
        <scope>application</scope>
        <metadata>
            <identity property="PRODUCT_ID"/>
        </metadata>
        <server>
            <database>
                <driver-class>org.hsqldb.jdbcDriver</driver-class>
                <!-- Modify the URL below with the actual location of the
                    flexdemodb database on your system -->
                <url>jdbc:hsqldb:hsql://myserver:9002/flexdemodb</url>
                <username>sa</username>
                <password></password>
                <login-timeout>15</login-timeout>
            </database>

            <!-- Use this syntax when using a JNDI data source-->
            <!--
            <database>
                <datasource>java:comp/env/jdbc/flexdemodb</datasource>
            </database>
            -->

            <actionscript-class>Product</actionscript-class>
            <fill>
                <name>all</name>
                <sql>SELECT * FROM PRODUCT ORDER BY NAME</sql>
            </fill>

            <fill>
                <name>by-name</name>
                <sql>SELECT * FROM PRODUCT WHERE NAME LIKE CONCAT('%',
                    CONCAT(#searchStr#,'%'))</sql>
            </fill>

            <fill>
                <name>by-category</name>
                <sql>SELECT * FROM PRODUCT WHERE CATEGORY LIKE CONCAT('%',
                    CONCAT(#searchStr#,'%'))</sql>
            </fill>

            <get-item>
                <sql>SELECT * FROM PRODUCT WHERE PRODUCT_ID = #PRODUCT_ID#</sql>
                </get-item>

            <create-item>
                <sql>INSERT INTO PRODUCT 
                    (NAME, CATEGORY, IMAGE, PRICE, DESCRIPTION, QTY_IN_STOCK) 
                    VALUES (#NAME#, #CATEGORY#, #IMAGE#, #PRICE#, #DESCRIPTION#,
                    #QTY_IN_STOCK#)</sql>
                <id-query>CALL IDENTITY()</id-query> <!-- HSQLDB syntax to
                    retrieve value of autoincremented column -->
        </create-item>

            <update-item>
                <sql>UPDATE PRODUCT SET NAME=#NAME#, CATEGORY=#CATEGORY#,
                    IMAGE=#IMAGE#, PRICE=#PRICE#, DESCRIPTION=#DESCRIPTION#,
                    QTY_IN_STOCK=#QTY_IN_STOCK# 
                    WHERE PRODUCT_ID=#_PREV.PRODUCT_ID#</sql>
            </update-item>

            <delete-item>
                <sql>DELETE FROM PRODUCT WHERE PRODUCT_ID=#PRODUCT_ID#</sql>
            </delete-item>

            <count>
                <name>all</name>
                <sql>SELECT count(*) FROM PRODUCT</sql>
            </count>
        </server>
    </properties>
</destination> 

For examples of MXML applications that perform CRUD and query operations on this destination, see the samples web application included with LiveCycle Data Services ES.

Specifying the Java adapter

Specify an instance of the Java adapter if it is not already set as the default adapter. In the following example, java-dao is the id value of a Java adapter instance defined in the configuration file:

<destination id="SqlPerson">
    <adapter ref="java-dao"/>
...
</destination>

Setting the destination source

Specify the flex.data.assemblers.SQLAssembler class name in the source element of the destination definition, as the following examples shows. Generally, you also set the scope value to application.

<destination id="SqlPerson">
    <adapter ref="java-dao"/>
    <properties>
        <use-transactions>true</use-transactions>
        <source>flex.data.assemblers.SQLAssembler</source>
        <scope>application</scope>
...
    </properties>
</destination>

Setting the identity property

Specify the identity property, which maps to the database field. Additionally, this name corresponds to the name of a property of an ActionScript object in the Flex client application. The ActionScript object can be anonymous or strongly typed. The following example shows the configuration for an identity element:

<destination id="SqlPerson">
    <adapter ref="java-dao"/>
    <properties>
...
        <metadata>
            <identity property="PRODUCT_ID"/>
        </metadata>
...
    </properties>
</destination>

Configuring the database connection

You can connect to a SQL database by specifying a data source or a driver in the destination definition. Using a data source is recommended because it provides application server enhancements, such as connection pooling.

The following example shows a datasource element in a destination definition:

<destination id="sql-product">
    <adapter ref="java-dao"/>

    <properties>
...
        <server>
            <database>
                <driver-class>org.hsqldb.jdbcDriver</driver-class>
                <!-- Modify the URL below with the actual location of the
                    flexdemodb database on your system -->
                <url>jdbc:hsqldb:hsql://yourserver:9002/flexdemodb</url>
                <username>sa</username>
                <password></password>
                <login-timeout>15</login-timeout>
            </database>
...
    </properties>

</destination>

You use the optional login-timeout property for a data source or a driver. You specify this value in seconds. When you use a data source, this value is used as a parameter to the DataSource.setLoginTimeout(seconds) method. If no timeout is specified, a timeout is not set and you rely on the data source/application server settings. The timeout settings are almost always included with the data source definition. When you use a driver, set this value as a parameter to the DriverManager.setLoginTimeout(seconds) method. If no timeout is specified, the default value is 20 seconds.

The following example shows a database element that provides database driver settings in a destination definition.

<destination id="sql-product">
    <adapter ref="java-dao"/>

    <properties>
...
        <server>
            <database>
                <datasource>java:comp/env/jdbc/flexdemodb</datasource>
            </database>
...
    </properties>
</destination>

Configuring return types

For operations that return objects to the Flex client, you can specify the return type in java-class or actionscript-class elements, as the following example shows:

<destination id="sql-product">
    <adapter ref="java-dao"/>

    <properties>
...
        <server>
...
            <actionscript-class>Product</actionscript-class>
            <!--<java-class>samples.Product</java-class> -->
...
        </server>
...
    </properties>
</destination>

The actionscript-class element specifies the ActionScript type that you want returned from the server when no alias mapping to a specific Java class exists. The ActionScript class that you specify must have the RemoteClass metadata syntax, but without an alias that specifies a Java class.

The java-class element specifies a server-side Java class that maps to an ActionScript class that uses the RemoteClass(alias=javaclassname) metadata syntax. You use this element when you map a specific ActionScript type to a specific Java type that uses this syntax. The Java and ActionScript property names match the database field names. The ActionScript class designating the Java class specified in the java-class element must be available on the client.

Configuring fill operations

You configure fill operations in fill elements in the server section of a destination definition. The fill elements contain name and sql or statement elements, depending on whether you are using a single SQL statement or a stored procedure. The SQL code indicates properties from the object to use with #propName#. The following example shows several fill elements:

<destination id="sql-product">
    <adapter ref="java-dao"/>

    <properties>
...
        <server>
...
            <fill>
                <name>all</name>
                <sql>SELECT * FROM PRODUCT ORDER BY NAME</sql>
            </fill>

            <fill>
                <name>by-name</name>
                <sql>SELECT * FROM PRODUCT WHERE NAME LIKE CONCAT('%',
                    CONCAT(#searchStr#,'%'))</sql>
            </fill>

            <fill>
                <name>by-category</name>
                <sql>SELECT * FROM PRODUCT WHERE CATEGORY LIKE CONCAT('%',
                    CONCAT(#searchStr#,'%'))</sql>
            </fill>
...
        </server>
...
    </properties>
</destination>

In a Flex client application, the DataService.fill() method uses the named fill query that is defined in the destination. The arguments can be an anonymous ActionScript object with named parameters or a strongly typed ActionScript object. These arguments are used to define the SQL parameter values. A list is returned. The return type can be anonymous, based on a java-class value, or based on an actionscript-class value.

The following example is a simple client-side DataService.fill() method without named parameters:

ds.fill(dataCollection, named-sql-query, parameters);

The following example is a client-side DataService.fill() method with named parameters:

<mx:Button label="Search" click="ds.fill(products, combo.selectedIndex == 0 ? 'by-name' : 'by-category', {searchStr: searchStr.text})"/>

Configuring a getItem operation

You configure a getItem operation in a get-item element in the server section of a destination definition. The get-item element contains a sql element or a statement element, depending on whether you are using a single SQL statement or a stored procedure. The SQL code indicates properties from the object to use with #propName# text, where propName is the property name. The following example shows a get-item element:

<destination id="sql-product">
    <adapter ref="java-dao"/>
    <properties>
...
        <server>
...
            <get-item>
                <sql>SELECT * FROM PRODUCT WHERE PRODUCT_ID = #PRODUCT_ID#</sql>
            </get-item>
...
        </server>
    </properties>
</destination>

In a Flex client application, the DataService.getItem() method takes a single argument that is the identity of the item. The identity maps to the identity property specified in the Data Management Service destination. This field also links with the database field and the property name on the ActionScript object. The PreparedStatement uses the id argument value for its single ordered parameter.

The item returned is an anonymous object with property names consistent with database field names, a strongly typed ActionScript object where the type was specified using the actionscript-class element in the destination, or a strongly typed ActionScript object where the RemoteClass(alias="java-class") is equivalent to the class specified using the java-class element in the destination. In all cases, the property names are consistent with the database field names.

The following example shows the code for a DataService.getItem() method:

ds.getItem({employee_id:cursor.current.employeeid}); 

The property name employee_id is the id property on the ActionScript object as well as the database field name.

Configuring a createItem operation

You configure a createItem operation in a create-item element in the server section of a destination definition. The create-item element contains one or more sql or statement elements, depending on whether you are using a single SQL statement or a stored procedure. The SQL code indicates properties from the object to use with #propName#.

The createItem operation requires that you define two SQL queries. The create-item element creates the object and the id-query element obtains the identity property of the newly created object from the database. The id-query element can precede the create-item SQL statement. The Data Management Service executes the sql-update and the id-query within the same DataServiceTransaction object and uses the same connection to the database. The parameters for the SQL statement are retrieved from the properties of the createItem instance. As an alternative, you can set up a procedure call in the database that does the insert and id selection. For the create-item element only, you can designate the procedure-param element as an OUT parameter, which means that the value is returned to the client in the property-value field of the ActionScript instance.

You can use the identity results of the id-query in any SQL statement that comes after it. If the id-query comes first, it is executed before all of the SQL statements. If the id-query comes after any SQL statement, it is always executed after the first SQL statement. If you require additional flexibility in ordering, use run-time configuration of destinations as described in Run-time configuration.

The following example shows two create-item elements. The second element uses procedure-param elements.

<destination id="sql-product">
    <adapter ref="java-dao"/>

    <properties>
...
        <server>
...
<create-item>
    <sql>INSERT INTO employee (first_name, last_name, title, email, phone,
            company_id) VALUES (#firstName#, #lastName#, #title#, #email#, 
            #phone#, #companyId#)</sql>
    <id-query>SELECT LAST_INSERT_ID()</id-query>
</create-item>

<create-item>
    <procedure name="MY_INSERT_PROC">
        <procedure-param property-value="#id#" type="OUT"/>
        <procedure-param property-value="#firstName#"/>
        <procedure-param property-value="#lastName#"/>
        <procedure-param property-value="#title#"/>
        <procedure-param property-value="#email#"/>
        <procedure-param property-value="#phone#"/>
        <procedure-param property-value="#companyId#"/>
        </procedure>
</create-item>
...
        </server>
    </properties>
</destination>

The following example shows Flex client code for an Employee object and a DataService.create() method that creates the employee in the database:

...
var employee:Employee = new Employee();
employee.first_name = "Joe";
employee.last_name = "Dev";
employee.title = "engineer";
employee.email = "joedev@adobe.com";
employee.phone = "617-229-2065";
employee.company_id = 2;
ds.createItem(employee);
ds.commit();
...

Configuring a deleteItem operation

You configure a deleteItem operation in a delete-item element in the server section of a destination definition. The delete-item element contains a sql element or a statement element, depending on whether you are using a single SQL statement or a stored procedure. The SQL code indicates which properties from the object to use with #propName#.

The following example shows a delete-item element:

<destination id="sql-product">
    <adapter ref="java-dao"/>

    <properties>
...
        <server>
...
            <delete-item>
                <sql>DELETE FROM PRODUCT WHERE PRODUCT_ID=#PRODUCT_ID#</sql>
            </delete-item>
</create-item>
...
        </server>
    </properties>
</destination>

The following example shows Flex client code for deleting an item from the database. The properties for the instance to delete are mapped to the fields in the delete-item query in the configuration file. If all properties are not set, the delete operation fails.

...
var removedItem:Object = cursor.remove();
ds.commit();
...

If the server value is changed, the delete SQL operation fails to execute. In this situation, a DataSyncException is thrown. The exception contains the results of the get-item query.

Configuring an updateItem operation

You configure an updateItem operation in an update-item element in the server section of a destination definition. The update-item element contains a sql element or a statement element, depending on whether you are using a single SQL statement or a stored procedure. The SQL code indicates properties from the object to use with #propName#.

The following example shows an update-item element:

<destination id="sql-product">
    <adapter ref="java-dao"/>

    <properties>
...
        <server>
...
            <update-item>
                <sql>UPDATE PRODUCT SET NAME=#NAME#, CATEGORY=#CATEGORY#,
                    IMAGE=#IMAGE#, PRICE=#PRICE#, DESCRIPTION=#DESCRIPTION#,
                    QTY_IN_STOCK=#QTY_IN_STOCK# 
                    WHERE PRODUCT_ID=#_PREV.PRODUCT_ID#</sql>
            </update-item>
...
        </server>
    </properties>
</destination>

The server passes in the current value and the previous value from the client. If these are different, the parameters of the SQL statement are replaced and the update against the database is executed. If these are different, then the server value could have changed. The item is retrieved from the database using the get-item query defined in the destination. You can use _PREV to reference the previous version of the object.

The following example shows the Flex client-side ActionScript code for an updateItem operation:

...
cursor.current.last_name = lastName.text;
cursor.current.first_name = firstName.text;
ds.commit();
...

Configuring a count operation

You configure a count operation in a count element in the server section of a destination definition. The count element contains a name element and a sql element or a statement element, depending on whether you are using a single SQL statement or a stored procedure. The SQL code indicates properties from the object to use with #propName#.

The following example shows a count element:

<destination id="sql-product">
    <adapter ref="java-dao"/>
    <properties>
...
        <server>
...
            <count>
                <name>all</name>
                <sql>SELECT count(*) FROM PRODUCT</sql>
            </count>
        </server>
    </properties>
</destination>

In a Flex client application, the DataService.count() method execution and definition are similar to those of the DataService.fill() method. You can define multiple SQL statements in the definition. The statement that you use, and its parameters, are passed during the count method execution.

The following example shows a simple use of the DataService.count() method:

ds.count(named-sql-query, parameters);

The following example shows a more complex use of the DataService.count() method:

var countToken:AsyncToken = ds.count("firstNamedCount", parameters);
public function firstNamedCountResultHandler(event):void
{
var count:int = event.result;
}

Using server-side logging with the SQL assembler

The SQL assembler logs messages by using the server-side logging system that is configured through the services-config.xml file. For information about server-side logging, see Logging. To log SQL assembler messages, use the following log category:

LOG_CATEGORY = "DataService.SQL";

When you use debug-level logging, the log provides details on the reasons for SQL failures.