How to access database from flex 2

We often hear people asking about how to config datasource in flex to connect to a database. It is a miss conception to think that you need to config a datasource in flex and can use that datasource to connect to a database. Then how does flex app access data from a database? Flex does not connect to DB directly, but thru Java or other means. The doc has all the details regarding this topic, and has a sample of using PHP. But it may not be clear for people who are not familiar with J2EE to connect using java. Here is the steps to create access to database using Java.

Basically, there are three steps you need to accomplish in order to communicating with database from flex.

1. Write java code to communicate to the database. We usually call this an Assembler. In here, you provide the information about what database and jdbc driver you are connecting to, and create a connection to it.

2. Then config a destination which point to the Assembler in data-management.xml. Any flex app uses this destination will use this Assembler, and connecting to the same database.

3. In your flex code, you reference the data service like this:
dsEmployee = new DataService(“crm.company”);
here crm.company is a destination defined in step 2.

Now, Let us look at the sample code included in the samples.war to see how it is done. Let us look at the crm sample in dataservice.

1. Open dataservice\crm\companyapp.mxml, In there you can see the following code:

dsCompany = new DataService(“crm.company”);
// if hibernate is used, change the destination of the data service
// dsCompany = new DataService(“crm.company.hibernate”);
dsCompany.addEventListener(ResultEvent.RESULT, companyResultHandler);
dsCompany.addEventListener(FaultEvent.FAULT, faultHandler);
dsCompany.addEventListener(DataConflictEvent.CONFLICT, conflictHandler);
dsCompany.autoCommit = false;

Here we are using a dataService named “crm.company” for our app.

2. Open WEF-INF\flex\data-management.xml, we can see crm.company is defined as:

<destination id=”crm.company;”>
<adapter ref=”java-dao” />
<properties>
<source>samples.crm.CompanyAssembler</source>
<scope>application</scope>
….

This destination point to java class amples.crm.CompanyAssembler. This is our Assembler.

3. Let us see how the Assembler is constructed.
— go to WEB-INF\src\samples\crm\CompanyAssembler.java, we can see :
CompanyDAO dao = new CompanyDAO(); //using CompanyDAO class
— go to CompanyDAO.java, we can see
c = ConnectionHelper.getConnection(); //using ConnectionHelper class
— go to ConnectionHelper.java, we can see the code to connect to database:

private ConnectionHelper()
{
try
{
Class.forName(“org.hsqldb.jdbcDriver”);
// Obtain a path to WEB-INF/classes/samples/crm
String str = URLDecoder.decode(getClass().getClassLoader().getResource(“samples/crm”).toString(),”UTF-8″);
// Create HSQLDB JDBC URL pointing to WEB-INF/db/crm/crm (where the last crm is the datanase name)
url = “jdbc:hsqldb:” + str.substring(0, str.indexOf(“classes/samples/crm”)) + “db/crm/crm”;
}
catch (Exception e)
{
e.printStackTrace();
}
}

This is the core of the Assembler. This is where you tell flex which database to connect.

The most important information you need to provide are the driver name and the URL. Each database has different driver name and URL, you have to make sure you are using the correct name and format. Here is a list of driver name and example of URL:

#MySql
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test

#Oracle
#driver=oracle.jdbc.OracleDriver
#url=jdbc:oracle:thin:@test:1521

#Sybase Enterprise
#driver=com.sybase.jdbc3.jdbc.SybDriver
#url=jdbc:sybase:Tds:localhost:2048/test

#Sybase Anywhere
#driver=com.sybase.jdbc3.jdbc.SybDriver
#url=jdbc:sybase:Tds:localhost:2638/test

#Informix
#driver=com.informix.jdbc.IfxDriver
#url=jdbc:informix-qli://localhost:50000/test:informixserver=testserver

#MS SQL
#driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
#url please see http://msdn2.microsoft.com/en-us/library/ms378428.aspx