Is there maximum amount of data you can put into the query?```
var done = plugins.rawSQL.executeSQL(“blah”,“blah_table”,query)
DELETE FROM test_child;
INSERT INTO test_child ( id, blah) VALUES ( 1,‘QUION’);
INSERT INTO test_child ( id, blah) VALUES ( 2,‘SMORGASBROTT’);
etc
etc
etc
let say a 500 INSERTS…
Servoy freezes up...and I need to go Ctrl+Alt+Del to stop the mess.
With smaller sql-statements it works like a charm....
Servoy Developer
Version R2 2.2.3-build 335
Java version 1.5.0_06-b05 (Windows 2000)
Hi, I have used the RawSQL plugin to insert 35,000 rows in one go.
You just collect all the INSERTS on separated lines into one string. Remember to add “;” at the end of each statement. Works like a dream.
swingman:
Hi, I have used the RawSQL plugin to insert 35,000 rows in one go.
You just collect all the INSERTS on separated lines into one string. Remember to add “;” at the end of each statement. Works like a dream.
Hi Swingman
Thankx for the info. Can you show an example…is it different from the query mentioned above? Becoz with the query mentioned above I couldn’t get in 1200 rows…without Servoy stopping all activities…
I’m doing pretty much the same as you, just more fields and more records – in batches rather than one by one. The only difference I can think of is that I use DB-generated keys, so I don’t have to insert a PK.
Are you checking to see if you get any error messages back from the rawSQL plugin?
Also, capture the insert statements as text and execute them outside Servoy, some tools like Aqua Data Studio (Any SQL database) or PGAdmin (Postgresql only) will tell you much more if there is something wrong with your SQL statements. You will be able to work out if this is a Servoy bug or a database problem.
I’m doing pretty much the same as you, just more fields and more records – in batches rather than one by one.
What do you mean in batches?
The only difference I can think of is that I use DB-generated keys, so I don’t have to insert a PK.
Gonna try that
Are you checking to see if you get any error messages back from the rawSQL plugin?
I just dont get that far…Servoy freezes up completely and the method is not finished, so I do get an errorcode
Also, capture the insert statements as text and execute them outside Servoy, some tools like Aqua Data Studio (Any SQL database) or PGAdmin (Postgresql only) will tell you much more if there is something wrong with your SQL statements.You will be able to work out if this is a Servoy bug or a database problem.
Did that with Sybase Central’s INteractive SQL…worked like a charm
Still having troubles with this plugin. Servoy freezes up completely when I do this:
if (arguments[0]){
var query = "DELETE FROM adres WHERE (adres='' OR adres is NULL) AND client_id="+arguments[0]
var done = plugins.rawSQL.executeSQL("assyst","adres",query)
if (done){
plugins.rawSQL.flushAllClientsCache("assyst","adres")
} else {
plugins.dialogs.showErrorDialog('Error', 'SQL error: '+plugins.rawSQL.getExceptionMsg(), 'Ok')
}
}
The query has also been tested in Sybase Central…and it works
Servoy Developer
Version R2 2.2.4-build 336
Java version 1.5.0_06-b05 (Windows 2000)
It could be you ran into a lock. One session blocks your raw sql statements and your method waits for an answer of the (blocked) statement. Check your database if you have a lock while Servoy “hangs”.
What database are you using? Do you have a transaction running while it happens?
patrick:
It could be you ran into a lock. One session blocks your raw sql statements and your method waits for an answer of the (blocked) statement. Check your database if you have a lock while Servoy “hangs”.
What database are you using? Do you have a transaction running while it happens?
I am the only user…the row(s) I am trying to delete don’t exist long enough to be locked be anything or any-one. I am using sybase. But I will look into it when it hangs…I now use the Servoy-way to search and delete for the time beeing.
Hi, I have found that sometimes an operation takes a long time, it kind of hangs for a while – like 5-10 minutes. I haven’t found a pattern yet, so I don’t know if I’m doing something stupid, my server has a problem or it is Servoy’s fault.
When I am correct in what I am thinking the plugin is a 1:1 implementation of Java stuff. Having that said I suspect it is the driver and not the plugin. Are you using the latest driver?
patrick:
It could be you ran into a lock. One session blocks your raw sql statements and your method waits for an answer of the (blocked) statement. Check your database if you have a lock while Servoy “hangs”.
What database are you using? Do you have a transaction running while it happens?
I stand corrected, there seems to be locks. See attachment
IT2BE:
When I am correct in what I am thinking the plugin is a 1:1 implementation of Java stuff. Having that said I suspect it is the driver and not the plugin. Are you using the latest driver?
I think you already have your answer to what caused the issue but I was talking about your database driver… Working this way you are working on the database without any ‘help from Servoy’. That’s also the tricky part. You need to be aware of database specific stuff in some cases and be aware locking stuff in this particular case
if the connection you are using locks a record for whatever reason (usually due to a transaction) and you then fire a sql to such a locked record, you are “dead”. The reason for this is that the SQL plugin uses its own connection for its stuff, so the database correctly refuses any changes to those records until the lock is released.
Then you are dead-locked. Your method fires a statement to a record that was locked by your method before and waits until that is finished. That can take a while
I have run into the same situation several times and already discussed this with Jan Blok. I think the behaviour would probably improve if the same connection was used. Then the database will allow any statement on locked records, because the lock comes from the same session as the statement is using. So maybe we should emphasize this wish and move this to the feature request section…
