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.
