Handling Date with Google Gears

Suppose you execute the following DML statement in Google Gears:
db.execute(
"insert or replace into mytable "+
"(id, modified_on) values (?, ?)",
[null, new Date()]
);

This seemingly works but you are in for a surprise because Date is stored as Date.toString(). SQLite ducktying brain can’t infer that it’s a date datum. If you ran select query you will get string back.
Sat Jan 12 2008 02:34:58 GMT-0800 ยป
(Pacific Standard Time)

The downside of storing date as text that you can’t use any SQLite date time functions in your queries. Due to manifest typing, SQLite happily stores text in a date column. We would like to do better. We would like to store JavaScript Date as native SQLite Date without any loss of information. SQLite itself stores date in Julian Day representation while JavaScript cannonical date representation is number of milliseconds since 1 January 1970 00:00:00 UTC. So, we need to convert JavaScript Date to either Julian Day or text as understood by SQLite. Considering the ease of implementation, efficiency and lossless representation, YYYY-MM-DDTHH:MM:SS.FFF date format seems to fit the bill. This is ISO8601 format. However after some digging in SQLite code, it turns out that if it SQLite also works even if we don’t zero pad fields. So this simple JavaScript method with minor departure from ISO8601 format does the job well.
dateToSQLiteFormat = function(date) {
return date.getUTCFullYear()+
"-"+date.getUTCMonth() + 1+
"-"+date.getUTCDate()+
"T"+date.getUTCHours()+
":"+date.getUTCMinutes()+
":"+date.getUTCSeconds()+"."+
date.getUTCMilliseconds();
};

So far so good. How about converting SQLite dates to JavaScript Date object. This function does that trick.
dateSqlFragment = function(column, alias) {
return " ((strftime('%s', "+column+") -
strftime('%S', "+column+"))*1000 +
strftime('%f', "+column+")*1000) "+
(alias "");
};

var rs = db.execute(
“select id, “+dateSqlFragment(“modified_on”) +
” from mytable”);

Date date = new Date(rs.field(1));

Smart Copy Plugin for IntelliJ

IntelliJ always seems to do The Right Thing. It comes with code-aware features such as Smart Line Joins, and Smart Line Split. However, there is not Smart Copy to complement Smart Paste. Besides symmetry, there are good reasons to have Smart Copy. Supppose, you have a SQL query in Java Code and you want to run it in SQLPlus to see an execution plan. Another example could be embedded XML as compile time constant. When you copy a query embedded in a Java class, perhaps you are interested in its value without without the quotes and plus characters clinging to the copied text.
If you are a surprised user, you are not alone. Smart Copy is a plugin to copy the literal values. Smart Copy action maps to Ctrl+Alt+Shift+C by default. It copies the value of compile time constants and String literals to the system clipboard. It also copies the selected literals. Entire expression is copied if noting is selected. If it cannot do smart copy, it will fallback to regular copy action. Certainly a candidate for your Ctrl+C mapping.

Maven debug output

Imagine you are frustrated by one of the maven plugin and you want to turn the debug output on. Google debugging a maven plugin or maven debug output and you may still be out of luck. Here is the brute force way of doing it.
Open MAVEN_HOME/core/plexus-container-default-1.0-alpha-9.jar and edit org/codehaus/plexus/plexus-bootstrap.xml to set threshold to debug.

<component>
<role>org.codehaus.plexus.logging.LoggerManager</role>
<implementation>
org.codehaus.plexus.logging.console.ConsoleLoggerManager
</implementation>
<lifecycle-handler>basic</LIFECYCLE-HANDLER>
<configuration>
<threshold>debug</threshold>
</configuration>
</component>

Oracle JDBC Connection KeepAlive

So, you’ve got a spoiled bloated app or a third party library and it’s fond of manopolizing JDBC connections. There are couple of options with Oracle.

1) Server side option: Oracle Database comes with a Dead Client Detection feature where Oracle Server sends a probe. It can be configured by specifying SQLNET.EXPIRE_TIME parameter in sqlnet.ora. The value specified in in minutes.

2) If you control the connection, then there are plenty of option. If you don’t (in case of third party libraries), then TCP KeepAlive may help you. However, Oracle JDBC driver doesn’t provide a good way to customize TCP level parameters. The only way to specify KeepAlive setting is to use TNSEntry. It works with Thin driver. However, on the downside, you need to have connect string in tnsnames.ora. (ENABLE=broken) option will set KeepAlive on TCP socket.
orcl =
(DESCRIPTION =(ENABLE=broken)
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

System.setProperty("oracle.net.tns_admin", "oracle/NETWORK/ADMIN");
OracleDataSource ods = new OracleDataSource();
ods.setTNSEntryName("orcl");
ods.setDriverType("thin");
ods.setUser("scott");
ods.setPassword("tiger");
ods.setLoginTimeout(2);
Properties cp = new Properties();
cp.setProperty("SetBigStringTryClob", "true");
ods.setConnectionProperties(cp);

A typical error when a connection is not used for extended period is,
java.sql.SQLException: Io exception: Connection reset
at oracle.jdbc.driver.DatabaseError.throwSqlException( DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException( DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException( DatabaseError.java:255)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows( T4CPreparedStatement.java:974 )
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe( OracleStatement.java:1054 )
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe( T4CPreparedStatement.java:836 )
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout( OracleStatement.java:1124 )
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal( OraclePreparedStatement.java:3285 )

You can keep the JDBC connection alive this way even if its not used for long time. However, if you are in such situation, get out before you employ this trick. You will be happier. Long time back, we ran into such a library and we found out the root cause and realized that we should pull the rip cord.

java.util.Scanner snafu

Java 5 java.util.Scanner provides a better replacement for StringTokenizer. And, the hope was that we should be able to write,

Scanner lines = new Scanner(new File("/etc/passwd"));
for(String line : lines) {
System.out.println(line);
}

Unfortunately, this doesn’t work because someone mistyped, and it implements Iterator instead of Iterable.

Blog at WordPress.com.