how to get value from an OpenEdge SQL/Java stored procedure using select statement?

[progresscommunities@progr]

this is my procedure

DROP PROCEDURE GetDayName |



CREATE PROCEDURE GetDayName (
IN DateValue DATE,
IN Shorte BIT,
OUT ReturnVal CHAR(12)
)
IMPORT
import java.util.*;
import java.sql.*;
BEGIN
String NameDay = new String();

if (Shorte.equals(1)) {
SQLCursor daycursor = new SQLCursor ("SELECT SUBSTRING(DAYNAME(DateVal), 1, 3) FROM PUB.WebTime WHERE DateVal = ?");
daycursor.setParam (1, DateValue);

daycursor.open ();
daycursor.fetch();

while (daycursor.found()) {
NameDay = (String) daycursor.getValue(1, CHARACTER);
}
}
else{
SQLCursor daycursor = new SQLCursor ("SELECT DAYNAME(DateVal) FROM PUB.WebTime WHERE DateVal = ?");
daycursor.setParam (1, DateValue);

daycursor.open ();
daycursor.fetch();

while (daycursor.found()) {
NameDay = (String) daycursor.getValue(1, CHARACTER);
}
}

ReturnVal = NameDay;
END

***************************

In my .NET web application i do a select to get the time sheet of a user:

public static DataSet GetTimeSheet(String UsrId, DateTime StartDate, DateTime EndDate)
{
DataSet oDs = null;



try
{
oDs = SqlQuery(string.Format("SELECT A.TimeId, CONVERT('VARCHAR', A.DateVal, 106) AS TransDate, DAYOFMONTH(A.DateVal) AS DayDate, dbo.GetDayName(A.DateVal, 1) AS NameDay, D.ProjectName, C.TaskName, A.JobRef, A.Comments, B.StatusText, A.Houres FROM PUB.WebTime AS A (NOLOCK) INNER JOIN PUB.WebStatus AS B ON B.StatusId = A.StatusId INNER JOIN PUB.WebTasks AS C ON C.TaskId = A.TaskId INNER JOIN PUB.WebProjects AS D ON D.ProjectId = A.ProjectId WHERE (A.DateVal >= '{1}') AND (A.DateVal < '{2}') AND (A.IdUser = '{0}') ORDER BY A.DateVal, D.ProjectName, C.TaskName, B.StatusText",
UsrId, StartDate.ToLongDateString(), (EndDate.AddDays(1)).ToLongDateString()));
}



catch (Exception ex)
{
Log.LogMsg(ex.Message);
}



return oDs;
}

Please can someone help me i'm a junior deeloper .

Continue reading...