database transactions

Classic Servoy
1

hi!

In the below code.

  1. foundset.deleteAllRecords (table1)
  2. create insert records from table2 and execute rawsql.executeSQL to table1
  3. plugins.maintenance.getServer.droptable(table2)
  4. update sequence for table 1

if i place databaseManager.starttransaction before line1 and committransaction after line4. Would the data be able to rollback if the all the steps were not completed?

2

Hi Rogel,

Is table2 a temp table ? I don’t see where you create it.
If it’s not a temp table you should consider that not all of the SQL vendors support transactions on DDL commands. PostgreSQL does support transactions on most DDL commands.

3

ROCLASI:
Hi Rogel,

Is table2 a temp table ? I don’t see where you create it.
If it’s not a temp table you should consider that not all of the SQL vendors support transactions on DDL commands. PostgreSQL does support transactions on most DDL commands.

I created table2 using the server.createTable and just placed TEMP_ as prefix. Is there a servoy method to create temp tables?

4

Hi Rogel,

For temp tables you use SQL and the rawSQL plugin.
Also server.createTable is a maintainance plugin function and I don’t think these functions work in the client. It’s designed for use in pre- and post import hooks when you import your solution into the server.
Are you writing this for those pre-/post import scripts ?

5

ROCLASI:
Hi Rogel,

For temp tables you use SQL and the rawSQL plugin.
Also server.createTable is a maintainance plugin function and I don’t think these functions work in the client. It’s designed for use in pre- and post import hooks when you import your solution into the server.
Are you writing this for those pre-/post import scripts ?

Hi Robert,

The solution is in a web service for it needs the process to be done in the server (i am not sure it headless client is better). I am not confident in using the “CREATE TEMP table” SQL because I am not sure if other database can support it. I am doing a backup/restore database utility.

6

Hi Rogel,

There are indeed some differences in syntax/behavior between the vendors. For example in PostgreSQL a temp table is only visible in the connection (session) it was created in. So multiple sessions can create the same temp table. Other vendors allow such temp table to be visible to other sessions as well.
Anyway, I think for your solution you need to use plain SQL to create any table since (if I am not mistaken) the maintainance plugin won’t do it for you in web/headless/smart client. And you need to make sure this table name is then unique as well for this specific user.
And since DDL (create/drop table, etc) with transactaction support (being able to roll it back) is also not cross-vendor you would have to handle that yourself as well (the cleaning up of the table).

7

ROCLASI:
And since DDL (create/drop table, etc) with transaction support (being able to roll it back) is also not cross-vendor you would have to handle that yourself as well (the cleaning up of the table).

The DDL you are referring to is the native SQL statements or servoy objects?

8

Hi Rogel,

I am refering to any Data Definition Language (DDL) commands (CREATE/ALTER/DROP) that is send to the backend database, be it via the rawSQL plugin or via Servoy objects.
If the backend doesn’t have support for these statements to execute inside a database transaction then you can’t roll them back.

9

ROCLASI:
Hi Rogel,

I am refering to any Data Definition Language (DDL) commands (CREATE/ALTER/DROP) that is send to the backend database, be it via the rawSQL plugin or via Servoy objects.
If the backend doesn’t have support for these statements to execute inside a database transaction then you can’t roll them back.

I see. But the SQL generated by the Servoy objects are likely to work on “most” backend databases?