Hi all I am trying to use a db connection using jdbc in order to insert a record into a table. The main issue is that I need to do a select on that same table first in order to find out what the last number was put in, as the record I am inserting is that number + 1.
I have the following, which I think is fairly close, but i'm not quite certain of the intricacies(sp) of how jdbc would convert this. I appreciate any comments or suggestions that anyone may have.
"INSERT INTO table (PRODUCT, ITEM-STATUS, PRICE, ORD-ITEM) VALUES " + " ( ?, 'Z', ?, " + " (Select MAX(table.ord-item) from table where table.entity ='"+Constants.ENTITY+ "' AND table.ORD-NUM=" +100 + " AND table.ORD-TYPE='ORD' AND table.BRANCH='006' )" + ")";
I am getting the error of unable to understand after Insert. Again any thoughts or suggestions would be greatly appreciated.
Well, I expect that is invalid SQL when all the dust settles and that string is evaluated. You might consider doing the SELECT first and the INSERT as a separate statement. Or if your database supports auto-numbering columns or some similar concept, then just make use of that.
To use auto-numbering, declare the column that is going to store the sequence number appropriately (varies by database). Then INSERT all the columns except that one, and the database will assign the default automatic number to it.
Joined: Aug 23, 2006
I wish I could use automatic numbering, but unfortunately that isn't an option in this case. I was initially doing the select and then passing that value in as an argument, but there was 2 things I was worried about 1) The time in between the select occuring and the insert occuring someone else may be working on that table. 2) I am also trying to keep both database access as well as code to a minimum.