Error Calling Connection.preparecall
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Calling stored function error in Java up vote 0 down vote favorite I am trying to call a stored function in Java. Function has no package, it's placed under user (schema) USER and returns a cursor. I tried twoways to call it and none of these works. First one Query query = coreDao.getEntityManager().createNativeQuery("{call USER.gen_rephead_sm_task_report(?, ?) }"); query.setParameter(1, dateFrom); query.setParameter(2, dateTo); List> queryResult = query.getResultList(); I got a not a procedure or not defined I also tried this approach with select gen_rephead_sm_task_report(?, ?) from dual as a createNativeQuery parameter but with the same result. Second one Connection connection = dataSource.getConnection();//javax.sql.DataSource CallableStatement statement = connection.prepareCall("{? = call USER.gen_rephead_sm_task_report(?, ?) }"); statement.registerOutParameter(1, OracleTypes.CURSOR);//oracle.jdbc.OracleTypes statement.setDate(2, new java.sql.Date(dateFrom.getTime())); statement.setDate(3, new java.sql.Date(dateTo.getTime())); statement.executeQuery(); ResultSet set = ((OracleCallableStatement) statement).getCursor(1); I got the ClassCastException on the last line (obviously OracleCallableStatement doesn't implement the CallableStatement). So which types shoud I use here? java oracle function stored-procedures jdb share|improve this question edited Nov 2 '12 at 10:20 asked Nov 2 '12 at 9:24 Petr Mensik 13.6k54877 Which driver do you use? –user75ponic Nov 2 '12 at 9:29 I am using Oracle 11g with od
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Unable http://stackoverflow.com/questions/13192477/calling-stored-function-error-in-java to execute stored Procedure using Java and JDBC on SQL server up vote 0 down vote favorite I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far. The stored procedure has 1 input and 1 output parameter. With every combination I use when setting up the stored procedure call in code I get http://stackoverflow.com/questions/1726935/unable-to-execute-stored-procedure-using-java-and-jdbc-on-sql-server an error stating that the stored procedure couldn't be found. I have provided the stored procedure I'm executing below (NOTE: this is vendor code, so I cannot change it). set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[spWCoTaskIdGen] @OutIdentifier int OUTPUT AS BEGIN DECLARE @HoldPolicyId int DECLARE @PolicyId char(14) IF NOT EXISTS ( SELECT * FROM UniqueIdentifierGen (UPDLOCK) ) INSERT INTO UniqueIdentifierGen VALUES (0) UPDATE UniqueIdentifierGen SET CurIdentifier = CurIdentifier + 1 SELECT @OutIdentifier = (SELECT CurIdentifier FROM UniqueIdentifierGen) END The code looks like: CallableStatement statement = connection .prepareCall("{call dbo.spWCoTaskIdGen(?)}"); statement.setInt(1, 0); ResultSet result = statement.executeQuery(); I get the following error: SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'. I have also tried CallableStatement statement = connection .prepareCall("{? = call dbo.spWCoTaskIdGen(?)}"); statement.registerOutParameter(1, java.sql.Types.INTEGER); statement.registerOutParameter(2, java.sql.Types.INTEGER); statement.executeQuery(); The above results in: SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'. I have also tried: CallableStatement statement = connection .prepareCall("{? = call spWCoTaskIdGen(?)}"); statement.registerOutParameter(1, java.sql.Types.INTEGER); statement.registerOutParameter(2, java.sql.Types.INTEGER); statement.executeQuery(); The code above resulted in the following error: Could not find stored procedure 'spWCoTaskIdGen'. Finally, I should also point out the following: I have used the MS SQL Server Management Studio tool and have be
App EngineApache AntApache MavenjQueryJava MongoDBQuartz SchedulerLog4jContact Us JDBC CallableStatement – Stored Procedure https://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/ OUT parameter exampleBy mkyong | April 11, 2011 | Updated : August 29, 2012 | Viewed : 281,059 times +1,689 pv/wFor stored procedure returns OUT parameters, it must beRegistered via JDBC CallableStatement.registerOutParameter(index,sqlType).Get it back via CallableStatement.getDataType(index).See error calling code snippets //getDBUSERByUserId is a stored procedure String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}"; callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql); callableStatement.setInt(1, 10); callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR); callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR); callableStatement.registerOutParameter(4, java.sql.Types.DATE); // execute getDBUSERByUserId store procedure callableStatement.executeUpdate(); String userName = callableStatement.getString(2); String createdBy = error calling connection.preparecall callableStatement.getString(3); Date createdDate = callableStatement.getDate(4); JDBC CallableStatement ExampleSee a full JDBC CallableStatement example for OUT parameter. 1. Stored ProcedureA stored procedure in Oracle database, with IN and OUT parameters. Later, calls it via JDBC. CREATE OR REPLACE PROCEDURE getDBUSERByUserId( p_userid IN DBUSER.USER_ID%TYPE, o_username OUT DBUSER.USERNAME%TYPE, o_createdby OUT DBUSER.CREATED_BY%TYPE, o_date OUT DBUSER.CREATED_DATE%TYPE) IS BEGIN SELECT USERNAME , CREATED_BY, CREATED_DATE INTO o_username, o_createdby, o_date from DBUSER WHERE USER_ID = p_userid; END; / 2. Calls Stored Procedure via CallableStatementJDBC example to call a stored procedure via CallableStatement.File : JDBCCallableStatementOUTParameterExample.java package com.mkyong.jdbc; import java.sql.CallableStatement; import java.sql.Date; import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; public class JDBCCallableStatementOUTParameterExample { private static final String DB_DR