Solved

Exception while executing MS SQL function. The statement did not return a result set.

Posted on 2015-01-31
4
171 Views
Last Modified: 2015-03-17
MSSQL procedure

USE [KingsBayY]
GO
/****** Object:  UserDefinedFunction [dbo].[test_fn_transaction_search]    Script Date: 01/30/2015 11:57:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
                   
alter PROCEDURE test_fn_transaction_insert_log @receipt varchar(25), @amount varchar(25), @stdt varchar(25),
@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.ReceiptNumber,
tr.Voided,it.ItemLookupCode, tr.Comment
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_log ?,?,?,?");  
            pstmt = connect.prepareStatement(SQL);
            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(rs);
       
        return "success";
    }




I get the below exception. Can anyone tell me whats going wrong.
exception
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.



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.
0
Comment
Question by:solarisinfosys
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40581782
if Transaction is a table name, apparently it is, remove the " from both sides.

from "Transaction" tr, TransactionEntry trent, Item it, Customer c

from Transaction tr, TransactionEntry trent, Item it, Customer c
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40581849
I'm not a Java expert...
 rs = pstmt.executeQuery();
looks like it is expecting a recordset - but the SP performs an INSERT and won't return a recordset, unless you follow the INSERT statement with a SELECT statement (e.g. to get the ID of the newly inserted record)...
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

Open in new window


Transaction is a reserved keyword in TSQL, so it is correct to enclose it in double quotes provided that SET QUOTED IDENTIFIER is ON (the default) and as I see you have declared in your SP.
0
 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 40582807
Because you have mentioned:

rs = pstmt.executeQuery();

Open in new window


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();

Open in new window

with
pstmt.executeQuery();

Open in new window


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)
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40582813
to avoid keyword as mentioned above use: from [Transaction] tr, TransactionEntry trent, Item it, Customer c
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
listing SQL login names of valid databases 2 20
Download ms sql express. 2 25
TSQL convert date to string 4 34
Get Next number from Stored Procedure 8 21
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question