Searching a by SolutionModel created DataSource

Classic Servoy
1

I have a dynamically created form that uses a datasource provided by a dataset. So the form is not directly linked to a server and/or table.
When I try to search in this form using custom code and the correct SQL then get an error that it can’t find the table.

Error loading primary key data, org.apache.commons.dbcp.DbcpException: java.sql.SQLException: 
  Table not found in statement [select "_sv_rowid" from "TEMP_7_ad574a26-df61-44c7-ac0b-cc644492516b"  where "_sv_rowid" in (SELECT pk_id,unique_name FROM individuals WHERE upper(unique_name) LIKE upper(%?%)) order by "_sv_rowid" asc] 

java.sql.SQLException: 
  Table not found in statement [select "_sv_rowid" from "TEMP_7_ad574a26-df61-44c7-ac0b-cc644492516b"  where "_sv_rowid" in (SELECT pk_id,unique_name FROM individuals WHERE upper(unique_name) LIKE upper(%?%)) order by "_sv_rowid" asc]

(error formatted for better readability)

Is this a limitation of working with custom datasources ?

Servoy 4.1.2 and 4.1.3
SQL Server 2005 (jtds)

2

Hi Robert,

I have had the same kind of trouble some time ago, when using a memory DataSource, Servoy insisted to generate SQL to update my record.

I haven’t found a workaround, I thought it was me so I just forgot about it and made things completely differently.
I am glad to know that I am not alone, but not glad to know that this problem is real when using memory DataSource on a form.

It looks like a bug to me.
You should submit a case to the support system.

3

Robert,

Can you show a snippet of the code?

Rob

4

Hi Rob,

Here is some simplified code that reproduces the problem.

function CustomDatasourceQuery()
{
 	var sForm 	= "myTestForm",
 		sServer	= "ihe"
 		sQuery 	= "SELECT pk_id,unique_name FROM individuals",
 		nWidth	 = 600,
 		nHeight	= 500;

 	ds = databaseManager.getDataSetByQuery(sServer,sQuery,null,-1);
	oForm = solutionModel.newForm(sForm ,ds.createDataSource("myData",[DM_COLUMNTYPE.INTEGER,DM_COLUMNTYPE.TEXT]),null,false,nHeight,nWidth);

 	application.showFormInDialog(forms[sForm],-1, -1, nWidth, nHeight, "Test Form", false, false, sForm, false);

	// lets load it again. ==> ERROR
	forms[sForm].controller.loadRecords(sQuery);

}
5

Robert,

When you create a form on a data set, the form works with this data and is not based on a database table, regardless where the data came from (a query, a file, etc).
Therefore you cannot use loadRecords(sql) on it since it has no link to the database anymore.

If you want to reload you will have to create a new data set, create a new data source from it and assign it to the forms dataSource property (or create a new form).
Note that the runtime form has to be recreated for it, because you are changing the form design.

A ds.removeRow(-1) will delete all data of the old data set, you may need that to prevent a memory leak.

Rob