• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Rob Spoor
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Holloway
  • Piet Souris
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Himai Minh

connecting to an Apache Derby database

 
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
According to Derby documentation you can use a java.sql.Date for a column that is of type Date. It also permits using a String IF the format is 'yyyy-mm-dd'. Is that the format you're giving it?

Your debugging code should print out the complete query before calling execute.

[correction]
Supports the following formats:
yyyy-mm-dd
mm/dd/yyyy
dd.mm.yyyy
 
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a compile error............

duplicate e on line 15
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Carey - yes format is yyy-MM-dd
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You have two Exceptions named 'e'. Change one of them.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
when I wrap the try {...} around just the one line of code for establishing the connection I get the result posted above

when I wrap the try {....} around all code including adding a new record to RAWDATA [as per Tim's suggestion] I get to 2 only ???
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One thing that bothers me as I follow this thread, is the lack of object-oriented-ness. Encapsulation, for one, is missing. Where is the breakdown into methods(), for another.

I see something more like this:
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Carey said

Your debugging code should print out the complete query before calling execute



please show me the code to do that in my case
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah, you make a point. I'm used to working on DB stuff where I wrote support libraries to handle stuff like that.

As an intermediate approach, you could add to my latest code post:

Which would require adding a toString() method to the RawData class, which is not a bad idea anyway.
 
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Carey Brown wrote:One thing that bothers me as I follow this thread, is the lack of object-oriented-ness. Encapsulation, for one, is missing. Where is the breakdown into methods(), for another.


Yep.

On print-before-you-leap:

If this looks like any other code I might have suggested recently, it's not a coincidence. That's my standard anti-bugging/diagnostic process. Except that I use loggers instead of brute-force printing.

Note that I bracketed the command display. I also do the same when noting file operations. Sometimes leading/trailing blank characters can be a problem, and this makes them visible.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
New day.................

Tim]
After adding your try {...} code
1st run today got to 4b
2nd run got to 2 and error:-
Time               Messages                                                                                                      
2021-08-05 20:50:00 java.lang.NullPointerException @ us_copiosus_EURUSD_August_2021.onBar(us_copiosus_EURUSD_August_2021.java:467)
Line 467 is the start of Derby

currently I have................
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Carey]

As soon as I get a new record added to RAWDATA I shall recode with your suggestions
 
Tim Holloway
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And you still didn't give us a full stack trace.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
which code do I need to change or where do I find it ?
 
Tim Holloway
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:which code do I need to change or where do I find it ?


If you mean the Stack trace, it's written to System.err and should show up as a long series of lines on your console. And we need all of them.

It looks like this:

 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I do not see anything like that on my console

e.printStackTrace()

do I need to put something in the brackets ?
 
Tim Holloway
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There's something wrong with your console. Something is re-routing stderr from the JVM.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
when I restart the Dukascopy platform, I get the following output on my Console
-----------------
Time               Messages                                                
2021-08-05 22:10:01 BID_price: 1.18328                                      
2021-08-05 22:10:01 ASK_price: 1.18333                                      
2021-08-05 22:10:01 time_current: 22:0:0                                    
2021-08-05 22:10:01 date_current: 5.8.2021                                  
2021-08-05 22:10:01 we get to here - 4b:                                    
2021-08-05 22:10:01 we get to here - 4a:                                    
2021-08-05 22:10:01 SQL COMMAND[INSERT INTO RAWDATA VALUES (?, ?, ?, ?)]    
2021-08-05 22:10:01 we get to here - 4:                                      
2021-08-05 22:10:01 Number of records in table RAWDATA: 6310                
2021-08-05 22:10:01 Connected to database: C:/Apache/Derby/Databases/DCEvents
2021-08-05 22:10:01 we get to here - 2:                                      
2021-08-05 22:10:01 we get to here - 1:                                      
2021-08-05 22:10:01 DerbyApp starting in embedded mode                      
2021-08-05 22:10:01 ASK Price: 1.18333; BID Price: 1.18328                  
2021-08-05 22:10:01 filename : EUR_USD 2021 8 5 22_0.txt                    
2021-08-05 22:03:44 File access error: null                                  
2021-08-05 22:03:44 Strategy Started: EUR_USD                                
----------------------------------------------------
from the above I can see that date-current is incorrect (should be yyyy-MM-dd) and time_current may be incorrect (should be hh:mm:ss)

and the program is stopped running with the following exception............
Time               Messages                                                                                                      
2021-08-05 22:10:01 java.lang.NullPointerException @ us_copiosus_EURUSD_August_2021.onBar(us_copiosus_EURUSD_August_2021.java:467)
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I now have...........

