Custom DataSource for SQL queries/stored procedures

[ectar]

I want to create custom SQL driven datasource, which runs queries/executes stored procedures, then massages data and returns it to client side. Is there any example how to do so within smartGWT.

Obviously I just can open JDBC connection to DB myself and execute all SQL queries, but that wont be using smartGWT features.

Please advice, thank you

[Isomorphic]

See the "SQL" folder. You can customize the SQL piecewise in your .ds.xml file. You can use <customSQL> to call stored procedures (just put the call right into the SQL). You can also add DMIs (see User-Specific Data example) to a SQLDataSource to add other custom logic (such as data massaging that cannot be done with SQL).

[ectar]

Based on Custom DataSources->Simple sample in showcase I've tried to do something like that:

<DataSource 
	ID="AFM_FLDS_SHORT2"
	serverConstructor="com.ho.nomad.server.AFMFLDSDataSource"  
>
	<fields>
		<field primaryKey="true" name="TABLE_NAME" length="32" type="text"></field>
		<field primaryKey="true" name="FIELD_NAME" length="32" type="text"></field>
		<field primaryKey="true" name="VALUE" length="32" type="text"></field>
		<field primaryKey="true" name="LABEL" length="32" type="text"></field>
	</fields>
</DataSource>

package com.ho.nomad.server;

import java.util.ArrayList;
import java.util.List;

import com.isomorphic.datasource.BasicDataSource;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class AFMFLDSDataSource extends BasicDataSource {
	private static List data = new ArrayList();

	public DSResponse executeFetch(DSRequest req) throws Exception {
		DSResponse resp = new DSResponse();

		// TODO Full fill Response

		resp.setData(data);
		resp.setStatus(0);
		return resp;
	}
}

Then just executing:

		DataSource ds23 = DataSource.get("AFM_FLDS_SHORT2");		
		ds23.fetchData();

It actually works, and I'm hitting executeFetch ! but now I have somehow contact database.
What would most appropriate method in this case ?

[Isomorphic]

Removing all of your server-side code and using the built-in SQL DataSource would be the most appropriate approach. You can still add custom business logic later, set eg the SQL -> User-Specific Data method.

[ectar]

I actually achieved what I want using BasicDataSource with SQL connection, now I'm going to try DMI (Still cannot understand how it works properly)

Here is my DataSource:

package com.ho.nomad.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;

import com.isomorphic.datasource.BasicDataSource;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;
import com.isomorphic.sql.SQLConnectionManager;

public class AFMFLDSDataSource extends BasicDataSource {
	private List<Map<String, String>> data = new ArrayList<Map<String, String>>();

	public DSResponse executeFetch(DSRequest req) throws Exception {
		DSResponse resp = new DSResponse();

		LinkedMap crit = (LinkedMap) req.getCriteria();

		String tableName = crit.get("TABLE_NAME").toString().toLowerCase();
		String fieldName = crit.get("FIELD_NAME").toString().toLowerCase();

		String enumList = "";

		try {
			Connection cc = SQLConnectionManager.getConnection();
			PreparedStatement prep = cc
					.prepareStatement("SELECT table_name, field_name, ENUM_LIST FROM afm_flds WHERE table_name=? AND field_name=?");
			prep.setString(1, tableName);
			prep.setString(2, fieldName);

			ResultSet res = prep.executeQuery();
			// it suppose to be only one
			if (res.next()) {
				enumList = res.getString("ENUM_LIST");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (enumList != null && enumList.length() != 0) {
			String[] result = enumList.split(";");

			for (int x = 0; x < result.length; x += 2) {

				Map<String, String> newRecord = new HashMap<String, String>();

				newRecord.put("TABLE_NAME", tableName);
				newRecord.put("FIELD_NAME", fieldName);
				newRecord.put("VALUE", result[x]);
				newRecord.put("LABEL", result[x + 1]);

				data.add(newRecord);
			}
		}

		resp.setData(data);
		return resp;
	}
}

This DataSource used in setOptionDataSource() for Select Item to set options in ListGrid during edit operation.