SQL Anywhere database - set chained off and commit

Author

set chained off and commit

Willie van Schalkwyk

2005-06-20, 7:23 am

Hi
On 20/05 Gregg Vine logged a query regarding autocommit.
The problem was as follow:
I am using Visual FoxPro Cursor adaptor class in order to connect via ODBC
to ASA (9.0.2.3044).
I would request data, modify the data and save. When looking at ASA the
changes are there. When I close the connection, the changes are reverted.

I fixed this problem by issuing the following command: "set Chained off" in
the Interactive SQL tool

I subsequently rebooted the server and the problem reappeared.

My first question, is there a way to set the chained setting so it will be
remembered automatically?
My second question, Is there another way to enforce changes made to data?

Thanks
--------------------------------------------------------------------------------
Willie van Schalkwyk
Managing Director
USP Designs (Pty) Ltd

E: willie@cyberprop.com
T: +27 11 955 9100
S: 086 112 9237
F: +27 11 955 9110
W: www.cyberprop.com
W: www.cyberagent.co.za
W: www.cyberbonds.co.za

USP Designs (Pty) Ltd is Proudly South African!

This message and any attachments are confidential and intended solely for
the addressee. Any unauthorized use, alteration or dissemination is
prohibited. USP Designs (Pty) Ltd. accepts no liability whatsoever for any
loss, whether it be direct, indirect or consequential, arising from
information made available and actions resulting there from.

Breck Carter [TeamSybase]

2005-06-20, 7:23 am

This will change the default:

SET OPTION PUBLIC.CHAINED = 'OFF';

FWIW this is an unusual setting; I assume you don't have any
multi-update business operations that need to be treated as single
transactions. The alternative is to execute explicit COMMIT
statements.

Breck

On 20 Jun 2005 02:52:57 -0700, "Willie van Schalkwyk"
<willie@cyberprop.com> wrote:

>Hi
>On 20/05 Gregg Vine logged a query regarding autocommit.
>The problem was as follow:
>I am using Visual FoxPro Cursor adaptor class in order to connect via ODBC
>to ASA (9.0.2.3044).
>I would request data, modify the data and save. When looking at ASA the
>changes are there. When I close the connection, the changes are reverted.
>
>I fixed this problem by issuing the following command: "set Chained off" in
>the Interactive SQL tool
>
>I subsequently rebooted the server and the problem reappeared.
>
>My first question, is there a way to set the chained setting so it will be
>remembered automatically?
>My second question, Is there another way to enforce changes made to data?
>
>
>Thanks
>--------------------------------------------------------------------------------
>Willie van Schalkwyk
>Managing Director
>USP Designs (Pty) Ltd
>
>E: willie@cyberprop.com
>T: +27 11 955 9100
>S: 086 112 9237
>F: +27 11 955 9110
>W: www.cyberprop.com
>W: www.cyberagent.co.za
>W: www.cyberbonds.co.za
>
>USP Designs (Pty) Ltd is Proudly South African!
>
>This message and any attachments are confidential and intended solely for
>the addressee. Any unauthorized use, alteration or dissemination is
>prohibited. USP Designs (Pty) Ltd. accepts no liability whatsoever for any
>loss, whether it be direct, indirect or consequential, arising from
>information made available and actions resulting there from.
>

--
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

Willie van Schalkwyk

2005-06-20, 9:23 am

Thanks Breck

We use Visual FoxPro 9 and it normally takes care of the commit internally.
I believe it is possible to overwrite this but I have used it in to many
places at this stage. Business rules are managed by FoxPro and only the
final result is updated to ASA so if I would update ASA it should be final
and committed.

What whould I use to see what the current settings are?

Regards
--

--------------------------------------------------------------------------------
Willie van Schalkwyk
Managing Director
USP Designs (Pty) Ltd

E: willie@cyberprop.com
T: +27 11 955 9100
S: 086 112 9237
F: +27 11 955 9110
W: www.cyberprop.com
W: www.cyberagent.co.za
W: www.cyberbonds.co.za

USP Designs (Pty) Ltd is Proudly South African!

This message and any attachments are confidential and intended solely for
the addressee. Any unauthorized use, alteration or dissemination is
prohibited. USP Designs (Pty) Ltd. accepts no liability whatsoever for any
loss, whether it be direct, indirect or consequential, arising from
information made available and actions resulting there from.
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:0i5db1td88pdtrq
l0i4goon8827a8fun0u@
4ax.com...
> This will change the default:
>
> SET OPTION PUBLIC.CHAINED = 'OFF';
>
> FWIW this is an unusual setting; I assume you don't have any
> multi-update business operations that need to be treated as single
> transactions. The alternative is to execute explicit COMMIT
> statements.
>
> Breck
>
> On 20 Jun 2005 02:52:57 -0700, "Willie van Schalkwyk"
> <willie@cyberprop.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

Breck Carter [TeamSybase]

2005-06-20, 1:23 pm

SELECT CONNECTION_PROPERTY ( 'CHAINED' ) will tell you what the
current connection is using.

Caveat: If you run that in ISQL, it will tell what the ISQL connection
is using. If you really want to know what setting is being used by
your application at some point in your code, you should run that
SELECT at that point in your code and display the result somehow (it's
a string).

Be aware that if you are running in unchained mode, and the "final
result" consists of two statements UPDATE this and UPDATE that, and
the first one works but the second one fails, the first change will be
permanent in the database but the second one will be undone.

That may or may not ever happen to you, and if it does, it may or may
not be a big deal.

Breck

On 20 Jun 2005 06:25:30 -0700, "Willie van Schalkwyk"
<willie@cyberprop.com> wrote:

>Thanks Breck
>
>We use Visual FoxPro 9 and it normally takes care of the commit internally.
>I believe it is possible to overwrite this but I have used it in to many
>places at this stage. Business rules are managed by FoxPro and only the
>final result is updated to ASA so if I would update ASA it should be final
>and committed.
>
>What whould I use to see what the current settings are?
>
>Regards

--
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

Sponsored Links