DBGETPROP( ) Function

  • Article

Retrieves the value of a property for the current database or for fields, named connections, tables, or views in the current database.

Note

You must open the database before you can retrieve its properties or those of its connections, tables, views, or fields. However, you do not need to open a table or view before retrieving its properties.

DBGETPROP(cName, cType, cProperty)

Parameters

  • cName
    Specifies the name of the current open database or the field, named connection, table, or view in the current open database for which DBGETPROP( ) returns information.

    To return information about a field in a table or a view, preface the name of the field with the name of the table or view containing the field. For example, to return information about the custid field in the customer table, specify the following for cName:

    customer.custid

  • cType
    Specifies whether cName is the current database, or a field, named connection, table, or view in the current database. The following table lists the values for cType.

    cType

    Description

    CONNECTION

    cName is a named connection in the current database.

    DATABASE

    cName is the current database.

    FIELD

    cName is a field in the current database.

    TABLE

    cName is a table in the current database.

    VIEW

    cName is a view in the current database.

  • cProperty
    Specifies the name of the property for which DBGETPROP( ) returns information.

    The following tables list the values you can specify for cProperty, the return value types, and descriptions of each property. Each description includes the read and write privileges for each property. If a property is read-only, its value cannot be changed with DBSETPROP( ).

    Connection properties

    cProperty

    Type

    Description

    Asynchronous

    L

    The connection mode. (Default) False (.F.) specifies a synchronous connection. True (.T.) specifies an asynchronous connection.

    Read/write.

    BatchMode

    L

    The batch-processing mode. (Default) True (.T.) specifies the connection that operates in batch mode.

    Read/write.

    Comment

    C

    The text of the connection comment.

    Read/write.

    ConnectString

    C

    The login connection string.

    Read/write.

    ConnectTimeout

    N

    The connection timeout interval in seconds. The default is 0 (wait indefinitely).

    Read/write.

    Database

    C

    The name of the server database specified with the DATABASE clause in the CREATE CONNECTION command or in the Connection Designer.

    Read/write.

    DataSource

    C

    The name of the data source as defined in the Odbc.ini file.

    Read/write.

    DisconnectRollback

    L

    Specifies if a pending transaction is committed or rolled back when SQLDISCONNECT( ) is called for the last connection handle.

    The default is false (.F.), indicating that a pending transaction is committed when SQLDISCONNECT( ) is called for the last connection handle.

    Specify true (.T.) to roll back a pending transaction when SQLDISCONNECT( ) is called for the last connection handle.

    Connections with automatic transaction processing are not affected by this setting.

    Read/write.

    DispLogin

    N

    Contains a numeric value that determines when the ODBC Login dialog box is displayed. DispLogin may assume the following values:

    1 or DB_PROMPTCOMPLETE (from Foxpro.h). 1 is the default.

    2 or DB_PROMPTALWAYS (from Foxpro.h).

    3 or DB_PROMPTNEVER (from Foxpro.h).

    If 1 or DB_PROMPTCOMPLETE is specified, Microsoft Visual FoxPro displays the ODBC Login dialog box only if any required information is missing.

    If 2 or DB_PROMPTALWAYS is specified, the ODBC Login dialog box is always displayed, allowing you to change settings before connecting.

    If 3 or DB_PROMPTNEVER is specified, the ODBC Login dialog box is not displayed and Visual FoxPro generates an error if the required login information is not available.

    Read/write.

    DispWarnings

    L

    Contains a logical value that determines if non-trappable warnings from the remote table, ODBC, or Visual FoxPro are displayed. (Default) True (.T.) specifies that non-trappable errors are displayed.

    Read/write.

    IdleTimeout

    N

    The idle timeout interval in minutes. Timeout is checked no more than once per minute, therefore it is possible that active connections might deactivate one or two minutes later after the specified time interval has elapsed. The default value is 0 (wait indefinitely).

    Read/write.

    PacketSize

    N

    The size of the network packet used by the connection. Adjusting this value can improve performance. The default value is 4096 bytes (4K).

    Read/write

    PassWord

    C

    The connection password.

    Read/write.

    QueryTimeout

    N

    The query timeout interval in seconds. The default value is 0 (wait indefinitely).

    Read/write.

    Transactions

    N

    Contains a numeric value that determines how the connection manages transactions on the remote table. Transactions may assume the following values:

    1 or DB_TRANSAUTO (from Foxpro.h). 1 is the default. Transaction processing for the remote table is handled automatically.

    2 or DB_TRANSMANUAL (from Foxpro.h). Transaction processing is handled manually through SQLCOMMIT( ) and SQLROLLBACK( ).

    Read/write.

    UserId

    C

    The user identification.

    Read/write.

    WaitTime

    N

    The amount of time in milliseconds that elapses before Visual FoxPro checks whether the SQL statement has completed executing. The default is 100 milliseconds.

    Read/write.

    Database properties

    cProperty

    Type

    Description

    Comment

    C

    The text of the database comment.

    Read/write.

    DBCEventFileName

    C

    Relative path and filename of external program file containing DBC Events code.

    DBCEvents

    L

    Enabled state of DBC Events. Set to True (.T.) to enable.

    Version

    N

    The database version number.

    Read-only.

    Field properties for tables

    cProperty

    Type

    Description

    Caption

    C

    The field caption. If an equal sign (=) precedes the value of this property, Visual FoxPro evaluates the value as an expression. Otherwise, it is treated as a string literal.

    Read/write.

    Comment

    C

    The text of the field comment.

    Read/write.

    DefaultValue

    C

    The field default value.

    Read-only.

    DisplayClass

    C

    Name of the class used for field mapping.

    Read/write.

    DisplayClassLibrary

    C

    Path to the class library specified with the DisplayClass property.

    Read/write.

    Format

    C

    The field display format. See the Format Property for a list of format settings.

    Read/write.

    InputMask

    C

    The field input format. See the InputMask Property for a list of input mask settings.

    Read/write.

    RuleExpression

    C

    The field rule expression.

    Read-only.

    RuleText

    C

    The field rule error text.

    Read-only.

    Field properties for views

    cProperty

    Type

    Description

    Caption

    C

    The field caption. If an equal sign (=) precedes the value of this property, Visual FoxPro evaluates the value as an expression. Otherwise, it is treated as a string literal.

    Read/write.

    Comment

    C

    The text of the field comment.

    Read/write.

    DataType

    C

    The data type for a field in a view. Initially set to the data type for the field in the data source.

    To specify a different data type for a field with DBSETPROP( ), use the syntax for creating fields in CREATE TABLE – SQL.

    For example, to change the data type of an integer field named iCost in a table named Mytable to numeric type with width 4 and 2 decimal places, use

    DBSETPROP('mytable.icost', 'field', ; 'DataType', 'N(4,2)')

    You can also include the NOCPTRANS clause to prevent translation of character and memo fields to a different code page.

    Read/write for remote views.

    DisplayClass

    C

    Name of the class used for field mapping.

    Read/write.

    DisplayClassLibrary

    C

    Path to the class library specified with the DisplayClass property.

    Read/write.

    DefaultValue

    C

    The field default value.

    Read/write.

    KeyField

    L

    Contains True (.T.) if the field is specified in an index key expression; otherwise, contains False (.F.).

    Read/write.

    RuleExpression

    C

    The field rule expression.

    Read/write.

    RuleText

    C

    The field rule error text.

    Read/write.

    Updatable

    L

    Contains True (.T.) if the field can be updated; otherwise, contains False (.F.).

    Read/write.

    UpdateName

    C

    The name of the field used when data in the field is updated to the remote table. By default, the remote table field name.

    Read/write.

    Table properties

    cProperty

    Type

    Description

    Comment

    C

    The text of the table comment.

    Read/write.

    DeleteTrigger

    C

    The Delete trigger expression.

    Read-only.

    InsertTrigger

    C

    The Insert trigger expression.

    Read-only.

    Path

    C

    The relative path with respect to the DBC to the table including the name of the file.

    Read-only.

    PrimaryKey

    C

    The tag name of the primary key.

    Read-only.

    RuleExpression

    C

    The row rule expression.

    Read-only.

    RuleText

    C

    The row rule error text.

    Read-only.

    UpdateTrigger

    C

    The Update trigger expression.

    Read-only.

    View properties

    cProperty

    Type

    Description

    AllowSimultaneousFetch

    L

    Applies when using remote views, a shared connection, and to cursors created using ODBC.

    AllowSimultaneousFetch contains True (.T.) when similarly configured cursors sharing the connection are permitted to fetch rows simultaneously.

    Otherwise, AllowSimultaneousFetch contains False (.F.) when fetching rows simultaneously is not permitted.

    Note

    This property is available in Visual FoxPro 8.0 and later. Views created in Visual FoxPro 8.0 and later are not compatible with earlier versions. Calling the VALIDATE DATABASE command in a version prior to Visual FoxPro 8.0 on a database container with remote views created or modified in Visual FoxPro 8.0 and later generate an error. Calling the VALIDATE DATABASE RECOVER in a version prior to Visual FoxPro 8.0 removes the AllowSimultaneousFetch property from any such views.

    BatchUpdateCount

    N

    The number of update statements sent to the back end for views. 1 is the default. Adjusting this value can greatly increase update performance.

    Read/write.

    Comment

    C

    The text of the view comment.

    Read/write.

    CompareMemo

    L

    Contains true (.T.) (default) if memo fields (of type Memo, General, or Picture, or, for remote views, type Timestamp) are included in the WHERE clause for updates; otherwise, contains false (.F.).

    Read/write.

    ConnectName

    C

    The named connection used when the view is opened.

    Read-only.

    FetchAsNeeded

    L

    If True (.T.), data is fetched only when needed, such as when record pointer moves to a row that has not been fetched.

    If False (.F.), additional data is fetched during idle time. (Default)

    > [!NOTE]
