com.pivotal.gemfirexd.internal.jdbc.package.html Maven / Gradle / Ivy

java.sql.Blob

Clarification for getBytes(int pos, int length)

Clarification for position(byte pattern, int start) and position(Blob pattern, int start)

java.sql.Clob

Clarification for getSubString(int pos, int length)

Clarification for position(String searchstr, int start) and position(Clob searchstr, int start)

java.sql.Connection

Clarification for createStatement(), prepareStatement() and prepareCall()

java.sql.PreparedStatement

Clarification for setAsciiStream(),setBinaryStream(),setCharacterStream()

Stream parameter values are not re-used. [JDBC3] says in the last paragraph of 13.2.2 that parameters are re-used but nothing special about streams. However javadoc for java.sql.PreparedStatement.clearParamters() says 'In general, parameter values remain in force for repeated use of a statement'. Maybe 'in general' can be interpreted to mean 'except for streams'. Stream parameter values are not re-used and if a stream is re-used, the statement execution will fail with 'Stream has already been read and end-of-file reached and cannot be re-used.'.

java.sql.ResultSet

Extension for getXXX()

• If the value column is null, then generally any getXXX method can be used to fetch the null value and a null or a representation of zero will be returned and a subsequent call to wasNull() will return true. It is not recommended that applications take advantage of this liberal conversion of SQL NULL values, use of a getXXX() method that works against NULL and non-NULL values is strongly recommended.

Clarification for deleteRow()

Clarification for updateRow()

Clarification for getAsciiStream()

An ASCII character is defined as an eight bit character (range 0x00 to 0xff), see CHAR() function definition by [JDBC3] in appendix C.2.

For character types (Types.CHAR, Types.VARCHAR & Types.LONGVARCHAR), each character in the value is translated to one byte in the ASCII stream:

• Unicode characters in the range 0x0000 to 0x00ff, are translated to a byte with the matching value (0x00 to 0xff).
• Unicode caracters in the range (0x0100 to 0xffff) are translated to 0x3f, ('?', question mark character).

Extension for getAsciiStream()

• Types.CLOB columns getAsciiStream() returns a stream with identical contents to that returned by getClob().getAsciiStream() on the same column if the CLOB value is not NULL. If the CLOB value is NULL then null is returned.
• Types.BLOB columns getAsciiStream() returns a stream with identical contents to that returned by getBlob().getBinaryStream() on the same column if the BLOB value is not NULL. If the BLOB value is NULL then null is returned.

Extension for getBinaryStream()

Clarification for getCharacterStream()

Extensions for getCharacterStream()

• On Types.CLOB columns returns a reader with identical contents to that returned by getClob().getCharacterStream() on the same column if the CLOB value is not NULL. If the CLOB value is NULL then null is returned.
• On Types.BLOB columns supported with conversion as a binary type.

Clarification for getString()

Extensions for getString()

• On Types.CLOB columns returns a String representing the contents of the CLOB value if it is not NULL. If the CLOB value is NULL then null is returned.
• On Types.BLOB columns supported with conversion as a binary type.

getUnicodeStream()

Binary column example

• getBinaryStream() returns a java.io.InputStream containing four bytes - 0x34, 0x87, 0xc2, 0x1f
• getAsciiStream() returns a java.io.InputStream containing four bytes - 0x34, 0x87, 0xc2, 0x1f [ same as getBinaryStream() ]
• getCharacterStream() returns a java.io.Reader containing two characters - 0x3487, 0xc21f
• getString() returns a String with eight characters "3487c21f"

Derby's SQL DATE interactions with JDBC

Derby’s SQL DATE type represents a date in the form yyyy-mm-dd with no associated time zone information.

java.sql.Date

A JDBC Date (java.sql.Date) by definition represents a point in time on a given date in a given time zone.

[JDBC3] intends that the point in time for a java.sql.Date object is 00:00 (midnight), but this is not enforced by the class.

JDBC drivers are required to return java.sql.Date objects that are normalized to 00:00 according to the required time zone.

Applications are expected to pass in java.sql.Date instances that are normalized to 00:00 (see section 18.1.1 of [TUTORIAL3]).

Conversion of a JDBC java.sql.Date to a Derby DATE value

1. setDate() without a Calendar object or passing null for a Calendar object:

   • The yyyy-mm-dd values will be calculated from the milli-seconds value of the java.sql.Date instance using a Calendar object set to the time zone of the virtual machine.

   • This yyyy-mm-dd value will match the output of java.sql.Date.toString().

2. setDate() with a Calendar object:

   • The yyyy-mm-dd values will be calculated from the milliseconds value of the java.sql.Date instance using the passed in Calendar.

   • The code for this is
     cal.setTimeInMillis(value.getTime());
     yyyy = cal.get(Calendar.YEAR);
     mm = cal.get(Calendar.MONTH) + 1;
     dd = cal.get(Calendar.DAY_OF_MONTH);

   • This yyyy-mm-dd value may not match the output of java.sql.Date.toString() for the value, since this method always uses the time zone of the virtual machine.

Derby does not require that the application’s java.sql.Date value is normalized to 00:00 according to the required time zone.

In both cases no time zone information is stored with the SQL DATE value.

Conversion of a Derby DATE value to a JDBC java.sql.Date

1. getDate() without a Calendar object or passing null for a Calendar object:

   • A java.sql.Date instance is returned with a millisecond value corresponding to 00:00 on yyyy-mm-dd according to the time zone of the java virtual machine

   • The toString() method of the returned value will return ‘yyyy-mm-dd’

2. getDate() with a Calendar object:

   • A java.sql.Date instance is returned with a millisecond value corresponding to 00:00 on yyyy-mm-dd according to the time zone of the Calendar

   • The toString() method of the returned value may not return ‘yyyy-mm-dd’, since this method always uses the time zone of the virtual machine.

Conversion of a string type to a JDBC java.sql.Date

Three different date formats are built into Derby.

If the format of the string matches one of the built in formats then a conversion to a java.sql.Date matches that of a SQL DATE value with value yyyy-mm-dd.

If the string does not match any of the built in formats Derby attempts to use the Java locale specific parser to interpret the string as a date.

Derby's SQL TIME interactions with JDBC

Derby’s SQL TIME type represents a time of day in the form hh:mm:ss with no associated time zone information.

java.sql.Time

A JDBC Time (java.sql.Time) by definition represents a point in time on an unspecified day in a given time zone.

Java.sql.Time extends java.util.date, so it includes a date. [JDBC3] intends that the date stored in a java.sql.Time be Jan 1 1970, but this is not enforced by the class.

JDBC drivers are required to return java.sql.Time objects that are normalized to Jan. 1 1970 according to the required time zone.

Applications are expected to pass in java.sql.Time instances that are normalized to Jan. 1 1970.

Derby's SQL TIMESTAMP interactions with JDBC

Derby’s SQL TIMESTAMP type represents a time of day in the form yyyy-mm-dd hh:mm:ss.fffffffff (nanosecond granularity) with no associated time zone information.

java.sql.Timestamp

A JDBC Timestamp (java.sql.Timestamp) by definition represents a point in time, with nanosecond resolution, in a given time zone.

Conversion of a JDBC java.sql.Timestamp to a Derby TIMESTAMP value

1. setTimestamp() without a Calendar object or passing null for a Calendar object

   • The year, month, day, hour, minute, and second values will be calculated from the milli-seconds value of the java.sql.Timestamp instance using a Calendar object set to the time zone of the virtual machine. The nanosecond value will be calculated from the nanoseconds value of the java.sql.Timestamp.

   • The timestamp component values will match the output of java.sql.Timestamp.toString().

2. setTime() with a Calendar object

   • The year, month, day, hour, minute, and second values will be calculated from the milliseconds value of the java.sql.Date instance using the passed in Calendar. The nanosecond value will be calculated from the nanoseconds value of the java.sql.Timestamp.

   • The code for this is
     cal.setTimeInMillis(value.getTime());
     year = cal.get(Calendar.YEAR);
     month = cal.get(Calendar.MONTH) + 1;
     day = cal.get(Calendar.DAY_OF_MONTH);
     hour = cal.get(Calendar.HOUR);
     minute = cal.get(Calendar.MINUTE);
     second = cal.get(Calendar.SECOND);
     nanos = value.getNanos();

   • This stored timestamp component value may not match the output of java.sql.Timestamp.toString() for the value, since this method always uses the time zone of the virtual machine.

Conversion of a Derby TIMESTAMP value to a JDBC java.sql.Timestamp

1. getTimestamp() without a Calendar object or passing null for a Calendar object

   • A java.sql.Timestamp instance is returned with a nanosecond value corresponding to yyyy-mm-dd hh:mm:ss.fffffffff according to the time zone of the java virtual machine

   • The toString() method of the returned value will return ‘yyyy-mm-dd hh:mm:ss.fffffffff’.

2. getTime() with a Calendar object

   • A java.sql.Time instance is returned with a nanosecond value corresponding to yyyy-mm-dd hh:mm:ss.fffffffff according to the time zone of the Calendar

   • The toString() method of the returned value may not return ‘yyyy-mm-dd hh:mm:ss.fffffffff’, since this method always uses the time zone of the virtual machine.

Conversion of a string type to a JDBC java.sql.Timestamp

Two different timestamp formats are built into Derby:

Note that only microsecond resolution is supported in converting strings to timestamps.

If the format of the string matches one of the built in formats then a conversion to a java.sql.Timestamp matches that of a SQL TIMESTAMP value with value yyyy-mm-dd hh:mm:ss.ffffff.

If the string does not match any of the built in formats Derby attempts to use the Java locale specific parser to interpret the string as a date.