Thread: com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'getMachineOnOffCou

Forum : Spring Jdbc, Spring ORM and Spring Transaction.   10 Days ago
User Image
Pritam91

Newbie

Joined: Nov 10, 2017

Points: 100

Threads: 3

Posts: 0

com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'getMachineOnOffCou

| Quote Date : Jan 11, 2018    Views:40    

















com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'getMachineOnOffCount'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:356)
at com.org.utills.CallableStatementTestProcedureOUT.main(CallableStatementTestProcedureOUT.java:40)



java class
--------------
---------------
package com.org.utills;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

public class CallableStatementTestProcedureOUT {

public static Connection getMyConnection() throws SQLException, ClassNotFoundException {
return OracleConnUtils.getOracleConnection();
}

public static void main(String... arg) {

Connection con = null;
CallableStatement callableStmt = null;
try {

con = ConnectionUtils.getMyConnection();
//String SPsql = "EXEC getMachineOnOffCount ?,?,?,?,?,?,?";
//callableStmt = con.prepareCall(SPsql);
callableStmt = con.prepareCall("{call getMachineOnOffCount(?,?,?,?,?,?,?)}");
// 1) set methods are used for setting IN parameter values of Stored procedure
callableStmt.setString(1, "Kliklok 60 Cartoner");

// 1) OUT parameters must be registered in java before executing the stored
// procedure,
callableStmt.registerOutParameter(2, java.sql.Types.NUMERIC);
callableStmt.registerOutParameter(3, java.sql.Types.NUMERIC);
callableStmt.registerOutParameter(4, java.sql.Types.NUMERIC);
callableStmt.registerOutParameter(5, java.sql.Types.NUMERIC);
callableStmt.registerOutParameter(6, java.sql.Types.NUMERIC);
callableStmt.registerOutParameter(7, java.sql.Types.NUMERIC);

// 2) Execute database stored procedure,
callableStmt.executeUpdate();

// 3) Then retrieve values of OUT parameters using using get methods.
System.out.println("machineOnSh1 = " + callableStmt.getInt(2));
System.out.println("machineOffSh1 = " + callableStmt.getInt(3));
System.out.println("machineOnSh2 = " + callableStmt.getInt(4));
System.out.println("machineOffSh2 = " + callableStmt.getInt(5));
System.out.println("machineOnSh3 = " + callableStmt.getInt(6));
System.out.println("machineOffSh3 = " + callableStmt.getInt(7));

System.out.println("Stored procedure executed successfully, "
+ "data has been fetched from [Analogic].[dbo].[Cartoner_parameters] table");

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (callableStmt != null)
callableStmt.close(); // close CallableStatement
if (con != null)
con.close(); // close connection
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}


store procedure
---------------------
----------------------

create procedure getMachineOnOffCount @machineName VARCHAR(30), @machineOnSh1 int Output, @machineOffSh1 int Output, @machineOnSh2 int Output, @machineOffSh2 int Output, @machineOnSh3 int Output,  @machineOffSh3 int Output

As

BEGIN

DECLARE @machineTotalOnOffSh1 int=0;

DECLARE @machineOntSh1 int=0;

DECLARE @machineOfftSh1 int=0;

 

DECLARE @machineTotalOnOffSh2 int=0;

DECLARE @machineOntSh2 int=0;

DECLARE @machineOfftSh2 int=0;

 

DECLARE @machineTotalOnOffSh3 int=0;

DECLARE @machineOntSh3 int=0;

DECLARE @machineOfftSh3 int=0;

 

DECLARE @ten int=10;

DECLARE @sixty int=60;

 

select @machineOntSh1=count(a.logging_time) from [Analogic].[dbo].[Cartoner_parameters] AS a INNER JOIN [Analogic].[dbo].[Cartoner_parameters] AS b ON a.production_count < b.production_count

WHERE (DATEDIFF(ss, a.logging_time, b.logging_time)=10) and CAST(a.logging_time as time) between '14:00:02' and '14:59:52' and a.machine_name=@machineName;

 

select @machineOfftSh1=count(a.logging_time) from [Analogic].[dbo].[Cartoner_parameters] AS a INNER JOIN [Analogic].[dbo].[Cartoner_parameters] AS b ON a.production_count = b.production_count

WHERE (DATEDIFF(ss, a.logging_time, b.logging_time)=10) and CAST(a.logging_time as time) between '14:00:02' and '14:59:52' and a.machine_name=@machineName;

SET @machineTotalOnOffSh1 =((@machineOntSh1 + @machineOfftSh1)*@ten)/@sixty;

SET @machineOnSh1=(@machineOntSh1*@ten)/@sixty;

SET @machineOffSh1=(@machineOfftSh1*@ten)/@sixty;

 

 

select @machineOntSh2=count(a.logging_time) from [Analogic].[dbo].[Cartoner_parameters] AS a INNER JOIN [Analogic].[dbo].[Cartoner_parameters] AS b ON a.production_count < b.production_count

WHERE (DATEDIFF(ss, a.logging_time, b.logging_time)=10) and CAST(a.logging_time as time) between '13:05:22' and '13:59:52' and a.machine_name=@machineName;

 

select @machineOfftSh2=count(a.logging_time) from [Analogic].[dbo].[Cartoner_parameters] AS a INNER JOIN [Analogic].[dbo].[Cartoner_parameters] AS b ON a.production_count = b.production_count

WHERE (DATEDIFF(ss, a.logging_time, b.logging_time)=10) and CAST(a.logging_time as time) between '13:05:22' and '13:59:52' and a.machine_name=@machineName;

SET @machineTotalOnOffSh2 =((@machineOntSh2 + @machineOfftSh2)*@ten)/@sixty;

SET @machineOnSh2=(@machineOntSh2*@ten)/@sixty;

SET @machineOffSh2=(@machineOfftSh2*@ten)/@sixty;

 

select @machineOntSh3=count(a.logging_time) from [Analogic].[dbo].[Cartoner_parameters] AS a INNER JOIN [Analogic].[dbo].[Cartoner_parameters] AS b ON a.production_count < b.production_count

WHERE (DATEDIFF(ss, a.logging_time, b.logging_time)=10) and CAST(a.logging_time as time) between '15:00:02' and '15:51:12' and a.machine_name=@machineName;

 

select @machineOfftSh3=count(a.logging_time) from [Analogic].[dbo].[Cartoner_parameters] AS a INNER JOIN [Analogic].[dbo].[Cartoner_parameters] AS b ON a.production_count = b.production_count

WHERE (DATEDIFF(ss, a.logging_time, b.logging_time)=10) and CAST(a.logging_time as time) between '15:00:02' and '15:51:12' and a.machine_name=@machineName;

SET @machineTotalOnOffSh3 =((@machineOntSh3 + @machineOfftSh3)*@ten)/@sixty;

SET @machineOnSh3=(@machineOntSh3*@ten)/@sixty;

SET @machineOffSh3=(@machineOfftSh3*@ten)/@sixty;

END;

























tags: jdbc
Load More

No More Comments