DBGetProp(), DBSetProp()

These functions let you check and change properties of a database and its contents.

Usage

uPropertyValue = DBGETPROP( cItem, cItemType, cProperty )
lSuccess = DBSETPROP( cItem, cItemType, cProperty, uNewValue )

Visual FoxPro has three sets of SetProp()/GetProp() functions. Each serves a slightly different role. SQLSetProp() and SQLGetProp() let you inquire about and change open connections to remote data and set up the defaults for new connections. CURSORSETPROP() and CURSORGETPROP() serve a similar role for open tables and views.

DBGetProp() and DBSetProp() are a little different than the other two pairs. They operate on a database itself and let you ask about and modify the contents of the database. Not the data, of course, but the description of the data known as the metadata. (We never met a data we didn’t like.) With these functions, you talk about the database itself, its stored connections, tables and views, and the fields of the tables and views. This is where you can determine that a particular view is always updateable or that a certain connection is asynchronous. The information stored in the database is then used when the connection, table or view is opened. Think of these properties as the defaults for the database members. In OOP terminology, DBGetProp() and DBSetProp() operate on the class, while the other GetProp()/SetProp() functions operate on instances of the class. (Of course, this isn’t really an OOP situation, but the analogy is handy.)

DBGetProp() and DBSetProp() handle a lot more cases than the others. So, you have to indicate whether the object of interest is a database, connection, table, view, or field. You also have to tell what object you’re interested in. When you’re looking at properties of a field, you have to add the table’s alias to the field name in the functions. For example, to see the caption for the LastName field of Customer, you’d write:

? DBGetProp("Customer.LastName", "Field", "Caption")

Unlike the other SetProp() functions, you can’t omit the new value to reset these properties to their defaults. Since that feature doesn’t always work as expected in the other cases, maybe it’s just as well.

We’ve worked out the logic of why some properties are read-write while others are read-only: If there’s another command in the language that lets you set a property, it’s read-only here. For example, the field property DefaultValue is read-only because you set it with CREATE TABLE or ALTER TABLE. On the other hand, the Comment property of each object, which can only be set through menu choices, is read-write.

The Help file contains a complete list of each type of property.

In all versions of VFP before VFP 6 (that is, through 5.0a), you can't successfully clear the value of field properties of views using DBSetProp(). To get rid of the values there, you have to set them to a single blank. But, when you do so, opening the view gives you an error message, although you can proceed.

The Tables property of views is a little confusing. Its purpose is to list all the tables that can be updated by the view. In essence, it’s the FROM clause of the SQL UPDATE command you need to send changes back to the source (if SQL UPDATE could handle multiple tables, anyway).

The database has to be current to apply these functions, even for the database-level properties (which include the database name in the function call). Having the database open, but not current, isn't enough! To use DBSetProp() in VFP 3, you need exclusive access to the database; that restriction has been removed from later versions.

In VFP 7, if the database has Database Events turned on, the BeforeDBGetProp and AfterDBGetProp events fire for DBGetProp(), and DBSetProp() fires the BeforeDBSetProp and AfterDBSetProp events.

Example

OPEN DATA TasTrade
? DBGETPROP("customer", "table", "comment")
* Returns "Customer Information"
? DBGETPROP("customer.discount", "field", "defaultvalue")
* Returns 0

See Also

Add Table, AfterDBGetProp, AfterDBSetProp, Alter Table, BeforeDBGetProp, BeforeDBSetProp, Create Connection, Create Database, Create SQL View, Create Table, CursorGetProp(), CursorSetProp(), DBC(), Display Database, Set Database, SQLGetProp(), SQLSetProp()