I need to create records via SQL INSERTS

[elmono_acosta]

Is it possible to setup a string with an sql INSERT command so I can create record in my database this way ?

Some times when you need to create records in a 3 or even 4 way relationship, it is much easier with sql rather than with the standard method with servoy.

If the answare is YES, HOW ??????
Thanks.

[pbakker]

You could do it by making a stored procedure in your database, to which you pass your insert statement, then the SP evaluates your string and executes it.

But, this insert will not be reflected in Servoy Client/Developer, untill you restart it.

I'm sure some other people on the forum will tell you it's not so hard to do the same through Servoy

[bcusick]

You cannot execute "raw" SQL statements (except for SELECT) in Servoy. The reason is because of client data consistency. If you did a delete or insert of tens or hundreds of records - how would Servoy "know" what records were affected? Answer - it wouldn't - so you could wind up in a situation where a client was looking at invalid data.

Instead - use a stored procedure to insert records or you can also use a loop in Servoy and add the records that way as well.

Hope this helps,

Bob Cusick

[r.rios]

You could use a plugin to do an insert. But, like Bob said, Servoy would not be aware of the changes and could mean chaos.

I'm using a plugin to do a special insert into a table under special circumstances, but this is not recommended if not necessary.

[elmono_acosta]

OK.
What about if I use the plug in, and every time servoy wants to read data from the database I use a method that does:

//flushes the client data cache for recordIndex -1
//in the related foundset
//-1 for all data

databaseManager.refreshRecordFromDatabase(order_to_orderdetails,-1);

[r.rios]

I'm not sure. It could work, maybe one of the Servoy kids can help answer that. Never the less, I wouldn't recommend intense use of this method.

[Jan Aleman]

Invaliditing cache can have enormous performance impact, imagine what would happen if you have 1000 users of your solution and you keep invaliditing theire entire cache all the time.

Why don't you simply create a form on the table you want to insert to and create new records through that? It doesn't matter at all how many relations away it is, you can address it directly. Aditionally that is also easier to code than INSERT statements and broadcasting and caching remains accurate.