Searching a by SolutionModel created DataSource

[ROCLASI]

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.

Code: Select all

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)

[ptalbot]

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.

[rgansevles]

Robert,

Can you show a snippet of the code?

Rob

[ROCLASI]

Hi Rob,

Here is some simplified code that reproduces the problem.

Code: Select all

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);

}

[rgansevles]

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