@DbCommand (ODBC data source) (Formula Language)

Syntax

@DbCommand( "ODBC" : cache ; data_source ; user_ID1 : user_ID2 ; password1 : password2 ; command_string : null_handling )

Parameters

"ODBC"

String argument. Indicates that you are accessing an ODBC data source.

cache

String argument. Optional. In the initial lookup, specify either "" or "NoCache." If the former case, subsequent lookups to the same data source, you can specify "ReCache."

• "" (null string) caches the results of the lookup. Each subsequent lookup to the same location (within the same Domino® session and so long as the database executing this lookup remains open) reuses that data until you specify "ReCache." Cached data improves performance and may be a good choice for stable data.
• "ReCache" refreshes the cache with the latest data from the database. If you want to ensure that this lookup gets the latest information, specify this option.
  Note: "ReCache" is new with Release 6.
• "NoCache" gets the results of the lookup from the database; no cache is used. If you want to ensure that Domino® retrieves the latest information for every lookup, specify this option

Text. The name of the external data source being accessed. A data source indicates the location of one or more database tables. See "Specifying the data source."

user_ID1 : user_ID2

Text list. The user IDs needed to connect to the external database. You may need up to two IDs, depending on the DBMS being accessed. See "Specifying IDs and passwords."

password1 : password2

Text list. The passwords required by the user ID(s). See "Specifying IDs and passwords."

command_string

Text. An SQL statement, command statement, or name of a procedure to be executed. See "Specifying a command string."

null_handling

Text. Specifies how null values are treated when the data is retrieved. See "Specifying null handling."

Return value

valuesFound

Text, number, date-time, or a list of these types. The values returned by the command_string. See "Accessing values found."

Specifying the data source

The data source name can contain up to 32 alphanumeric characters.

@DbCommand can access data sources that have already been registered in the ODBC.INI file (or similar registry on platforms other than Windows™).

Specifying IDs and passwords

You only need these arguments if your DBMS requires them.

Instead of storing the IDs in the @DbCommand formula, you can replace them with null strings (""). If an ID is required, the user will be prompted for it. This is useful when you do not want other designers to see IDs, or when you want users to enter their own IDs when accessing external data. However, you must include IDs and passwords in formulas that will run automatically (such as an agent) because these formulas don't prompt for information.

The user IDs and passwords for accessing a data source are required only once per Domino® database session as long as that database remains open. If the user opens another Domino® database and executes a formula that accesses the same data source, the user ID and password will be required again.

Password parameters are necessary only when ID parameters are specified. Like IDs, passwords can either be stored in the @DbColumn formula, or prompted for by substituting the null string. If the database password is null, you can omit it from the formula.

For example, for the full ID/password specification, enter:

• "";"" (two null strings, separated by a semicolon) to specify no ID and password, or to prompt for both
• "user_ID1";"password1" to specify one user ID and password combination
• "user_ID1":"user_ID2";"password1":"password2" to specify two user ID and password combinations

Specifying the command string

The command_string can be any of the following:

• An SQL statement (it must use the SQL syntax accepted by the back-end DBMS).
• A command statement using the back-end DBMS command language.
• The name of a procedure stored within the back-end DBMS (the procedure contains one or more command strings that are activated when the procedure is called by @DbCommand).

A date-time value must be entered in the format of the database, not that of Domino®; for example, use 1996-01-31-12.00.00 for DB2/2, not 1996-01-31-12:00:00.

Specifying null handling

To control how null values are handled, specify one of the following, appended to the command_string parameter with a colon:

• "Fail" generates this error message if the column of data contains any null values:

  Null values found - canceling @Db function

  No data is returned with the message.

• "Discard" discards the null values, thus shortening the returned list of values. If one or more values are discarded when the @DbCommand formula is executed, you see this message on the status bar:

  Caution: NULL values discarded from @Db list.

• "Replacement value" specifies a replacement value for null values. The replacement value must be a quoted string, but if the column is numeric or date-time, the string must be convertible to that type.
• If your command string includes a sort string argument, the list of values to be returned is sorted before the replacement values are inserted. During sorting, all null values are placed at the beginning or end of the list, depending on the driver. They are not replaced until sorting is complete. This can result in a list that has some values sorted incorrectly.

  If one or more values are replaced when the @DbCommand formula is executed, you see this message on the status bar:

  Caution: NULL value replaced with user-defined value in @Db list.

  Generally, the replacement value should be one that is not likely to appear in the list as valid data; for example, if the column is text, your replacement value might be "***" so that you can easily find those values.

Accessing values found

@DbCommand can return no more than 64KB of data. Use the following equations to determine how much of your data can be returned with @DbCommand.

• For lookups that return text:

  2 + (2 * number of entries returned) + total text size of all entries

  Each text string is limited to 511 bytes; if only one text string is returned, it is limited to 64KB.

• For lookups that return numbers or dates:

  (10 * number of entries returned) + 6

If the user's NOTES.INI file includes the statement

NoExternalApps=1

the @DbCommand formula is disabled. The user will not see an error message; the formula fails to execute.

Usage

@DbCommand is useful for testing a non-equal relationship (such as less-than), or for testing multiple conditions at the same time. To use @DBCommands, pass a command to the back-end database for processing.

For example, to return data from records where:

BALANCE >= 1000.00 and DAYS_OVERDUE > 30

Write the selection statement in SQL, and then use @DbCommand to pass that statement to the DBMS for processing; @DbCommand then returns the requested data.

For Web applications, you can use this function only with the syntax:

@DbCommand("Domino";"ViewNextPage") 

or

@DbCommand("Domino";"ViewPreviousPage")

to create a link to the next or previous page in a view. You cannot use @DbCommand in any other context with Web applications.

Examples

@DbCommand("ODBC";"PUBLISHERS";"dbo":"";"vanilla":"";   
"SELECT au_lname FROM dbo.authors WHERE contract=1 AND state='CA' ")