exec (Database Toolbox User's Guide)

exec	Examples See Also

Execute SQL statement and open cursor

Syntax

cursor = exec(conn, 'sqlquery')

Description

cursor = exec(conn, 'sqlquery')

executes a valid SQL statement, sqlquery, against the database connection, conn and opens a cursor. exec returns the cursor structure to the variable, cursor.

Use querytimeout to determine the maximum amount of time for which exec will try to complete the SQL statement.

You can have multiple cursors open at one time.

After opening a cursor, use fetch to import data from the cursor.

A cursor stays open until you close it using the close command. Always close a cursor after you finish using it.

Examples

Example 1 - Select all data from database table

Select all data from the customers table accessed via connA. Assign the variable cursA to the returned cursor structure:

cursA = exec(connA, 'select * from customers')

Example 2 - Select one column of data from database table

Select country data from the customers table accessed via conn. Assign the variable str to the SQL statement and assign curs to the returned cursor:

str = 'select country from customers';
curs = exec(conn, str)

Example 3 - Roll back or commit data exported to database table

Use exec to roll back or commit data after running an insert or an update for which the autocommit flag was off. To roll back data for connect, type:

exec(connect, 'rollback')

To commit the data, type:

exec(connect, 'commit')