Time               Messages              
2021-08-05 22:30:01 time_current: 22:20:0
2021-08-05 22:30:01 date_current: 2021-8-5

date_current is stored as yyyy-MM-dd
Time_current looks OK
- both strings

how do I format date_current properly ?
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
e.printStackTrace(myConsole.getOut()); // show the stack trace on the console

here is the stack trace..........
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
2021-08-06 01:00:00 java.sql.SQLDataException: The syntax of the string representation of a date/time value is incorrect.

2021-8-6 should probably be 2021-08-06
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Question: why is the date and time correct at the start but both 'null' later on
-----------------------------------------------------------------------------------
Time               Messages                                                                                    
2021-08-06 01:50:01 java.sql.SQLIntegrityConstraintViolationException: Column 'DATE'  cannot accept a NULL value.
2021-08-06 01:50:01 we get to here - 4c:                                                                        
2021-08-06 01:50:01 BID_price: 1.18249                                                                          
2021-08-06 01:50:01 ASK_price: 1.18252                                                                          
2021-08-06 01:50:01 time_current: null                                                                          
2021-08-06 01:50:01 date_current: null                                                                          
2021-08-06 01:50:01 we get to here - 4b:                                                                        
2021-08-06 01:50:01 we get to here - 4a:                                                                        
2021-08-06 01:50:01 SQL COMMAND[INSERT INTO RAWDATA VALUES (?, ?, ?, ?)]                                        
2021-08-06 01:50:01 we get to here - 4:                                                                          
2021-08-06 01:50:01 Number of records in table RAWDATA: 6310                                                    
2021-08-06 01:50:01 Connected to database: C:/Apache/Derby/Databases/DCEvents                                    
2021-08-06 01:50:01 we get to here - 2:                                                                          
2021-08-06 01:50:01 we get to here - 1:                                                                          
2021-08-06 01:50:01 DerbyApp starting in embedded mode                                                          
2021-08-06 01:50:00 ASK Price: 1.18252; BID Price: 1.18249                                                      
2021-08-06 01:50:00 date_current: 2021-08-06; time_current: 01:50:00.500                                        
2021-08-06 01:50:00 filename : EUR_USD 2021 8 6 1_40.txt                                                        
2021-08-06 01:46:17 File access error: null                                                                      
2021-08-06 01:46:17 Strategy Started: EUR_USD
----------------------------------------------------------------------------------------------                                                                    
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You may have two separate variables with the same name, that is, both are named "date_current". One has been populated and the other has not. You have not shown us enough code to help you with this problem.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm afraid you're going to have to roll up your sleeves on this one. Everywhere that "date_current" appears put in a print statement before it and one after it and see if you can find out where it's changing.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Time               Messages                  
2021-08-06 04:50:01 BID_price: 1.18223        
2021-08-06 04:50:01 ASK_price: 1.18225        
2021-08-06 04:50:01 time_current: 04:50:00.551
2021-08-06 04:50:01 date_current: 2021-08-06  

Time               Messages                                                                                            
2021-08-06 04:50:01 java.sql.SQLDataException: The syntax of the string representation of a date/time value is incorrect.

Time: I am seeking hh:mm:ss

How do I get rid of the .551 in the time variable ?
 
Marshal
Posts: 3647
516
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:How do I get rid of the .551 in the time variable ?


If you are starting with LocalDate and LocalTime, then don't transform them to Strings - it just makes things harder.

 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
my stored date and time are as strings
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use a formatter then like the code I posted way back in the beginning of this thread (or was it your other thread?).
 
Ron McLeod
Marshal
Posts: 3647
516
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:my stored date and time are as strings


Where do the date and time values come from - an external source?
 
Carey Brown
Saloon Keeper
Posts: 8458
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Marshal
Posts: 26750
81
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You need to capture the result of addMinutes:


 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Time               Messages                  
2021-08-06 06:00:00 time_current: 05:59:59.631
2021-08-06 06:00:00 date_current: 2021-08-06  
Time               Messages                                                                                            
2021-08-06 06:00:00 java.sql.SQLDataException: The syntax of the string representation of a date/time value is incorrect.

I presume the time variable is the problem......
Same question: how do I get rid of the '.631' ?

Background: I populate the table with a lot of historical data that comes like this.........

INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:10:00',1.22291,1.22294);
INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:20:00',1.22353,1.22355);
INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:30:00',1.22314,1.22317);
INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:40:00',1.22331,1.22334

so that explains why I seek to add realtime data of the same nature

Strictly speaking I am logging in every ten minutes [e.g. 5:20, 5:30, 5:40 etc.]
so ideally the time should be 06:00:00
 
Tim Holloway
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
THANK YOU for the stack trace! It immediately resolved that problem.

