set (Database Toolbox User's Guide)

set	Examples See Also

Set autocommit flag for database or rowlimit for fetch

Syntax

set(cursor, 'rowlimit', value)
set(conn, 'autocommit', 'value')

Description

set(cursor, 'rowlimit', value)

sets the rowlimit for fetch to value, where value is a positive integer. This is an alternative to setting the rowlimit as an argument of fetch. You can use set to define rowlimit for JDBC connections, but not for ODBC connections; for ODBC connections, define rowlimit as an argument of fetch.

set(conn, 'autocommit', 'value')

assigns a value to the autocommit flag for the database accessed via the connection conn, where value is on or off.

If you set value to on, the database data is written and committed automatically when you run insert or update. You cannot use an SQL rollback to reverse it and you do not need to use an SQL commit because the data is already committed.
If you set value to off, the database data is not committed automatically when you run insert or update. In this case, after you run insert or update, you can use an SQL rollback to reverse the insert or update. When you are sure the data is correct, follow an insert or update with an SQL commit. This commits the data in the database, after which you cannot use an SQL rollback to reverse it.
Perform an SQL commit or rollback using exec. Note that if you do not run an SQL commit after update or insert, and then close the database connection using close, the data usually is committed automatically at that time. Your database administrator can tell you how your database deals with this.

For command line help on set, use the overloaded methods:

help cursor/set.m
help database/set.m

Limitations

Microsoft Access does not use SQL commands and therefore has no rollback option. Setting the autocommit flag for a connection has no effect when you use insert or update to export data to a Microsoft Access database. The autocommit flag is always reported as on, and the data is always automatically written and committed.

Examples

Example 1 - Set rowlimit

This example uses set to define the rowlimit. It establishes a JDBC connection, retrieves all data from the emp table, sets the rowlimit to one, and uses fetch with no arguments to retrieve the data. Only one row of data is returned by fetch as you can see from the result of the rows command.

conn=database('oracle','scott','tiger',...
     'oracle.jdbc.driver.OracleDriver','jdbc:oracle:oci7:')
curs=exec(conn, 'select * from emp')
set(curs, 'rowlimit', 1)
fetch(curs)
rows(curs)

ans =
     1

Run fetch again without a rowlimit argument to retrieve the next row of data.

Example 2 - Set autocommit flag to on

This example shows a database update when the autocommit flag is on. First determine the status of the autocommit flag for the database connection conn1:

get(conn1, 'autocommit')

ans =
off

The flag is off. Set the flag status to on and verify it:

set(conn1, 'autocommit', 'on');
get(conn1, 'autocommit')

ans =
on

Insert data, cell array G, into the column names ColNames of the Growth table:

insert(conn1, 'Growth', ColNames, G)

The data is inserted and committed.

Example 3 - Set autocommit flag to off and commit data

This example shows a database insert when the autocommit flag is off and the data is then committed. First set the autocommit flag to off for database connection connA:

set(connA, 'autocommit', 'off');

Insert data, cell array A, into the column names ColNames of the Avg_Freight_Cost table:

insert(connA, 'Avg_Freight_Cost', ColNames, A)

Commit the data:

cursorset = exec(connA,'commit')

Example 4 - Set autocommit flag to off and roll back data

This example shows a database update when the autocommit flag is off and the data is then rolled back. First set the autocommit flag to off for database connection connB:

set(connB, 'autocommit', 'off');

Update the data in the column names specified by ColNames of the Avg_Freight_Weight table for the record selected by whereClause using data contained in cell array B:

update(connB, 'Avg_Freight_Weight', ColNames, B, whereClause)

The data was written but not committed.

Roll back the data:

cursorset = exec(connB,'rollback')

The data in the table is now the same as it was before update was run.