PostgreSQL JDBC Driver version 9_3_1102 released

This is a maintenance release with a few interesting upgrades

Version 9.3-1102 (2014-07-10)

Author:epgrubmair bug #161
    fix copyOut close hanging bug #161 from epgrubmair

Author:romank0

    backpatch exception during close of fully read stream from romank0

Author:Christophe Canovas

    Added caching for ResultSetMetaData  complete commit

Author:Elizabeth Chatman
    NullPointerException in AbstractJdbc2DatabaseMetaData.getUDTs

    setNull, setString, setObject may fail if a specified type cannot be transferred in a binary mode #151

    backpatch fix for changing datestyle before copy

Author:TomonariKatsumata
    binary transfer fixes new feature -1 for forceBinaryTransfer

Author:Sergey Chernov
    connectTimeout property support backpatch
   
Author:Naoya Anzai
    fix prepared statement ERROR due to EMPTY_QUERY defined as static.

9.4 jars can also be found on the site 

PostgreSQL JDBC example with Spring Transactions

Using PostgreSQL JDBC with Spring.

This article came about after a user filed an issue on the JDBC list

The specific issue is that I’m using SimpleJdbcCall.execute() to call the database and getting back a Jdbc4Array. When I then try to do something like Jdbc4Array.getArray() I get a SQL error that can be tracked down to the Jdbc driver trying to use a connection object which has already been closed by the Spring Framework.

The problem is that once you get the array back more work has to be done to get the values out of it. The following code is an example of how to use transactions with spring and PostgreSQL JDBC

	package ca.credativ.jdbc;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.context.ApplicationContext;
	import org.springframework.context.support.ClassPathXmlApplicationContext;
	import java.sql.Array;
	/**
	* Created by davec on 2014-04-16.
	*/
	public class SpringTransactionExample
	{
	public static void main(String []args)
	{
	ApplicationContext applicationContext = new ClassPathXmlApplicationContext("context.xml");
	try
	{
	PostgresArray postgresArray = (PostgresArray)applicationContext.getBean("postgresArrayImpl");
	String []strings = postgresArray.get() ;
	for (int i=0;i < strings.length;i++)
	{
	System.out.println(strings[i]);
	}
	}
	catch( Exception ex )
	{
	ex.printStackTrace();
	}
	}
	}

view raw SimpleTransactionExample.java hosted with ❤ by GitHub

Simple interface to get a value out of an array

	package ca.credativ.jdbc;
	/**
	* Created by davec on 2014-04-16.
	*/
	public interface PostgresArray
	{
	String[] get() throws Exception;
	}

view raw PostgresArray.java hosted with ❤ by GitHub

Implementation which does the actual work Note the @Transaction annotation on line 22, this is required to ensure that the connection is not closed after the first call on line 42 without this annotation spring would close the connection and the next line 43 would throw an exception. The constructor line 27 is required for spring to create the implementation and inject the datasource defined in the context

	package ca.credativ.jdbc;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.jdbc.core.SqlOutParameter;
	import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
	import org.springframework.jdbc.core.simple.SimpleJdbcCall;
	import org.springframework.jdbc.core.support.JdbcDaoSupport;
	import org.postgresql.jdbc4.Jdbc4Array;
	import org.springframework.stereotype.Repository;
	import org.springframework.transaction.annotation.Transactional;
	import javax.sql.DataSource;
	import java.sql.SQLException;
	import java.util.Map;
	import java.util.logging.Level;
	import java.util.logging.Logger;
	/**
	* Created by davec on 2014-04-16.
	*/
	@Transactional
	@Repository
	public class PostgresArrayImpl extends JdbcDaoSupport implements PostgresArray
	{
	@Autowired
	public PostgresArrayImpl(DataSource dataSource) {
	setDataSource(dataSource);
	}
	@Override
	public String [] get() throws Exception
	{
	SimpleJdbcCall call = new SimpleJdbcCall(this.getJdbcTemplate());
	call.withFunctionName("usecase01_get");
	call.withSchemaName("public");
	call.withoutProcedureColumnMetaDataAccess();
	call.declareParameters(new SqlOutParameter("testcolumn", java.sql.Types.ARRAY));
	MapSqlParameterSource map = new MapSqlParameterSource();
	Map<String, Object> result = call.execute(map);
	Jdbc4Array jdbc4Array = (Jdbc4Array)result.get("testcolumn");
	try {
	Object o = jdbc4Array.getArray();
	Logger.getLogger(PostgresArrayImpl.class.getName()).log(Level.SEVERE, null, o);
	return (String [])o;
	} catch (SQLException ex) {
	Logger.getLogger(PostgresArrayImpl.class.getName()).log(Level.SEVERE, null, ex);
	throw ex;
	}
	}
	}

view raw PostgresArrayImpl.java hosted with ❤ by GitHub

Spring context

	<?xml version="1.0" encoding="UTF-8"?>
	<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
	http://www.springframework.org/schema/context
	http://www.springframework.org/schema/context/spring-context-3.2.xsd
	http://www.springframework.org/schema/tx
	http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="org.postgresql.Driver"/>
	<property name="url" value="jdbc:postgresql://localhost:5432/usecase"/>
	<property name="username" value="postgres"/>
	<property name="password" value="########"/>
	</bean>
	<context:component-scan base-package="ca.credativ.jdbc" />
	<tx:annotation-driven transaction-manager="txManager"/>
	<!-- a PlatformTransactionManager is still required -->
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<!-- (this dependency is defined somewhere else) -->
	<property name="dataSource" ref="dataSource"/>
	</bean>
	</beans>

view raw context.xml hosted with ❤ by GitHub

SQL to create function and data

	CREATE TABLE usecase01 (
	testcolumn text[][]
	);
	CREATE FUNCTION usecase01_get()
	RETURNS TABLE
	(
	testcolumn text[][]
	)
	AS $$
	BEGIN
	RETURN QUERY SELECT
	usecase01.testcolumn
	FROM
	usecase01;
	RETURN;
	END;
	$$ LANGUAGE plpgsql;
	INSERT INTO usecase01 VALUES('{"Use","Case","01"}');
	--SELECT * FROM usecase01_get();

view raw UseCase01.sql hosted with ❤ by GitHub

Code for this can be found here SpringTransactionExample
Thanks to Michael Miller for the initial code used in this example

PostgreSQL Driver now under continuous integration

Continuous Integration is all about being able to push our code-changes to github and instantly find out if our build fails, or to discover if our code-changes fail our prepared tests. 

The last few days I have been testing a continuous integration service provided by the good folks at Travis-CI. Best of all, this continous integration service is free for open source projects!

The build can be found here

JDBC driver 9.3 version 1101 released today.

Changes are primarily bug fixes

Changelog can be found  here 

scp protocol using jsch

Using Jsch to  scp a file to a remote computer I found Jan Pechanec's excellent blog how_the_scp_protocol_works . The one thing I couldn't figure out was how to specify the remote directory. 

Originally I tried using D mode dir to specify the directory. That results in an error.

As you can specify the target directory using scp manually there must have been a way. Turns out it is simple.

Line 43 of scp is String command="scp " + (ptimestamp ? "-p" :"") +" -t "+rfile; 

All that is required is to specify the target dir in the command as follows:

String command="scp " + (ptimestamp ? "-p" :"") +" -t "+targetDir+'/'+rfile;