callablestatement

java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!

by

Ordinal binding or index binding: String name = stat.getString(2); BigDecimal salary = stat.getBigDecimal(3); Timestamp createdDate = stat.getTimestamp(4); Named binding: String name = stat.getString("NAME"); BigDecimal salary = stat.getBigDecimal("SALARY"); Timestamp createdDate = stat.getTimestamp("CREATED_DATE"); If we mixed both like this: String name = stat.getString(2); BigDecimal salary = stat.getBigDecimal("SALARY"); Timestamp createdDate = stat.getTimestamp(4); Error: java.sql.SQLException: operation not allowed: Ordinal …

Read more

JDBC CallableStatement – PostgreSQL Stored Function

by

A JDBC CallableStatement example to show you how to call a stored function from PostgreSQL database. P.S Tested with PostgreSQL 11 and Java 8 pom.xml <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.5</version> </dependency> 1. Call Function 1.1 Create a stored function and calling it via JDBC. FunctionReturnString.java package com.mkyong.jdbc.callablestatement; import java.sql.*; public class FunctionReturnString { public static void …

Read more

JDBC CallableStatement – Stored Procedure OUT parameter example

by

A JDBC CallableStatement example to call a stored procedure which accepts IN and OUT parameters. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure which accepts IN and OUT parameters. CREATE OR REPLACE PROCEDURE get_employee_by_id( p_id IN EMPLOYEE.ID%TYPE, o_name OUT …

Read more

JDBC CallableStatement – Stored Procedure CURSOR example

by

A JDBC CallableStatement example to call a stored procedure which returns a cursor. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure which returns a cursor. CREATE OR REPLACE PROCEDURE get_employee_by_name( p_name IN EMPLOYEE.NAME%TYPE, o_c_dbuser OUT SYS_REFCURSOR) AS BEGIN OPEN …

Read more

JDBC CallableStatement – Stored Procedure IN parameter example

by

A JDBC CallableStatement example to call a stored procedure which accepts IN parameters. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure to insert a row. CREATE OR REPLACE PROCEDURE insert_employee( p_name IN EMPLOYEE.NAME%TYPE, p_salary IN EMPLOYEE.SALARY%TYPE, p_date IN EMPLOYEE.CREATED_DATE%TYPE) …

Read more