Table.sqlaction.property

Description

The way data is updated in the database. When the Update method is executed, it can send UPDATE, INSERT, and DELETE SQL statements to the DBMS. You can specify that a stored procedure be used instead of the default SQL statement for each type of data modification.

Applies to

DataWindows

Syntax

Describe and Modify argument:

"DataWindow.Table.sqlaction.property { = value }"

Parameter

Description

sqlaction

The SQL statement that would ordinarily be executed as part of a database update.

Values are:

  • UPDATE

  • INSERT

  • DELETE

property

A property for sqlaction. Properties and appropriate values are listed in the table below.

value

The value to be assigned to the property.

Property for Table

Value

Arguments

A string specifying the arguments used in the stored procedure. The string takes this format:

("argname", valuetype { =("valuesrc" {, datasrc, paramtype } )

Argname is the name of the stored procedure parameter.

Valuetype is one of the keywords described below. Datasrc and paramtype apply to the COLUMN keyword.

Valuesrc is the column, computed field, or expression that produces the value to be passed to the stored procedure.

Method

A string specifying the name of the stored procedure. The stored procedure is used only if the value of Type is SP.

Type

Specifies whether the database update is performed using a stored procedure.

Values are:

  • SP – The update is performed using a stored procedure.

  • SQL – The update is performed using standard SQL syntax (default).

Keyword for valuetype

Description

COLUMN

The argument value will be taken from the table and column named in valuesrc. Valuesrc has the form:

"tablename.column"

For COLUMN, you must also specify whether the data is the new or original column value. Values for datasrc are:

  • NEW The new column value that is being sent to the database.

  • ORIG The value that the DataWindow originally read from the database.

You can also specify the type of stored procedure parameter. Values for paramtype are:

  • IN (Default) An input parameter for the procedure.

  • OUT An output parameter for the procedure. The DataWindow will assign the resulting value to the current row and column (usually used for identity and timestamp columns).

  • INOUT An input and output parameter.

A sample string for providing a column argument is:

("empid", COLUMN=("employee.empid", ORIG,
IN))

COMPUTE

The computed field named in valuesrc is the source of the value passed to the stored procedure.

A sample string for providing a computed field argument is:

("newsalary", COMPUTE=("salary_calc"))

EXPRESSION

The expression specified in valuesrc is evaluated and passed to the stored procedure.

A sample string for providing an expression argument is:

("dept_name", EXPRESSION=("LookUpDisplay(dept_id)"))

UNUSED

No value is passed to the stored procedure.

Usage


In the painter

Set the values using Rows>Stored Procedure Update. Select the tab page for the SQL command you want to associate with a stored procedure.


In code

If you enable a DataWindow object to use stored procedures to update the database when it is not already using stored procedures, you must change Type to SP first. Setting Type ensures that internal structures are built before you set Method and Arguments. If you do not change Type to SP, then setting Method or Arguments will fail.

When the values you specify in code are nested in a longer string, you must use the appropriate escape characters for quotation marks.

Examples

Example 1

Each is all on one line:

[Visual Basic]
dw_x.Describe("DataWindow.Table.Delete.Method")

Example 2

dw_x.Describe("DataWindow.Table.Delete.Arguments")

Example 3

dw_x.Modify("DataWindow.Table.Delete.Type=SP")

Example 4

dw_x.Modify("DataWindow.Table.Delete.Arguments=
		((~"id~", COLUMN=(~"department.dept_id!~", ORIG)))") 

Example 5

dw_x.Modify("DataWindow.Table.Delete.Method=
		~"spname~"")