Pivotal Knowledge Base

Follow

How to test JDBC and Greenplum Datadirect JDBC

Environment

Purpose

  This article provides a method for users and developers to test JDBC connectivity to GPDB and HAWQ.  The code sample included in this article is only for testing purposes and can be used as an example of how to write a JDBC application.

Usage

[gpadmin@pccadmin jdbc]$ java -classpath ./jdbc-pivotal-support.jar io.pivotal.support.JDBCTest

Usage: pivotal.support.JDBCTest     

JDBCTest:
java -classpath ./postgresql-8.4-701.jdbc4.jar:./jdbc-postgres.jar pivotal.support.JDBCTest "jdbc:postgresql://mdw:5432/gpadmin?protocolVersion=3&user=gpadmin&password=changeme&loglevel=2" "select * from table"

JDBCTestGreenplum:
java -classpath ./jdbc-postgres.jar:greenplum.jar pivotal.support.JDBCTestGreenplum "jdbc:pivotal:greenplum://mdw:4342;DatabaseName=gpadmin;" "select * from table"

Example running using Postgres JDBC

[gpadmin@pccadmin jdbc]$ java -classpath ./postgresql-8.4-701.jdbc4.jar:./jdbc-pivotal-support.jar io.pivotal.support.JDBCTest "jdbc:postgresql://hdm1:5432/gpadmin?protocolVersion=3&user=gpadmin&password=changeme&loglevel=2" "select * from test" org.postgresql.Driver
Num of args=3
ARG0: jdbc:postgresql://hdm1:5432/gpadmin?protocolVersion=3&user=gpadmin&password=changeme&loglevel=2
ARG1: select * from test
ARG2: org.postgresql.Driver
user=
pass=
07:24:03.059 (1) PostgreSQL 8.4 JDBC4 (build 701)
07:24:03.067 (1) Trying to establish a protocol version 3 connection to hdm1:5432
07:24:03.108 (1)  FE=> StartupPacket(user=gpadmin, database=gpadmin, client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
07:24:03.115 (1)  <=BE AuthenticationOk
07:24:03.133 (1)  <=BE ParameterStatus(application_name = )
07:24:03.133 (1)  <=BE ParameterStatus(client_encoding = UNICODE)
07:24:03.134 (1)  <=BE ParameterStatus(DateStyle = ISO, MDY)
07:24:03.134 (1)  <=BE ParameterStatus(integer_datetimes = on)
07:24:03.134 (1)  <=BE ParameterStatus(IntervalStyle = postgres)
07:24:03.134 (1)  <=BE ParameterStatus(is_superuser = on)
07:24:03.134 (1)  <=BE ParameterStatus(server_encoding = UTF8)
07:24:03.134 (1)  <=BE ParameterStatus(server_version = 8.2.15)
07:24:03.134 (1)  <=BE ParameterStatus(session_authorization = gpadmin)
07:24:03.135 (1)  <=BE ParameterStatus(standard_conforming_strings = off)
07:24:03.135 (1)  <=BE ParameterStatus(TimeZone = US/Pacific)
07:24:03.135 (1)  <=BE BackendKeyData(pid=795796,ckey=869565242)
07:24:03.135 (1)  <=BE ReadyForQuery(I)
07:24:03.135 (1)     compatible = 8.4
07:24:03.135 (1)     loglevel = 2
07:24:03.135 (1)     prepare threshold = 5
getConnection returning org.postgresql.Driver
Attempt to execute: 0
07:24:03.176 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@74dbe8cd, maxRows=0, fetchSize=0, flags=1
07:24:03.178 (1)  FE=> Parse(stmt=S_1,query="BEGIN",oids={})
07:24:03.178 (1)  FE=> Bind(stmt=S_1,portal=null)
07:24:03.181 (1)  FE=> Execute(portal=null,limit=0)
07:24:03.182 (1)  FE=> Parse(stmt=null,query="select * from test",oids={})
07:24:03.182 (1)  FE=> Bind(stmt=null,portal=null)
07:24:03.183 (1)  FE=> Describe(portal=null)
07:24:03.183 (1)  FE=> Execute(portal=null,limit=0)
07:24:03.183 (1)  FE=> Sync
07:24:04.640 (1)  <=BE ParseComplete [S_1]
07:24:04.641 (1)  <=BE BindComplete [null]
07:24:04.641 (1)  <=BE CommandStatus(BEGIN)
07:24:04.641 (1)  <=BE ParseComplete [null]
07:24:04.641 (1)  <=BE BindComplete [null]
07:24:04.642 (1)  <=BE RowDescription(1)
07:24:04.643 (1)  <=BE DataRow
07:24:04.643 (1)  <=BE DataRow
07:24:04.643 (1)  <=BE DataRow
07:24:04.643 (1)  <=BE DataRow
07:24:04.643 (1)  <=BE DataRow
07:24:04.653 (1)  <=BE CommandStatus(SELECT)
07:24:04.669 (1)  <=BE ReadyForQuery(T)
Attempt to execute: 1
07:24:04.670 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@709ff881, maxRows=0, fetchSize=0, flags=1
07:24:04.670 (1)  FE=> Parse(stmt=null,query="select * from test",oids={})
07:24:04.670 (1)  FE=> Bind(stmt=null,portal=null)
07:24:04.670 (1)  FE=> Describe(portal=null)
07:24:04.670 (1)  FE=> Execute(portal=null,limit=0)
07:24:04.671 (1)  FE=> Sync
07:24:04.745 (1)  <=BE ParseComplete [null]
07:24:04.745 (1)  <=BE BindComplete [null]
07:24:04.745 (1)  <=BE RowDescription(1)
07:24:04.756 (1)  <=BE DataRow
07:24:04.756 (1)  <=BE DataRow
07:24:04.756 (1)  <=BE DataRow
07:24:04.756 (1)  <=BE DataRow
07:24:04.756 (1)  <=BE DataRow
07:24:04.756 (1)  <=BE CommandStatus(SELECT)
07:24:04.756 (1)  <=BE ReadyForQuery(T)
07:24:04.756 (1)  FE=> Terminate

Example running query with Greenplum.jar

[gpadmin@pccadmin jdbc]$ java -classpath ./jdbc-pivotal-support.jar:greenplum.jar pivotal.support.JDBCTest "jdbc:pivotal:greenplum://hdm1:5432;DatabaseName=gpadmin;" "select * from test" com.pivotal.jdbc.GreenplumDriver gpadmin changeme
Num of args=5
ARG0: jdbc:pivotal:greenplum://hdm1:5432;DatabaseName=gpadmin;
ARG1: select * from test
ARG2: com.pivotal.jdbc.GreenplumDriver
ARG3: gpadmin
ARG4: changeme
user=gpadmin
pass=changeme
Attempt to execute: 0
Attempt to execute: 1

Get Source Code and build jar file

  1. git clone https://github.com/randomtask1155/PivotalJDBCTest.git
  2. cd PivotalJDBCTest
  3. mvn package

 

Comments

  • Avatar
    Yi Sun

    Hey Dan,

    Looks like GP 4.x are still gonna work with PG8.2 for a long while, also the most up to date JDBC should be 5.1.4...

Powered by Zendesk