Updating 2 derby tables

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Updating 2 derby tables

Bob M
This post was updated on .
Hi
Until recently I had just the one single derby database table and could update it successfully.
I now have two separate tables in the same derby database - the first is updating successfully, the second is NOT!

I use similar code for both............................
*********************************************************
psInsert = conn.prepareStatement("INSERT INTO xxxxx VALUES (?, ?, ?)");
statements.add(psInsert);

psInsert.setInt(1, aaaaaaaa);
psInsert.setString(2, bbbbb);
psInsert.setString(3, ccccc);

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

 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Rick Hillegas-3
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 xxxxx VALUES (?, ?, ?)");
> statements.add(psInsert);
>
> psInsert.setInt(1, aaaaaaaa);
> psInsert.setString(2, bbbbb);
> psInsert.setString(3, ccccc);
>
> 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.
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
This post was updated on .
More code ................................

psInsert = conn.prepareStatement("INSERT INTO TABLE1 VALUES (?, ?, ?)");
statements.add(psInsert);

psInsert.setString(1, aaaa);
psInsert.setInt(2, bbbbb);
psInsert.setDouble(3, ccccc);

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, ddddd);
psInsert.setString(2, eeeee);
psInsert.setString(3, fffffff);
psInsert.setString(4, ggggg);

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 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);

Bob M
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

John English-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
This post was updated on .
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
Adding a new trade number 4101 is correct
Latest trade record is 4102 - I am perplexed as to how this number comes about (I feel it should be 4101)

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: 20000
 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: 20000
 Message: Cannot close a connection while a transaction is still active.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Rick Hillegas-3
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.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
Sorry Rick

I may have deleted that line :(
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
This post was updated on .
I have reinitialized my system and have run my program on a testing platform

Table2 has 4100 records at the start..............

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: 20000
 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: 20000
 Message: Cannot close a connection while a transaction is still active.
Derby finished
*******************************************


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

John English-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

John English-3
In reply to this post by Bob M
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
Hi John

Trade_no_temp2 is a variable

I do not understand why the error message relates this variable to a table?

Bob M
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
In reply to this post by John English-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
In reply to this post by John English-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
This post was updated on .
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: 20000
 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 with trade_no running consecutively from 1 to 4,100

so why the error message regarding duplicate keys ?????
I don't get it !
The key is the trade number

Bob M
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bryan Pendleton-3
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 <[hidden email]> 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: 20000
 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.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

John English-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bob M
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables

Bryan Pendleton-3
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:


thanks,

bryan
 

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Updating 2 derby tables error message interpretation

Bob M
This post was updated on .
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 (table 2) 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: 20000
 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: 20000
 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
12
Loading...