Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
This issue would like to address the "Script error on line X" message that appears in case of an SQL error in Command mode or via the \source command.
Previously, before ADStudio 12 Beta 20, the following pattern was used:
[SQL error message goes here]
Script error on line X
In ADS 12 Beta 20, the pattern was changed to:
[SQL error message goes here]
Script error on line X:
package.to.exception: Error message here, it was a part of the SQL error message from above
In current build, ADS 12 Beta 32, the behavior was changed to match back the one before ADS 12 Beta 20.
Is this as intended? (Or maybe it was caused by the fix from #7561 - \source -ss does not work?)
However, neither pattern seems to present the problem in the expected way.
In the first case, is not too good that the Script error on line X message is displayed after the error;
in the second case, is not too good because the error message gets redundant, the same SQL error message being displayed twice.
I would see as a better approach to use the following pattern:
Script error on line X:
[SQL error message goes here]
The Script error on line X is displayed first, and after that it is detailed by the specific SQL error that occurred.
Also, the "Script error on line X" header is displayed twice when invoking an erroneous SQL input file as parameter on the source command, which command itself is executed in batch mode. One header with trailing colon char ( : ) is displayed due to the batch mode script execution and the second one due to the \source script execution.
Maybe these headers should be renamed somehow to better indicate the execution context in which the script has failed.
> [SQL error message goes here]
> Script error on line X
>
> In ADS 12 Beta 20, the pattern was changed to:
>
> [SQL error message goes here]
> Script error on line X:
> package.to.exception: Error message here, it was a part of the SQL error message from above
>
> In current build, ADS 12 Beta 32, the behavior was changed to match back the one before ADS 12 Beta 20.
>
> Is this as intended? (Or maybe it was caused by the fix from #7561 - \source -ss does not work?)
Yes, it is.
A backend database object is used to execute the SQL statements extracted from the source script. This database object will report SQL error in a specific format, below is an example:
>•[Error] •Script lines: 1-2 ------------------------
Table 'test_db.abc' doesn't exist
•Warnings: --->
W (1): Table 'test_db.abc' doesn't exist
<---
•[Executed: 9/13/2012 5:19:03 PM] [Execution: 0•ms]
In the current fluid implementation, SQL exception is handled differently; when an SQL exception is detected, only "Script error on line X" is reported, but not the message comes with the exception; because the error message has been reported by the backend database object.
In addition, if you have some other commands in front of SQL statements (e.g. \echo test) in the original source script, you will notice that the line number of 'Script lines: NNN' error reported by the backend database object is different than the line number reported by the shell.
We probably will not modify backend database object at the moment, but I will discuss with Niels to see whether we want to repeat the error message after "Script error on line X" (reported by shell).
> [SQL error message goes here]
> Script error on line X
>
> In ADS 12 Beta 20, the pattern was changed to:
>
> [SQL error message goes here]
> Script error on line X:
> package.to.exception: Error message here, it was a part of the SQL error message from above
>
> In current build, ADS 12 Beta 32, the behavior was changed to match back the one before ADS 12 Beta 20.
>
> Is this as intended? (Or maybe it was caused by the fix from #7561 - \source -ss does not work?)
Yes, it is.
A backend database object is used to execute the SQL statements extracted from the source script. This database object will report SQL error in a specific format, below is an example:
>•[Error] •Script lines: 1-2 ------------------------
Table 'test_db.abc' doesn't exist
•Warnings: --->
W (1): Table 'test_db.abc' doesn't exist
<---
•[Executed: 9/13/2012 5:19:03 PM] [Execution: 0•ms]
In the current fluid implementation, SQL exception is handled differently; when an SQL exception is detected, only "Script error on line X" is reported, but not the message comes with the exception; because the error message has been reported by the backend database object.
In addition, if you have some other commands in front of SQL statements (e.g. \echo test) in the original source script, you will notice that the line number of 'Script lines: NNN' error reported by the backend database object is different than the line number reported by the shell.
We probably will not modify backend database object at the moment, but I will discuss with Niels to see whether we want to repeat the error message after "Script error on line X" (reported by shell).
>> One header with trailing colon char ( : ) is displayed due to the batch mode script execution...
Do you mean something similar to the one shown below? This is generated by the backend database object. I will look into this to see if we can prefix "Script error on line X" with 'source | fluidshell': filename:.
>•[Error] •Script lines: 1-2 ------------------------
Table 'test_db.abc' doesn't exist
•Warnings: --->
W (1): Table 'test_db.abc' doesn't exist
<---
•[Executed: 9/13/2012 5:19:03 PM] [Execution: 0•ms]
>> One header with trailing colon char ( : ) is displayed due to the batch mode script execution...
Do you mean something similar to the one shown below? This is generated by the backend database object. I will look into this to see if we can prefix "Script error on line X" with 'source | fluidshell': filename:.
>•[Error] •Script lines: 1-2 ------------------------
Table 'test_db.abc' doesn't exist
•Warnings: --->
W (1): Table 'test_db.abc' doesn't exist
<---
•[Executed: 9/13/2012 5:19:03 PM] [Execution: 0•ms]
If you look at issues #7579 and #7580, they are both usability issues. They make a good point, and I am sure that some users will have the same thoughts. This usability issue exists because FluidShell is trying abstract and merge the functionality of a Unix shell and an SQL shell, when the behavior of each are not exactly the same. Even further, there is already an abstraction in Aqua Data Studio on how an SQL shell works accross all databases. So, we are trying to abstract the behavior to a Unix shell with an already abstracted SQL Shell. The difference in behavior comes down to how an SQL shell handles the execution of an SQL statement. A Unix shell has a 1-to-1 correlation for a command and an exception, but an SQL Shell does not have a 1-to-1 correlation for an SQL statement and errors. An SQL shell doesn't actually see an SQLException as an error the way the Unix shell does. Now, some databases do have 1-to-1 correlations such as Oracle and DB2, while SQL Server and Sybase do not. The JDBC driver API has abstracted this and is handled by the Statement.execute() method :
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#execute%28java.lang.String%29
Where the basic logic of execution for a statement is as follows :
boolean hasResult = Statement.executeQuery()
while ( notFinished ) {
if ( hasResult ) {
result = Statement.getResultSet();
// process results and display to Standard Out
} else {
rowCount = Statement.getUpdateCount();
// message the user of updates rows, display to Standard Out
}
}
// Now get the warnings ... which can be a list of chained warnings.
The logic/code for the execution of an SQL statement across all databases is much larger and very complicated. It takes into account many things. This includes the fact that on some databases you can execute a stored procedure or t-sql statement which can return multiple exceptions. So, getting an exception doesn't mean to stop the processing of the results of an SQL statement execution.
The next issue to take into account is that in an SQL shell, a user can execute a statement that returns alot of data, messages and exceptiosn. More messages then can be held in memory before it starts streaming to the StandardOut. So, when the GO command executes an SQL statement, it needs to stream the results to Standard Out as above while it is looping through the results. It can't just queue everything into a big message and return back to the shell to have the shell display it. An SQL execution can actually return multiple resultsets, warnings, updates and exceptions with one Statement.executeQuery() call.
With this in mind, you can't use option #1 because the command is displaying the error to standard out, not the shell. This is similar to a command writing to standard error, but not throwing an exception.
We could try to do what option #2 suggests, but it doesn't make complete sense since there is no 1-to-1 correlation on the command an error. Although I guess we can always display the 1st exception if there is one. Or we could display the error message "Query Failed : <The first 15 characters of the SQL statement>.
I agree we could make the usability better, but I am not sure what is the best way keeping in mind the full design of the universal unix/sql shell. Unless we have a better way, I suggest leaving as is for now.
If you look at issues #7579 and #7580, they are both usability issues. They make a good point, and I am sure that some users will have the same thoughts. This usability issue exists because FluidShell is trying abstract and merge the functionality of a Unix shell and an SQL shell, when the behavior of each are not exactly the same. Even further, there is already an abstraction in Aqua Data Studio on how an SQL shell works accross all databases. So, we are trying to abstract the behavior to a Unix shell with an already abstracted SQL Shell. The difference in behavior comes down to how an SQL shell handles the execution of an SQL statement. A Unix shell has a 1-to-1 correlation for a command and an exception, but an SQL Shell does not have a 1-to-1 correlation for an SQL statement and errors. An SQL shell doesn't actually see an SQLException as an error the way the Unix shell does. Now, some databases do have 1-to-1 correlations such as Oracle and DB2, while SQL Server and Sybase do not. The JDBC driver API has abstracted this and is handled by the Statement.execute() method :
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#execute%28java.lang.String%29
Where the basic logic of execution for a statement is as follows :
boolean hasResult = Statement.executeQuery()
while ( notFinished ) {
if ( hasResult ) {
result = Statement.getResultSet();
// process results and display to Standard Out
} else {
rowCount = Statement.getUpdateCount();
// message the user of updates rows, display to Standard Out
}
}
// Now get the warnings ... which can be a list of chained warnings.
The logic/code for the execution of an SQL statement across all databases is much larger and very complicated. It takes into account many things. This includes the fact that on some databases you can execute a stored procedure or t-sql statement which can return multiple exceptions. So, getting an exception doesn't mean to stop the processing of the results of an SQL statement execution.
The next issue to take into account is that in an SQL shell, a user can execute a statement that returns alot of data, messages and exceptiosn. More messages then can be held in memory before it starts streaming to the StandardOut. So, when the GO command executes an SQL statement, it needs to stream the results to Standard Out as above while it is looping through the results. It can't just queue everything into a big message and return back to the shell to have the shell display it. An SQL execution can actually return multiple resultsets, warnings, updates and exceptions with one Statement.executeQuery() call.
With this in mind, you can't use option #1 because the command is displaying the error to standard out, not the shell. This is similar to a command writing to standard error, but not throwing an exception.
We could try to do what option #2 suggests, but it doesn't make complete sense since there is no 1-to-1 correlation on the command an error. Although I guess we can always display the 1st exception if there is one. Or we could display the error message "Query Failed : <The first 15 characters of the SQL statement>.
I agree we could make the usability better, but I am not sure what is the best way keeping in mind the full design of the universal unix/sql shell. Unless we have a better way, I suggest leaving as is for now.
(A) OK, we will not change the way how SQL error is reported at the moment.
(B) Made change so that 'Script error on line X:' is displayed with a prefix which can be one of the followings:
(1) fluidshell: filename:
(2) fluidshell: initialization script:
(3) source: filename:
(4) source: (standard input):
where
case (1) - execute a script using fluid shell command mode,
case (2) - execute the script defined in the server property file upon opening a fluid shell tab,
case (3) - execute a script using \source command with a filename supplied,
case (4) - execute \source with input read from standard input.
(A) OK, we will not change the way how SQL error is reported at the moment.
(B) Made change so that 'Script error on line X:' is displayed with a prefix which can be one of the followings:
(1) fluidshell: filename:
(2) fluidshell: initialization script:
(3) source: filename:
(4) source: (standard input):
where
case (1) - execute a script using fluid shell command mode,
case (2) - execute the script defined in the server property file upon opening a fluid shell tab,
case (3) - execute a script using \source command with a filename supplied,
case (4) - execute \source with input read from standard input.
> (A) OK, we will not change the way how SQL error is reported at the moment.
Yes, OK.
> (B) Made change so that 'Script error on line X:' is displayed with a prefix which can be one of the followings:
> (1) fluidshell: filename:
> (2) fluidshell: initialization script:
> (3) source: filename:
> (4) source: (standard input):
> where
> case (1) - execute a script using fluid shell command mode,
> case (2) - execute the script defined in the server property file upon opening a fluid shell tab,
> case (3) - execute a script using \source command with a filename supplied,
> case (4) - execute \source with input read from standard input.
Verified all cases.
For case (3), if calling the \source command via Command mode, the error message is displayed as in case (1), while the expected behavior would be that from case (3).
> (A) OK, we will not change the way how SQL error is reported at the moment.
Yes, OK.
> (B) Made change so that 'Script error on line X:' is displayed with a prefix which can be one of the followings:
> (1) fluidshell: filename:
> (2) fluidshell: initialization script:
> (3) source: filename:
> (4) source: (standard input):
> where
> case (1) - execute a script using fluid shell command mode,
> case (2) - execute the script defined in the server property file upon opening a fluid shell tab,
> case (3) - execute a script using \source command with a filename supplied,
> case (4) - execute \source with input read from standard input.
Verified all cases.
For case (3), if calling the \source command via Command mode, the error message is displayed as in case (1), while the expected behavior would be that from case (3).
Also, additionally should be considered if it's OK to use case (4) in a scenario like:
$ echo grep > file
$ source < file
Also, additionally should be considered if it's OK to use case (4) in a scenario like:
$ echo grep > file
$ source < file
>> For case (3), if calling the \source command via Command mode, the error message is displayed as in case (1), while the expected behavior would be that from case (3).
This is fixed in SVN r29395.
>> Also, additionally should be considered if it's OK to use case (4) in a scenario like:
>>
>> $ echo grep > file
>> $ source < file
File redirect is done by shell, \source will not see " < file ". \source only knows arguments are not supplied, and hence expect something to be coming in from the standard input.
>> For case (3), if calling the \source command via Command mode, the error message is displayed as in case (1), while the expected behavior would be that from case (3).
This is fixed in SVN r29395.
>> Also, additionally should be considered if it's OK to use case (4) in a scenario like:
>>
>> $ echo grep > file
>> $ source < file
File redirect is done by shell, \source will not see " < file ". \source only knows arguments are not supplied, and hence expect something to be coming in from the standard input.
Verified using ADStudio 12 RC 1.
Closed.
Verified using ADStudio 12 RC 1.
Closed.
Issue #7579 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build trunk/29281, 29395 |
| No time estimate |
1 issue link |
relates to #7580
Issue #7580Command mode FS or \source with -ss false does not display Script error on line X message |
Also, the "Script error on line X" header is displayed twice when invoking an erroneous SQL input file as parameter on the source command, which command itself is executed in batch mode. One header with trailing colon char ( : ) is displayed due to the batch mode script execution and the second one due to the \source script execution.
Maybe these headers should be renamed somehow to better indicate the execution context in which the script has failed.