Vaadin lets you build secure, UX-first PWAs entirely in Java.
Free ebook & tutorial.
Inserting Row with Composite Key Fails with SQLContainer's TableQuery
I'm unable to insert a new row into a table in that has a composite primary key consisting of two columns, using SQLContainer's TableQuery. The database is a Microsoft SQL Server DB, and the two columns are clearly marked in the database as primary key columns.
I first obtain the table as follows:rowItem.getItemProperty ("CHILD_NODE_ID").setValue
TableQuery tq = new TableQuery (tableName, connectionPool, new MSSQLGenerator());
// Set the version column to allow writes from several concurrent users.
I then insert a new row using the following code and verify that rowItem is non-null.
Item rowItem = linksTable.getItem (linksTable.addItem());
I then insert the primary keys in the correct order:
rowItem.getItemProperty ("PARENT_NODE_ID").setValue (sourceLink); // e.g. "PR-05"
rowItem.getItemProperty ("CHILD_NODE_ID").setValue (destLink); // e.g. "MS-03"
followed by all the other required (non-null) fields:
rowItem.getItemProperty ("...").setValue (...);
So far so good. But the moment I call
com.microsoft.sqlserver.jdbc.SQLServerException: The column array is not valid. Its length must be 1.
I set up breakpoints in the code and discovered that
a) The SQL INSERT query string is correct: it has the correct column names and values;
b) In the TableQuery.java file, line 584, the primaryKeyColumns List <String> DOES contain both the PARENT_NODE_ID and CHILD_NODE_ID columns in the correct order, but
c) the TableQuery code's executeUpdateReturnKeys method FORCIBLY IGNORES the second part of the key (CHILD_NODE_ID) by passing
primaryKeyColumns.toArray (new String) i.e. just the PARENT_NODE_ID
to the connection.prepareStatement method.
It's as if one part of Vaadin knows that the primary key is composite, but the other part of Vaadin forcibly erases that knowledge.
* I was using an older version of Vaadin, v7.1.x when the error first occurred. I upgraded to the latest version, v7.5.10, but the error persists.
* The suggestion given in the following post did not work: https://vaadin.com/forum#!/thread/4987569
* The code works if the primary key consists of just one column i.e. if it is not composite.
Any help would be greatly appreaciated. If the error can't be corrected with TableQuery and can be avoided only by using a FreeformQuery, it would be great if you can point me to an example/tutorial/code of its use, preferably one where it is used to insert a row with a composite key.
Thanks in advance,