trying to insert records in db2:
ok when using the rawsql plugin while refused when the servoy controller does it.
sql instruction in the log is pretty similar to mine except I don’t use arguments, what else could this be?
I tried 2 different drivers, same thing, so obviously there is something servoy does that the rawsql plugin does not do…
Can you post your query and error msg ?
the servoy issued query is (from the log)
insert into RS_RESEAU (RS_RESEAU_ID, SKCLI, RESEAU, RS) values (?, ?, ?, ?) with params: [5 ,type: java.lang.Long, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, ‘cvbncvbn’ ,type: java.lang.String, ‘vcbncvbn’ ,type: java.lang.String]
it returns:
“incorrect table for this operation”
mine using the rawsql plugin:
insert into eravf.rs_reseau (rs_reseau_id,skcli,rs,reseau) values (610371,‘100’,‘toto’,‘tutu’),(612717,‘1001’,‘toto2’,‘tutu2’),(613137,‘1002’,‘toto3’,‘tutu3’)
returns true.
I have also tried using ? in the query + argument, it also works if only one argument (but I never found how to use more with this strange plugin which requires a 1 line array only, so I put a joined array in my query most of the time).
The only syntax difference I see is that you prefix the table with the schema name (eravf.rs_reseau ) and Servoy doesn’t (RS_RESEAU).
Do you use multiple schema’s in this database? If not then setup the schema in the connection settings.
Hope this helps.
I have others, but this one is the default and works for “selects”. just tested the rawsql method without the prefix, still OK.
so still a mistery to me.
Very weird. I suggest you file a case.
waste of time I am afraid, they don’t have the db resource to repeat the case: this only happens with this db.
I would be more interested by the exact query issued by servoy as the log does not show it once arguments are inserted.
lesouef,
This does sound like a permission issue.
Probably the db2 account has select but not insert privileges on that database.
Rob
using the raw sql plugin and using the servoy controller to insert a record is using the same account, the one specified for the resources/db server… so if insert was disabled, it would be for both.
Yes, of course.
This is very strange, if the jdbc connection does not work with the right schema, the select (which was done when loading the form) would also have failed for the same reason.
What is the exact exception that you get?
Looking at the insert statement, I see that the RS_RESEAU_ID is set to null, maybe that is the issue here?
Do you use a sequence generator with that column?
Rob
but I never found how to use more with this strange plugin which requires a 1 line array only
What do you mean here? What is a “1 line array”?
Just put multiple questionmarks in the SQL string and supply an array with the appropriate amount of values.
Paul
Rob,
It is a std servoy serial seq, integer. I noticed this was declared as Long by java in the log, not sure this matches was the db2 beast understands.
the driver returned msg can be translated (IBM drivers are regional and thus french in my case) by: “this table [name] is not correct for this operation”.
and the seq is shown on screen before I get the error msg. I have also tried to remove the servoy seq, but no difference. Only the real query issued could help to locate it I think.
I have the same problem with delete: via the rawsql plugin, this is accepted, from the controller no. obviously here I can see the difference, and this has to see with a temp table creation (for rollback I guess) which is not accepted.
Paul,
Unless I got it wrong, you still must know the amount of record you want to insert, so I can’t make a generic query and only use the ? to insert data.
how would you write this query:
var sql= insert into table (f1,f2,f3) values (?,?,?)
rawsql…(server,table, sql,[1,2,3])
for 3 records for intance? if you build an array for values with
array[0]=[1,2,3]
array[1]=[4,5,6] etc…
the plugin does not accept it. to me this arguments array was meant to be used this way…
Paul,
Unless I got it wrong, you still must know the amount of record you want to insert, so I can’t make a generic query and only use the ? to insert data.
how would you write this query:
var sql= insert into table (f1,f2,f3) values (?,?,?)
rawsql…(server,table, sql,[1,2,3])for 3 records for intance? if you build an array for values with
array[0]=[1,2,3]
array[1]=[4,5,6] etc…
the plugin does not accept it. to me this arguments array was meant to be used this way…
Hi lesouef,
Now I get what you mean. I don’t think this is possible in SQL in general (but then again, I do not know all the details of all the different SQL dialects out there).
An “insert into table … values …” statement is to insert only 1 row, so when you have to insert 3 rows, you call the prepared statement 3 times with a different array of values.
If your flavor of database supports an SQL statement like the following to insert in one statement three rows:
var sql= insert into table (f1,f2,f3) values (?,?,?), (?,?,?), (?,?,?)
Your arguments array ought to look like:
[1a,1b,1c,2a,2b,2c,3a,3b,3c]
BTW: I never understand why people use rawSQL to insert records into the database and not the native Servoy way…
Paul
Rob,
I do not confirm the null value issued for the id, I get the correct sequence number.
Paul,
OK, in this case, it makes sense. And I’ll go ahead with my way which is to setup a big variable for 1000 rows of values, and call the query with this var as argument.
the major drawback is that it is not portable in between all sqls (sql 2005 for instance needs “union” for several lines), but at least mssql2008, mysql and db2 can cope with the same inserts, not too bad in my env.
lesouef,
What is the exact exception that you get?
Rob
com.servoy.j2db.ApplicationException: Impossible de sauvegarder les données du formulaire
at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:598)
at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:165)
at com.servoy.j2db.smart.TableView$8.mouseReleased(TableView.java:1278)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: com.servoy.j2db.dataprocessing.DataException: [SQL7008] Table RESEAUX_RS de SL incorrecte pour cette opération.
at com.servoy.j2db.dblayer.Zb.Za(Zb.java:119)
at com.servoy.j2db.server.Zc.Zo.Za(Zo.java:442)
at com.servoy.j2db.dataprocessing.Zu.performUpdates(Zu.java:396)
at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:464)
… 22 more
lesouef,
It seems this error has something to do with using that table in a transaction.
Something with a table not being journalled?
I guess the servoy insert using servoy code was done in a transaction and the raw sql insert was not?
Rob
correct, is it worth trying to open a transaction before issuing the raw sql query to confirm?
and can I work with the servoy cnotroller without transactions then?
If you need transactions in your solution depends on how you deal with your data in your solution.
My best guess is to try to ‘fix’ the table, it seems the mentioned journalling can be added to the table.
Rob
no, I don’t need transactions, this is a ref table, I only add 1 line from time to time.