Date or TimeStamp crashes if there is an empty string in the date field

[mkchalla]

Steps to reproduce:

• Create an empty SQLite database with a table which has a date field.
• Insert some records into the database with date value as ""
• Now try reading the table and it fails
  .getDate()
  .getTime()
  .getTimestamp()

I have used the below sample to reproduce the issue.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteConfig.Pragma;

import java.util.Properties;

public class Sample {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            // create a database connection
            SQLiteConfig sqLiteConfig = new SQLiteConfig();
            Properties properties = sqLiteConfig.toProperties();
            properties.setProperty(Pragma.DATE_STRING_FORMAT.pragmaName, "yyyy-MM-dd");

            connection = DriverManager.getConnection("jdbc:sqlite:sample.db", properties);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30); // set timeout to 30 sec.

            statement.executeUpdate("drop table if exists person");
            statement.executeUpdate("create table person (id integer, name string, dob date)");
            statement.executeUpdate("insert into person values(1, 'leo', '1997-05-17')");
            statement.executeUpdate("insert into person values(2, 'yui', '')");
            ResultSet rs = statement.executeQuery("select * from person");
            while (rs.next()) {
                // read the result set
                System.out.println("name = " + rs.getString("name"));
                System.out.println("id = " + rs.getInt("id"));
                System.out.println("dob = " + rs.getDate("dob"));
            }
        } catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                // connection close failed.
                System.err.println(e.getMessage());
            }
        }
    }
}

First Issue:
At first, I had issue with the default date format. As you can see in the above example, I am only using date without the time, but the sqlite-jdbc is using default date format string (Pragma.DATE_STRING_FORMAT.pragmaName) as "yyyy-MM-dd HH:mm:ss.SSS". I fixed this by passing the date format as a property as mentioned in the #88.

name = leo
id = 1
Error parsing time stamp
java.sql.SQLException: Error parsing time stamp
        at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:372)
        at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:391)
        at Sample.main(Sample.java:29)
Caused by: java.text.ParseException: Unparseable date: "1997-05-17" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)
        at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
        at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
        at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:367)
        ... 2 more

Second Issue:
However, in case of the second record in the sample, I am not providing any value for the date field, which is causing the same error irrespective of which format I use. Normally the application can handle the data not to create empty strings for the date field. Due SQLite data type affinity date field is treated as string and when we load CSV files into SQLite it is creating empty date fields.

name = leo
id = 1
dob = 1997-05-17
name = yui
id = 2
Error parsing time stamp
java.sql.SQLException: Error parsing time stamp
        at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:331)
        at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:350)
        at Sample.main(Sample.java:35)
Caused by: java.text.ParseException: Unparseable date: "" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)
        at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
        at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
        at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:328)
        ... 2 more

When I further looked into the code, I found that the value is being parsed without even checking for a NULL or EmptyString on this line

sqlite-jdbc/src/main/java/org/sqlite/jdbc3/JDBC3ResultSet.java

Line 296 in 4f3adc5

return new Date(getConnectionConfig().getDateFormat().parse(db.column_text(stmt.pointer, markCol(col))).getTime());

I have added the below code and tested it and it worked. Is this the right solution?

...
String rawValue = db.column_text(stmt.pointer, markCol(col));
if (rawValue == null || rawValue.isEmpty()){
    return null;
}else{
    FastDateFormat dateFormat = FastDateFormat.getInstance(getConnectionConfig().getDateStringFormat(), cal.getTimeZone());    
    return new java.sql.Date(dateFormat.parse(rawValue).getTime());
}
...