Created attachment 147303 [details]
NetBeans does not show the output table of a stored procedure.

I checked against my test system - and in this broadness the bug is not correct:

I ran this:

exec sys.sp_help

and got two resultsets (both displayed). I went one step further and ran (t is a table in the db):

exec sys.sp_help 't'

and got five result sets. So there seems to be something different wrong here.

This is:

- SQL Server 2008 (Express)
- Driver: Microsoft JDBC Driver 4.0 for SQL Server
- Version: Microsoft JDBC Driver 4.0 for SQL Server

Could you give some more information please and run the above statements to narrow down the problem?

The stored procedure in question creates and populates a temporary table and then returns it. Is that a supported use case?

Running `exec sys.sp_help` does show results.

Driver: Microsoft SQL Server 2005
Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver: Microsoft JDBC Driver 4.0 for SQL Server
Database product name: Microsoft SQL Server
Database product version: 10.50.1600

When I call that stored procedure from C/C++ code through ODBC API (unixODBC + FreeTDS) I can get the result table as expected.

Let me know what kind of information you need to help fix this issue.

(In reply to max0x7ba from comment #5)
> Let me know what kind of information you need to help fix this issue.

It would be helpful if you could provide a minimal testcase (sample data with a minimal stored procedure, that reproduces your problem).

Please note: I'm not a netbeans developer but have some work in the db module - I'll have a look at the problem, but won't promise a solution.

Test case sql:

    DECLARE @RawData TABLE(Column1 VARCHAR(50),Column2 DATETime)
    
    INSERT INTO @RawData
    (Column1 ,Column2   )
    Select 'abc',GETDATE()
    union all
    Select 'def',GETDATE()
    
    SELECT *
    FROM @Rawdata

Created attachment 147408 [details]
test case MS SQL editor correct output

Created attachment 147409 [details]
test case NetBeans missing output

Created attachment 147417 [details]
proposed patch v1

Thank you! That helped debugging - This is the culprit is in  SQLExecutionHelper.java around line 140:

                     // Read multiple Resultsets
                     boolean isResultSet = executeSQLStatement(stmt, sql);
 
-                    // @todo: This needs clearing up => in light of request for
-                    // the ability to disable autocommit, this need to go
-                    if (!isResultSet || dataView.getUpdateCount() != -1) {
-                        if (!conn.getAutoCommit()) {
-                            conn.commit();
-                        }
-                        return;
-                    }
                     if (Thread.interrupted()) {
                         return;
                     }


My test worked, because the first return was already a resultset, so the removed code path above is not taken. In the sample case the first return is the update count from the insert and only the second result is the resultset you are interested in.

My reading of SQLStatementExcecutor is, that the above statement is necessary to ensure, that a statement modifying data is committed. This is necessary, because SQLStatementExecutor switches into non-autocommit mode and then executes statements. The other users of that class: INSERT, DELETE, UPDATE and TRUNCATE handling all use this and use commitOrRollback to flush their batch out. For the usecase of the "executeQuery" method this is not sane, as each line of input is executed seperatetly and virtual autocommit mode is established.

My fix pushes this virtual autocommit mode down into the SQLStatementExecutor and uses the "real" Autocommit-Mode. That renders the statement useless and it can be removed.

If this change is not acceptable it might be worth checking whether removing the return is feasible, but would not trust database vendors to do sane things with an open resultset and an intermittend commit.

The colleteral changes I did (introducing final and tightening the access modifiers) should be save, as the classes are not public api.

Patch applied - http://hg.netbeans.org/core-main/rev/b923cb93fef5

Thank you Matthias for it.

Integrated into 'main-silver', will be available in build *201407190718* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress)

Changeset: http://hg.netbeans.org/main-silver/rev/b923cb93fef5
User: Libor Fischmeistr <lfischmeistr@netbeans.org>
Log: #244547: calling stored procedures does not show the output table