I wanted to create a sample desktop application using Adobe AIR. Application was supposed to get the data from local oracle express edition database. I started working on it but soon i realize that I cannot connect to local database through AIR. I googled a lot but could not find anything. So i decided to create something new and then i started working on AirDBConnector.
AirDBConnector has three parts –
- java4airdb – Local Java Server that sits between air application and database.
- air-lib – flex library that provides API for DB communication.
- sql-gen – An Air application to generate sql query configurations.
java4airdb:
Its a java application that uses ServerThread and listens on particular port for air application db requests.
- airdb.connector.port : Specifies port to be used for communication.
- airdb.connector.sql-config: Path to sql config file.
- java4airdb.bat : Batch file to start java server for db interaction.
- java4airdb.jar: Java jar file
Starting server is very simple just define the connector port in properties file, specify the sql-config file path and run the java4airdb.bat file.
*This requires Java5+. Set JAVA_HOME property.
sql-gen:
Its a very simple utility air application to generate sql-config file in proper syntax.
- Run AirDbConfigGenerator. Click on ‘New Config’. Enter DSN Name. If required username and password.
- To add SQL queries, click on ‘+’ button in SQL queries. Add below fields and Save Query.
- Unique query id per config file.
- query type: Select, Insert/Update, Proc/Function
- SQL query
- Parameters
You can have any number of queries in config file.
- Once you are done with adding all queries. Click on ‘Generate XML’ button on top to generate sql-config.xml file.
Thats It! you have successfully generated you sql-config.xml and you can find it on desktop.
Isn’t that easy. You do not need to know XML syntaxes.
Sample XML file –
<sql-config>
<connection>
<dsn password="password" name="MyDSN" username="user1"/>
</connection>
<queries>
<select id="Query1">
<sql>Select * from test_table1 where id=?</sql>
<parameters>
<parameter>
<name>param1</name>
<type>BIGINT</type>
<typeVal>-5</typeVal>
</parameter>
</parameters>
</select>
</queries>
</sql-config>
air-lib:
This provides flex API to communicate to JAVA server. Integration with your AIR application is very easy, you just need to add AirDBConnector.swc in you application.
Below code shows the sample usage of library API –
1: <?xml version="1.0" encoding="utf-8"?>
2: <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml"
3: layout="vertical" xmlns:airdb="com.mm.apps.airdb.*"
4: creationComplete="airDB.init();">
5:
6: <mx:Script>
7: <![CDATA[
8: import com.mm.apps.airdb.AirConnectorEvent;
9: private function onIncomingData(event:AirConnectorEvent):void{
10: dg.dataProvider = event.resultRows;
11: }
12: ]]>
13: </mx:Script>
14:
15: <airdb:AirDBConnector id="airDB" serverPort="9000"
16: serverAddress="localhost"
17: dataReceived="onIncomingData(event)"/>
18:
19: <mx:Button label="Get Data" click="{airDB.executeQuery('get_frameworks');}"/>
20:
21: <mx:DataGrid id="dg" x="10" y="423" height="250" width="100%">
22: <mx:columns>
23: <mx:DataGridColumn dataField="framework_id"/>
24: <mx:DataGridColumn dataField="name"/>
25: <mx:DataGridColumn dataField="version"/>
26: <mx:DataGridColumn dataField="description"/>
27: <mx:DataGridColumn dataField="source_url"/>
28: </mx:columns>
29: </mx:DataGrid>
30:
31: </mx:WindowedApplication>
Line 15: Create an instance of AirDBConnector. Specify serverPort, serverAddress and event handling method for dataReceived event.
_Line 4: THE MOST IMPORTANT, CALL airDB.init()_
Line 19: on button click, query with id ‘get_frameworks’ is executed by calling executeQuery method on airDB. Optionally you can also pass parameters and callback function to executeQuery method. Below are two flavours of execute query method. These two methods returns an unique string id for each method call. If required you can use this id to match with the result id in event object.
1: executeQuery(queryId:String, callback:Function = null):String
2:
3: executeQueryParams(queryId:String, params:Array, callback:Function = null):String
callback function must have below signature-
function (event:AirConnectorEvent) void
AirConnectorEvent has below set of properties –
- message – XML Response from java4db. Sample XML Response is –
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <transporter> <message>Query with id end is processed.</message> <status>100</status> <data> <result size="4"> <row> <framework_id>1</framework_id> <name>Spring</name> <version>3.0.5</version> <description>Spring Framework</description> <source_url>http://www.springsource.com</source_url> </row> <row> <framework_id>2</framework_id> <name>Spring</name> <version>2.0</version> <description>Spring Framework</description> <source_url>http://www.springsource.com</source_url> </row> <row> <framework_id>3</framework_id> <name>Hibernate</name> <version>2.0</version> <description>Hibernate</description> <source_url>Hibernate.com</source_url> </row> <row> <framework_id>4</framework_id> <name>Hibernate</name> <version>3.0</version> <description>Hibernate</description> <source_url>Hibernate.com</source_url> </row> </result> </data> <requestId>B540B76E-4433-46F5-64DF-459F46715E16</requestId> </transporter>
- resultId : Unique id for each db request
- resultRowCount: Number of row return by select query
- resultRow: XMLList containing
elements from response. You can use this property for assignment to dataProviders.
So that’s all. I hope this is easy to interact with local database.
Project home – http://code.google.com/p/air-db-connector/
Download Release 1.0 – http://code.google.com/p/air-db-connector/downloads/list
Please let me know your comments/issues/changes.
