solarisinfosys
asked on
Exception while executing MS SQL function. The statement did not return a result set.
MSSQL procedure
USE [KingsBayY]
GO
/****** Object: UserDefinedFunction [dbo].[test_fn_transaction
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE test_fn_transaction_insert
@enddt varchar(25)
AS
SET NOCOUNT ON
INSERT INTO [TransactionLog] ( TimeStamp, CashierID, Total,RecurringStartDate,
RecurringStopDate, CustomerID, ReceiptNumber, Voided, Attendee, ItemLookupCode)
SELECT CURRENT_TIMESTAMP AS [TimeStamp], tr.CashierID, @amount, @stdt,@enddt,c.ID,tr.Recei
tr.Voided,it.ItemLookupCod
from "Transaction" tr, TransactionEntry trent, Item it, Customer c
where trent.TransactionID = tr.ID
and trent.ItemID = it.ID
and c.ID=tr.CustomerID
and tr.ReceiptNumber=@receipt
GO
And i am using it in Java as below
Java Dao method
public String saveTr(String a, String b, String c, String d) {
try
{
SqlServerConn conn = new SqlServerConn();
connect=conn.getConnection
String SQL = String.format("Exec test_fn_transaction_insert
pstmt = connect.prepareStatement(S
pstmt.setString(1, a);
pstmt.setString(2, b);
pstmt.setString(3, c);
pstmt.setString(4, d);
rs = pstmt.executeQuery();
}
catch (Exception e2)
{
e2.printStackTrace();
return "fail";
}
//ArrayList<Transactions> list=getrowFromResultSet2(
return "success";
}
I get the below exception. Can anyone tell me whats going wrong.
exception
com.microsoft.sqlserver.jd
i read the following online about MSSQL which causes a lot of confusion.
a) functions cannot be used for Inserting values into a table other than a temp table.
b) procedures cannot return a table value.
so how can one do the below three
a)send 3 parameters to a function/proc
b) use the input parameters as inputs for a select statement,
c) insert the returned values into a table,
d)return the inserted row / success/fail status.
USE [KingsBayY]
GO
/****** Object: UserDefinedFunction [dbo].[test_fn_transaction
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE test_fn_transaction_insert
@enddt varchar(25)
AS
SET NOCOUNT ON
INSERT INTO [TransactionLog] ( TimeStamp, CashierID, Total,RecurringStartDate,
RecurringStopDate, CustomerID, ReceiptNumber, Voided, Attendee, ItemLookupCode)
SELECT CURRENT_TIMESTAMP AS [TimeStamp], tr.CashierID, @amount, @stdt,@enddt,c.ID,tr.Recei
tr.Voided,it.ItemLookupCod
from "Transaction" tr, TransactionEntry trent, Item it, Customer c
where trent.TransactionID = tr.ID
and trent.ItemID = it.ID
and c.ID=tr.CustomerID
and tr.ReceiptNumber=@receipt
GO
And i am using it in Java as below
Java Dao method
public String saveTr(String a, String b, String c, String d) {
try
{
SqlServerConn conn = new SqlServerConn();
connect=conn.getConnection
String SQL = String.format("Exec test_fn_transaction_insert
pstmt = connect.prepareStatement(S
pstmt.setString(1, a);
pstmt.setString(2, b);
pstmt.setString(3, c);
pstmt.setString(4, d);
rs = pstmt.executeQuery();
}
catch (Exception e2)
{
e2.printStackTrace();
return "fail";
}
//ArrayList<Transactions> list=getrowFromResultSet2(
return "success";
}
I get the below exception. Can anyone tell me whats going wrong.
exception
com.microsoft.sqlserver.jd
i read the following online about MSSQL which causes a lot of confusion.
a) functions cannot be used for Inserting values into a table other than a temp table.
b) procedures cannot return a table value.
so how can one do the below three
a)send 3 parameters to a function/proc
b) use the input parameters as inputs for a select statement,
c) insert the returned values into a table,
d)return the inserted row / success/fail status.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Because you have mentioned:
the code is expecting a result set (i.e. some SELECT statement inside the procedure actually returning data to the caller). Since it does not exist, the "rs" cannot be populated - causing the error.
I'm not a java guy, so I'm not sure if it will work or not, but try replacing
Alternatively (this one will work), you can place a SELECT 1 or a SELECT 0 after the INSERT to tell the caller that the procedure executed successfully.
(as an FYI, the rs = pstmt.executeQuery(); should be a valid syntax for a "get" SP, i.e. one that fetches data from the database)
rs = pstmt.executeQuery();the code is expecting a result set (i.e. some SELECT statement inside the procedure actually returning data to the caller). Since it does not exist, the "rs" cannot be populated - causing the error.
I'm not a java guy, so I'm not sure if it will work or not, but try replacing
rs = pstmt.executeQuery();pstmt.executeQuery();Alternatively (this one will work), you can place a SELECT 1 or a SELECT 0 after the INSERT to tell the caller that the procedure executed successfully.
(as an FYI, the rs = pstmt.executeQuery(); should be a valid syntax for a "get" SP, i.e. one that fetches data from the database)
to avoid keyword as mentioned above use: from [Transaction] tr, TransactionEntry trent, Item it, Customer c
from "Transaction" tr, TransactionEntry trent, Item it, Customer c
from Transaction tr, TransactionEntry trent, Item it, Customer c