Re: Updating 2 derby tables error message interpretation
> So my question is: why do I not see the new 54 table2 records that my > program says have been created? > > Perhaps you didn't COMMIT the 54 records in your test program? Perhaps your test program is using a different Derby database than you're looking at with NetBeans? Perhaps your test program uses an in-memory database, and your records were committed to the in-memory database, then that database was lost when your program exited from its in-memory configuration? Perhaps your connection information is slightly different, so you are using two different SCHEMA settings in the two configurations, so that you have two different sets of tables, in two different SCHEMA? There are many possible ways in which one Derby configuration can be looking at a different set of database tables than another, all of which are intentional, but which are easy configurations to find yourself in accidentally. thanks, bryan
Re: Updating 2 derby tables error message interpretation
Hi Rick, John, Bryan et al Things are a *little* clearer now :) I was a bit premature in stating that all was OK To try and rectify my problem (without understanding what was wrong) I changed the index in table2 from a single column (INT) to a 2 column index (DATE and TIME) exactly as Table1 was setup I then ran the program on a historical tester platform for a period of 3 months The end result was that it seemed to run perfectly - carrying out 54 new trades in that period It also told me that the number of records in table2 (TRADES) increased by 54 BUT, and it is a big *BUT* when I use Netbeans to view the data in table2 I see just the 4,100 records that existed when I initialized the setup i.e. I can not see the 54 new records ? This explains why, in the past an error concerning duplicate keys came about - the error was correct but I could not see any new records (and still cannot) I am puzzled by this because to setup table2 I opened Netbeans, connected to the database I require - where I can see table1 and ran a file which set up table2 with 4,100 records Table2 then appeared and I could view the 4,100 records So my question is: why do I not see the new 54 table2 records that my program says have been created? Thank you Bob M New Zealand -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147442.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables error message interpretation
hello everybody I looked at the setup of both tables and found that the key in the first, made up of two fields, had each field NOT NULL So I applied this to the single field key in table2 - Trade Number Now - no errors :) Thanks to all, for such great advice Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147437.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables error message interpretation
On 23/05/2017 15:14, Bob M wrote: The error message(s): mining routine finished simulation routine commenced simulation routine finished final prediction routine commenced Prediction_Trend: 0.0 Current Trade Direction: debug: we get to here(8) final prediction routine finished Weka non-initialization successful Weka finished No. 2 - Initialize: false No. 2 - myCanTrade: false No. 2 - do_not_trade: false No. 2 - curr_trade_dir: No. 2 - prev_trade_dir: Down Inserted newest record:- Trading_Date/Trading_Time: 2017-5-23, 12 Oldest record:- Trading_Date/Trading_Time: 2012-02-06, 6 Deleted oldest record Once again, none of this is generated by the code you posted... *Adding a new trade record Number: 4101 * Something very similar is generated by you code (but again, it's not *exactly* the same)... - SQLException - SQL State: 23505 Error Code: 2 Message: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL170412104646890' defined on 'TRADES'. - SQLException - SQL State: 25001 Error Code: 2 Message: Cannot close a connection while a transaction is still active. And here, you should probably print a stacktrace from the exception and look at the line which generates the exception in your code (probably the call to executeUpdate() -- but only you have that information). And now you have two clues: 1) "The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL170412104646890' defined on 'TRADES'." So, which constraint might have been violated? (Hint, again: give names to your constraints.) And why might it have happened? (A stacktrace might give you a clue, but since you didn't post one, that's something you'll have to figure out.) 2) "Cannot close a connection while a transaction is still active." Which connection is being closed, and why is there a transaction still active at the time? (Again, a stacktrace might help to figure out where and why this is happening.) I'm sorry, but I can't spend any more time trying to understand your problem until you make some more effort to understand your own problem. What you've posted so far is certainly not enough for me to help any further than I already have, but hopefully you'll be able to work it out from the exception messages and the clues you've been given already. -- John English
Re: Updating 2 derby tables error message interpretation
Hi Descriptive: My program runs every six hours. Two independent tables are setup - each with 4,100 records On the first run a trade is opened and table 1 has a new record added and the oldest one is deleted - all OK On the second run, I am expecting the TRADES table to have a new record added. The subroutine to add a new record is triggered by the boolean variable 'trade' which is true The program prints out the correct trade number as 4,101 The code: // Adding a new record (if required) to the Derby database TRADES table // [b] add a new record to the table TRADES if (trade) { // parameter 1 is Trade_No (int), // parameter 2 is Class1_predicted (varchar), parameter 3 is Class2_predicted (varchar) // parameter 4 is Class3_predicted (varchar), parameter 5 is Class4_predicted (varchar) // parameter 6 is Class5_predicted (varchar), parameter 7 is Class6_predicted (varchar) // parameter 8 is Class7_predicted (varchar), parameter 9 is Ensemble_predicted (varchar) // parameter 10 is Profit/Loss (dec) fw.writetoFile(("Adding a new trade record Number: " + trade_no), FILE_NAME); psInsert = conn.prepareStatement("INSERT INTO TRADES VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); statements.add(psInsert); psInsert.setInt(1, trade_no); psInsert.setString(2, curr_class1_predicted); psInsert.setString(3, curr_class2_predicted); psInsert.setString(4, curr_class3_predicted); psInsert.setString(5, curr_class4_predicted); psInsert.setString(6, curr_class5_predicted); psInsert.setString(7, curr_class6_predicted); psInsert.setString(8, curr_class7_predicted); psInsert.setString(9, curr_ensemble_predicted); psInsert.setNull(10, java.sql.Types.DECIMAL); psInsert.executeUpdate(); myConsole.getOut().println("Inserted newest record:- Trade Number: " + trade_no); fw.writetoFile(("Inserted newest record:- Trade Number: " + trade_no), FILE_NAME); trade = false; // commit the above transactions conn.commit(); // retrieve and output trade number of latest record from the table TRADES rs = s.executeQuery("SELECT * FROM TRADES ORDER BY TRADE_NO DESC FETCH FIRST ROW ONLY"); rs.next(); int trade_no_temp3 = rs.getInt("Trade_No"); fw.writetoFile(("Latest trade record:- Trade Number: " + trade_no_temp3), FILE_NAME); } // end of adding new trade record to database table TRADES The error message(s): mining routine finished simulation routine commenced simulation routine finished final prediction routine commenced Prediction_Trend: 0.0 Current Trade Direction: debug: we get to here(8) final prediction routine finished Weka non-initialization successful Weka finished No. 2 - Initialize: false No. 2 - myCanTrade: false No. 2 - do_not_trade: false No. 2 - curr_trade_dir: No. 2 - prev_trade_dir: Down Inserted newest record:- Trading_Date/Trading_Time: 2017-5-23, 12 Oldest record:- Trading_Date/Trading_Time: 2012-02-06, 6 Deleted oldest record *Adding a new trade record Number: 4101 * - SQLException - SQL State: 23505 Error Code: 2 Message: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL170412104646890' defined on 'TRADES'. - SQLException - SQL State: 25001 Error Code: 2 Message: Cannot close a connection while a transaction is still active. Derby finished Label: USDJPY_2352017_6 Currency Pair: USDJPY Closed Trade: USDJPY_2352017_64101 | P/L: -15.5 pips PL_updates = 1.0 Many thanks Bob M New Zealand -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147428.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
> > Exactly what would you wish me to post? > > Bob M > > Here are some useful guidelines for how to ask a question in a way that helps others help you: https://stackoverflow.com/help/mcve thanks, bryan
Re: Updating 2 derby tables
OK John I have reinitialized the data in table1, table 2 remains static. After the program has run for 2 times I should have the usual error about a duplicate key in table2 Exactly what would you wish me to post? Bob M p.s. the 2 tables are independent -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147424.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
On 21/05/2017 21:18, Bob M wrote: I keep feeling that there is something I am not understanding about updating two different tables in the same dbase Some reasons an update might fail, off the top of my head: 1) constraint violation in the data inserted into the table (e.g. primary key or check constraint error) 2) constraint violation in a dependent table (e.g. foreign key constraint error) 3) timeout due to the table being locked by another query 4) rollback of the transaction enclosing the update due to an error later in the transaction 5) error in a trigger fired by the update, which rolls back the update (another version of case 4, but an easy one to overlook) Unfortunately, although you've posted a lot of information, it's been either inaccurate or missing vital details, so it's hard to know what to advise. -- John English
Re: Updating 2 derby tables
Thanks Bryan Good advice.. I have successfully run the program excluding any ref to the TRADES table - all OK so it is in the Trades Table code where I am going wrong.. As I use same code with the other table = it seems perplexing using a debugger - I would love to but as I need to load a strategy onto the Dukascopy platform and then run it - I do not understand how to include a debugger in the loop but I do have Netbeans IDE and can load my program into it (of course) I keep feeling that there is something I am not understanding about updating two different tables in the same dbase Regards Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147419.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
Perhaps you made a change to the code, but forgot to recompile, or forgot to re-update your JAR files, or made some other simple build mistake. As a technique for understanding the behavior of your program, commenting parts of it in and out and re-running the program is a very good technique, but it is easy to make a simple build mistake and not run the modified version of the program which you think you are running. Another very good technique for understanding the behavior of your program is to run it under a Java debugger, and then you can stop and start it and various points, step through it line by line, look at the values of variables at various points, etc. thanks, bryan On Sat, May 20, 2017 at 11:12 PM, Bob M wrote: > The situation has deteriorated.. > > I have placed /* */ around all code which adds new trade records and > which > updates trade records > > I was expecting x number of trades to take place and no error messages > > Instead I got the following:- > > Adding a new trade record Number: 4101 > > - SQLException - > SQL State: 23505 > Error Code: 2 > Message: The statement was aborted because it would have caused a > duplicate > key value in a unique or primary key constraint or unique index identified > by 'SQL170412104646890' defined on 'TRADES'. > > I checked the whole program for "adding a new trade record" and found only > a > single instance - the code is within the /* */ so how can I continue to > get a reference to these words in an error code? > > Bob M > > > > -- > View this message in context: http://apache-database.10148. > n7.nabble.com/Updating-2-derby-tables-tp147386p147412.html > Sent from the Apache Derby Users mailing list archive at Nabble.com. >
Re: Updating 2 derby tables
The situation has deteriorated.. I have placed /* */ around all code which adds new trade records and which updates trade records I was expecting x number of trades to take place and no error messages Instead I got the following:- Adding a new trade record Number: 4101 - SQLException - SQL State: 23505 Error Code: 2 Message: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL170412104646890' defined on 'TRADES'. I checked the whole program for "adding a new trade record" and found only a single instance - the code is within the /* */ so how can I continue to get a reference to these words in an error code? Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147412.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
Inserted newest record:- Trading_Date/Trading_Time: 2016-1-11, 12 Oldest record:- Trading_Date/Trading_Time: 2012-02-06, 6 Deleted oldest record Adding a new trade record Number: 4101 - SQLException - SQL State: 23505 Error Code: 2 Message: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL170412104646890' defined on 'TRADES'. The above occurs after the program is part way through its second run(it runs every six hours) At this stage, a new trade record is required to be added (since a trade was opened and closed on the first run The new trade number is correctly stated as 4101 At this stage the trade table has 4,100 records so why the error message regarding duplicate keys ? I don't get it ! The key is the trade number Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147411.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
Hi John Understanding a bit more now... I see when I look at the key to table2 it has the identifier 'SQL170412104645890' as stated in the error message -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147410.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
Hi John Hint: it's always a good idea to give names to your constraints and suchlike, so that the error message will refer to a name that appears in your DB schema, rather than a system-generated name like SQL170412104646890. If you can't figure out which is the violated constraint referred to in the error message, you might have to dig around in SYS.SYSCONSTRAINTS to identify it. You are way above my head here :) Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147409.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
Hi John Trade_no_temp2 is a variable I do not understand why the error message relates this variable to a table? Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147408.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
On 19/05/2017 23:13, Bob M wrote: Message: Column 'TRADE_NO_TEMP2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'TRADE_NO_TEMP2' is not a column in the target table. Well, that should be easy enough to fix... the error message is pretty clear. -- John English
Re: Updating 2 derby tables
On 20/05/2017 05:07, Bob M wrote: Message: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL170412104646890' defined on 'TRADES'. Hint: it's always a good idea to give names to your constraints and suchlike, so that the error message will refer to a name that appears in your DB schema, rather than a system-generated name like SQL170412104646890. If you can't figure out which is the violated constraint referred to in the error message, you might have to dig around in SYS.SYSCONSTRAINTS to identify it. It's also a good idea to post the actual output so that we can see what went wrong, rather than a bunch of source code that doesn't include the most important piece of information -- the error message! -- John English
Re: Updating 2 derby tables
I have reinitialized my system and have run my program on a testing platform At the first time period, everything goes perfectly and it opens a trade but there is no requirement to update the second of the two derby tables (the first table is updated OK) On the second run, the first derby table is updated OK but then an error occurs I have never seen the second table having a record added Inserted newest record:- Trading_Date/Trading_Time: 2016-1-11, 12 Oldest record:- Trading_Date/Trading_Time: 2012-02-06, 6 Deleted oldest record *the above refers to table1* Adding a new trade record Number: 4101 *this refers to table2* - SQLException - SQL State: 23505 Error Code: 2 Message: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL170412104646890' defined on 'TRADES'. - SQLException - SQL State: 25001 Error Code: 2 Message: Cannot close a connection while a transaction is still active. Derby finished *** -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147398.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
Sorry Rick I may have deleted that line :( -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147397.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
On 5/19/17 1:13 PM, Bob M wrote: hey - we got to here - [b] !! There is more going on in your code than is apparent from the snippet you included. For instance, the output contains the line "hey - we got to here - [b] !!" but that line does not appear in the source code you provided. I would recommend looking into derby.log for more clues.
Re: Updating 2 derby tables
Hi John As I read it, only Table1 ever gets updated - correctly The output suggests that Table2 is updated but that is NOT the case Output:- Inserted newest record:- Trading_Date/Trading_Time: 2017-5-18, 18 Oldest record:- Trading_Date/Trading_Time: 2012-02-06, 6 Deleted oldest record Adding a new trade record Number: 4101 Inserted newest record:- Trade Number: 4101 PL_Updates(penultimate-begin) = 1.0 PL_Updates(latest-begin) = 1.0 hey - we got to here - [b] !! Successfully retrieved the latest trade record from TABLE2 table: Latest trade record:- Trade No: 4102 - SQLException - SQL State: 42X04 Error Code: 2 Message: Column 'TRADE_NO_TEMP2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'TRADE_NO_TEMP2' is not a column in the target table. - SQLException - SQL State: 25001 Error Code: 2 Message: Cannot close a connection while a transaction is still active. -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147394.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
On 19/05/2017 03:05, Bob M wrote: More code Do the commits succeed? Are any exceptions thrown? Are all the messages printed correctly? What changes occur in the affected tables? What do you see going wrong? -- John English
Re: Updating 2 derby tables
More code psInsert = conn.prepareStatement("INSERT INTO TABLE1 VALUES (?, ?, ?)"); statements.add(psInsert); psInsert.setString(1, ); psInsert.setInt(2, b); psInsert.setDouble(3, c); psInsert.executeUpdate(); fw.writetoFile(("Inserted newest record:- Trading_Date/Trading_Time: " + date_current + ", " + time_current), FILE_NAME); // retrieve and output date and time of oldest record from the table TABLE1 rs = s.executeQuery("SELECT * FROM TABLE1 ORDER BY Trading_Date ASC," + " Trading_Time ASC FETCH FIRST ROW ONLY"); rs.next(); String Date_temp = rs.getString("Trading_Date"); int Time_temp = rs.getInt("Trading_Time"); fw.writetoFile(("Oldest record:- Trading_Date/Trading_Time: " + Date_temp + ", " + Time_temp), FILE_NAME); // and now delete this record. s.setCursorName("MYCURSOR"); rs = s.executeQuery("SELECT * from TABLE1 WHERE Trading_Date = '" + Date_temp + "' AND Trading_Time = " + Time_temp + " FOR UPDATE"); rs.next(); conn.prepareStatement("DELETE FROM TABLE1 WHERE CURRENT OF MYCURSOR").executeUpdate(); fw.writetoFile(("Deleted oldest record"), FILE_NAME); // commit the above transactions conn.commit(); // end of adding new record and deleting oldest record from database table TABLE1 // Adding a new record (if required) to the Derby database TABLE2 table // [b] add a new record to the table TABLE2 if (trade) { fw.writetoFile(("Adding a new trade record Number: " + trade_no), FILE_NAME); psInsert = conn.prepareStatement("INSERT INTO TRADES VALUES (?, ?, ?, ?)"); statements.add(psInsert); psInsert.setInt(1, d); psInsert.setString(2, e); psInsert.setString(3, fff); psInsert.setString(4, g); psInsert.executeUpdate(); fw.writetoFile(("Inserted newest record:- Trade Number: " + trade_no), FILE_NAME); trade = false; } // commit the above transactions conn.commit(); // retrieve and output trade number of latest record from the table TABLE2 rs = s.executeQuery("SELECT * FROM TABLE2 ORDER BY Trading_No DESC FETCH FIRST ROW ONLY"); rs.next(); int trade_no_temp3 = rs.getInt("Trade_No"); fw.writetoFile(("Latest trade record:- Trade Number: " + trade_no_temp3), FILE_NAME); Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386p147389.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Updating 2 derby tables
Hi Bob, It is hard to tell what the problem is from the code below, since that code only refers to one table. A more complete picture of the code might suggest some clues. Thanks, -Rick On 5/18/17 3:53 PM, Bob M wrote: Hi Until recently I had just the one single derby database table and could update it successfully. I now have two separate tables - the first is updating successfully, the second is NOT! I use similar code for both * psInsert = conn.prepareStatement("INSERT INTO x VALUES (?, ?, ?)"); statements.add(psInsert); psInsert.setInt(1, ); psInsert.setString(2, b); psInsert.setString(3, c); psInsert.executeUpdate(); // commit the above transactions conn.commit(); * I update the two tables, one after the other. Is there some missing code or am I not understanding something? Thanks for any advice / comments :) Bob M New Zealand -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Updating 2 derby tables
Hi Until recently I had just the one single derby database table and could update it successfully. I now have two separate tables - the first is updating successfully, the second is NOT! I use similar code for both * psInsert = conn.prepareStatement("INSERT INTO x VALUES (?, ?, ?)"); statements.add(psInsert); psInsert.setInt(1, ); psInsert.setString(2, b); psInsert.setString(3, c); psInsert.executeUpdate(); // commit the above transactions conn.commit(); * I update the two tables, one after the other. Is there some missing code or am I not understanding something? Thanks for any advice / comments :) Bob M New Zealand -- View this message in context: http://apache-database.10148.n7.nabble.com/Updating-2-derby-tables-tp147386.html Sent from the Apache Derby Users mailing list archive at Nabble.com.