sybperl-l Archive

From: "David Owen" <dowen at midsomer dot org>
Subject: Re: newbie : transaction levels
Date: Sep 18 2000 2:30PM

>>>>> "Matthew" == Matthew E Studley  writes:

    Matthew> Having to migrate from Oracle 8 (NT) to Sybase 11.9.2 (Debian
    Matthew> Linux) - we will be writing apps in C and Perl.  Initial
    Matthew> overview of Sybase docs (first time I've seen Sybase was last
    Matthew> week) worries me; default transaction and isolation behaviour
    Matthew> not similar to Oracle.

Having just had to do some work the other way I am similarly worried ,
so it must be just familiarity with our surroundings!

    Matthew> Q1.  Can I reset transaction / isolation behaviour globally
    Matthew> for a user so that all requests / own procs executed by user
    Matthew> have desired behaviour.  Alternately, do I just reset them
    Matthew> with each new db connection?

I am not sure if either the transaction mode or the isolation levels can be
set globally, I don't think so.  Transaction mode can be set for a
particular stored procedure (look at sp_procxmode) or for a particular
session (set chained {on|off}).  Similarly, you can set the isolation level
for a session using set transaction isolation level {0|1|3}, this can then
be overridden on an individual query level by appending "AT ISOLATION {READ
UNCOMMITTED|READ COMMITTED|SERIALIZABLE}" to the select clause.

    Matthew> Q2.  Has anybody got any suggestions for writing Sybase stored
    Matthew> procs that return the equivalent of tables / varrays... so
    Matthew> that an output parameter in my proc call can return a list of
    Matthew> rows through which I can iterate.  I'd rather encapsulate all
    Matthew> db access than give the developers any native SQL to embed in
    Matthew> their code.

Sybase does this differently to Oracle.  By default a query or stored
procedure returns a result set to the calling process, you do not need to
declare a variable of type RSET and pass it in.

$srv->ct_execute(<ct_results($restype)) == CS_SUCCEED) {

    next if($restype == CS_CMD_DONE || $restype == CS_CMD_FAIL ||
            $restype == CS_CMD_SUCCEED);

    while(@dat = $srv->ct_fetch) {

    }
}

is a fairly typical loop within Sybperl.  The outer loop processes queries
within a batch and the inner rows within a particular query.  If you had a
stored proc defined as

create proc my_proc (@col1 char(10)) as
select col1, col2, col3, ...
  from userTable
 where col1 = @col1

you could call that in place of the SQL to get exactly the same result:

$srv->ct_execute(<ct_results($restype)) == CS_SUCCEED) {

    next if($restype == CS_CMD_DONE || $restype == CS_CMD_FAIL ||
            $restype == CS_CMD_SUCCEED);


    while(@dat = $srv->ct_fetch) {

    }
}

All I can really say about moving from Oracle to Sybase is: you lucky lucky
b@$^@&d! 

dowen

-- 
David Owen     Midsomer Consultants Inc.      dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