Database update error inside an iteration

Question

question

Xabier A asked Jul 29, '20 Benjamin W2 answered Jul 29, '20

Database update error inside an iteration

Hello,

I have a database connection defined and it works.

I have a resultset fill with a "select" statement and it works.

I am iterating the previous resultset with a "while" control sentence.

I have a prepared Statement for database update well defined:

AND If I put the update prepared statement outbounds of the "while" loop the sentence works perfectly,

BUT if put the same prepared statement inside the "while" loop it gives the following error:

"FlexScript exception: Commands out of sync; you can't run this command now".

Database.PreparedStatement statement = con.prepareStatement("UPDATE TABLE SET STATUS=1 WHERE ID = :id");

statement.bindParam("id", id, Database.DataType.Int);

statement.execute();

1.- Does anybody know why it gives this error?

2.- I have to update a database for each row in a resultset (for each data I read of the resultset I have to update it in the database), how can I do it?

Thanks in advance.

Software Version:

FlexSim 20.1.2

flexsim 20.1.2 database

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2020-07-29T20:44:17Z

Benjamin W2 answered Jul 29, '20

Hi @xabier.a,

I think the error is showing up because you are trying to use the PreparedStatement.execute() command while itterating through your result set. the Execute() command also returns a result set, so I'm guessing there is a conflict there. For example the following code compiles just fine:

int i = 1;

while (i < 3)
{
   Database.PreparedStatement statement = con.prepareStatement("UPDATE actor SET last_name = 'Goodbye World' WHERE actor_id = :id");
   statement.bindParam("id",2,Database.DataType.SmallInt);
   statement.execute():
   i++;
}

Database.ResultSet result = con.query("SELECT * FROM actor");
while (result.fetchNext()) {
...
}

However, if I move the query before the first while loop:

int i = 1;

Database.ResultSet result = con.query("SELECT * FROM actor");

while (i < 3)
{
   Database.PreparedStatement statement = con.prepareStatement("UPDATE actor SET last_name = 'Goodbye World' WHERE actor_id = :id");
   statement.bindParam("id",2,Database.DataType.SmallInt);
   statement.execute():
   i++;
}

while (result.fetchNext()) {
...
}

Then I get the same error you did. If your result set is relatively small (less than a few thousand entries), then I would recommend using the ResultSet.cloneTo() method to clone it to a global table. You can then use FlexScript to make any changes you need, the use the Prepared Statement to communicate back to your database.

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

question