I guess that's a dataview issue and one of the reasons a DataView instance can return multiple result components.

I don't think its a dataview issue. This has nothing to do with multiple resultset.

We may want to enhance the splitter to be able to recognize this as two statement. 

Yes it is a dataview issue. When looking I this all I saw that all statements were executed, and
Statement.getMoreResults() could be used to retrieve the next result set or update count. No need to fix the splitter
when the server can cope with it.

If one the database does work with the given statement, that does not mean it will work with all major jdbc drivers and
database servers, this is big assumption and I don't think dataview should handle this. IMO, we should have a clear
convention and splitter should do a good job if at all we wanted to support this. 

> this is big assumption and I don't think dataview should handle this.

What is the big assumption? Dataview should just use the JDBC API to test for all result sets returned by a statement.
Note that even a single procedure could return two statements, so you should be supporting multiple result sets already.
There is no requirement for you to parse or split the statement.

Also, could you please explain why this cannot be fixed in dataview using the JDBC API as hinted in desc4? Looks quite
doable to me. You have a SQL statement, you send it to the server, you get two result sets, you create two results
components. No?

We don't support multiple resultset in dataview and that was not planned anyway.

The assumption is that the following statement will work when executed as single statement by all the databases and
drivers that we support. 

delete from "guest"."t_customer" where id=101select * from
"guest"."t_customer"insert into "guest"."t_customer" (id,name) values (2,'lala') 

To me this is not dataview functionality. If you think we must have such functionality, then  it should handled by the
editor and pass one statement at a time to dataview. If one statement returns multiple result set that will be handled
in future, for now dataview will return the 1st resultset; that is what we agreed as far as remember. 

Please don't assign this back to me, just because you think its dataview related, which I strongly disagree.

Why do we have the splitter? We could also submit everything from editor as one statement? I don't think there is any
need to overload dataview like this. 

Because it is not up to the splitter to do it. The splitter just looks at semicolors, or whatever delimiter the user set
using the DELIMITER statement. The splitter should not try to split the statement just because dataview cannot handle
it, especially *when the JDBC driver can*! The fact that there are three statements does not matter if the server can
deal with it.

The reason we are not sending the whole script to dataview is that most servers or drivers cannot deal with the separators.

It is not upto the dataview to handle this. I don't think we should overload dataview like this, specifically when an
statement used in this use case, can return combination resultset and affected rowcount. If the splitter can't separate
statements which does not have statement separator, then splitter need to be enhance in future to handle this.

For the same reason why you are not sending the whole script as single statement to dataview is also true for this use case.

> I don't think we should overload dataview like this

What is "this"? For two times already I suggested a viable solution to extract the multiple result sets through the JDBC
API. You need to do that in any case for stored procedures. So where exactly is the overloading?

This is not about multiple resultset, please read the original post again.

In case of stored procedure it will either return one or more resultset or updated rowcount. In this statement it
deletes a row and select some rows and then insert one row, which is clearly an overload for dataview. I am not sure why
dataview should support this. 

IMO, if we must support this, then the sql editor need to be enhanced to be able to split this kind of statements before
calling dataview.

Here is what I executed in MySQL:

DELETE FROM sakila.actor WHERE actor_id = 201select * from actor where actor_id=10INSERT INTO sakila.actor (first_name,
last_name, last_update) VALUES ('AA', 'BB', '2006-02-15 04:34:33.0')

And I got the following error.

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'from actor where actor_id=10INSERT INTO sakila.actor (first_name,
last_name, las' at line 1

If you want to support this, I have no issue, but I don't think we should do this in dataview.

We don't want to support that in a generic way for multiple databases, because it doesn't make sense on most databases.
But again, the statement makes sense on MS SQL server, and it is supported by the driver. That is why I disagree that
anything need to be done in the splitter. It is not the splitter's job, because the job is done in the driver. It seems
that dataview stands in the way between the splitter and the driver.

I frankly don't understand why dataview can't just send the statement to the server as is.

Its not about sending the statement to server; I have problem with combination of multiple resultset and multiple update
count and then each statement will have their own exception etc.

If it is just MS SQLServer, I don't think its worth spending time on this.

Please keep this issue with you if you think you have a clear idea what to do and how to do. 

> I have problem with combination of multiple resultset and multiple update count

> If it is just MS SQLServer

It is also about Sybase, not only MS SQL. And stored procedures could return multiple result sets and/or update counts
too on these two servers and at least also MySQL.

