How to Pass Multiple Timestamps as Dynamic Parameters into a Derby Query?

Question

I'm converting a Java application from PostGresSQL to Derby (10.10.1.1). The PG database has many procedures that ideally will transfer to Derby procedures.

One of the PG stored procedures passes an array of Timestamps, similar to this Procedure/SQL:

CREATE FUNCTION getDownloads(_download_array timestamp without time zone[])
    LANGUAGE plpgsql AS $$
DECLARE mycurs refcursor;
    BEGIN
        SELECT * FROM download_time d
        WHERE d.downloadtime = ANY(_download_array);
    END
RETURN mycurs;

Derby procedures are basically declarations that reference your procedures class that contains public static Java methods. The methods typically use the java.SQL PreparedStatement object, and may contain dynamic parameters. The procedure is called via the java.SQL CallableStatement object, with set param values, executed to return a ResultSet.

I would like to translate the above PG procedure into a Derby procedure that accepts multiple Timestamp values, possibly using the ANY or IN statements. In limited searches, it appears that Derby does not support arrays as dynamic parameters.

Using the Squirrel SQL client, this syntax proves acceptable:

SELECT * FROM download_time d 
WHERE d.downloadtime 
IN('2011-11-13 13:24:00.0', '2011-11-13 13:28:00.0', '2014-05-06 07:08:09.0')

However in practice, passing comma-delimited Timestamps to the IN or ANY statements does not work, pseudo-code below:

try {
    Connection conn = getConnection();
    CallableStatement cstmt = null;
    cstmt = conn.prepareCall("{ call getDownloads(?) }");
    cstmt.setTimestamp(3, "'2011-11-13 13:24:00.0', '2011-11-13 13:28:00.0'");
    //Also tried this:
    cstmt.setString(3, "2011-11-13 13:24:00.0, 2011-11-13 13:28:00.0");

    cstmt.execute();
    rs = cstmt.getResultSet();
    while (null != rs && rs.next()) {
            ...
            }
    } catch (SQLException sqle) {
      ...handle errors
            }

Following the above examples, this error occurs:

java.sql.SQLException:
The syntax of the string representation of a date/time value is incorrect.

I'm in search of alternative methods, and am considering solutions I've found in an excellent article on StackOverflow, PreparedStatement IN clause alternatives? I would be willing to consider simply writing dynamic SQL instead of a parameterized procedure, but the real query is rather beastly. :)

You're not receiving N parameters in your database function, you're receiving an array, so pass an array instead. Use [`PreparedStatement#setArray`](http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setArray(int,%20java.sql.Array)) — Luiggi Mendoza, Mar 07 '14 at 16:41
Luiggi, I tried that and received the "Not Supported" error. — MAbraham1, Mar 07 '14 at 16:44
Try upgrading your JDBC driver for your database. If it's still not supported or you cannot upgrade it, then you will need to find another way to solve this, usually ugly. — Luiggi Mendoza, Mar 07 '14 at 16:46
I believe that this is a Derby issue because arrays are not SQL Type-to-Wrapper Match list. https://db.apache.org/derby/docs/10.10/ref/rrefsqljargmatching.html — MAbraham1, Mar 07 '14 at 17:12
Is the number of timestamps always the same? Or can it vary? If the number of timestamps can vary, perhaps you should store those timestamps in a separate table, and have your procedure look up the timestamps from that table by doing a "IN (select ... from )" syntax. — Bryan Pendleton, Mar 07 '14 at 22:30
Bryan, that's a good solution. Maybe you could also add a function to write the timestamps to that table? I ended up adding a string parameter that wrote the timestamps as CSV strings to the IN() statement. I hope that you post your solution so that we can vote on it. — MAbraham1, Mar 08 '14 at 19:58

score 0 · Answer 1 · answered Mar 18 '14 at 15:22

Since no one offered an answer, I'm posting my solution to the problem. The solution is to pass a String variable, "downloadTimes" containing concatenated date/times in a comma-delimited-like format. For brevity, the NULL-check condition was excluded. If a NULL is passed, that line is simply excluded.

Here is the procedure:

public static void getDownloads(int theId, String downloadTimes, ResultSet[] rs)
throws SQLException {
    String DML = null;
    PreparedStatement ps = null;
    DML = "SELECT d.* FROM download_time d WHERE d.id = ? " + 
    "AND d.downloadtime IN(" + downloadTimes + ") " : "") + //Add chk null condition
"ORDER BY 1, 2 DESC, 3 ";
    ps = conn.prepareStatement(DML);
    ps.setInt(1, theId);
    rs[0] = ps.executeQuery();
    }

Note that the "getDownloads" procedure is declared in Derby later in the same class (see declaration in my original question), left out for simplicity. The procedure is called by a method in a different class:

public Map<GregorianCalendar, List<Fault>> getDownloadFaultList(
        Integer theId, String subsystem, List<GregorianCalendar> downloadTimes) {
    CallableStatement cstmt = null;
    ResultSet rs = null;
    String downloadCalListToCsv = null;

    // parseGregorianCalListToCsv() creates a CSV string out of dates.
    // I.e., "2011-11-13 13:24:00.0, 2011-11-13 13:28:00.0"
    if (false == downloadTimes.isEmpty()) {
            downloadCalListToCsv = DataTypeConverter
            .parseGregorianCalListToCsv(downloadTimes, timestampFormat);
    }
    try {
        cstmt = getConn().prepareCall("{ call getDownloads(?, ?) }");

        // Register the parameters
        cstmt.setInt(1, theId);

        // Get timezone from first entry, assuming all same timezone
        if (! downloadTimes.isEmpty()) {
            cal.setTimeZone(downloadTimes.get(0).getTimeZone());
        }
        cstmt.setString(2, downloadCalListToCsv);
        cstmt.execute();
        rs = cstmt.getResultSet();
        while (null != rs && rs.next()) {
            //Use the download timestamps here
        }
    } catch (SQLException sqle) {
        //error handling here
    } finally {
        //Close resources
        close(rs, cstmt);
    }
    return faultMap;
}

The solution is not elegant, but works in practice.

How to Pass Multiple Timestamps as Dynamic Parameters into a Derby Query?

1 Answers1