Adaptive Server can handle multistatement SQL in a single batch as shown in this example:
my $sth = $dbh->prepare("
insert into publishers (col1, col2, col3) values (10, 12, 14)
insert into publishers (col1, col2, col3) values (1, 2, 4)
insert into publishers (col1, col2, col3) values (11, 13, 15)
");
my $rc = $sth->execute();
If any of these statements fail, sth->execute() returns undef
.
If AutoCommit is on, statements that complete
successfully may have inserted data in the table, which may not
be the result you expect or want.
The Perl driver allows you to prepare multiple statements with one call and execute them with another single call. For example, executing a stored procedure that contains multiple selects returns multiple result sets.
Results of multiple statements prepared with one call are
returned to the client as a single stream of data. Each distinct
set of results is treated as a normal single result set, which means
that the statement handle's fetch() method returns undef
at
the end of each set.
The CT-Lib API ct_fetch() returns CS_END_RESULTS
that
the driver converts to undef
after
the last rows have been retrieved.
The driver allows the application to obtain the result type by checking sth->{syb_result_type}. You can then use the sth->{syb_more_results} statement handle attribute to determine if there are additional result sets still to be returned. The (numerical) value returned by sth->{syb_results_type} is one of:
CS_MSG_RESULT
CS_PARAM_RESULT
CS_CURSOR_RESULT
CS_STATUS_RESULT
CS_COMPUTE_RESULT
CS_ROW_RESULT
Example for multiple result sets:
do {
while($a = $sth->fetch) {
..for example, display data..
}
} while($sth->{syb_more_results});
Sybase recommends that you use this if you expect multiple result sets.
The Perl driver currently does not support cursors using
the ct_cursor() API. Therefore, the
driver does not report CS_CURSOR_RESULT.
There can be multiple active statements on a single database handle by opening a new connection in the $dbh->prepare() method if there is already an active statement handle on this $dbh.
The dbh->{syb_no_child_con} attribute controls whether this feature is on or off. By default, DatabaseHandle is off, which indicates that multiple statement handles are supported. If it is on, multiple statements on the same database handle are disabled.
If AutoCommit is off, multiple statement handles on
a single $dbh are unsupported. This
avoids deadlock problems that may arise. Also, using multiple statement
handles simultaneously provides no transactional integrity, as different
physical connections are used.