how to get value from an OpenEdge SQL/Java stored procedure

Posted by ifall on 25-Sep-2013 15:41

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 .

All Replies

Posted by Brian K. Maher on 14-Nov-2013 11:44

Are you seeing any errors?  If so, please post them.

This thread is closed