Value before specific timestamp : Where timestampColumn <= '2014-10-16 00:00:00'

[Benoit Brayer]

Select * from myfile Where datetime <= '2014-10-16 00:00:00' Limit 15;

This feature is returning an empty resultset.

Thanks in advance.

[Simon Chenery]

This SQL statement works correctly for me.

The timestamp '2014-10-16 00:00:00' in the SQL statement is the correct timestamp format.

Do the timestamps in the CSV file also have the correct format?

If a timestamp cannot be parsed it will have the value SQL NULL.

And did you set the database connection property columnTypes so that the column is interpreted as a timestamp and not as a string?

[Benoit Brayer]

Firstly I am using the v1.0-20 of CSVJDBC because I have very a very strange error with the version 1.0-20.

You can get my source code from my owncloud (I don't have a valid ssl certificate don't worry about this) :

https://www.owncloud.brayerbenoit.fr/public.php?service=files&t=a2ce81eac882db22798c2ca6b74345bc

I will explain my errors for both versions :

• Using version 1.0-20 :

My test called test_getThreeLinesBeforeLastDate is throwing IndexOutOfBoundsException.

Every others test are working perfectly : I can get the two lines after the first date.

StackTrace :

java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
at java.util.ArrayList.rangeCheck(ArrayList.java:635)
at java.util.ArrayList.get(ArrayList.java:411)
at com.st.csvjdbc.test.GetThreeLinesBeforeLastDateTest.test_getThreeLinesBeforeLastDate(GetThreeLinesBeforeLastDateTest.java:97)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

---

• Using version 1.0-21 :

With this library, both tests are throwing SQLException.

StackTrace :

java.sql.SQLException: Syntax error: Encountered " ")" ") "" at line 1, column 108.
Was expecting:
<EOF>

at org.relique.jdbc.csv.CsvStatement.executeQuery(Unknown Source)
at com.st.csvjdbc.datastructure.CSVJdbcManager.executeQuery(CSVJdbcManager.java:91)
at com.st.csvjdbc.datastructure.CSVJdbcManager.execute(CSVJdbcManager.java:84)
at com.st.csvjdbc.test.GetTwoLinesAfterFirstDateTest.test_getTenLinesAfterFirstDate(GetTwoLinesAfterFirstDateTest.java:86)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

---

My sourcecode generated queries :
SELECT DateTime, Severity, Message FROM 2014-09-04_08-38-55_apf.activity_manager.execution_history.job.log()_c2x9222.dev.cr2.st.com WHERE DateTime <= 'null' LIMIT 3

SELECT DateTime, Severity, Message FROM 2014-09-04_08-38-55_apf.activity_manager.execution_history.job.log()_c2x9222.dev.cr2.st.com WHERE DateTime >= '2014-09-04_09-10-13' LIMIT 2

To reproduce it, just run my unit tests.
If my code is too "custom" I am try to create for you a more simple example.

More informations :
- I am using windows at work (any encoding errors ???)
- I am using a custom format for the timestamp : "yyyy-MM-dd_HH-mm-ss"
and I add it to the properties file before creating the connection using the : "timestampFormat" property.

Thanks in advance and for your fast reply ;)

Benoit

[Simon Chenery]

With csvjdbc-1.20.jar, the second unit test fails because WHERE Datetime <= 'null' matches no records -- the timestamp 'null' is not valid and parses to SQL NULL. The timestamps from the CSV file are not NULL, so do not match the WHERE clause.

GetThreeLinesBeforeLastDateTest:

SELECT DateTime, Severity, Message
FROM 2014-09-04_08-38-55_apf.activity_manager.
execution_history.job.log()_c2x9222.dev.cr2.st.com
WHERE DateTime <= 'null'  
LIMIT 3

With csvjdbc-1.21.jar, parsing the SQL statement in both unit tests now fails
(due to some changes in the CsvJdbc SQL parser). Can you please add double quotes around the table name because the parentheses in the table name are confusing CsvJdbc. Then the SQL statement is parsed correctly:

SELECT * FROM "2014-bla.().bla.st.com" WHERE ...

[Benoit Brayer]

Thanks a lot, everything is now working perfectly.

[Benoit Brayer]

Thanks a lot, everything is now working perfectly.