SQL Anywhere database - Database Migration Error

Author

Database Migration Error

M. Sawalski

2005-04-09, 8:24 pm

I recently migrated a database (unloaded, created a new database then loaded
it all back into new database) from SQL Anywhere 5.0 to SQL Anywhere 9. The
migration was a success, and we are back up and running. There is, however,
one critical area that provides some updates to records with info that we
use over the weekend. When we tried to execute this routine, the program (a
Powerbuilder 10 application) returned the following error message:

DATABASE ERROR: Error performing UPDATE1
-1
SQLSTATE=S1009
[Sybase][ODBC Driver][Adaptive Server Anywhere]operation on a read-only
cursor

Then it returns another error message:

DATBASE ERROR: Error performing CLOSE 0

Any idea what this means, and what happened during the migration? Any idea
on what I should do to fix this? This was working fine before we migrated.

Thanks!

Greg Fenton

2005-04-09, 8:24 pm

M. Sawalski wrote:
> [Sybase][ODBC Driver][Adaptive Server Anywhere]operation on a read-only
> cursor
>
> Any idea what this means, and what happened during the migration? Any idea
> on what I should do to fix this? This was working fine before we migrated.
>

This is an application error, not a database error. Your application is
attempting to do an UPDATE on a cursor that it has open, but that cursor
does not support updates (it is "read-only").

Some of ASA's syntax has been tightened up between 5.x and 9.x. In some
cases it is because the SQL definitions themselves may have changed
(though the query team might tell me I'm off on this one).

So if your application has not been changed, it is likely that it is
using the wrong type of cursor (which was likely wrong back in 5.x but
the engine let you do it anyways).

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-04-10, 8:23 pm

Try changing this option:

SET OPTION PUBLIC. ANSI_UPDATE_CONSTRAI
NTS = 'OFF';

This option appeared in version 7; the default for old (un-reloaded)
databases is 'OFF' which is good, but the default for new databases is
'CURSORS' which applies some arcane ANSI limitations on updating
cursors. It frequently pounds people like you, who upgrade "the right
way" (unload-dbinit-reload). The folks at iAnywhere *rarely* make
mistakes when they pick default values, but this is one of them.

Breck

On 9 Apr 2005 13:39:31 -0700, "M. Sawalski" <msawalski@wi.rr.com>
wrote:

>I recently migrated a database (unloaded, created a new database then loaded
>it all back into new database) from SQL Anywhere 5.0 to SQL Anywhere 9. The
>migration was a success, and we are back up and running. There is, however,
>one critical area that provides some updates to records with info that we
>use over the weekend. When we tried to execute this routine, the program (a
>Powerbuilder 10 application) returned the following error message:
>
>DATABASE ERROR: Error performing UPDATE1
>-1
>SQLSTATE=S1009
>[Sybase][ODBC Driver][Adaptive Server Anywhere]operation on a read-only
>cursor
>
>Then it returns another error message:
>
>DATBASE ERROR: Error performing CLOSE 0
>
>Any idea what this means, and what happened during the migration? Any idea
>on what I should do to fix this? This was working fine before we migrated.
>
>Thanks!
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

M. Sawalski

2005-04-11, 7:23 am

Thanks Breck. This is probably a dumb question, but where do I find this
option?

-Mike-

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:75ii511f9uv6puu
ej1s72ms0a75a2us35n@
4ax.com...
> Try changing this option:
>
> SET OPTION PUBLIC. ANSI_UPDATE_CONSTRAI
NTS = 'OFF';
>
> This option appeared in version 7; the default for old (un-reloaded)
> databases is 'OFF' which is good, but the default for new databases is
> 'CURSORS' which applies some arcane ANSI limitations on updating
> cursors. It frequently pounds people like you, who upgrade "the right
> way" (unload-dbinit-reload). The folks at iAnywhere *rarely* make
> mistakes when they pick default values, but this is one of them.
>
> Breck
>
> On 9 Apr 2005 13:39:31 -0700, "M. Sawalski" <msawalski@wi.rr.com>
> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Chris Keating $iAnywhere Solutions$

2005-04-11, 7:23 am

It is discussed in the documentation.

To set the option, simply connect to the database (i.e. from dbisql) and
issue the statement:

SET OPTION PUBLIC. ANSI_UPDATE_CONSTRAI
NTS = 'OFF';

--

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 CaseXpress to report bugs http://casexpress.sybase.com

********************
********************
********************
*****************

"M. Sawalski" <msawalski@wi.rr.com> wrote in message
news:4259de74$1@foru
ms-2-dub...
> Thanks Breck. This is probably a dumb question, but where do I find this
> option?
>
> -Mike-
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> message news:75ii511f9uv6puu
ej1s72ms0a75a2us35n@
4ax.com...
>
>

M. Sawalski

2005-04-13, 7:23 am

Chris:

One last question. Will this change be permanent? Stupid question, but if
I shut down the database service, will this option stay the way we are
setting it below?

Thanks!

"Chris Keating (iAnywhere Solutions)" < FightSpam_keating@iA
nywhere.com>
wrote in message news:4259e684@forums
-2-dub...
> It is discussed in the documentation.
>
> To set the option, simply connect to the database (i.e. from dbisql) and
> issue the statement:
>
> SET OPTION PUBLIC. ANSI_UPDATE_CONSTRAI
NTS = 'OFF';
>
>
> --
>
> 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 CaseXpress to report bugs http://casexpress.sybase.com
>
> ********************
********************
********************
*****************
>
> "M. Sawalski" <msawalski@wi.rr.com> wrote in message
> news:4259de74$1@foru
ms-2-dub...
>
>

Sponsored Links