You need to look at how you are set up to run that app though. It's definitely "losing" stderr nd that is not good, since stderr is the authoritative place for error messages due to program bugs, JVM failures and the like. At least find out where it's being sent so that in cases of error you can retrieve it.

I may be getting a false sense of what you are doing, but if you want to write the current time/date to a database, don't convert it to a string and write the string. Just write time_current directly. Not only can you have transcription problems, but it's inefficient.

Also a word about Dates, Times and Databases. Date/time objects are worse than floating-point numbers. Both are imprecise, but timestamps are unpredictably imprecise. So they're even more dangerous that floating-point!

More accurately, the precision of a timestamp object can vary with the database, the database schema definition and the application logic.

A java.util.Date object represents a time/date with a precision of 1 millisecond. Literally, a Date object contains the number of milliseconds since Midnight UTC January 1 1970. You shouldn't attempt to pretend it contains a local timezone time - that will only get you into trouble. It's like trying to pretend that Strings are ASCII when they are actually Unicode. The timezone for Date is Universal Co-ordinated Time (UTC is the French initials).

Databases, on the other hand, are much more varied and more than a few have several Data, Time, and Date/Time (Timestamp) column data types with varying precisions. Some database date fields are precise to 1 day (24 hours). Others are precise to one second. Some are precise to 100ths, to milliseconds, or even microseconds. If you try and write a Java Date to a microsecond-precise date field, the data will not be precise to microseconds, only milliseconds. Contrariwise, if you attempt to read a microsecond-precise date from a database into a Java Date, you will lose precision. If you write a Java Date that has an odd number of milliseconds to a database column that has 1-second precision, what will come back when you read it again won't have those milliseconds in it.

Databases also often support a LOCALTIME timestamp column type. If you don't take UTC into account that can get you into trouble. As a rule, I recommend avoiding LOCALTIME, since we're a world-wide community these days and you can never tell when your little only-runs-at-home system might want to go international. Otherwise you might have to spend a lot of effort adjusting your application time/data information.

If your JDBC driver does not properly adjust Dates to local time when talking to a LOCALTIME DB field, it's easy to compensate. To make a Java Date compensated for local timezone EST/EDT, you'd simply get the Date's value (milliseconds) and subtract 4/5 hours from it to create a new date (4 hours would be 4*3600*1000), where 3600 is number of seconds/hour (60^60 minutes) and 1000 is millseconds/second.  So:

Note that since compensatedDate is corrupt by the strict definition of java.util.Date, you should immediately discard it.
 
Marshal
Posts: 73766
332
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:. . . (UTC is the French initials). . .

Not according to Cay Horstmann, it isn't. In French it would be temps universel coordiné (TUC) and the English initials would be UCT. So UTC is wrong for both languages.
 
Tim Holloway
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:

Tim Holloway wrote:. . . (UTC is the French initials). . .

Not according to Cay Horstmann, it isn't. In French it would be temps universel coordiné (TUC) and the English initials would be UCT. So UTC is wrong for both languages.


What??? I've been lied to!  
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Still confused as ever.....................

Background: I populate the table with a lot of historical data that comes like this.........

INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:10:00',1.22291,1.22294);
INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:20:00',1.22353,1.22355);
INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:30:00',1.22314,1.22317);
INSERT INTO US_COPIOSUS.RAWDATA VALUES ('2021-06-01','01:40:00',1.22331,1.22334

i.e. date and time are stored as strings and time is a multiple of 10 minutes exactly

Question: How do I code for real time to setup date and time as above examples ?

(looking past the fact that storing dates and time as strings is irresponsible

 
Tim Holloway
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Historically you could just concatenate the date and time into a single string and feed it to a DateFormat object to get a Date object. I'm sure some here could also suggest some tricks with some of the newer chronological classes as well.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes but am I not still having problems with parts of a second

also time may read as 10:59:59 and I need 11:00:00

......i.e. more fancy coding ??
 
Tim Holloway
Saloon Keeper
Posts: 24214
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:yes but am I not still having problems with parts of a second

also time may read as 10:59:59 and I need 11:00:00

......i.e. more fancy coding ??



Oh. You want to round the time, not keep it "as is". No problem.

As I said, the Date is the number of milliseconds since Midnight Jan 1, 1970. If you want to round (or truncate) a timestamp, extract its millis() value. Now you can lop off stuff you don't want.


For example, to truncate to the nearest second, it's (millis / 1000 * 1000) using integer arithmetic. To round, it's ((millis+5000)/1000 * 1000)

10 minutes is 1000 milliseconds * 60 seconds/minute * 10 minutes. Use the same technique. Then construct a new Date(millis).
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks guys..............

Time to roll up my sleeves
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic