Would like to check if a behavior seen with PostgresSQL JDBC Driver 5.1 is as expected or not

Printable View

Information

Title	Would like to check if a behavior seen with PostgresSQL JDBC Driver 5.1 is as expected or not

URL Name	would-like-to-check-if-a-behavior-seen-with-postgressql-jdbc-driver-5-1-is-as-expected-or-not

Article Number	000155064

Environment	Product: Connect for JDBC PostgreSQL Driver Version : 5.1 OS: JAVA Database : PostgreSQL Application: All supported applications

Question/Problem Description

The application code has a Prepared Statement which compares an input parameter to a numeric literal ( "select 1 C0 where 0 < ?" ).
Then the input parameter is set to a BigDecimal value.
After calling getParameterMetadata() on the Prepared Statement, it was found that the data type of the parameter was an INT instead of the type of the value set for input parameter.
It looks like the type of the parameter returned from metadata is same as the type of the literal it is compared with in the prepared statement even if a different type was set for the input parameter.
Is that an expected behavior for PostgreSQL JDBC driver?

Steps to Reproduce	Java code snippets to run: ----------------------------------- String sql = "select 1 C0 where 0 < ?"; PreparedStatement pst = conn.prepareStatement(sql); BigDecimal val = new BigDecimal(0.50); System.out.println("val:"+val); pst.setBigDecimal(1,val); ParameterMetaData pmd = pst.getParameterMetaData(); int pcount = pmd.getParameterCount(); System.out.printf("There are %d parameters\n",pcount); System.out.println("ParameterMetaData: "); for (int i=1;i<=pcount;i++) { String pType = pmd.getParameterTypeName(i); int precision = pmd.getPrecision(i); int scale = pmd.getScale(i); System.out.println(i+" of type "+pType+" ("+precision+","+scale+")"); } pst.execute(); rs = pst.getResultSet(); System.out.println("Printing result..."); while (rs.next()) { int c0 = rs.getInt("C0"); System.out.println("C0: " + c0 ); }

Steps to Reproduce

Java code snippets to run:
-----------------------------------

String sql = "select 1 C0 where 0 < ?";
PreparedStatement pst = conn.prepareStatement(sql);
BigDecimal val = new BigDecimal(0.50);
System.out.println("val:"+val);
pst.setBigDecimal(1,val);
ParameterMetaData pmd = pst.getParameterMetaData();
int pcount = pmd.getParameterCount();
System.out.printf("There are %d parameters\n",pcount);
System.out.println("ParameterMetaData: ");
for (int i=1;i<=pcount;i++) {
String pType = pmd.getParameterTypeName(i);
int precision = pmd.getPrecision(i);
int scale = pmd.getScale(i);
System.out.println(i+" of type "+pType+" ("+precision+","+scale+")");
}

pst.execute();
rs = pst.getResultSet();
System.out.println("Printing result...");
while (rs.next()) {
int c0 = rs.getInt("C0");
System.out.println("C0: " + c0 );
}

Clarifying Information

Error Message	No error, just wrong results. Output from the script provided above: val:0.5 There are 1 parameters ParameterMetaData: 1 of type INTEGER (10,0) Printing result...

Defect Number

Enhancement Number

Cause

In a prepared statement, the metadata is established based on a static analysis of the SQL statement itself, and does not change based on any setXXX calls that are subsequently done.
The reason SQL engines do that is because the types do not change over the lifetime of the statement.
Otherwise, it would not be prepared! Remember that "preparing" is "compiling", and it's at that time that the query plan is often built.
Metadata is based on the statement itself without knowledge of any potential parameter values that are subsequently set.
This is a general pattern for SQL engines, and not unique to PostgreSQL.

Resolution

This is an expected behavior from the database.

Workaround

Notes

Last Modified Date	3/18/2020 6:22 PM

Files

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Progress, Telerik, Ipswitch, Chef, Kemp, Flowmon and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.

Sitefinity

NativeChat

Kendo UI

Telerik

Test Studio

Fiddler Everywhere

Chef

MOVEitNew Release

WS_FTP

OpenEdge

Kinvey

WhatsUp Gold

DataDirect

Corticon

Kemp

Flowmon

Would like to check if a behavior seen with PostgresSQL JDBC Driver 5.1 is as expected or not

Information

MOVEit
New Release