getDataSetByQuery and temporary tables in MySQL

[rioba]

I am trying to get a dataset from a temporary table in MySql using databaseManager.getDataSetByQuery.

Code: Select all

var table = "mytable";
var ID = "Sales"               
var sqlExe = "DROP TABLE IF EXISTS " + table;
plugins.rawSQL.executeSQL(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()),sqlExe);
plugins.rawSQL.flushAllClientsCache(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()));

var sqlExe2 = "CREATE TEMPORARY TABLE " + table + " SELECT * FROM tb_sample WHERE fd_sample_id LIKE '" + ID + "%'";
plugins.rawSQL.executeSQL(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()),sqlExe2);
plugins.rawSQL.flushAllClientsCache(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()));

var sqlQry = "SELECT * FROM " + table + " WHERE fd_sample_id LIKE '" + ID + "%' ORDER BY fd_sample_id";
var sqlRes = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),sqlQry,null,-1);

At this point I always get this error message

Code: Select all

Table 'test.mytable' doesn't exist

The same query in SQL Explorer always gives the expected result. Any suggestion?

[Harjo]

you say, that it is a temp table, and you get the servername & table from your currentcontroller, is that the temp-table also?

so, please try:

Code: Select all

plugins.rawSQL.executeSQL('yourtempserver','yourtemptable',sqlExe);

[ROCLASI]

Hi Rioba,

Perhaps Servoy uses multiple connections (from the connection pool) and therefor it doesn't see the temp table (it's per session/connection).
I suggest you start a database transaction before (and commit after) to force Servoy to use one and the same connection.

So your code would look like this:

Code: Select all

var table = "mytable";
var ID = "Sales"               
var sqlExe = "DROP TABLE IF EXISTS " + table;
databaseManager.startTransaction(); // force to use 1 connection from the connection pool
plugins.rawSQL.executeSQL(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()),sqlExe);
plugins.rawSQL.flushAllClientsCache(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()));

var sqlExe2 = "CREATE TEMPORARY TABLE " + table + " SELECT * FROM tb_sample WHERE fd_sample_id LIKE '" + ID + "%'";
plugins.rawSQL.executeSQL(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()),sqlExe2);
plugins.rawSQL.flushAllClientsCache(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),databaseManager.getDataSourceTableName(currentcontroller.getDataSource()));

var sqlQry = "SELECT * FROM " + table + " WHERE fd_sample_id LIKE '" + ID + "%' ORDER BY fd_sample_id";
var sqlRes = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()),sqlQry,null,-1);
databaseManager.commitTransaction(); // commit all the changes and release the connection back into the pool

Hope this helps.

[pbakker]

The temporary table you create is most likely created only in the context of a certain database connection.

Servoy uses a pool of database connections and for each action that a client performs on a database, a connection is taken from the pool.

So, most likely, the database connection used for each interaction in this script (the two executeSQL() calls and the getDataSetByQuery) is a different one.

So, a couple of options to get this working:
- Not using temporary tables
- Use temporary tables, that are not connection specific (I do not know which (if any) database supports this)
- Start a transaction for the duration of the usage of the temporary table: as long as there is a transaction active on a certain database server from within a client, the same database connection is re-used for all interaction with that database.

Hope this helps,

Paul

[rioba]

This forum rocks, really, and has the fastest guns in the West.
Using start/commit transaction solved the problem. I had the suspect that the problem was with Servoy "losing" the temporary table, but I thought that within the same method a single database connection was used. Thank you to everybody.

[rgansevles]

rioba,

It seems you want to run a form on a specific data set (based on a query).

Servoy has a special feature for this: dataset.createDataSource().
It does all the temporary table handling for you under the hood.

http://wiki.servoy.com/display/public/D ... DataSource

Rob

[rioba]

Hi Rob,

thank you for the information. But how can I do searches inside that specific datasource? I think I must use Servoy find/search instead of SQL SELECT statements. I must admit that I have never been comfortable using Servoy find/search functions. I think they lack flexibility of SQL, also for simple searches.

rioba

[rgansevles]

rioba,

Yes, find/search would be the preferred way.
Currently, we user an inmemory hsql database under the hood but this may change in a future release.
You could probably query this using sql, but that could break since you would be using an internal API that way.

What limitations do you have with find/search?

Rob

[rioba]

Rob,

I think SQL gives me more flexibility and I also find SELECT statements easier to write and understand. But maybe this is just an habit.