> <P>FetchAsNeeded does not apply when progressive fetching is disabled (FetchSize is -1).</P>


<p>Read/write.</p></td>
</tr>
<tr class="odd">
<td><p>FetchMemo</p></td>
<td><p>L</p></td>
<td><p>Contains true (.T.) (default) if memo and general fields are fetched with the view results; otherwise, contains false (.F.).</p>
<p>Use the <a href="ms977992(v=vs.90).md">ISMEMOFETCHED( ) Function</a> to determine if the memo field has been fetched.</p>
<p>Read/write.</p></td>
</tr>
<tr class="even">
<td><p>FetchSize</p></td>
<td><p>N</p></td>
<td><p>Contains the number of records fetched at a time from the remote tables. The default is 100 records. Setting FetchSize to –1 retrieves the complete result set, limited by the MaxRecords setting.</p>

> [!NOTE]
> <P>Progressive fetching holds the connection until all rows are retrieved. Use caution coding with FetchSize if ShareConnection is True (.T.).</P>


<p>Read/write.</p></td>
</tr>
<tr class="odd">
<td><p>MaxRecords</p></td>
<td><p>N</p></td>
<td><p>The maximum number of records fetched when result sets are returned. The default is – 1 (all rows are returned). A value of 0 specifies that the view is executed but no results are fetched.</p>
<p>Read/write.</p></td>
</tr>
<tr class="even">
<td><p>Offline</p></td>
<td><p>L</p></td>
<td><p>Contains true (.T.) if the view is an offline view.</p>
<p>Read-only.</p></td>
</tr>
<tr class="odd">
<td><p>ParameterList</p></td>
<td><p>C</p></td>
<td><p>The WHERE clause parameters. The format for the parameters is ''ParameterName1, 'Type1'; ParameterName2, 'Type2'; ...'' where Type is a one of the following characters specifying the parameter type:</p>
<p>C – Character D – Date T – DateTime N – Numeric F – Floating B – Double I – Integer Y – Currency L - Logical</p>
<p>For example, ''MyParam1, 'C' '' specifies a single character type parameter named MyParam1.</p>
<p>For more information about creating parameterized views, see <a href="c11kd98s(v=vs.90).md">How to: Create Parameterized Views</a>.</p>
<p>Read/write.</p></td>
</tr>
<tr class="even">
<td><p>Prepared</p></td>
<td><p>L</p></td>
<td><p>Contains True (.T.) if SQL statements are prepared for subsequent <strong>REQUERY( )</strong> function calls. <strong>REQUERY( )</strong> is used to retrieve data again for a SQL view. See <a href="1ce73wce(v=vs.90).md">SQLPREPARE( )</a> for additional information about preparing SQL statements. The default is false (.F.).</p>
<p>Read/write.</p></td>
</tr>
<tr class="odd">
<td><p>RuleExpression</p></td>
<td><p>C</p></td>
<td><p>The row rule expression.</p>
<p>Read/write.</p></td>
</tr>
<tr class="even">
<td><p>RuleText</p></td>
<td><p>C</p></td>
<td><p>The rule text expression displayed when an error occurs when data is edited in a Browse or Edit window.</p>
<p>Read/write.</p></td>
</tr>
<tr class="odd">
<td><p>SendUpdates</p></td>
<td><p>L</p></td>
<td><p>Contains true (.T.) if a SQL update query is sent to update remote tables; otherwise, contains false (.F.) (default).</p>
<p>Read/write.</p></td>
</tr>
<tr class="even">
<td><p>ShareConnection</p></td>
<td><p>L</p></td>
<td><p>Contains true (.T.) if the view can share its connection handle with other connections; otherwise, contains false (.F.).</p>
<p>Read/write.</p></td>
</tr>
<tr class="odd">
<td><p>SourceType</p></td>
<td><p>N</p></td>
<td><p>The view source. SourceType may assume the following values: 1 - The view uses local tables.</p>
<p>2 - The view uses remote tables.</p>
<p>Read-only.</p></td>
</tr>
<tr class="even">
<td><p>SQL</p></td>
<td><p>C</p></td>
<td><p>The SQL statement executed when the view is opened.</p>
<p>Read-only.</p></td>
</tr>
<tr class="odd">
<td><p>Tables</p></td>
<td><p>C</p></td>
<td><p>A comma-delimited list of the names of the tables.</p>
<p>Read/write.</p></td>
</tr>
<tr class="even">
<td><p>UpdateType</p></td>
<td><p>N</p></td>
<td><p>The update type. Valid values are: 1 or DB_UPDATE (from Foxpro.h). The old data is updated with the new data (default).</p>
<p>2 or DB_DELETEINSERT (from Foxpro.h). The old data is deleted and the new data is inserted.</p>
<p>Read/write.</p></td>
</tr>
<tr class="odd">
<td><p>UseMemoSize</p></td>
<td><p>N</p></td>
<td><p>The minimum size (in bytes) for which result columns are returned in memo fields. For example, if the width of a column result is greater than the value of UseMemoSize, the column result is stored in a memo field. UseMemoSize may vary from 1 to 255; the default value is 255.</p>
<p>Read/write.</p></td>
</tr>
<tr class="even">
<td><p>WhereType</p></td>
<td><p>N</p></td>
<td><p>The WHERE clause for updates to remote tables. WhereType may assume the following values: 1 or DB_KEY (from Foxpro.h). The WHERE clause used to update remote tables consists of only the primary fields specified with the <strong>KeyFieldList</strong> property.</p>
<p>2 or DB_KEYANDUPDATABLE (from Foxpro.h). The WHERE clause used to update remote tables consists of the primary fields specified with the <strong>KeyFieldList</strong> property and any updatable fields.</p>
<p>3 or DB_KEYANDMODIFIED (from Foxpro.h) (default). The WHERE clause used to update remote tables consists of the primary fields specified with the <strong>KeyFieldList</strong> property and any other fields that are modified.</p>
<p>4 or DB_KEYANDTIMESTAMP (from Foxpro.h). The WHERE clause used to update remote tables consists of the primary fields specified with the <strong>KeyFieldList</strong> property and a comparison of the time stamps.</p>
<p>For more information about the <strong>WhereType</strong> property, see <a href="wdsbhehb(v=vs.90).md">Managing Updates by Using Views</a>.</p>
<p>Read/write.</p></td>
</tr>
</tbody>
</table>

    Return Value

    Character, Numeric, or Logical data type. DBGETPROP( ) returns the value for the specified property.

    Remarks

    For more information about setting property values, see DBSETPROP( ) Function.

    Example

    The following example displays the name of the primary key field for the customer table. It then displays the field comments for the cust_id field in the customer table. If there are no comments for the field, a message is displayed indicating there are no field comments. To add comments, see the example for DBSETPROP( ).

    CLOSE DATABASES
CLEAR

OPEN DATABASE (HOME(2) + 'Data\testdata')

* Displays the primary key field
cResults = DBGETPROP("customer", "Table", "PrimaryKey")
=MESSAGEBOX(cResults)   && Displays    'cust_id'

* Displays comments for the field 'cust_id'
cResults = DBGETPROP("customer.cust_id", "Field", "Comment")
IF LEN(ALLTRIM(cResults)) = 0
   =MESSAGEBOX("No Comment for this field." + CHR(13) + ;
     CHR (13) + "Use DBSETPROP( ) to add comments.")
ELSE
   =MESSAGEBOX("Cust_id field comments: " + cRESULTS)
ENDIF

    See Also

    Reference

    ADD TABLE Command

    CREATE CONNECTION Command

    CREATE DATABASE Command

    CREATE SQL VIEW Command

    CURSORGETPROP( ) Function

    CURSORSETPROP( ) Function

    DBSETPROP( ) Function

    DISPLAY DATABASE Command

    ISMEMOFETCHED( ) Function

    LIST DATABASE Command

    RENAME CONNECTION Command

    SQLCOMMIT( ) Function

    SQLROLLBACK( ) Function

    Other Resources

    Functions

    Language Reference (Visual FoxPro)