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 you can set for object are

• Database Connection Object, created using database
• Cursor Object, created using exec or fetch
• Drivermanager Object, created using drivermanager

Not all databases allow you to set all of these properties. If your database does not allow you to set a particular property, you will receive an error message when you try to do so.

Database Connection Object

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

Property	Value	Description
'AutoCommit'	'on'	Database data is written and committed automatically when you run an insert or update function. You cannot use rollback to reverse it and you do not need to use commit because the data is committed automatically.
	'off'	Database data is not committed automatically when you run an insert or update function. In this case, after you run insert or update, you can use rollback to reverse the insert or update. When you are sure the data is correct, follow an insert or update with a commit.
'ReadOnly'	0	Not read only, that is, writable
	1	Read only
'TransactionIsolation'	positive integer	Current transaction isolation level

Note that if you do not run commit after running an update or insert function, 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.

Cursor Object

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

Property	Value	Description
'RowLimit'	positive integer	Sets the RowLimit for fetch. This is an alternative to defining the RowLimit as an argument of fetch. Note that the behavior of fetch when you define RowLimit using set differs depending on the database.

Drivermanager Object

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

Property	Value	Description
'LoginTimeout'	positive integer	Sets the logintimeout value for the set of loaded database drivers as a whole.

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 uses set to define the RowLimit. It establishes a JDBC connection, retrieves all data from the EMP table, sets the RowLimit to 5, and uses fetch with no arguments to retrieve the data.

Only five rows of data are returned by fetch.

• 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 seen above, the RowLimit property of curs is now 5 and the Data property is 5x8 cell, meaning five rows of data were returned.

For the database in this example, the RowLimit acts as the maximum number of rows you can retrieve. Therefore, if you run the fetch function again, no data is returned.

Example 2--Set AutoCommit Flag to On for Connection

This example shows a database update when the AutoCommit flag is on. First 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 it.

• set(conn, 'AutoCommit', 'on');
  get(conn, 'AutoCommit')
  
  ans =
  on
  

Insert data, cell array exdata, into the column names colnames, of the Growth table.

• insert(conn, 'Growth', colnames, exdata)
  

The data is inserted and committed.

Example 3--Set AutoCommit Flag to Off for Connection 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 conn.

• set(conn, 'AutoCommit', 'off');
  

Insert data, cell array exdata, into the column names colnames, of the Avg_Freight_Cost table.

• insert(conn, 'Avg_Freight_Cost', colnames, exdata)
  

Commit the data.

• commit(conn)
  

Example 4--Set AutoCommit Flag to Off for Connection 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 conn.

• set(conn, '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 exdata.

• update(conn, 'Avg_Freight_Weight', colnames, exdata, 
  whereclause)
  

The data was written but not committed.

Roll back the data.

• rollback(conn)
  

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

Example 5--Set LoginTimeout for Drivermanager Object

In this example, create a drivermanager object dm, and set the LoginTimeout value to 3 seconds. Type:

• dm = drivermanager;
  set(dm,'LoginTimeout',3);
  

To verify the result, type

• logintimeout
  

MATLAB returns

• ans =
       3
  

See Also

database, drivermanager, exec, fetch, get, insert, logintimeout, ping, update

rsmd

setdbprefs

© 1994-2005 The MathWorks, Inc.