Lost exceptions on functions call in ASA9 via ADO
Alexander Goldun
when using in queries. This problem may be reproduced very simple.
I'm using 9.0.2.3643 installed on Win XP SP2. Standard asademo9 database
and "ASA 9.0 Sample" ODBC data source.
SQL-function:
CREATE FUNCTION "DBA"."TestBug"()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
DECLARE myexception EXCEPTION FOR SQLSTATE '99009';
DECLARE return_name INTEGER;
SET return_name=321;
signal myexception;
SET return_name=123;
RETURN(return_name);
END
And two testing vbs-files, using connetion via ADO
TestCall.vbs:
Set conn1 = WScript.CreateObject("ADODB.Connection")
conn1.Open("DSN=ASA 9.0 Sample;UID=dba;PWD=sql")
Set cmd1 = WScript.CreateObject("ADODB.Command")
cmd1.ActiveConnection = conn1
Cmd1.CommandText = "call dba.TestBug()"
Cmd1.Execute
'------- This MsgBox will not be executed:
MsgBox "command executed successfully"
'---end of file
Second file TestSelect.vbs
Set conn1 = WScript.CreateObject("ADODB.Connection")
conn1.Open("DSN=ASA 9.0 Sample;UID=dba;PWD=sql")
Set cmd1 = WScript.CreateObject("ADODB.Command")
cmd1.ActiveConnection = conn1
Cmd1.CommandText = "select 987 as Field1, dba.TestBug() as Field2"
set RecordSet = Cmd1.Execute
RecordSet.MoveFirst
MsgBox "command executed successfully"
MsgBox "Function returned Field1=" & _
RecordSet.Fields("Field1").Value & _
" Field2=" & RecordSet.Fields("Field2").Value
'---end of file
First file "TestCall.vbs" runs correctly. It displays error message
"[sybase][ODBC Driver][Adaptive server anywhere] user-defined exception
signaled" as expected.
But "TestSelect.vbs" runs WITHOUT ANY ERROR MESSAGES!!!! It shows
message "command executed successfully" and then
"Function returned Field1=987 Field2=0"
Is it correct??????
P.S. SQL-function may be simplified with the same result:
ALTER FUNCTION "DBA"."TestBug"()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
DECLARE return_name INTEGER;
SET return_name=321;
SET return_name = return_name / 0;
RETURN(return_name);
END
Unknown
I would not say 'lost' so much as 'not expected'.
The whole signaling facility is built around stored
procedures and triggers, but not functions.
Since functions are normally just value-returning
entities (aka expressions) they are not expected
to throw an exception or return a signal. The
un-orthodox use of a 'call' changes the context
enough that the signal handling gear gets engaged
enough to catch that signal.
I don't recommend that approach since it is
an undocumented behaviour and may change
in the future.
You may need to consider another approach.
Returning null (or some other special value) to indicate
an error is more typical of error signaling with
functions. Designing the operation to be a
stored procedure is another possible approach.
Having the function, log errors to a table or
message to client are other options.
HTH (Hopefully This is Helpful)
"Alexander Goldun" <alex...@inbox.ru> wrote in message
news:482308e1@forums-1-dub...
Alexander Goldun
> It's hard to call this one a bug ...
>
> I would not say 'lost' so much as 'not expected'.
> The whole signaling facility is built around stored
> procedures and triggers, but not functions.
It's very bad idea and very significant behavior change IMO.
> Since functions are normally just value-returning
> entities (aka expressions) they are not expected
> to throw an exception or return a signal.
Are you sure? What about these samples:
SELECT HEXTOINT('qwerty')
SELECT DATE('2008-13-13')
and so on??????
> The
> un-orthodox use of a 'call' changes the context
> enough that the signal handling gear gets engaged
> enough to catch that signal.
>
> I don't recommend that approach since it is
> an undocumented behaviour and may change
> in the future.
Sorry, but I cant find in documentation this limitations on functions.
And vice versa there is defifinition in documentation: "User-defined
functions are A CLASS OF PROCEDURES that return a single value to the
calling environment"
> You may need to consider another approach.
> Returning null (or some other special value) to indicate
> an error is more typical of error signaling with
> functions. Designing the operation to be a
> stored procedure is another possible approach.
> Having the function, log errors to a table or
> message to client are other options.
Sometimes its very inconvenient, undesirable. By the way, my exaple
works correctly in SA10 - it displays error message. This behaviour was
changed approximately between ASA 9.0.2.3137 and 9.0.2.3249.
ASA 9.0.2.3137 and more old versions downto Sql anywhere 5.5 workerd
correctly.
--
Best regards,
Alexander Goldun
Alexander Goldun
> It's hard to call this one a bug ...
>
> I would not say 'lost' so much as 'not expected'.
> The whole signaling facility is built around stored
> procedures and triggers, but not functions.
>
> Since functions are normally just value-returning
> entities (aka expressions) they are not expected
> to throw an exception or return a signal.
ASA SQL User's Guide
-Using Procedures, Triggers, and Batches
--Introduction to user-defined functions
---Calling user-defined functions
...
Declarations of cursors and exceptions are discussed in later sections.
...
I assume from this sentence than exceptions are allowed in functions. I
can't find any limitations.
Can I hope normal behaviour returned? (Or documentation will be changed :) )
--
With the best regards,
Alexander Goldun
Unknown
While I still think you have (effectively) coded
to a bug (or at least a side-effect) and you are
exposed to possible behaviour changes in the
future . . .
It appear that EBF 3624 behaves the same way
as V10.0.1 does. Have you tried out the latest
ebfs yet?
Alexander Goldun
> I am sorry to hear this has affected you so.
>
> While I still think you have (effectively) coded
> to a bug (or at least a side-effect) and you are
> exposed to possible behaviour changes in the
> future . . .
Why are you sure it was side-effect? I think there are troubles with
exception reporting in ADO or/and ODBC driver. For example you can
execute select statement:
SELECT 987 as Field1, dba.TestBug() as Field2
via standard ISQL and see normal error message "User defined exception
signaled...". Is it side effect to?
I did not checked yet, but Delphi's dbexpress driver seems to transfer
exceptions from functions correctly.
May be my problem is consequence of this? :
================(Build #3143 - Engineering Case #392484)================
If an application using either the ASA ODBC driver, or the iAnywhere
JDBC driver, fetched a set of rows in which one of the rows encountered
a data exception, then it was likely that the error would not have been
reported.
Note that Prefetch must have been on for the problem to occur. This
problem has now been fixed, but in addition to this change, the changes
to the server for Engineering Case 395662 are also required
================================
Fixing of one problem makes new ones?
For the first time I've meet this problem 2 years ago. It was discussed
here:
http://groups.google.com/group/sybase.public.sqlanywhere.general/browse_thread/thread/bcc5583670b91b47/5e9bcb2d0da05e09
Than nobody said me that exceptions in functions is side effect.
Are you sure that exceptions must not be used in functions?
> It appear that EBF 3624 behaves the same way
> as V10.0.1 does. Have you tried out the latest
> ebfs yet?
I'm using now 9.0.2.3643
--
Alexander Goldun
Alexander Goldun
Chris Keating (Sybase iAnywhere)
positioned the cursor on the correct row and at the point where the
engine needs to return the values to the client. In 10, the expressions
are evaluated as part of positioning the cursor. Because the result has
not been bound, 9 treats the fetch as just a request to position the
cursor; since there is no place to return the SELECT list results, the
engine does not bother evaluating them. Hence the function is not called
and the error is not signalled. In 10, the function is called even
though there is nowhere to return the result.
In 9.0, you should get the signal error (using the most recent EBF) if
you bind a column before fetching.
--
Chris Keating
****************************************
Please only post to the newsgroup
SQL Anywhere Developer Community
http://www.sybase.com/developer/library/sql-anywhere-techcorner
SQL Anywhere Blog Center
http://www.sybase.com/sqlanyblogs
Maintenance releases and EBFs
http://downloads.sybase.com/swx/sdmain.stm
Use Case Express to report bugs
http://case-express.sybase.com
Alexander Goldun
Why undocumented behaviour???? Fuctions described in documentation as
subclass of stored procedures without any limitation on using exceptions.
Chris Keating (Sybase iAnywhere) wrote:
> not been bound, 9 treats the fetch as just a request to position the
> cursor; since there is no place to return the SELECT list results, the
> engine does not bother evaluating them. Hence the function is not called
> and the error is not signalled.
You are mistaken here. Did you see scripts in my first message? The
function really called. You can see it after small modification of function:
ALTER FUNCTION "DBA"."TestBug"()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
DECLARE return_name INTEGER;
SET return_name=321;
MESSAGE 'message before division' type warning TO CLIENT;
SET return_name = return_name / 0;
MESSAGE 'message after division' type warning TO CLIENT;
RETURN(return_name);
END
When executed script TestSelect.vbs message box "message before
division" displayed on client. Then "Command executed successfully" and
then "Function returned Field1=987 Field2=0"
Thus function really executed, but exception from function lost.
> In 9.0, you should get the signal error (using the most recent EBF) if
> you bind a column before fetching.
What does you mean? Initially I've found this problem in Delphi 6,7
accessing to DB via BDE+ODBC or ADO using standard components. I've made
vbs scripts only for you to simplify reproducing this trouble. Two years
ago you said me that my trouble is missing dialect. Now I'm getting
another explanations except recognition of bug in ASA.
Let me know, can I hope for EBF for this or not?
P.S. For you information, SQL Anywhere 10.0.1.3685 has this problem too,
but it appear in other conditions. Statement "select 987 as Field1,
dba.TestBug() as Field2" brings error to client via ADO provider
MSDASQL.1, but not via native SAOLEDB.10 (SQL Anywhere OLE DB Provider
10). MSDASQL.1 brings error "Division by zero" as expected, but
SAOLEDB.10 silently returns empty resultset
--
Whith best regards,
Alexander Goldun
Chris Keating (Sybase iAnywhere)
that you pursue this as a support case or via the "report a bug"
service. Your issue was investigated a and the response I provided was
documented in the case notes. I had not looked carefully at the code in
this posting (my bad) as I erroneously assumed that it was the same code
you had presented in your 5/7/2008 post to the old thread and earlier in
this thread. I will point out that at the time the answer provided was
based on the test case. It may be that there is a different issue here
but showing a similar symptom.
The code that you are providing now has no exceptions defined and is
relying solely on any errors returned by the engine when processing the
function. In my testing of this function in DBISQL I get the "division
by zero" error as expected. Here is what I am doing in dbisql (which
binds results):
begin
declare ret integer;
--set ret = testbug();
select testbug() into ret;
end
I have also tried "call testbug()" with the same results.
So, the issue that you are seeing is not that a function cannot handle
errors. There must be some issue with how the clients are handling those
errors. I would recommend that you pursue this with support or as a bug
case ( the forums are not a technical support channel so it is best to
have issue reported via appropriate channels).
--
Chris Keating
****************************************
Please only post to the newsgroup
SQL Anywhere Developer Community
http://www.sybase.com/developer/library/sql-anywhere-techcorner
SQL Anywhere Blog Center
http://www.sybase.com/sqlanyblogs
Maintenance releases and EBFs
http://downloads.sybase.com/swx/sdmain.stm
Use Case Express to report bugs
http://case-express.sybase.com
Alexander Goldun
> The code that you are providing now has no exceptions defined and is
> relying solely on any errors returned by the engine when processing the
> function.
First version of TestBug() was with exceptions:
DECLARE myexception EXCEPTION FOR SQLSTATE '99009';
....
signal myexception;
But it works with the same simpthoms
> In my testing of this function in DBISQL I get the "division
> by zero" error as expected.
All of these samples works correctly in DBISQL. I suppose it may me
trouble in ADO or ODBC driver.
> Here is what I am doing in dbisql (which
> binds results):
>
> begin
> declare ret integer;
> --set ret = testbug();
> select testbug() into ret;
> end
Thank you for idea! But this workaround applicable only for simple
situations, when we need single value.
It is not applicable if I want to use function in more complex query or
in views. For example:
SELECT field1, field2, SomeFunc(field3) as F3 FROM SomeTable
> So, the issue that you are seeing is not that a function cannot handle
> errors. There must be some issue with how the clients are handling those
> errors.
Yes, of course! I've tried to notice attention to it. This problem
appears on ODBC+BDE, ADO, but not on dbExpress or EmbeddedSQL applications.
> I would recommend that you pursue this with support or as a bug
> case ( the forums are not a technical support channel so it is best to
> have issue reported via appropriate channels).
Ok, I'll try it again.
--
With best regards,
Alexander Goldun
Alexander Goldun
> So, the issue that you are seeing is not that a function cannot handle
> errors. There must be some issue with how the clients are handling those
> errors. I would recommend that you pursue this with support or as a bug
> case ( the forums are not a technical support channel so it is best to
> have issue reported via appropriate channels).
I've made bug case 11446323
It seems to me, I've located error very exactly. I've tried to connect
to my ASA9 database via ODBC diver from ASA8. This diver works
correctly! It raises error as expected! I hope this information is
helpful to solve this problem.
Unknown
behaviour this issue, when ran with the older versions? Or 8.0.3?
I've just tested this out with both the latest 9.0.2 ebf as well as the GA
version of 9.0.2 and neither show the exception. Neither does 8.0.3 EBF.
The ODBC trace shows the VBS environment is binding the result columns
only after it calls SQLExecDirectW( )
It also shows *** we are returning that signal *** on the fetch!!!
11446323_TestSe c20-168 EXIT SQLExtendedFetch with return code 1
(SQL_SUCCESS_WITH_INFO)
HSTMT 06611BC8
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0013E6EC (1)
UWORD * 0x03118C70 (5)
DIAG [99009] [Sybase][ODBC Driver][Adaptive Server Anywhere]
User-defined exception signaled (-297)
So it is unlikely to be a driver or version issue but a change (possibly) in
the
VBS runtime? Maybe????
"Alexander Goldun" <alex...@inbox.ru> wrote in message
news:4833da31@forums-1-dub...
Alexander Goldun
> Are you sure the test case provided with 11446323 shows the expected
> behaviour this issue, when ran with the older versions? Or 8.0.3?
> I've just tested this out with both the latest 9.0.2 ebf as well as the GA
> version of 9.0.2 and neither show the exception. Neither does 8.0.3 EBF.
I've used ODBC driver ASA 8.0 version 8.00.02.4218 dated 22 feb 2003
for connect with the same ASA9 database. It shows exception correctly.
ODBC driver ASA 9.00.02.3658 does not show exception.
Driver SA 10.00.01.3685 does not show exception too whit the same
database on ASA 9.0.2.3658 server.
> The ODBC trace shows the VBS environment is binding the result columns
> only after it calls SQLExecDirectW( )
>
>
> It also shows *** we are returning that signal *** on the fetch!!!
I've shown similar trace 2 years ago:
http://groups.google.com/group/sybase.public.sqlanywhere.general/msg/9135e2f9934c92f4
> So it is unlikely to be a driver or version issue but a change (possibly) in
> the
> VBS runtime? Maybe????
Maybe. But I have the same trouble not only in VBS. I've used VBS only
for reproducing. Maybe some changes was made in mechanism of exceptions
transferring to client?
Alexander Goldun
> Maybe. But I have the same trouble not only in VBS. I've used VBS only
> for reproducing. Maybe some changes was made in mechanism of exceptions
> transferring to client?
YES!!! I have found differences! Please, pay attention on return code:
ODBC-trace made by 8.00.02.4218:
TestSel2 dfc-1a0 EXIT SQLExtendedFetch with return code -1
(SQL_ERROR)
HSTMT 017E1B98
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0013E6FC
UWORD * 0x00F28C10
DIAG [S1000] [Sybase][ODBC Driver][Adaptive Server Anywhere]General
error: User-defined exception signaled (-297)
===================================================================
ODBC-trace made by 9.00.02.3658
ODBC-trace made by 8.00.02.4218:
TestSel2 b44-66c EXIT SQLExtendedFetch with return code 1
(SQL_SUCCESS_WITH_INFO)
HSTMT 017E1BC8
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0013E6FC (1)
UWORD * 0x00F28CC8 (5)
DIAG [99009] [Sybase][ODBC Driver][Adaptive Server
Anywhere]User-defined exception signaled (-297)
ODBC Driver 8.00.02.4218 returns SQL_ERROR on SQLExtendedFetch, but ASA9
returns SQL_SUCCESS_WITH_INFO
Alexander Goldun
database. It shows exception correctly and SQLExtendedFetch returns
SQL_ERROR in trace:
TestSel2 f78-648 EXIT SQLExtendedFetch with return code -1
(SQL_ERROR)
HSTMT 017E1B98
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0013E6FC
UWORD * 0x00F28C28
DIAG [S1000] [Sybase][ODBC Driver][Adaptive Server
Anywhere]User-defined exception signaled (-297)
Problem located exact as possible. Isn't it?
--
Alexander Goldun
Alexander Goldun wrote:
>
> ODBC-trace made by 9.00.02.3658
Unknown
"Alexander Goldun" <alex...@inbox.ru> wrote in message
news:48352ab8$1@forums-1-dub...
Unknown
behaviour.
First off, you are using generic 'bridge' machinery to make
an ODBC connection. The use of
Set conn1 = WScript.CreateObject("ADODB.Connection")
you are using (what is sometimes called) the 'ADO-ODBC Bridge'
to make an ODBC API connection from a ADO.Net client API.
This is one reason why the change (noted below) is so noticible
to you. We believe the ODBC-Bridge is incorrectly handling
this and you may find that using our ADO.Net (ASAProv)
will resolve this for you.
The actual change (in the ODBC driver) was made to correct
the behaviour of SQLExtendedFetch( ) under these conditions.
As noted by engineering the old SQL_ERROR behaviour was
incorrect according to the ODBC specification; so the change
is deliberated and not something that is going to be corrected.
"As part of Engineering Change# 392484 (9.0.2.3143),
SQLExtendedFetch was changed to return
SQL_SUCCESS_WITH_ERROR if the engine returned a row
but the row had an error. This was done to match the
SQLExtendedFetch ODBC spec, which indicates
SQL_SUCCESS_WITH_ERROR should be returned if a row
was returned even if it had an error, ... Previously,
SQLExtendedFetch returned SQL_ERROR in this case."
And further, Engineering
"... tried to see if we could check for the warning using conn.Errors,
but conn.Errors.Count was 0 after the rs.MoveFirst, so the Microsoft
ADO-ODBC bridge driver is ignoring the warning completed."
So the ADO-ODBC bridge leaves you nothing to work with. You have
way to work around this, and the ADO-ODBC (from Microsoft) is not
able to handle this compliant aspect of the ODBC specification for you.
Try out ADO.Net driver and see how well this works instead.
"Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message
news:4835e132$1@forums-1-dub...
Alexander Goldun
> So the ADO-ODBC bridge leaves you nothing to work with. You have
> way to work around this, and the ADO-ODBC (from Microsoft) is not
> able to handle this compliant aspect of the ODBC specification for you.
>
> Try out ADO.Net driver and see how well this works instead.
Similar trouble exists in ASAProv.90 too..........
:(