Code Name*
@DbCommand(ODBC) | Date*
04/28/2024 | Source (or email address if you prefer)*
Rlatulippe@romac.com
IP address:.3.139.62.103 |
Description*
Given data source information from the ODBC.INI file, uses this information to activate the appropriate ODBC driver. The driver then locates the specified DBMS, passes the specified command to it for processing, and returns the data retrieved by that command. | Type*
Formula | Categories*
(Misc)
|
Implementation:
| Required Client:
| Server:
|
Limitations:
| Comments:
@DbCommand only works with non-Notes databases. You can use it only to retrieve data; @DbCommand can't add, delete, or modify data. @DbCommand is useful for testing a non-equal relationship (such as lessthan), or for testing multiple conditions at the same time. To use @DBCommands, you must pass a command to the backend database for processing. For example, to return data from records where BALANCE >= 1000.00 and DAYS_OVERDUE > 30. You can 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. |
Files/Graphics attachments (if applicable):
Code:
@DbCommand(ODBC : NoCache; data_source; user_ID1 : user_ID2 ; "password1" : "password2"; command_string : null_handling)
Parameters
"ODBC"
Keyword. Indicates that you are accessing an ODBC data source.
"NoCache"
Keyword. Optional. If you want to ensure that Notes retrieves the latest information for every lookup, specify this option, as in ODBC:NoCache. Omit "NoCache" if you want the results of the lookup to be cached, that is, stored for re-use by each subsequent lookup to the same location (within the same Notes session and so long as the database executing this lookup remains open).
If you omit NoCache, you do not have to replace it with anythingthe lookup results are cached automatically, but you can specify "Cache" for readability. See "Specifying NoCache."
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. A SQL statement, command statement, or name of a procedure to be executed. See "Specifiyng 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 NoCache
The decision to use NoCache is based on performance issues. Forcing Notes to connect to the database and retrieve the same information again and again takes time, and slows overall performance. However, if you expect data to be changing on a frequent basis, it's worth the extra time to get uptodate information.
For example, if youre using lookups to a database that stores customer addresses, its generally safe to use caching because you dont expect the addresses to change very often. But if youre using a lookup to a database where stock prices are stored, and that database gets updated hourly, its safer to omit caching and force a new lookup each time.
Note If you use the option button or the check-box user interface for a keywords field, Notes updates the keyword list only when the document is composed or is loaded for editing. If you use the Standard user interface for the list, the keyword list is additionally updated every time the document is recalculated.
Specifying the data source
The data source name can contain up to 32 alphanumeric characters.
@DbLookup can access data sources that have already been registered in the ODBC.INI file (or similar registry on platforms on other than Windows). It can also autoregister a data source not present in the registry. For autoregistration, specify the data source as "data_source" : "type" : "path" where data_source is the name to be assigned, type identifies the DBMS (for example, "Oracle7"), and path is a server name, directory, or other string that locates the DBMS.
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 Notes database session as long as that database remains open. If the user opens another Notes 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 semi-colon) 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
Note For complex connections, additional ID and password parameters may be required to connect to the data source.
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).
Specifying null handling
To control how null values are handled, specify one of the following, appended to the command_string parameter with a colon:
- Generates "Fail" 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.
- Discards "Discard" the null values, thus shortening the returned list of values. If one or more values are discarded when the @DbCommand formula is executed, Notes displays this message on the status bar:
Caution: NULL values discarded from @Db list.
- Value "Replacement value" that you specify to be used instead of 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 keyword, 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, Notes displays 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 you can easily find those values in Notes.
Accessing values found
@DbCommand can return no more than 55K bytes 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 55K bytes.
- 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. This applies to @DbCommand only when you use it with ODBC.