I have the following Stored Procedure:
ALTER PROCEDURE Uspinformation3 (@TypeNr VARCHAR(50),
@Process VARCHAR(50),
@StartDate VARCHAR(50),
@EndDate VARCHAR(50))
AS
BEGIN try
SET dateformat dmy;
SELECT Count(rownr)
FROM diff_ct
WHERE type_number = @TypeNr
AND process = @Process
AND full_time_stamp BETWEEN @StartDate AND @EndDate
END try
If I execute the Procedure from SQL Server, it gives me the wanted answer
EXEC uspInformation3 1137328582, 427,
{ts '2015-05-24 12:00:00'}, {ts ' 2015-05-24 16:00:00 '}
But, when I try to do it from Java, the Result Set is always empty. Where am I doing a mistake? I guess that it has something to do with the data types that I am using?
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
Connection conn = DriverManager.getConnection(DB_Connection);
String partno = pn.getText();
String processname = procname.getText();
// String StartTime = DataStart.getText();
String StartTime = "{ts " +"'"+ DataStart.getText() +"'}";
String EndTime = "{ts " +"'"+ DataFinal.getText()+"'}";
System.out.println(StartTime);
Statement state = conn.createStatement();
CallableStatement cs = null;
cs = conn.prepareCall("{call uspInformation3(?,?,?,?)}");
cs.setString(1, partno);
cs.setString(2, processname);
cs.setString(3, StartTime);
cs.setString(4, EndTime);
cs.executeQuery();
ResultSet rs = cs.getResultSet();
System.out.println(rs);
if(rs.next()) {
String totalct = rs.getString(1);
System.out.println(totalct);
totalctno.setText(totalct);
}
Did you try send the parameter as string? Or change the parameter type to datetime
Also there was extra spaces there on second date.
EXEC uspInformation3 1137328582, 427,
'2015-05-24 12:00:00', '2015-05-24 16:00:00';