1

Let say i have only one record in my invoice table.

When iserting a new row in my invoice table and in case of an error, i get this message :"The statement has been rolled back".

Now if i insert a new invoice, the id i get in the invoice table is id=3 insted of id=2.

here is my invoice table DDL:

CREATE TABLE "APP"."INVOICES" ("ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "INVOICEDATE" DATE NOT NULL, "ISCASHINVOICE" BOOLEAN NOT NULL, "CUSTOMERID" INTEGER, "DISCOUNT" DECIMAL(12,2), "ADDITIONALCHARGE" DECIMAL(12,2), "ADDITIONALTVACHARGE" DECIMAL(12,2),"GARANTIEID" INTEGER,"INVOICETOTAL" DECIMAL(12,2),"TYPEPAYEID" INTEGER , "ISPAYEINVOICE" BOOLEAN NOT NULL DEFAULT FALSE);

how to solve this problem the invoice id should auto increment normally.

i did add this :

System.setProperty("derby.language.sequence.preallocator", "1");

the probleme still persiste

xfocus99
  • 31
  • 7
  • Why do you care whether the id is 3 or 2? It's a system-generated number, it could just as easily be 47109 or 85852. Can you explain more about why this is causing you problems? If you need to find out what the number is, *after* you have inserted your new row, use the JDBC feature described here: https://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc – Bryan Pendleton Sep 14 '19 at 19:41
  • i want a consecutive invoice id without gaps, that why. – xfocus99 Sep 16 '19 at 13:15
  • There are lots of reasons why that's a problematic design goal (for just one example, what happens if you delete a row? Will you re-number all the other rows?). But if it's really what you want, you might try using `select max(id) from invoices` (in a transaction), then add 1 to that number, and insert that value. E.g. https://stackoverflow.com/a/13282214 – Bryan Pendleton Sep 16 '19 at 17:52
  • i will prevent the user from deleting invoices. – xfocus99 Sep 17 '19 at 08:31

1 Answers1

-1

Derby is good for testing, but i would suggest you not expend too much time on it's bugs.

"Do not use Derby in a Production environment" is found in multiple forums and documentation.

Julio Arboleda
  • 1
  • 1