# getDataSetByQuery and temporary tables in MySQL

**URL:** <https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423>\
**Category:** Classic Servoy\
**Created:** [July 1, 2010, 9:47am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423 "2010-07-01T09:47:00Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![rioba](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@rioba](https://forum.servoy.com/u/rioba)\
**Post date:** [July 1, 2010, 9:47am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/1 "2010-07-01T09:47:00Z")

</div>

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

```auto
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

```auto
Table 'test.mytable' doesn't exist

```

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

---

<div class="post-metadata">

**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\
**Post date:** [July 1, 2010, 9:51am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/2 "2010-07-01T09:51:43Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [July 1, 2010, 9:55am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/3 "2010-07-01T09:55:30Z")

</div>

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:

```auto
 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.

---

<div class="post-metadata">

**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\
**Post date:** [July 1, 2010, 9:58am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/4 "2010-07-01T09:58:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rioba](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@rioba](https://forum.servoy.com/u/rioba)\
**Post date:** [July 1, 2010, 10:31am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/5 "2010-07-01T10:31:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\
**Post date:** [July 5, 2010, 12:29pm UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/6 "2010-07-05T12:29:55Z")

</div>

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](http://wiki.servoy.com/display/public/DOCS/JSDataSet#JSDataSet-createDataSource)

Rob

---

<div class="post-metadata">

**Author:** ![rioba](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@rioba](https://forum.servoy.com/u/rioba)\
**Post date:** [July 8, 2010, 7:50am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/7 "2010-07-08T07:50:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\
**Post date:** [July 9, 2010, 6:57am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/8 "2010-07-09T06:57:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rioba](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@rioba](https://forum.servoy.com/u/rioba)\
**Post date:** [July 13, 2010, 7:06am UTC](https://forum.servoy.com/t/getdatasetbyquery-and-temporary-tables-in-mysql/12423/9 "2010-07-13T07:06:10Z")

</div>

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.