I do have a good idea of what do to, and I described it in desc4. I will note that you never explained why is not
practical for dataview.

I did explain why I will not do this. If you have a good idea what to do, why don't you implement that as well. 

There might be some misunderstanding here.

I believe this issue is specific to Microsoft SQL Server and Sybase.

In desc13,  the statement that is expected to execute was executed against MySQL, not Microsoft SQL Server.  Sometimes
these databases get mixed up.

No you did not explain. I really cannot see any place where you explain the exact technical difficulties that prevent
this from being implemented in dataview. All I see is "because it doesn't make sense on most databases" in desc14. And
to that (disregarding the fact that it is not a technical argument) I replied by listing three databases where it does
make sense. One of those databases is the one owned by Sun, which NetBeans needs support to the greatest extent (hence
the P2 priority).

If there is another explanation, sorry, but I do not see it. Please point to it exactly, like "paragraph 1 in desc1" or
something like that.

By the way, you say "if you have a clear idea, why don't you implement it yourself". So it would appear you do not have
a clear idea. Do you have any specific issues in mind that make the implementation impossible or difficult? If yes,
please list them here as well for future reference.

John: yes, you are right, this is only specific to MS SQL and Sybase. The thing is that in the MS SQL Query Analyzer (a
tool shipped with MS SQL) you can write

select * from foo where id = 1select * from bar

That is, two statements, not separated by a semicolon. Query Analyzer will happily execute such SQL code, so NetBeans
should too. Of course, most other databases don't support such a syntax, so the SQL above should fail for them.

The code required to support this is the same as the code needed to support multiple result sets from stored procedures.
And stored procedures *can* return multiple result sets on other databases too, not only Sybase and MS SQL -- for
example on MySQL.

Correct, I don't know how to do it. Please don't assign this to me.

Hi, all.  So, as I understand it, the question is not so much about multiple result sets but about multiple statements,
some of which return results and some of which return update counts, and also supporting multiple exceptions.  In a
stored procedure, if there is an exception, all execution ends.  In this scenario, however, each statement is executed
independently and each one can throw a new exception.

I am having a conversation with Lance about whether you can even get all this information out of JDBC, and will update
this issue once I get the information I need.

And I'm sorry, Ahi, but this *does* belong in the showdata module.  It doesn't really matter who it is "assigned" to - I
can take it for now - but if and when we do work on this, it needs to be done within that module.

To be clear, there are three separate "feature" that could potentially be supported here:

- multiple result sets
- multiple operations, some of which return update counts and some which return result sets
- multiple exceptions from a single statement execution (which is the original complaint of this issue)

lgrela: it may be a while before we support multiple exceptions.  In the meantime you're going to need to work around
this issue by adding delimiters to your SQL or by running it outside of NetBeans.

I logged issue 144491 and issue 144493 for support of a combination of result sets and update counts and for multiple
exceptions in a single execution.  So this issue can be specifically for multiple result sets.  

lgrela, please add yourself as a cc to 144493 as I think this is the one you're particularly interested in.

Thanks David!

OK, Let me agree that it belongs to showdata module, also your description of this issue is more accurate and align with
my understanding, however, I am not sure how to do this; so I did not wanted this to sit my Q.  

OK, got a response back from Lance.  JDBC *does* support multiple results, multiple update counts and multiple
exceptions for a single statement.execute().  Here is some working code from a program he wrote that processed SQL from
a file.  The trick is that you get exceptions for a given result in a set of multiple results by calling stmt.getWarnings().


            results = stmt.execute (cmd);

            // Will catch our print statments
            SQLExceptionHandler.printExceptions(stmt.getWarnings() );
            stmt.clearWarnings();

            do
            {

                if(results)
                {
                    rs = stmt.getResultSet();
                    SQLExceptionHandler.printExceptions(stmt.getWarnings() );
                    stmt.clearWarnings();

                    displayResultSet(rs);

                }
                else
                {
                    rowsAffected = stmt.getUpdateCount();

                    if (rowsAffected >= 0)
                    {
                        System.out.println(rowsAffected + " rows Affected.");
                    }
                }

                results = stmt.getMoreResults();
                SQLExceptionHandler.printExceptions(stmt.getWarnings() );
                stmt.clearWarnings();
            }
            while (results || rowsAffected != -1);

Reassigned to new owner.

Multiple resultsets are now supported by bug 227588.