Мне трудно выполнить хранимую процедуру MS SQL Server из Java/jsp. Я хочу вернуть таблицу данных; последняя строка хранимой процедуры - это регулярный оператор select из таблицы.
(С этого момента выполнение хранимой процедуры является cinch в PHP.)
Я просмотрел эти сайты за помощью:
www.2netave.com
qaru.site/info/234151/...
Я не знал, что есть функция только для хранимых процедур, поскольку вместо этого я использовал createStatement().
Теперь, пожалуйста, поймите, что хранимая процедура отлично работает в SQL Server Management Studio, и у меня не было проблем с выполнением специальных запросов в jsp/java с помощью createStatement().
Я создал простую хранимую процедуру, которая не принимает никаких аргументов, просто чтобы сузить проблему:
CREATE PROCEDURE sp_test AS
BEGIN
PRINT 'HELLO WORLD'
END
Вот код на моей странице jsp:
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();
Браузер сообщает мне, что страница не может быть отображена, поскольку произошла ошибка межсетевого сервера. Я знаю, это означает, что есть проблема с приведенным выше кодом.
Я пробовал это:
java.sql.ResultSet ResultSet = cs.executeQuery();
И это:
java.sql.CallableStatement cs = conn.prepareCall("{execute sp_test}");
И это:
java.sql.CallableStatement cs = conn.prepareCall("{exec sp_test}");
И ничего не получилось. Как только я получу эту работу, я могу запустить фактическую хранимую процедуру, которая возвращает данные таблицы из оператора select. Но я даже не могу заставить эту фиктивную хранимую процедуру работать.
Что я здесь делаю неправильно?
Спасибо.
Обновление:
Проверено журналы сервера (IIS) и мой HTTP-прокси, скрипт, и он ничего не сообщает. Тем не менее, IIS использует tomcat в качестве механизма сервлета для jsp-страниц. И файл журнала tomcat сообщил следующее:
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:
Я попробовал изменить выше:
cs.execute();
И сообщается о файлах журналов:
- Servlet.service() for servlet jsp threw exception
java.sql.SQLException: The EXECUTE permission was denied on the object 'sp_test', database 'test', schema 'dbo'.
Итак, я понял, что я должен GRANT EXECUTE для пользователя. Другая проблема - возврат табличных данных из хранимой процедуры.
Если у меня есть такая процедура:
CREATE PROCEDURE sp_test2 AS
BEGIN
SELECT * FROM TABLE
END
Как мне манипулировать данными таблицы в jsp? Будет ли работать ResultSet или только для специальных запросов, в отличие от хранимых процедур, где можно использовать createStatement() для выполнения запроса?
Спасибо.
Обновление2:
Решение:
Чтобы управлять табличными данными, мне пришлось использовать это:
java.sql.ResultSet RS = cs.executeQuery();
Не удалось выполнить execute() и не удалось присвоить имя объекту ResultSet "ResultSet". В прошлом он никогда не жаловался на createStatement(). Но по какой-то причине, с хранимыми процедурами, ему не понравилось это соглашение об именах.
Спасибо.
Наш сервер вызывает хранимые procs из Java, как это делается - работает как на SQL Server 2000, так и на 2008:
String SPsql = "EXEC <sp_name> ?,?"; // for stored proc taking 2 parameters
Connection con = SmartPoolFactory.getConnection(); // java.sql.Connection
PreparedStatement ps = con.prepareStatement(SPsql);
ps.setEscapeProcessing(true);
ps.setQueryTimeout(<timeout value>);
ps.setString(1, <param1>);
ps.setString(2, <param2>);
ResultSet rs = ps.executeQuery();
Спасибо Брайану за код. Я пытался подключиться к серверу sql с {call spname(?,?)}, и я получил ошибки, но когда я меняю свой код на exec sp..., он работает очень хорошо.
Я отправляю свой код в надежде, что это поможет другим с такими проблемами, как мой:
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<ObjectX> listaObjectX = new ArrayList<ObjectX>();
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();
}
}
Часто мы имеем дело с другими работниками java-программистов, которые создают эту Хранимую процедуру. и мы не хотим обходиться с ним. но есть вероятность, что вы получите результирующий набор, где эти exec sample return 0 (почти сохраненный вызов процедуры возвращает ноль).
проверьте этот пример:
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();}
}
}
Я пришел к этим решениям после нескольких дней проб и ошибок, по поиску и запутанию;) он выполняется ниже хранимой процедуры:
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 >= @pStartDateTime ) OR (@pStartDateTime IS NULL))
And ((access_event_logs.LOCALTIMESTAMP < 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 > @TransactionCountOnEntry
Begin
If @ErrorCode = 0
COMMIT TRANSACTION
Else
ROLLBACK TRANSACTION
End
return @ErrorCode
вы получите "код Java Java", щелкнув правой кнопкой мыши по хранимой процедуре в своей базе данных. что-то вроде этого:
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
проверить запрос String, который я сделал, это ваша домашняя работа;) поэтому жаль, ответив на это долго, это мой первый ответ, так как я регистрирую несколько недель назад, чтобы получить ответ.
FWIW, sp_test не будет возвращать ничего, кроме целого (все хранимые в SQL Server procs просто возвращают целое число) и никаких наборов результатов на проводнике (так как без операторов SELECT). Чтобы получить выходные данные операторов PRINT, вы обычно используете событие InfoMessage в соединении (а не команде) в ADO.NET.