How do I execute a MS SQL Server stored procedure in java jsp returning table data

Question

I am having difficulty executing a MS SQL Server stored procedure from Java jsp   I wish to return a table set of data  the last line of the stored procedure is a regular select statement from a table    From this point  executing a stored procedure is a cinch in PHP      I took a look at these sites for help  www 2netave com www stackoverflow com  I didn t realize there s a function just for stored procedures  as I was using createStatement   instead   Now  please understand the stored procedure executes perfectly in SQL Server Management Studio and I have had no problems executing ad-hoc queries in jsp java with createStatement       I created a simple stored procedure that takes no arguments  just to narrow down the problem   CREATE PROCEDURE sp test AS BEGIN PRINT  HELLO WORLD  END   Here is the code in my jsp page   Class forName  net sourceforge jtds jdbc Driver    java sql Connection conn   java sql DriverManager getConnection  jdbc jtds sqlserver   MySQLServer 1433 test    user    pass    java sql CallableStatement cs   conn prepareCall   call sp test      java sql ResultSet ResultSet   cs execute      The browser is telling me that the page cannot be displayed because an interal server error has occurred  I know this means there is an issue with the code above   I tried this   java sql ResultSet ResultSet   cs executeQuery      And this   java sql CallableStatement cs   conn prepareCall   execute sp test       And this   java sql CallableStatement cs   conn prepareCall   exec sp test       And nothing worked  Once I can get this working  then I can run an actual stored procedure that returns table data from a select statement  But I can t even get this dummy stored procedure to work   What am I doing wrong here   Thank you   Update     Checked the server logs  IIS  and my HTTP proxy  fiddler  and it doesn t report anything  However  the IIS is using tomcat as the servlet engine for jsp pages   And tomcat log file reported the following   An error occurred at line  20 in the jsp file   test jsp Type mismatch  cannot convert from boolean to ResultSet 17   18      java sql CallableStatement cs   conn prepareCall   call sp test     19       20      java sql ResultSet ResultSet   cs execute    21   22      java sql ResultSet ResultSet   state executeQuery SQL    23     I tried changing the above to   cs execute      And the log files reported   - Servlet service   for servlet jsp threw exception java sql SQLException  The EXECUTE permission was denied on the object  sp test   database  test   schema  dbo     So  I have figured out I have to GRANT EXECUTE to the user  The other issue is returning table data from a stored procedure     If I have a procedure like this   CREATE PROCEDURE sp test2 AS BEGIN SELECT   FROM TABLE END   How do I manipulate the table data in jsp  Would ResultSet work or is that only for ad-hoc queries  as opposed to stored procedures  where one would use createStatement   to execute a query   Thank you   Update2     Solution   In order to manipulate table data  I had to use this   java sql ResultSet RS   cs executeQuery      It failed on execute   and it failed on naming the ResultSet object  ResultSet   It never complained about this in the past with createStatement    But for some reason  with stored procedures  it didn t like this naming convention   Thank you

User · Answer

Thank to Brian for the code  I was trying to connect to the sql server with  call spname       and I got errors  but when I change my code to exec sp    it works very well   I post my code in hope this helps others with problems like mine   ResultSet rs   null  PreparedStatement cs null  Connection conn getJNDIConnection     try       cs conn prepareStatement  exec sp name                      cs setEscapeProcessing true       cs setQueryTimeout 90        cs setString 1   valueA         cs setString 2   valueB         cs setString 3   0418           commented  because no need to register parameters out   I got results from the resultset         cs registerOutParameter 1  Types VARCHAR         cs registerOutParameter 2  Types VARCHAR        rs   cs executeQuery        ArrayList lt ObjectX gt  listaObjectX   new ArrayList lt ObjectX gt         while  rs next               ObjectX to   new ObjectX            to setFecha rs getString 1            to setRefId rs getString 2            to setRefNombre rs getString 3            to setUrl rs getString 4             listaObjectX add to              return listaObjectX         catch  SQLException se            System out println  Error al ejecutar SQL   se getMessage             se printStackTrace            throw new IllegalArgumentException  Error al ejecutar SQL      se getMessage            finally            try                rs close                cs close              con close               catch  SQLException ex                ex printStackTrace

User · Answer

