New stupid bug in ASA 9.0.2.3249?
Alexander Goldun
Very simple function:
CREATE FUNCTION DBA.TestBug3249()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
DECLARE return_name INTEGER;
SET return_name=321;
raiserror 99999 'What is this?';
SET return_name=123;
RETURN return_name;
END
CALL dba.TestBug3249() raises error, but SELECT dba.TestBug3249()
returns 541130821 WITHOUT ANY ERRORS!
This behaviour checked on ASA 9.0.2.3249 on Win2000 pro, Win2000 server
and ASA 9.0.2.3228 on Win 2003 server SP1
Version 9.0.2.3137 works correctly.
Alexander Goldun
connected to database via ODBC+BDE. For examplr from SQLExplorer or any
application written in Delphi.
Reg Domaratzki (iAnywhere Solutions)
I can't reproduce the problem in dbisqlc or dbisql.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"
"Alexander Goldun" <alex...@spamtest.ru> wrote in message
news:44103e6d$1@forums-1-dub...
Alexander Goldun
> Does this only happen from a Delphi app?
> I can't reproduce the problem in dbisqlc or dbisql.
I've tried dbisql too - it works correctly from dbisql.
But from MS Access XP via ODBC it has the same behavior as Delphi app -
select statement return some random value without any error messages.
P.S. I've just downloaded 9.0.2.3267 - the same bug.
Chris Keating(iAnywhere Solutions)
handling in Watcom dialect. When you mix dialects, you can get
unexpected results. Whether this is the cause of the problem that you
are seeing is unclear.
--
Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8
*****************************************************************************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition =and try out the market-leading database for mobile, embedded
and small to medium sized business environments for free!
http://www.ianywhere.com/promos/deved/index.html
*****************************************************************************
iAnywhere Solutions http://www.iAnywhere.com
** Please only post to the newsgroup
** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com
*****************************************************************************
Chris Keating(iAnywhere Solutions)
against ASA9.0.2.3267.
I would still recommend not mixing dialects. Look at exception handling
for this.
Alexander Goldun
> You are mixing Watcom and TSQL dialect. You should be using exception
> handling in Watcom dialect.
Can you show me correct source of the same function in pure WatcomSQL
which will raise errors and will not return any values if error occured?
I've tried this:
ALTER FUNCTION "DBA"."TestBug3249"()
returns integer
not deterministic
begin
DECLARE myexception EXCEPTION
FOR SQLSTATE '99999';
declare return_name integer;
set return_name=321;
signal myexception;
set return_name=123;
return (return_name);
end
But behaviour was not changed.
Breck Carter [Team iAnywhere]
from PowerBuilder. The value of CONTINUE_AFTER_RAISERROR controls
whether the function terminates when it reaches the RAISERROR or not,
but it does *not* affect the fact that the PowerBuilder client always
sees the error. In other words, the following code...
SELECT dba.TestBug3249()
INTO :ll_return_value
FROM dummy
USING SQLCA;
IF SQLCA.SQLCODE <> 0 THEN
MessageBox ( 'Error', &
'SELECT failed in open:' &
+ '~r~nSQLCode = ' &
+ String ( SQLCA.SQLCode ) &
+ '~r~nSQLDBCode = ' &
+ String ( SQLCA.SQLDBCode ) &
+ '~r~n' &
+ SQLCA.SQLErrText )
// RETURN
END IF
MessageBox ( 'll_return_value', ll_return_value );
...always displays the "Error" box, and also displays zero in
ll_return_value.
Perhaps there was some subtle change in the way the error is fed back
to the client side, in a way that affects Access and BDE clients... I
don't have either, so I can't try it out.
Breck
On 9 Mar 2006 07:11:12 -0800, "Reg Domaratzki \(iAnywhere Solutions\)"
<FirstName...@ianywhere.com> wrote:
>I also can't reproduce this from a simple ODBC app (see attached basic.c and
>makefile) either. The SQLFetch always returns an error.
>
>[0] [90] basic dba_sql_90
>*** Success - SQLAllocHandle(env) ***
>*** Success - SQLSetEnvAttr ***
>*** Success - SQLAllocHandle(dbc) ***
>*** Success - SQLConnect ***
>*** Success - SQLAllocHandle(stmt) ***
>*** Success - SQLExecDirect ***
>*** Success - SQLBindCol ***
>*** Failure - SQLFetch (-1) ***
> *** Environment ***
> *** Connection ***
> *** Statement ***
> [HY000][-99999] [Sybase][ODBC Driver][Adaptive Server Anywhere]RAISERROR
>executed: What is this?
>*** Success - SQLFreeHandle(stmt) ***
>*** Success - SQLDisconnect ***
>*** Success - SQLFreeHandle(dbc) ***
>*** Success - SQLFreeHandle(env) ***
>
>If the error occurs when using ODBC+BDE, can you post an ODBC trace of the
>error occuring?
--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck....@risingroad.com
Alexander Goldun
> error occuring?
Below ODBC-trace of executing "select testbug3249()" from sqlexplorer.
You can see lines with error in log: "DIAG [S1000] [Sybase][ODBC
Driver][Adaptive Server Anywhere]User-defined exception signaled (-297)"
But this select returns a random value without error message.
=========== begin log ======================
dbexplor 6c0-6e8 ENTER SQLAllocStmt
HDBC 02D61808
HSTMT * 0156E954
dbexplor 6c0-6e8 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 02D61808
HSTMT * 0x0156E954 ( 0x02d62760)
dbexplor 6c0-6e8 ENTER SQLExecDirect
HSTMT 02D62760
UCHAR * 0x0156E91C [ -3] "select
testbug3249()\ d\ a\ 0"
SDWORD -3
dbexplor 6c0-6e8 EXIT SQLExecDirect with return code 0
(SQL_SUCCESS)
HSTMT 02D62760
UCHAR * 0x0156E91C [ -3] "select
testbug3249()\ d\ a\ 0"
SDWORD -3
dbexplor 6c0-6e8 ENTER SQLNumResultCols
HSTMT 02D62760
SWORD * 0x0156E980
dbexplor 6c0-6e8 EXIT SQLNumResultCols with return code 0
(SQL_SUCCESS)
HSTMT 02D62760
SWORD * 0x0156E980 (1)
dbexplor 6c0-6e8 ENTER SQLDescribeCol
HSTMT 02D62760
UWORD 1
UCHAR * 0x0156E41E
SWORD 32
SWORD * 0x0012F97E
SWORD * 0x0012F936
SQLULEN * 0x0012F930
SWORD * 0x0156E444
SWORD * 0x0012F97C
dbexplor 6c0-6e8 EXIT SQLDescribeCol with return code 0
(SQL_SUCCESS)
HSTMT 02D62760
UWORD 1
UCHAR * 0x0156E41E [ 14] "testbug3249(*)"
SWORD 32
SWORD * 0x0012F97E (14)
SWORD * 0x0012F936 (4)
SQLULEN * 0x0012F930 (10)
SWORD * 0x0156E444 (0)
SWORD * 0x0012F97C (1)
dbexplor 6c0-6e8 ENTER SQLBindCol
HSTMT 02D62760
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR 0x0156D8DC
SQLLEN 4
SQLLEN * 0x0156D84C
dbexplor 6c0-6e8 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 02D62760
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR 0x0156D8DC
SQLLEN 4
SQLLEN * 0x0156D84C (1128416335)
dbexplor 6c0-6e8 ENTER SQLSetStmtOption
HSTMT 02D62760
UWORD 9 <SQL_ROWSET_SIZE>
SQLPOINTER 0x00000014
dbexplor 6c0-6e8 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 02D62760
UWORD 9 <SQL_ROWSET_SIZE>
SQLPOINTER 0x00000014 (BADMEM)
dbexplor 6c0-6e8 ENTER SQLExtendedFetch
HSTMT 02D62760
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0012F960
UWORD * 0x0156D73C
dbexplor 6c0-6e8 EXIT SQLExtendedFetch with return code 1
(SQL_SUCCESS_WITH_INFO)
HSTMT 02D62760
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0012F960 (1)
UWORD * 0x0156D73C (5)
DIAG [S1000] [Sybase][ODBC Driver][Adaptive Server
Anywhere]User-defined exception signaled (-297)
dbexplor 6c0-6e8 ENTER SQLSetStmtOption
HSTMT 02D62760
UWORD 9 <SQL_ROWSET_SIZE>
SQLPOINTER 0x00000014
dbexplor 6c0-6e8 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 02D62760
UWORD 9 <SQL_ROWSET_SIZE>
SQLPOINTER 0x00000014 (BADMEM)
dbexplor 6c0-6e8 ENTER SQLExtendedFetch
HSTMT 02D62760
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0012E7FC
UWORD * 0x0156D73C
dbexplor 6c0-6e8 EXIT SQLExtendedFetch with return code 100
(SQL_NO_DATA_FOUND)
HSTMT 02D62760
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 0
SQLULEN * 0x0012E7FC
UWORD * 0x0156D73C
dbexplor 6c0-6e8 ENTER SQLFreeStmt
HSTMT 02D62760
UWORD 1 <SQL_DROP>
dbexplor 6c0-6e8 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 02D62760
UWORD 1 <SQL_DROP>
=========== The end =================
Alexander Goldun
Option continue_after_raiserror set to Off
Breck Carter [Team iAnywhere]
<NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> wrote:
>You are mixing Watcom and TSQL dialect. You should be using exception
>handling in Watcom dialect. When you mix dialects, you can get
>unexpected results. Whether this is the cause of the problem that you
>are seeing is unclear.
I disagree. RAISERROR might have been implemented because of TSQL
compatibility, but is is valuable in WATCOM syntax nonetheless: it
offers a more flexible alternative to SIGNAL... which, IMO, sucks :)
It also comes with its own EVENT type, and CREATE EVENT is about as
far from TSQL as you can get.
And ROLLBACK TRIGGER ... WITH RAISERROR is doubly cool.
I am also not sure about "unexpected results"... if behavior changes
without a notation in the "Behavior Changes" section of the Help, I'd
call that a bug.
OK, now I'm done... :)
Breck The Impaler
Alexander Goldun
> I get the raiseerror in MS Access 2003 (11.6355.6568) SP1 running
> against ASA9.0.2.3267.
>
> I would still recommend not mixing dialects. Look at exception handling
> for this.
After replacing raiserror string to "signal myexception" I've got
exception in Access too. But sqlexplorer via bde+odbc still retun value
without any error messages.
Breck Carter [Team iAnywhere]
database, calling the same function with the same owner (dba)?
You could add MESSAGE TO CONSOLE statements to be sure:
CREATE FUNCTION DBA.TestBug3249()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
DECLARE return_name INTEGER;
SET return_name=321;
MESSAGE STRING ( 'CONTINUE_AFTER_RAISERROR = ',
CONNECTION_PROPERTY ( 'CONTINUE_AFTER_RAISERROR' ) )
TO CONSOLE;
raiserror 99999 'What is this?';
SET return_name=123;
MESSAGE STRING ( 'return_name = ',
return_name )
TO CONSOLE;
RETURN return_name;
END
Breck
On 9 Mar 2006 08:43:56 -0800, Alexander Goldun <alex...@spamtest.ru>
wrote:
--
Chris Keating(iAnywhere Solutions)
Alexander Goldun
> Just a question: Are you absolutely sure you are connecting to same
> database, calling the same function with the same owner (dba)?
Absolutely. If you doubt I can post here unloaded database schema. Or I
can step-by-step explain process beginning from creating empty DB.
I've tried it on 4 several servers. 3249 and 3267 on my computer, and
3249, 3228, 3137 on other comps. Only 3137 works properly
And I've tried to connect to DB from another computer to exclude
possibility of troubles with my OS, hardware and local software
> You could add MESSAGE TO CONSOLE statements to be sure:
I've added to be sure as you wrote. Each execution of call or select
adds only one string to console:
CONTINUE_AFTER_RAISERROR = Off
Alexander Goldun
> I've tried it on 4 several servers. 3249 and 3267 on my computer, and
> 3249, 3228, 3137 on other comps. Only 3137 works properly
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
Konstantinov Alex (ASCRUS)
---------------------------
SET OPTION PUBLIC.CONTINUE_AFTER_RAISERROR = 'OFF';
CREATE FUNCTION DBA.TestBug()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
DECLARE return_name INTEGER;
SET return_name=321;
raiserror 99999 'What is this?';
SET return_name=123;
RETURN return_name;
END;
CREATE PROCEDURE DBA.TestBug_Proc()
BEGIN
SELECT DBA.TestBug() AS TestValue
FROM Dummy;
// Must be exit (since CONTINUE_AFTER_RAISERROR = 'OFF' and raiserror in
function)
MESSAGE '!!! Ooops !!!' TO CLIENT;
END;
CALL DBA.TestBug_Proc();
---------------------------
results pane in ISQL:
have empty result set (not correct)
messages pane in ISQL:
a.. Execution time: 0.046 seconds
b.. Execution time: 0 seconds
c.. !!! Ooops !!!
d.. Execution time: 5.657 seconds
Resume: "raiserror" in UDF not correct act with option
CONTINUE_AFTER_RAISERROR = 'OFF'
P.S. Sorry for my bad english, but want do correct this error :)
Alexander Goldun
without mixing of dialects!!!
I used pure dbisql, but jdbc driver was not jConnect 5. I selected
JDBC-ODBC bridge on "advanced" tab of connect dialog.
I made some modifications
SET OPTION PUBLIC.CONTINUE_AFTER_RAISERROR = 'OFF';
CREATE FUNCTION DBA.TestBug()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
DECLARE myexception EXCEPTION
FOR SQLSTATE '99999';
DECLARE return_name INTEGER;
SET return_name=321;
-- raiserror 99999 'What is this?';
MESSAGE STRING ( 'CONTINUE_AFTER_RAISERROR = ',
CONNECTION_PROPERTY ( 'CONTINUE_AFTER_RAISERROR' ) )
TO CONSOLE;
SIGNAL myexception;
SET return_name=123;
RETURN return_name;
END;
CREATE PROCEDURE DBA.TestBug_Proc()
BEGIN
SELECT DBA.TestBug() AS TestValue
FROM Dummy;
-- Must be exit (since CONTINUE_AFTER_RAISERROR = 'OFF' and
raiserror in function)
MESSAGE '!!! Ooops !!!' TO CONSOLE;
END;
CALL DBA.TestBug_Proc();
Execution of this code from dbisql via JDBC-ODBC bridge driver on ASA
9.0.2.3267 adds to console two strings:
CONTINUE_AFTER_RAISERROR = Off
!!! Ooops !!!
Please, let me know, is it correct?
Alexander Goldun
> I can't reproduce the problem in dbisqlc or dbisql.
As shown below, it reproducable even in dbisql.
P.S. It is serious ocasion for me to begin migration from BDE at last. I
want to do it for a long time. But it may be a great work...
Chris Keating(iAnywhere Solutions)
database option CONTINUE_AFTER_RAISERROR. This option is a TSQL option
that addresses behaviour on encountering a RAISERROR. You have no
raiserror and as such this option has absolutely no role in the logic
you presented.
Konstantinov Alex (ASCRUS)
"Chris Keating(iAnywhere Solutions)"
<NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> wrote in message
news:4411bb64$1@forums-2-dub...
> You are still mixing dialects -- except this time is related to the
> database option CONTINUE_AFTER_RAISERROR. This option is a TSQL option
> that addresses behaviour on encountering a RAISERROR. You have no
> raiserror and as such this option has absolutely no role in the logic you
> presented.
>
Chris - really operator "SIGNAL" must break performing the procedure
"TestBug_Proc", but procedure do continues and run next operator "MESSAGE".
Chris Keating(iAnywhere Solutions)
exception returned by the function.
See
ASA SQL User's Guide
Using Procedures, Triggers, and Batches
Errors and warnings in procedures and triggers
Using exception handlers in procedures and triggers
Chris Keating(iAnywhere Solutions)
should not be displayed in the exception. It appears that this could be
an issue with TestBug() being a function.
Alexander Goldun
> You are still mixing dialects
Where?
Chris Keating(iAnywhere Solutions)
the option only is meaningful with the tsql RAISERROR.
I think I may have reproduced this behaviour.
To workaround this issue, use SET to get the result of the function
rather than a SELECT.
Alexander Goldun
> To workaround this issue, use SET to get the result of the function
> rather than a SELECT.
It's convenient only within other procedures or functions. But initially
I've found this problem when retrieving values of function in my app
with simple "select funcname(param)" statements. As I shown earlier
using SIGNAL instead of RAISERROR doesn't save me from problem.
I agree that BDE is ancient enough technology, but it worked properly
whit the same technologies from sa 5.5 till ASA 9.0.2.3137 and suddenly
stop working in 9.0.2.3249.
Have I forced to migrate to other database components or development
tools? More simple way is downgrading ASA.
However I don't believe that only me using Delphi 6 and BDE+ODBC to
develop database applications with ASA and this trouble significant only
for me. Pay attention, please, this trouble is serious enough because it
can make difficult-to-locate errors! I've found it accidentally by
disclosing suspicious prices in invoice after filling from pricelist
instead of getting error message "inapplicable currency of document". I
had luck to find it on development installation.
Can anyone say me finally, is it bug or not? Or may be it's bug of
BDE/ODBC which not appeared earlier? Will it be corrected in future
EBF's or this behaviour change is irreversible? I've asked people on
other forums to reproduce this on Delphi 6 or 7 - they confirmed
existing of trouble.
Chris Keating(iAnywhere Solutions)
submitted) but it does appear to be a defect.
Alexander Goldun
> I reserve the final call to engineering (a engineering case has been
> submitted) but it does appear to be a defect.
I've just downloaded 9.0.2.3288 - the same bug.
Need I make new case about this problem?
Chris Keating(iAnywhere Solutions)
report a bug service. Please provide a test case in Delphi that
demonstrates the problem.
The behaviour that is seen in DBISQL is simply the result of an IMPLIED
RESUME when the Show Multiple Resultsets option is enabled.
Alexander Goldun
> I would recommend that you open a technical support case or use the
> report a bug service. Please provide a test case in Delphi that
> demonstrates the problem.
Ok, I'll do it. FYI jasper doesn't have this problem.
> The behaviour that is seen in DBISQL is simply the result of an IMPLIED
> RESUME when the Show Multiple Resultsets option is enabled.
I can't reproduce this behaviour in dbisql from latest ebf.
Alexander Goldun
> Chris Keating(iAnywhere Solutions) пишет:
>> I reserve the final call to engineering (a engineering case has been
>> submitted) but it does appear to be a defect.
>
> I've just downloaded 9.0.2.3288 - the same bug.
2 years passed.... Migration from BDE to ADO performed. RAISERROR
replaced by SIGNAL. But this bug still not fixed!
ASA 9.0.2.3643
Alexander Goldun
>> Chris Keating(iAnywhere Solutions) wrote:
>>> I reserve the final call to engineering (a engineering case has been
>>> submitted) but it does appear to be a defect.
>>
>> I've just downloaded 9.0.2.3288 - the same bug.
>
> 2 years passed.... Migration from BDE to ADO performed. RAISERROR
> replaced by SIGNAL. But this bug still not fixed!
>
> ASA 9.0.2.3643
This bug is fully reproducable!!!! May I hope to get EBF for it?
Simple way to reproduce: I'm using 9.0.2.3643 installed on Win XP SP2.
Standard asademo9 database and "ASA 9.0 Sample" ODBC data source.
Very simple 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.
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")
ConnectionString1 = "DSN=ASA 9.0 Sample;UID=dba;PWD=sql"
conn1.Open(ConnectionString1)
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. FYI TestSelect.vbs on SQL Anywhere 10.0.1.3685 database work
correctly - displays error message "[sybase][ODBC Driver][SQL anywhere]
user-defined exception signaled"