Configuring a destination that uses the SQL assembler -- LiveCycle Data Services ES 2.5

Before using the SQL assembler from a Flex client, you must configure a destination that uses the SQL assembler. This topic describes how to do that and shows corresponding client-side code where applicable.

The following example shows a complete destination definition to use the SQL assembler; the samples web application included with LiveCycle Data Services 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:file:C:/lcds/jrun4/servers/default/samples/
                    WEB-INF/db/flexdemodb/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.

Specifying the Java adapter

To use the SQL assembler, you must specify an instance of the Java 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

To use the SQL assembler, you must 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

To use the SQL assembler, you must specify the identity property, which should map to the database field. Additionally, this name should correspond to the name of a property on 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.

<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:file:C:/lcds/jrun4/servers/default/samples/
                    WEB-INF/db/flexdemodb/flexdemodb</url>
                <username>sa</username>
                <password></password>
                <login-timeout>15</login-timeout>
            </database>
...
    </properties>

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>

You can use the optional login-timeout property for a data source or a driver. This value is specified 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 to handle this. The timeout settings are almost always included with the data source definition. When you use a driver, use this value as a parameter to the DriverManager.setLoginTimeout(seconds) method. If no timeout is specified, the default value is 20 seconds.

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 there is no alias mapping to a specific Java class. 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 should 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 fill in the SQL parameters. A list is returned. The return type can be anonymous, based on a java-class value, or based 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 or 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 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 ID of the item. The name of the ID maps to the identity property specified in the data 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 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 java-class element in the destination. In all cases, the property names are consistent with the database field names.

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

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 a sql or 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 createItem operation requires two different SQL queries to be defined, the create-item element creates the object and the id-query element obtains the identity of the newly created object from the database. Alternatively, the id-query may precede the create-item SQL. The sql-update and the id-query are executed within the same DataServiceTransaction and use 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 setup a procedure call in the database that does the insert and id select. For the create-item element only, you can designate the procedure-param as an OUT parameter, which means that the value is returned to the client in the ActionScript instance's property-value field.

The following example shows two create-item elements. The second one 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 or 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#.

<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 matched up with the fields in the delete-item query in the configuration file. If all properties are not available, the delete fails.

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

In the case where the delete SQL fails to execute, the server value might have changed. In this conflict 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 or 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#.

<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 may 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 or 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#.

<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 is similar to that 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.

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

The following example shows a more complex usage 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 services-config.xml; for information about server-side logging, see Server-side service logging. To log SQL assembler messages, use the following log category:

LOG_CATEGORY = "DataService.SQL";

When you use debug-level logging, on a SQL Exception or error executing, the log provides details on the reason for the SQL failure.

Current page: http://livedocs.adobe.com/livecycle/es/sdkHelp/programmer/lcds/data_adapters_17.html