Our server calls stored procs from Java like so - works on both SQL Server 2000  amp  2008   String SPsql    EXEC  lt sp name gt             for stored proc taking 2 parameters Connection con   SmartPoolFactory getConnection         java sql Connection PreparedStatement ps   con prepareStatement SPsql   ps setEscapeProcessing true   ps setQueryTimeout  lt timeout value gt    ps setString 1   lt param1 gt    ps setString 2   lt param2 gt    ResultSet rs   ps executeQuery

User · Answer

FWIW  sp test will not be returning anything but an integer  all SQL Server stored procs just return an integer  and no result sets on the wire  since no SELECT statements    To get the output of the PRINT statements  you normally use the InfoMessage event on the connection  not the command  in ADO NET

User · Answer

Frequently we deal with other fellow java programmers work which create these Stored Procedure  and we do not want to mess around with it  but there is possibility you get the result set where these exec sample return 0  almost Stored procedure call returning zero    check this sample    public void generateINOUT String USER  int DPTID        try             conUrl   JdbcUrls   dbServers    databaseName    dbSrcNames   instance MSSQLSERVER            con   DriverManager getConnection conUrl dbUserNames dbPasswords             stat   con createStatement            con setAutoCommit false            Statement st   con createStatement              st executeUpdate  DECLARE  RC int n                     DECLARE  pUserID nvarchar 50  n                     DECLARE  pDepartmentID int n                     DECLARE  pStartDateTime datetime n                     DECLARE  pEndDateTime datetime n                     EXECUTE  AccessManager   dbo   SP GenerateInOutDetailReportSimple   n                       USER                        DPTID                       STARTDATE                         ENDDATE                ResultSet rs   st getGeneratedKeys             while  rs next                   String userID   rs getString  UserID                  Timestamp timeIN   rs getTimestamp  timeIN                  Timestamp timeOUT   rs getTimestamp   timeOUT                  int totTime   rs getInt   totalTime                  int pivot   rs getInt   pivotvalue                   timeINS   sdz format timeIN                 userIN add timeINS                  timeOUTS   sdz format timeOUT                 userOUT add timeOUTS                  System out println  User     userID    IN     timeIN    OUT     timeOUT    Total Time     totTime     PivotValue     pivot                      con commit          catch  Exception e            e printStackTrace            System out println e           if  e getCause      null            e getCause   printStackTrace                 I came to this solutions after few days trial and error  googling and get confused    it execute below Stored Procedure    USE  AccessManager  GO         Object   StoredProcedure  dbo   SP GenerateInOutDetailReportSimple      Script Date  04 05 2013 15 54 11         SET ANSI NULLS OFF GO SET QUOTED IDENTIFIER ON GO   ALTER PROCEDURE  dbo   SP GenerateInOutDetailReportSimple     pUserID nvarchar 50    pDepartmentID int   pStartDateTime datetime   pEndDateTime datetime   AS   Declare  ErrorCode int Select  ErrorCode     Error  Declare  TransactionCountOnEntry int If  ErrorCode   0 Begin     Select  TransactionCountOnEntry     TranCount     BEGIN TRANSACTION End   If  ErrorCode   0 Begin             -- Create table variable instead of SQL temp table because report wont pick up the temp table     DECLARE  tempInOutDetailReport TABLE               UserID nvarchar 50           LogDate datetime             LogDay varchar 20            TimeIN datetime          TimeOUT datetime          TotalTime int          RemarkTimeIn nvarchar 100           RemarkTimeOut nvarchar 100           TerminalIPTimeIn varchar 50           TerminalIPTimeOut varchar 50           TerminalSNTimeIn nvarchar 50           TerminalSNTimeOut nvarchar 50           PivotValue int                       -- Declare variables for the while loop     Declare  LogUserID nvarchar 50      Declare  LogEventID nvarchar 50      Declare  LogTerminalSN nvarchar 50      Declare  LogTerminalIP nvarchar 50      Declare  LogRemark nvarchar 50      Declare  LogTimestamp datetime       Declare  LogDay nvarchar 20       -- Filter off userID  departmentID  StartDate and EndDate if specified  only process the remaining logs     -- Note  order by user then timestamp     Declare LogCursor Cursor For      Select distinct access event logs USERID  access event logs EVENTID           access event logs TERMINALSN  access event logs TERMINALIP          access event logs REMARKS  access event logs LOCALTIMESTAMP  Datename dw access event logs LOCALTIMESTAMP  AS WkDay     From access event logs         Left Join access user on access user User ID   access event logs USERID         Left Join access user dept on access user User ID   access user dept User ID     Where   Dept ID    pDepartmentID  OR   pDepartmentID IS NULL           And   access event logs USERID LIKE        pUserID        OR   pUserID IS NULL            And   access event logs LOCALTIMESTAMP  gt    pStartDateTime   OR   pStartDateTime IS NULL            And   access event logs LOCALTIMESTAMP  lt  DATEADD day  1   pEndDateTime    OR   pEndDateTime IS NULL            And  access event logs USERID     UNKNOWN USER   -- Ignore UNKNOWN USER     Order by access event logs USERID  access event logs LOCALTIMESTAMP      Open LogCursor      Fetch Next      From LogCursor     Into  LogUserID   LogEventID   LogTerminalSN   LogTerminalIP   LogRemark   LogTimestamp   LogDay      -- Temp storage for IN event details     Declare  InEventUserID nvarchar 50      Declare  InEventDay nvarchar 20      Declare  InEventTimestamp datetime     Declare  InEventRemark nvarchar 100      Declare  InEventTerminalIP nvarchar 50      Declare  InEventTerminalSN nvarchar 50       -- Temp storage for OUT event details     Declare  OutEventUserID nvarchar 50              Declare  OutEventTimestamp datetime     Declare  OutEventRemark nvarchar 100      Declare  OutEventTerminalIP nvarchar 50      Declare  OutEventTerminalSN nvarchar 50       Declare  CurrentUser varchar 50  -- used to indicate when we change user group     Declare  CurrentDay varchar 50  -- used to indicate when we change day     Declare  FirstEvent int -- indicate the first event we received          Declare  ReceiveInEvent int -- indicate we have received an IN event     Declare  PivotValue int -- everytime we change user or day - we reset it  reporting purpose   if same user  keep increment its value     Declare  CurrTrigger varchar 50  -- used to keep track of the event of the current event log trigger it is handling      Declare  CurrTotalHours int -- used to keep track of total hours of the day of the user      Declare  FirstInEvent datetime     Declare  FirstInRemark nvarchar 100      Declare  FirstInTerminalIP nvarchar 50      Declare  FirstInTerminalSN nvarchar 50      Declare  FirstRecord int -- indicate another day of same user      Set  PivotValue   0 -- initialised     Set  CurrentUser      -- initialised     Set  FirstEvent   1 -- initialised     Set  ReceiveInEvent   0 -- initialised       Set  CurrTrigger      -- Initialised         Set  CurrTotalHours   0 -- initialised     Set  FirstRecord   1 -- initialised     Set  CurrentDay      -- initialised      While   FETCH STATUS   0     Begin         -- use to track current log trigger         Set  CurrTrigger  LOWER  LogEventID           If   CurrentUser       And  CurrentUser     LogUserID  -- new batch of user         Begin             If  ReceiveInEvent   1  -- previous IN event is not cleared  no OUT is found              Begin                 -- Check day                 If   CurrentDay     InEventDay  -- change to another day                                         Set  PivotValue   0 -- Reset                                          Else -- same day                     Set  PivotValue    PivotValue   1 -- increment                 Set  CurrentDay    InEventDay -- update the day                  -- invalid row  only has IN event                  Insert into  tempInOutDetailReport  UserID  LogDay  TimeIN  RemarkTimeIn  TerminalIPTimeIn                       TerminalSNTimeIn  PivotValue  LogDate                   values   InEventUserID   InEventDay   InEventTimestamp   InEventRemark   InEventTerminalIP                        InEventTerminalSN   PivotValue  DATEADD HOUR  0  DATEDIFF DAY  0   InEventTimestamp                                                                         End                               Set  FirstEvent   1 -- Reset flag  we are having a new user group              Set  ReceiveInEvent   0 -- Reset             Set  PivotValue   0 -- Reset             --Set  CurrentDay      -- Reset         End          If LOWER  LogEventID     in  -- IN event         Begin                        If  ReceiveInEvent   1  -- previous IN event is not cleared  no OUT is found              Begin                 -- Check day                 If   CurrentDay     InEventDay  -- change to another day                 Begin                     Set  PivotValue   0 -- Reset                      --Insert into  tempInOutDetailReport  UserID  LogDay  TimeIN  TimeOUT  TotalTime  RemarkTimeIn                      --  RemarkTimeOut  TerminalIPTimeIn  TerminalIPTimeOut  TerminalSNTimeIn  TerminalSNTimeOut  PivotValue                      --  LogDate                      --values   LogUserID   CurrentDay   FirstInEvent   LogTimestamp    CurrTotalHours                      --   FirstInRemark   LogRemark   FirstInTerminalIP   LogTerminalIP   FirstInTerminalSN   LogTerminalSN   PivotValue                      --  DATEADD HOUR  0  DATEDIFF DAY  0   InEventTimestamp                      End                 Else                     Set  PivotValue    PivotValue   1 -- increment                 Set  CurrentDay    InEventDay -- update the day                   -- invalid row  only has IN event                  Insert into  tempInOutDetailReport  UserID  LogDay  TimeIN  RemarkTimeIn  TerminalIPTimeIn                       TerminalSNTimeIn  PivotValue  LogDate                   values   InEventUserID   InEventDay   InEventTimestamp   InEventRemark   InEventTerminalIP                        InEventTerminalSN   PivotValue  DATEADD HOUR  0  DATEDIFF DAY  0   InEventTimestamp                                     End                       If   CurrentDay     LogDay And  CurrentDay        Or   CurrentUser     LogUserID And  CurrentUser                      Begin                      Insert into  tempInOutDetailReport  UserID  LogDay  TimeIN  TimeOUT  TotalTime  RemarkTimeIn                          RemarkTimeOut  TerminalIPTimeIn  TerminalIPTimeOut  TerminalSNTimeIn  TerminalSNTimeOut  PivotValue                          LogDate                      values   CurrentUser   CurrentDay   FirstInEvent   OutEventTimestamp   CurrTotalHours                           FirstInRemark   OutEventRemark   FirstInTerminalIP   OutEventTerminalIP   FirstInTerminalSN   LogTerminalSN   PivotValue                          DATEADD HOUR  0  DATEDIFF DAY  0   InEventTimestamp                           Set  FirstRecord   1             End             -- Save it             Set  InEventUserID    LogUserID                                      Set  InEventDay    LogDay             Set  InEventTimestamp    LogTimeStamp             Set  InEventRemark    LogRemark             Set  InEventTerminalIP    LogTerminalIP             Set  InEventTerminalSN    LogTerminalSN              If   FirstRecord   1  -- save for first in event record of the day             Begin                 Set  FirstInEvent    LogTimestamp                 Set  FirstInRemark    LogRemark                 Set  FirstInTerminalIP    LogTerminalIP                 Set  FirstInTerminalSN    LogTerminalSN                 Set  CurrTotalHours   0 --initialise total hours for another day             End              Set  FirstRecord   0 -- no more first record of the day             Set  ReceiveInEvent   1 -- indicate we have received an  IN  event             Set  FirstEvent   0  -- no more  first  event         End         Else If LOWER  LogEventID     out  -- OUT event         Begin             If  FirstEvent   1 -- the first OUT record when change users              Begin                 -- Check day                 If   CurrentDay     LogDay  -- change to another day                     Set  PivotValue   0 -- Reset                 Else                     Set  PivotValue    PivotValue   1 -- increment                 Set  CurrentDay    LogDay -- update the day                  -- Only an OUT event  no IN event  - invalid record but we show it anyway                 Insert into  tempInOutDetailReport  UserID  LogDay  TimeOUT  RemarkTimeOut  TerminalIPTimeOut  TerminalSNTimeOut                      PivotValue  LogDate                   values   LogUserID   LogDay   LogTimestamp   LogRemark   LogTerminalIP   LogTerminalSN   PivotValue                      DATEADD HOUR  0  DATEDIFF DAY  0   LogTimestamp                                                                   Set  FirstEvent   0 -- not  first  anymore             End             Else -- Not first event             Begin                         If  ReceiveInEvent   1 -- if there are IN event previously                 Begin                                -- Check day                     If   CurrentDay     InEventDay  -- change to another day                                                 Set  PivotValue   0 -- Reset                                             Else                         Set  PivotValue    PivotValue   1 -- increment                     Set  CurrentDay    InEventDay -- update the day                          Set  CurrTotalHours    CurrTotalHours    DATEDIFF second  InEventTimestamp   LogTimeStamp  -- update total time                                   Set  OutEventRemark    LogRemark                     Set  OutEventTerminalIP    LogTerminalIP                     Set  OutEventTerminalSN    LogTerminalSN                     Set  OutEventTimestamp    LogTimestamp                               -- valid row                     --Insert into  tempInOutDetailReport  UserID  LogDay  TimeIN  TimeOUT  TotalTime  RemarkTimeIn                      --  RemarkTimeOut  TerminalIPTimeIn  TerminalIPTimeOut  TerminalSNTimeIn  TerminalSNTimeOut  PivotValue                      --  LogDate                      --values   LogUserID   InEventDay   InEventTimestamp   LogTimestamp   Datediff second   InEventTimestamp   LogTimeStamp                       --   InEventRemark   LogRemark   InEventTerminalIP   LogTerminalIP   InEventTerminalSN   LogTerminalSN   PivotValue                      --  DATEADD HOUR  0  DATEDIFF DAY  0   InEventTimestamp                                               Set  ReceiveInEvent   0 -- Reset                 End                 Else -- no IN event previously                 Begin                     -- Check day                     If   CurrentDay     LogDay  -- change to another day                         Set  PivotValue   0 -- Reset                     Else                         Set  PivotValue    PivotValue   1 -- increment                     Set  CurrentDay    LogDay -- update the day                                               -- invalid row  only has OUT event                      Insert into  tempInOutDetailReport  UserID  LogDay  TimeOUT  RemarkTimeOut  TerminalIPTimeOut  TerminalSNTimeOut                          PivotValue  LogDate                       values   LogUserID   LogDay   LogTimestamp   LogRemark   LogTerminalIP   LogTerminalSN   PivotValue                          DATEADD HOUR  0  DATEDIFF DAY  0   LogTimestamp                                  End                          End                  End          Set  CurrentUser    LogUserID -- update user                  Fetch Next          From LogCursor         Into  LogUserID   LogEventID   LogTerminalSN   LogTerminalIP   LogRemark   LogTimestamp   LogDay     End       -- Need to handle the last log if its IN log as it will not be processed by the while loop     if  CurrTrigger  in      Begin      -- Check day             If   CurrentDay     InEventDay  -- change to another day                 Set  PivotValue   0 -- Reset             Else -- same day                 Set  PivotValue    PivotValue   1 -- increment             Set  CurrentDay    InEventDay -- update the day              -- invalid row  only has IN event              Insert into  tempInOutDetailReport  UserID  LogDay  TimeIN  RemarkTimeIn  TerminalIPTimeIn                   TerminalSNTimeIn  PivotValue  LogDate               values   InEventUserID   InEventDay   InEventTimestamp   InEventRemark   InEventTerminalIP                        InEventTerminalSN   PivotValue  DATEADD HOUR  0  DATEDIFF DAY  0   InEventTimestamp             End     else if  CurrTrigger    out      Begin         Insert into  tempInOutDetailReport  UserID  LogDay  TimeIN  TimeOUT  TotalTime  RemarkTimeIn          RemarkTimeOut  TerminalIPTimeIn  TerminalIPTimeOut  TerminalSNTimeIn  TerminalSNTimeOut  PivotValue          LogDate          values   LogUserID   CurrentDay   FirstInEvent   LogTimestamp    CurrTotalHours           FirstInRemark   LogRemark   FirstInTerminalIP   LogTerminalIP   FirstInTerminalSN   LogTerminalSN   PivotValue          DATEADD HOUR  0  DATEDIFF DAY  0   InEventTimestamp          End      Close LogCursor     Deallocate LogCursor      Select        From  tempInOutDetailReport tempTable         Left Join access user on access user User ID   tempTable UserID     Order By tempTable UserID  LogDate   End    If   TranCount  gt   TransactionCountOnEntry Begin      If  ErrorCode   0             COMMIT TRANSACTION      Else             ROLLBACK TRANSACTION End  return  ErrorCode   you will get the  java SQL Code  by right click on stored procedure in your database  something like this    DECLARE  RC int DECLARE  pUserID nvarchar 50  DECLARE  pDepartmentID int DECLARE  pStartDateTime datetime DECLARE  pEndDateTime datetime  -- TODO  Set parameter values here   EXECUTE  RC    AccessManager   dbo   SP GenerateInOutDetailReportSimple    pUserID  pDepartmentID  pStartDateTime  pEndDateTime GO   check the query String I ve done  that is your homework    so sorry answering this long  this is my first answer since I register few weeks ago to get answer

[sql] How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?

Examples related to sql

Examples related to sql-server

Examples related to jsp

Examples related to stored-procedures

Examples related to jdbc