In response to your submission, and thanks, by the way, here is a snip of my code and the s.p. :
<%!
public String getLCN()
{
String sDriverProtocol = "jdbc:odbc:xxxx";
String sDBUserID = "xxxx";
String sDBPassword = "xxxx";
Connection con;
CallableStatement stmt = null;
ResultSet rs = null;
int ibalance = 0;
ibalance = Integer.parseInt(balance);
try
{
new sun.jdbc.odbc.JdbcOdbcDriver();
con = DriverManager.getConnection(sDriverProtocol, sDBUserID, sDBPassword);
stmt = con.prepareCall("{call usp_LeaveIndocVal(?,?,?,?,?,?,?,?,?)}");
stmt.setString(1, ssn);
stmt.setString(2, sDate);
stmt.setString(3, sTime);
stmt.setString(4, eDate);
stmt.setString(5, eTime);
stmt.setString(6, type);
stmt.setString(7, user);
stmt.setInt(8, ibalance);
stmt.registerOutParameter(9, java.sql.Types.VARCHAR);
stmt.execute();
rtrvr = stmt.getString(9);
rs.close();
stmt.close();
con.close();
}
catch (Exception err)
{
System.err.println("getLCN method error: " + err.toString());
}
return rtrvr;
}
%>
And here is the sp (it's pretty big):
CREATE PROCEDURE dbo.usp_LeaveIndocVal
@sSsnchar(9),
@dLvStartdatetime,
@sStartTimevarchar(4),
@dLvEnddatetime,
@sEndTimevarchar(4),
@sLvTypevarchar(15),
@sUserIdvarchar(25),
@iLvBalanceint,
@sLcnvarchar(8)OUTPUT
AS
DECLARE @sEventIdvarchar(15),
@iEventIdint,
@iCourseIdint,
@dEvStartdatetime,
@dEvEnddatetime,
@dCmdIndocdatetime,
@iNewEventIdint,
@dNewStartDatedatetime
DECLARE crList CURSOR FOR
SELECT te.trngeventid, te.courseid, te.startdate, te.enddate, id.cmdindoc
FROM etjroster tr
LEFT JOIN IndocDates id ON id.ssn = tr.ssn
LEFT JOIN etjtrngevent te ON te.trngeventid = tr.trngeventid
WHERE tr.ssn = @sSsn
AND (te.courseid = 1 OR te.courseid = 2)
AND ((@dLvStart >= startdate AND @dLvStart <= enddate)
OR (@dLvEnd >= startdate AND @dLvStart <= enddate))
OPEN crList
/* Get the first row from the cursor */
FETCH NEXT FROM crList INTO @iEventId, @iCourseId, @dEvStart, @dEvEnd, @dCmdIndoc
/* Close the cursor & Deallocate memory */
CLOSE crList
DEALLOCATE crList
IF @iEventId IS NULL
BEGIN
/* If we are here, then there is no conflict with Indoc.
Get the leave control number, insert record in leave log
and update muster control table
*/
EXEC usp_LeaveCntrlNum @sLcn OUTPUT
EXEC usp_LeaveLogInsert @sLcn, @dLvStart,
@sStartTime, @dLvEnd, @sEndTime,
@sLvType, @sSsn
EXEC usp_LeaveMusterUpd@sLvType, @sSsn, @dLvStart,
@dLvEnd, @sLcn, @iLvBalance
END
ELSE
BEGIN
/* If we are here, then there is a conflict with Indoc.
Get the scheduled Indoc events and find one that is
less than 13 months after the indiv inital command indoc.
Get the leave control number, insert record in leave log
and muster control
*/
DECLARE crNewList CURSOR FOR
SELECT te.trngeventid, te.StartDate
FROM etjtrngevent te
WHERE te.courseid = @iCourseId
AND (te.startdate > @dEvStart AND te.startdate > @dLvEnd)
AND seatsUsed < seatsAvail
OPEN crNewList
FETCH NEXT FROM crNewList INTO @iNewEventId, @dNewStartDate
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @iCmdMonint,
@iEventMonint
SELECT @iCmdMon = DATEPART(mm,@dCmdIndoc)
SELECT @iEventMon = DATEPART(mm, @dNewStartDate)
IF @iCmdMon - @iEventMon > 0
BEGIN
EXEC usp_LeaveCntrlNum @sLcn OUTPUT
EXEC usp_LeaveLogInsert @sLcn, @dLvStart,
@sStartTime, @dLvEnd, @sEndTime,
@sLvType, @sSsn
EXEC usp_LeaveMusterUpd@sLvType, @sSsn, @dLvStart,
@dLvEnd, @sLcn, @iLvBalance
BREAK
END
ELSE
BEGIN
FETCH NEXT FROM crNewList INTO @iNewEventId, @dNewStartDate
END
END
CLOSE crNewList
DEALLOCATE crNewList
/* If the leave control number equals 0, then there was a
conflict. Emergency leaves take precedent, so get the
leave control number, insert record in leave log
and muster control or notify the Indoc Coordinator
*/
IF @sLcn = '0'
BEGIN
IF @sLvType = 'Emergency'
BEGIN
EXEC usp_LeaveCntrlNum @sLcn OUTPUT
EXEC usp_LeaveLogInsert @sLcn, @dLvStart,
@sStartTime, @dLvEnd, @sEndTime,
@sLvType, @sSsn
EXEC usp_LeaveMusterUpd@sLvType, @sSsn, @dLvStart,
@dLvEnd, @sLcn, @iLvBalance
END
/*
ELSE
EXEC usp_IndocCoordinatorEMail @sSsn
*/
END
END
