Retrieving data using SQLAssembler in LCDS

Data Management service of LiveCycle DS (LCDS) is amazing. It gives developers enormous power to create data driven applications.

I created a sample application which will retrieve data from a database using LCDS. In this sample I used SQLAssembler packaged with LCDS. SQLAssembler allows you to create CRUD applications by just creating SQL queries in an XML file. Isn’t that amazing? I just have to mention SQL queries in an XML file and everything else is handled by the assembler. You can also create custom assemblers, but that is not part of this discussion🙂

mx:DataService is the component used in the Flex application to communicate with data management service of LCDS on the server. This component is packaged in fds.swc.

You might be thinking why I should use Data Management service when I can retrieve data from a database using Remoting service. But that is not all it does, when you use Data Management service of LCDS. It will also automatically synchronize data on the client with data on the server. Imagine if there are two clients are accessing same data and one client changes data and other client still is looking at the old data. Data management service of LCDS will solve this by pushing the changes to all other clients. Isn’t that amazing.🙂 It’s got more. Check out this URL for more features you get: http://www.adobe.com/products/livecycle/dataservices/features.html

Let’s jump into the code🙂

Sample application

We will create one MXML file and add a destination to the data-management-config.xml. In this sample I am using the samples web application packaged with LCDS.

You have to configure your Flex application to access services-config.xml of the samples web application. This configuration is very similar to configuring your Flex application to use BlazeDS. Please check this URL for details on how to configure your Flex application to point to your BlazeDS folder. All the steps are same except that the folder you point should be wither samples web application of LCDS.

Flex Application

You can see that we are invoking initApp() method on creationComplete event of the application. in the initApp() method we are invoking fill() method of the DataService component. fill() method is the one which will connect to the server, get data and fill the array collection. First parameter to the fill() method is reference to the array collection and second parameter is used by the SQlAssembler to execute appropriate fill operation. Another worth mentioning in the code below is the DataService component. You can observe that we just gave the name of the destination to the DataService.

.mxml file

<?xml version=”1.0″ encoding=”utf-8″?>

<mx:Application xmlns:mx=”http://www.adobe.com/2006/mxml&#8221; layout=”vertical” creationComplete=”initApp();”>

<mx:Script>

<![CDATA[

import mx.events.CollectionEvent;

import mx.rpc.events.ResultEvent;

import mx.collections.ArrayCollection;

[Bindable]

private var companies:ArrayCollection = new ArrayCollection();

private var product:Product;

private function initApp():void

{

dsCompany.fill(companies, “all”);

}

]]>

</mx:Script>

<mx:DataService id=”dsCompany” destination=”sql-product”/>

<mx:DataGrid id=”companiesDg” dataProvider=”{companies}” editable=”true” width=”100%” height=”100%”/>

</mx:Application>

Adding destination to data-management-config.xml

I used the destination which is available in the samples application packaged with LCDS. If you are using samples web application of LCDS then please check of there is a destination with id sql-product in the data-management-config.xml. If this destination is not available in the configuration file, please add destination in this xml file to your data-management-config.xml file under <service> tag. For just retrieving data, the content in the destination you have to worry about is the <fill> tag. The SQL query inside the <fill> tag is the one which is executed when you invoke fill() method in the Flex application. The second parameter we passed “all” is used to select the appropriate query inside the <fill> tag. You can find the <fill> tag extracted from the destination tag below. Observe the <name> tag and the <sql> tag.

Please find more details on configuring SQLAssembler destination at this URL

<fill>

<name>all</name>

<sql>SELECT * FROM PRODUCT ORDER BY NAME</sql>

</fill>

That is all you need to do.🙂 You are all set to go. Just restart your server and the run the Flex application. You will be able to view the data in the DataGrid. We did not discuss the updating part here, but when we update the data from the Flex application you can see that all other clients viewing this data will get the latest data.🙂

10 Responses to Retrieving data using SQLAssembler in LCDS

  1. Bob Scott says:

    Hi Sujit,
    This is a great example. I was just tonight looking for something like this.

    After typing in the code for the mxml file, I’m getting the following error message:

    “Type not found or not a compile time constant: Product”

    This error is flagging the line that is declaring the variable product. It isn’t finding a Product class.

    Any help is appreciated.
    Thanks again,
    Bob

  2. Bob Scott says:

    Update:
    I commented out that line and it ran A-OK. Now I’m going to try and figure out what that line was for.

    Looking forward to the update portion!

    Bob

  3. Sujit Reddy G says:

    Hi Bob,

    You should create a class with name Product with properties you are expecting SQL query to return. LCDS will return the data as objects of the type you specify in the configuration file under action-script file tag. For the sample I used, please try using the class below.

    package
    {
    [Managed]
    [RemoteClass]
    public class Product
    {
    public var PRODUCT_ID:int;

    public var NAME:String = “”;

    public var CATEGORY:String = “”;

    public var PRICE:Number = 0;

    public var IMAGE:String = “”;

    public var DESCRIPTION:String = “”;

    public var QTY_IN_STOCK:int = 0;
    }
    }

    Hope this helps.🙂

  4. […] G. is a fantastic blogger with quite a few good articles on data services. I found his blog while search of information on custom […]

  5. Fernando says:

    Is there any “simple” way to get notified (and the clients updated) when the data changes in the datasource? Example: I have FLEX client app opened in 3 different machines. When data is changed in the (editable) datagrid, Livecycle DS takes care of updating the clients. Datasource is MS SQL server table. Now, if I change the data directly on that SQL table, nothing happens in the FLEX apps. This is important in environments where different technologies (not only Flex apps) interact with a data source. In my case we have a few different financial applications dealing with different sets of data. Any clue on how to deal with that? Updating the clients when changes in the SQL table occur? (without using fill commands polled every few seconds?).

  6. Sujit Reddy G says:

    Hi Fernando,

    I did not check out if LCDS has some way to do this directly.
    From top of my mind, I would create a Java class which will act as one of the client for the Data-Management destination. When some other application sends a request for updating the database i would call this Java class which is acting as client to update the data in the database using LCDS.
    This came from top of my mind, Please check out if this helps.

    I will try to find out if there are better ways to do this🙂

    Can you please have a look at “creating custom adapters” at the URL below and see if you can use that to solve your problem.

    https://sujitreddyg.wordpress.com/2008/03/10/creating-custom-messaging-adapters/

    Hope this helps.

  7. Joe says:

    Hi Fernando

    I am facing the same problem, the changes are being made by an engine that is inserting data into a table and i want the changes to be reflected to the clients. Did you find a way to do that? i would be very thankful if i got a reply🙂 because i couldn’t find a solution for that problem from the URL provided by Sujit Reddy G

  8. Hi Joe,

    Please find an article explaining how to do this at the URL below

    http://livedocs.adobe.com/livecycle/8.2/programLC/programmer/lcds/help.html?content=dms_advanced_3.html

    Hope this helps.

  9. Cato says:

    Hi mister🙂
    I’m using the LCDS SQLAssembler, when I use the create_item and return the id of that record up to this point it works fine, but in my database I have some default values thise are not returned together with the record created and sent back to the client.

  10. rana says:

    Hi Sujith,

    good example, but i am getting an error when run this

    ReferenceError: Error #1056: Cannot create property maxFrequency on mx.data.DataManagementConsumer.

    any help.

    Thanks.

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

%d bloggers like this: