# slq syntax with db2 **URL:** \ **Category:** Classic Servoy\ **Created:** [October 8, 2010, 2:13pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006 "2010-10-08T14:13:30Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 8, 2010, 2:13pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/1 "2010-10-08T14:13:30Z")
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… ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [October 8, 2010, 2:19pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/2 "2010-10-08T14:19:23Z")
Can you post your query and error msg ? ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 8, 2010, 3:52pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/3 "2010-10-08T15:52:36Z")
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). ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [October 8, 2010, 3:57pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/4 "2010-10-08T15:57:59Z")
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. ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 8, 2010, 5:56pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/5 "2010-10-08T17:56:33Z")
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. ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [October 9, 2010, 5:25pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/6 "2010-10-09T17:25:49Z")
Very weird. I suggest you file a case. ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 11, 2010, 6:48am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/7 "2010-10-11T06:48:25Z")
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. ---
**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\ **Post date:** [October 11, 2010, 12:33pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/8 "2010-10-11T12:33:39Z")
lesouef, This does sound like a permission issue. Probably the db2 account has select but not insert privileges on that database. Rob ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 11, 2010, 2:05pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/9 "2010-10-11T14:05:50Z")
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. ---
**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\ **Post date:** [October 12, 2010, 7:31am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/10 "2010-10-12T07:31:07Z")
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 ---
**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\ **Post date:** [October 12, 2010, 8:04am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/11 "2010-10-12T08:04:25Z")
> 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 ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 13, 2010, 7:27am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/12 "2010-10-13T07:27:58Z")
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… ---
**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\ **Post date:** [October 13, 2010, 7:46am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/13 "2010-10-13T07:46:00Z")
> 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: ```auto 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 ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 13, 2010, 7:51am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/14 "2010-10-13T07:51:08Z")
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. ---
**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\ **Post date:** [October 18, 2010, 6:29am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/15 "2010-10-18T06:29:21Z")
lesouef, What is the exact exception that you get? Rob ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 18, 2010, 8:26am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/16 "2010-10-18T08:26:46Z")
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 ---
**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\ **Post date:** [October 18, 2010, 8:48am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/17 "2010-10-18T08:48:19Z")
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 ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 18, 2010, 11:09am UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/18 "2010-10-18T11:09:12Z")
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? ---
**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\ **Post date:** [October 18, 2010, 3:33pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/19 "2010-10-18T15:33:31Z")
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 ---
**Author:** ![lesouef](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@lesouef](https://forum.servoy.com/u/lesouef)\ **Post date:** [October 18, 2010, 4:09pm UTC](https://forum.servoy.com/t/slq-syntax-with-db2/13006/20 "2010-10-18T16:09:40Z")
no, I don’t need transactions, this is a ref table, I only add 1 line from time to time.