sql-update in a action and a route leads to an error

[Jean-Marc Lebourg]

Hi there,

I've set an action with sql-update and then a route with this action but it doesn't work :(

I've managed to work with sql-insert, sql-delete etc. but not for sql-update and I've always an error:
"An exception occurred while executing 'UPDATE myTable SET WHERE id = ?"

My route is ok with :id in the url, and it works for sql-delete... but for sql-update I don't get it!

Any clue about this ?

[Christoph Kappestein]

Hi,

it looks like your update does not update any fields, could you show me the action config and also the payload which you send to your route to produce this error?

best regards

Christoph

[Jean-Marc Lebourg]

I'm using QML and JS to call the route:

HttpRequest .put(fUrl + "/" + fAppEP + "/" + strCmd + params) .set("Authorization", "Bearer " + fToken) .set("Accept", "application/json").timeout(5000) .send(jsData) .then(function(res) { usersCmdCompleted(res, sender) }) .catch(function(err) { console.log(" --- error message for: " + sender + " = " + JSON.stringify(err)) })

I'm using the same type of code and the same data:
.send(jsData)
for the sql-insert action and other sql actions and it works, the sql-update is the only one I can't manage to work!

The Action in Fusio:

Name

AppUsersUpdate

Class

Fusio\Adapter\Sql\Action\SqlUpdate

Engine

Fusio\Engine\Factory\Resolver\PhpClass

Config

{ "connection": "6", "table": "users" }

The route:

/app/updateUser/:id

• PUT

Status

ActivePrivate

Description

updateUser

Operation-Id

put.app.updateUser.id

Action

AppUsersUpdate

[Christoph Kappestein]

Hi,

ok, everything looks fine but could you show me also the table structure and the actual JSON data which you send over the wire, I would assume, that the JSON data which you send to the endpoint is either empty or has no matching columns.

best regards

Christoph

[Jean-Marc Lebourg]

Hi and thank you for your answer.

I guess the troubles come from the columns, because now I've got the same type of error, even on the SQL-Select-All action after I've deleted some columns from the table...
So it makes me think there's another more general trouble: 

even after I've erased all the schema, actions, routes and connection... And recreate all, to be sure it will take the new structure of my table, it continues to try to access old columns that doesn't exist anymore in my table...

So do I have to reinstall Fusio???

I'm a little disappointed by some things that seems to be weak in this software, like when I delete a route or other entries, I can't create a new one with the same name, the system raise an error and I must use a new name just as if it hasn't been deleted properly...

If you can help with this it would be very kind because, except these troubles, I like Fusio and I intend to use it but I can't understand why simple things like that are so complicated to manage.

Best,

Jim

[Christoph Kappestein]

Hi Jim,

so the SQL Actions cache the table structure because of performance reasons, you can simply delete all files in the cache/ folder to clear the cache. Then you actions should also use the current schema.

The other behaviour which you have noticed regarding the deletion is indeed intended. So in general at Fusio if you delete i.e. a route, action or schema those entries are not actual deleted from the database

instead they are only marked as deleted, under System / Trash you can see and restore all deleted records. We don`t actual delete them so that a user has the option to restore the record in case of a mistake

and to keep all references in the logs etc. clean.

best regards

Christoph

[Jean-Marc Lebourg]

Thank you Christoph, your answer gives me courage, I will try what you told me and we will see if it works better like this!

Best,

Jim

[Jean-Marc Lebourg]

Beyond the cache problem and to be sure of the procedure, when we change the structure of a table in the database: should we just adjust the schema "Entity" or should we modify other things?

[Jean-Marc Lebourg]

Hi,

Everything works perfectly now! Even the sql-update action.

But the cache was only part of the problem.

On the other hand I noticed yet another problem: the columns of the table that are named with a capital letter in the name such as "userData", are not taken into account correctly in the schema SQL_Entity because it generated me: "userdata" instead of "userData" and that's why the update did not work in the first place.

Thank you for your help.

Jim