Database Toolbox™

set

Set properties for database, cursor, or drivermanager object

Syntax

set(object, 'property', value) set(object)

Description

set(object, 'property', value) sets the value of property to value for the specified object.
set(object) displays all properties for object.

Allowable values for object are:

Database Connection Objects, created using database
Cursor Objects, created using exec or fetch (cursor.fetch)
Drivermanager Objects, created using drivermanager.

You cannot set all of these properties for all databases. You receive an error message when you try to set a property that the database does not support.

Database Connection Objects

The allowable values for property and value for a database connection object appear in the following table.

Property	Value	Description
`'AutoCommit'`	`'on'`	Database data is written and automatically committed when you run `fastinsert`, `insert`, or `exec`. You cannot use `rollback` to reverse this process.
`'AutoCommit'`	`'off'`	Database data is not committed automatically when you run `fastinsert`, `insert`, or `update`. Use `rollback` to reverse this process. When you are sure that your data is correct, use the `commit`function to commit it to the database.
`'ReadOnly'`	`0`	Not read-only; that is, writable
`'ReadOnly'`	`1`	Read-only
`'TransactionIsolation'`	positive integer	Current transaction isolation level

Note For some databases, if you insert data and then close the database connection without having committed the data to the database, the data gets committed automatically. Your database administrator can tell you whether your database behaves this way.

Cursor Objects

The allowable property and value for a cursor object appear in the following table.

Property	Value	Description
`'RowLimit'`	positive integer	Sets the `RowLimit` for `fetch`. Specify this property instead of passing `RowLimit` as an argument to the `fetch` function. When you define `RowLimit` for`fetch` by using `set`, `fetch` behaves differently depending on what type of database you are using.

Drivermanager Objects

The allowable property and value for a drivermanager object appear in the following table.

Property	Value	Description
`'LoginTimeout'`	positive integer	Sets the `logintimeout` value for all loaded database drivers.

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

help cursor/set
help database/set
help drivermanager/set

Examples

Example 1 — Set RowLimit for Cursor

This example does the following:

Establishes a JDBC connection to a data source
Runs fetch to retrieve data from the table EMP,
Sets RowLimit to 5

Run the command:

conn=database('orcl','scott','tiger',...
	'oracle.jdbc.driver.OracleDriver',...
	'jdbc:oracle:thin:@144.212.123.24:1822:');
curs=exec(conn, 'select * from EMP');
set(curs, 'RowLimit', 5)
curs=fetch(curs)
curs =
     Attributes: []
           Data: {5x8 cell}
 DatabaseObject: [1x1 database]
       RowLimit: 5
       SQLQuery: 'select * from EMP'
        Message: []
           Type: 'Database Cursor Object'
      ResultSet: [1x1 oracle.jdbc.driver.OracleResultSet]
         Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
      Statement: [1x1 oracle.jdbc.driver.OracleStatement]
          Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

As displayed above, the RowLimit property of curs is 5 and the Data property is 5x8 cell, indicating that fetch returned five rows of data.

In this example, RowLimit limits the maximum number of rows you can retrieve. Therefore, rerunning the fetch function returns no data.

Example 2 — Set the AutoCommit Flag to On

This example shows what happens when you run a database update function on a database whose AutoCommit flag is set to on.

Determine the status of the AutoCommit flag for the database connection conn.
```
get(conn, 'AutoCommit')

ans =
off
```
The flag is off.

Set the flag status to on and verify its value.

set(conn, 'AutoCommit', 'on');
get(conn, 'AutoCommit')

ans =
on

Insert a cell array exdata into column names colnames in the table Growth.
```
fastinsert(conn, 'Growth', colnames, exdata)
```
The data is inserted and committed to the database.

Example 3 — Set the AutoCommit Flag to Off and Commit Data

This example shows the results of running fastinsert and commit to insert and commit data into a database whose AutoCommit flag is off.

First set the AutoCommit flag to off for database connection conn.
```
set(conn, 'AutoCommit', 'off');
```
Insert a cell array exdata into the column names colnames in the table Avg_Freight_Cost.
```
fastinsert(conn, 'Avg_Freight_Cost', colnames, exdata)
```
Commit the data to the database.
```
commit(conn)
```

Example 4 — Set the AutoCommit Flag to Off and Roll Back Data

This example runs update to insert data into a database whose AutoCommit flag is off. It then uses rollback to roll back the data.

Set the AutoCommit flag to off for database connection conn.
```
set(conn, 'AutoCommit', 'off');
```
Update the data in colnames in the table Avg_Freight_Weight table, for the record selected by whereclause, with data from the cell array exdata.
```
update(conn, 'Avg_Freight_Weight', colnames, exdata, 
whereclause)
```
Roll back the data.
```
rollback(conn)
```
The data in the table is now as it was before you ran update.

Example 5 — Set the LoginTimeout for a Drivermanager Object

Create a drivermanager object dm and set its LoginTimeout value to 3 seconds.
```
dm = drivermanager;
set(dm,'LoginTimeout',3);
```
Verify this result.
```
logintimeout
ans =
     3
```

set