DB2Command ExecuteNonQuery Insert multiple rows problem

Question

I'm attempting to insert multiple rows into a DB2 database using C# code like this:

string query = "INSERT INTO TESTDB2.RG_Table (V,E,L,N,Q,B,S,P) values" + "('lkjlkj', 'iouoiu', '2009-03-27 12:01:19', 'nnne', 'sdfdf', NULL, NULL, NULL)," + "('lkjlk2', 'iuoiu2', '2009-03-27 12:01:19', 'nnne2', 'sddf2', NULL, NULL, NULL)";

DB2Command cmd = new DB2Command(query, this.transactionConnection, this.transaction); cmd.ExecuteNonQuery();

If I stop building the query string after the first set of values is included it executes without an error. Attempting to load multiple values using this method results in the following error: Upload error : ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "," was found following "". Expected tokens may include: "". SQLSTATE =42601

The SQL syntax matches that which I have read elsewhere, such as http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query and IBM's documentation gives this example: cmd = conn.CreateCommand(); cmd.Transaction = trans; cmd.CommandText = "INSERT INTO company_a VALUES(5275, 'Sanders', 20, 'Mgr', 15, 18357.50), " + "(5265, 'Pernal', 20, 'Sales', NULL, 18171.25), " + "(5791, 'O''Brien', 38, 'Sales', 9, 18006.00)"; cmd.ExecuteNonQuery();

Can anyone explain what could account for this?

Answer 1

Did you try the insert thru command line?. Syntax seems to be correct as per the link below http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.sqlassist2.doc/sqlassist2/t_insert.htm

Answer 2

your syntax looks broadly correct.

• does it work from the the cli?
• have you taken a look at JDBC batched update. I think that you will find it nearly as fast., and a lot more readable and supportable.

Answer 3

I'm pretty sure that inserting mutliple rows doesn't work if you specify the rowlist; in this case

(V,E,L,N,Q,B,S,P)

To insert multiple rows you have to give the values in native order of the table's columns.

Try modifying your SQL to :

string query = "INSERT INTO TESTDB2.RG_Table values" +   
"('lkjlkj', 'iouoiu', '2009-03-27 12:01:19', 'nnne', 'sdfdf', NULL, NULL, NULL)," +   
"('lkjlk2', 'iuoiu2', '2009-03-27 12:01:19', 'nnne2', 'sddf2', NULL, NULL, NULL)";