Oracle to EDB Postgres Java embedded SQL issues

Question

I'm working on a migration project to move from Oracle to EDB Postgres (including changing Java code that makes DB calls via JDBC, to make it work wth both Oracle and Postgres). EDB Postgres comes with an in-built PL/ Java layer that does some conversion of Oracle functions to their respective equivalents in Postgres. However, this isn't working in all cases.

We are using Oracle 11g and EDB Postgres 9.5

Some of the issues we've faced so far with postgres are:

org.postgresql.util.PSQLException: ERROR: date format not recognized (for format 'DD/MM/YYYY HH24:MI:SSxFF3)
org.postgresql.util.PSQLException: Bad value for type long (getClob and setClob don't work with postgres text columns)
org.postgresql.util.PSQLException: ERROR: syntax error at or near "NAME" (Any column alias named NAME fails during JDBC PreparedStatement execution)
org.postgresql.util.PSQLException: ERROR: syntax error at or near "TEMPLATE" (Any column alias named TEMPLATE fails during JDBC PreparedStatement execution)

EDIT: Java code is straight-forward. Snippets below include Java calls and table details:

Issue #1

create table table_name (TEMPLATE varchar2(10))

Column Data types:

Oracle
TEMPLATE varchar2(10)

Postgres

TEMPLATE character varying (10) (Postgres table was created by the EDB postgres migration toolkit)

Issue in the below SQL is with the column alias TEMPLATE, works if I change the alias to say TEMPLATE1, TEMPLATE seems to be a keyword in Postgres.

sqlQuery = "SELECT A.TEMPLATE TEMPLATE FROM TABLE_NAME A";
preparedStatement = conn.prepareStatement(sqlQuery); 
resultSet = preparedStatement.executeQuery();
//Works in Oracle, ERROR in postgres: syntax error at or near "TEMPLATE"

Issue #2

create table table_name1 (date_col TIMESTAMP(6))

Column Data types:

Oracle
Date_Col TIMESTAMP(6)

Postgres

Date_Col timestamp without time zone (Postgres table was created by the EDB postgres migration toolkit)

sqlQuery = "Select TO_CHAR(DATE_COL,'DD/MM/YYYY HH24:MI:SSxFF3') from TABLE_NAME1";
preparedStatement = conn.prepareStatement(sqlQuery); 
resultSet = preparedStatement.executeQuery();
//Works in Oracle, ERROR in postgres: date format not recognized

While we have specific solutions to each of the issues listed above, suggestions/ recommendations to handle such differences in Java based on experiences of moving from Oracle to Postgres would be helpful. We would want to move to Java code that would work for both Postgres and Oracle (Ideally!) and would like to avoid if-else code to call Oracle vs Postgres specific SQLs.

What data type is date_col? What is the create table statement for the table_name from the first query? — user330315
– user330315, Commented Jun 2, 2017 at 5:47
You can't work around the fact that template is a reserved keyword in Postgres. The to_char() format mask works for me in Postgres without an error. However the xFF3 is not recognized properly and is displayed as is in the output. As far as timestamp formatting is concerned I would simply not do that in SQL, but use SimpleDateFormat from within Java and retrieve a real timestamp from the database — user330315
– user330315, Commented Jun 2, 2017 at 7:12

Sripriya V · Accepted Answer · 2017-06-06 04:04:33Z

1

One possible solution I found for the NAME, TEMPLATE keyword errors is to quote the column alias, that way postgres does not treat NAME, TEMPLATE as keyword(s).

SELECT A.TEMPLATE TEMPLATE FROM TABLE_NAME A
org.postgresql.util.PSQLException: ERROR: syntax error at or near "TEMPLATE" (Any column alias named TEMPLATE fails during JDBC PreparedStatement execution)

Change it to:

SELECT A.TEMPLATE "TEMPLATE" FROM TABLE_NAME A

answered Jun 6, 2017 at 4:04

Sripriya V

2953 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle to EDB Postgres Java embedded SQL issues

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related