Error: No ResultSet was produced

Martin_Kloop · 9. Mai 2006 um 16:27

Hi again

I have an SQL Statement which looks as follows:

BEGIN TRANSACTION
insert into tabletext bla bla
insert into tabletext bla bla
insert into tabletext bla bla
insert into tabletext bla bla
COMMIT TRANSACTION

This works fine, the data is inserted in the database. If I ommit the COMMIT data is not inserted as desired. But the problem is that I get in either case the SQL MEssage

No ResultSet was produced?

So i dont know in the end if the commit was successfull or if not. Anyone has got an idea what the problem could be? I use MS SQL Server

Cheers
Patrick

Peter_Goppelt_Langer_d5bddf · 9. Mai 2006 um 16:57

Hi Patrick,

of course, the message is correct. You did never produce a Resultset, which is the answer of the database of a SELECT.

If you are using an application you may have user some function maned like executeQuery, which usually sends a query to the database and receives the Resultset.

Regards

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Martin_Kloop · 9. Mai 2006 um 17:03

Hi again Peter

If you are using an application you may have user some
function maned like executeQuery, which usually sends a query
to the database and receives the Resultset.

Yeah thats correct, I am currently using Matlab. The intersting thing is that if I just use insert then i dont get this message that no ResultSet was produced. Only if I use a transaction and in the end the commit I get this error. The problem I have now is, how can i find out if the commit was successfull or if there was something wrong?

Cheers
Patrick

Peter_Goppelt_Langer_d5bddf · 9. Mai 2006 um 18:17

Hi Patrick,

if you are using a programming language within Mathlab,there should be a function like execute() or executeUpdate(), which is more appropriate for INSERTS, UPDATES and DELETES. In Java the execute Method returns whether a Resultset has been produced or not and executeUpdate returns the number of lines.
In the case of a syntax error you shoul get the error message from the server vie an exception. Just try any wrong statement as „HI DATABASE HOW ARE YOU“. If you don’t get the Message immediately, there must be a function for displaying them.

Regards

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Martin_Kloop · 10. Mai 2006 um 08:38

Hi Peter

Thanks a bunch for your support so far. Of course I get an exeption if the SQL Statement is wrong. In Matlab I use the exec command which requires the SQL query sent to the database. The problem here is, that the return value of the exec statement is the same either I ommit the final commit or not. So I cant distinguish from the answer of the database if the commit was executed or not. I always get the error message No ResultSet was produced.

Any idea how i can find out if the commit was executed or not?

Hi Patrick,

if you are using a programming language within Mathlab,there
should be a function like execute() or executeUpdate(), which
is more appropriate for INSERTS, UPDATES and DELETES. In Java
the execute Method returns whether a Resultset has been
produced or not and executeUpdate returns the number of lines.
In the case of a syntax error you shoul get the error message
from the server vie an exception. Just try any wrong statement
as „HI DATABASE HOW ARE YOU“. If you don’t get the Message
immediately, there must be a function for displaying them.

Regards

Peter

Hi again Peter

If you are using an application you may have user some
function maned like executeQuery, which usually sends a query
to the database and receives the Resultset.

Peter_Goppelt_Langer_d5bddf · 10. Mai 2006 um 09:46

Hi Patrick,

I guess, there is no problem.

There are 2 reasons for a rollback:

If you send the statements to the database and any of them is wrong, you will get an error message. In this case you must rollback the whole transaction. (The system automatically rollbacks the single statement, but processes all before)
The connection is cut on any reason: Network, Client, Server doesn’t matter. Then the System is offline. The connection to the database must be established again. A login is required. Then the System automatically processes a complete rollback.

SQL-Server has a Variable: @@TRANCOUNT to get the Information about the open transaction depth. (You can have many transactions, but only the outermost is important)

begin
 any sql statement
 begin
 any other sql statement
 commit
 another sql statement
commit

SELECT 'Number of open begins', @@TRANCOUNT

returns the number of open transactions. Should be 0 if no transaction is open.

The inner begin - commit is rollbacked if the outer begin - is rollbacked. (This feature is not really useful)

Regards

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]