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:

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.

PropertyValueDescription

'AutoCommit'

'on'

Database data is written and automatically committed when you run fastinsert, insert, or exec. You cannot use rollback to reverse this process.

'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

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 committing 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.

PropertyValueDescription

'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.

PropertyValueDescription

'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.

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]

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.

  1. Determine the status of the AutoCommit flag for the database connection conn.

    get(conn, 'AutoCommit')
    
    ans =
    off
    

    The flag is off.

  2. Set the flag status to on and verify its value.

    set(conn, 'AutoCommit', 'on');
    get(conn, 'AutoCommit')
    
    ans =
    on
    
  3. 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.

  1. First set the AutoCommit flag to off for database connection conn.

    set(conn, 'AutoCommit', 'off');
    
  2. Insert a cell array exdata into the column names colnames in the table Avg_Freight_Cost.

    fastinsert(conn, 'Avg_Freight_Cost', colnames, exdata)
    
  3. 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.

  1. Set the AutoCommit flag to off for database connection conn.

    set(conn, 'AutoCommit', 'off');
    
  2. Update the data in colnames in the 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)
    
  3. 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

  1. Create a drivermanager object dm and set its LoginTimeout value to 3 seconds.

    dm = drivermanager;
    set(dm,'LoginTimeout',3);
    
  2. Verify this result.

    logintimeout
    ans =
         3
    
Was this topic helpful?