Thanks for the fix regarding Identity fields...it worked like a charm.
There's one more class of situation we're trying to test with the tool but so far haven't been able to accomplish.....if the SP is coded to capture an exception and return a non-zero return code, such as:
select @my_error = @@error
if (@my_error != 0)
begin
select @err_msg = ...
RAISEERROR 99999 @err_msg
return(11)
end
It appears that if you get an exception back, you no longer have the option to look at the return code as well...is that correct?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the feedback regarding the identity fields fix.
Regarding this one, not sure how SQLUnit would handle it. When the Stored procedure does a RAISEERROR, the flow terminates and I would think it should never reach the return call. Is that not correct?
On the java side, the RAISEERROR results in an SQLException being thrown, which I trap and get the ErrorCode and ErrorMessage and display in the exception element. If you can verify that the return statement is reached and a return code returned (from sqsh or isql), then I should be able to capture it as well, and add another (non-mandatory) element to return the return code in the exception element. But you are right, it does not do it this way currently.
Let me know how it behaves with these clients, if possible cut and paste the session here.
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
So to handle this kind of situation, should we have an extra field in the error tag that contains the returned value from the stored procedure, if the stored procedure declared a return value?
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-06-03
Sujit,
Sounds like that's what we need. I had to have the DB developer explain it to me as I was also under that assumption. As I said though, we're looking through alolt of SPs to see what, if anything, the tool can't help us with. Not to sound like the guy in Office Space, but if you could add a return code tag within the exception clause, "that would be grrrreat". And thanks for the response, Dave.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am kind of partial to Mr Burns' "Egzellent" (Homer Simpson's boss) myself :-).
If you are using this kind of setting, and I am assuming you have Java code upstream that consume the results of the procedures, it would be a great help if you could post some sample code of how you actually get back the exception and also the return code. I could use this as a base for modifying the code in SQLUnit to handle this.
Dave, if you happen to have examples of Java code that work with the continue setting, then would appreciate your posting that too.
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Dave, if you happen to have examples of Java code that work with the continue setting, then would appreciate your posting that too.
I am happy to try if you can supply me with a very simple Java program to start with.
If you have one that makes some call and does the usual checks, I can modify it with the new test stream I am working on.
Dave
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Actually, I was able to simulate this case with the mock testing framework that I spoke about in an earlier thread. Looks like this is supported already, but the dtd file needs a tweak. Your test case will look something like this:
<test name="return code with exceptions">
..<call>
....<stmt>{? = call myprocedure()}</stmt>
....<param id="1" name="rc" type="INTEGER" inout="out">${rc}</param>
..</call>
..<result>
....<outparam id="1" type="INTEGER">-100</outparam>
....<exception>
......<code>2001</code>
......<message>Message</message>
....</exception>
..</result?
</test>
The change to sqlunit.dtd is already there in CVS, you may want to try this out and see if it works for you, or you can wait for the next release which should be sometime next week.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the fix regarding Identity fields...it worked like a charm.
There's one more class of situation we're trying to test with the tool but so far haven't been able to accomplish.....if the SP is coded to capture an exception and return a non-zero return code, such as:
select @my_error = @@error
if (@my_error != 0)
begin
select @err_msg = ...
RAISEERROR 99999 @err_msg
return(11)
end
It appears that if you get an exception back, you no longer have the option to look at the return code as well...is that correct?
Hi Ken,
Thanks for the feedback regarding the identity fields fix.
Regarding this one, not sure how SQLUnit would handle it. When the Stored procedure does a RAISEERROR, the flow terminates and I would think it should never reach the return call. Is that not correct?
On the java side, the RAISEERROR results in an SQLException being thrown, which I trap and get the ErrorCode and ErrorMessage and display in the exception element. If you can verify that the return statement is reached and a return code returned (from sqsh or isql), then I should be able to capture it as well, and add another (non-mandatory) element to return the return code in the exception element. But you are right, it does not do it this way currently.
Let me know how it behaves with these clients, if possible cut and paste the session here.
Thanks
Sujit
I take it they are using Sybase ASE for this (or possibly SQL Server).
I believe this is a configurable option.
In Sybase ASA you can do the following (to emulate ASE):
set temporary option ON_TSQL_ERROR='Stop';
Where your options are:
Continue, Stop, Conditional
I believe the default is Continue, which would execute the RETURN after the RAISERROR (silly default IMHO).
I always set it to Stop, since an error was detected.
Conditional allows for IF statements following the code, that may programmatically deal with exceptions/errors as they arise.
Dave/Ken,
Interesting setting :-).
So to handle this kind of situation, should we have an extra field in the error tag that contains the returned value from the stored procedure, if the stored procedure declared a return value?
-sujit
Sujit,
Sounds like that's what we need. I had to have the DB developer explain it to me as I was also under that assumption. As I said though, we're looking through alolt of SPs to see what, if anything, the tool can't help us with. Not to sound like the guy in Office Space, but if you could add a return code tag within the exception clause, "that would be grrrreat". And thanks for the response, Dave.
Hi Ken,
I am kind of partial to Mr Burns' "Egzellent" (Homer Simpson's boss) myself :-).
If you are using this kind of setting, and I am assuming you have Java code upstream that consume the results of the procedures, it would be a great help if you could post some sample code of how you actually get back the exception and also the return code. I could use this as a base for modifying the code in SQLUnit to handle this.
Dave, if you happen to have examples of Java code that work with the continue setting, then would appreciate your posting that too.
Thanks
Sujit
Dave, if you happen to have examples of Java code that work with the continue setting, then would appreciate your posting that too.
I am happy to try if you can supply me with a very simple Java program to start with.
If you have one that makes some call and does the usual checks, I can modify it with the new test stream I am working on.
Dave
Hi Ken/Dave,
Actually, I was able to simulate this case with the mock testing framework that I spoke about in an earlier thread. Looks like this is supported already, but the dtd file needs a tweak. Your test case will look something like this:
<test name="return code with exceptions">
..<call>
....<stmt>{? = call myprocedure()}</stmt>
....<param id="1" name="rc" type="INTEGER" inout="out">${rc}</param>
..</call>
..<result>
....<outparam id="1" type="INTEGER">-100</outparam>
....<exception>
......<code>2001</code>
......<message>Message</message>
....</exception>
..</result?
</test>
The change to sqlunit.dtd is already there in CVS, you may want to try this out and see if it works for you, or you can wait for the next release which should be sometime next week.
-sujit
This is in release 3.7 which was released late last Friday night.
-sujit