Skip to content

DF_DATABASE_DEFAULT_DATABASE

This attribute returns or changes the default database of a database connection. It allows you to dynamically switch between different databases on the same server that use identical schemas.

Level

Database

Supported by

The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 6.2 and higher.

Type

String, temporary

Access

Read/Write

Values

String, should be a valid database name.

Syntax

Use cli.pkg
Get_Attribute DF_DATABASE_DEFAULT_DATABASE of {driverNumber} {databaseHandle} to {StringVariable}
Set_Attribute DF_DATABASE_DEFAULT_DATABASE of {driverNumber} {databaseHandle} to {StringVariable}

Remarks

The default database is the database where the driver sends its SQL commands. It can be compared to changing the USE that is used in SQL scripts. The default database is initially set to the database specified in the connection string.

Limitations

  • Can only switch between databases on the same server.
  • Databases will be accessed with the same credentials.
  • Tables must have identical structures in both databases. Differences in structures will give unpredictable results.

Example

Connection String: 

MssqlOrderID=SERVER=(local); DATABASE=MssqlOrder190; Trusted_Connection=yes

If logged in with this connection ID, the default database will be MssqlOrder190.

Function to Get Default Database

Function GetDefaultDatabase Returns String
    String sDefaultDatabase
    tConnection Conn
    Handle hConnectionHandle

    Get ConnectionIdInfo of ghoConnection "MssqlOrderID" to Conn
    Get ConnectionDatabaseIdHandle of ghoConnection Conn to hConnectionHandle

    If (hConnectionHandle) Begin
        Get_Attribute DF_DATABASE_DEFAULT_DATABASE of Conn.iDriverIndex hConnectionHandle to sDefaultDatabase
        Showln (SFormat("DF_DATABASE_DEFAULT_DATABASE = %1", sDefaultDatabase))
    End

    Function_Return sDefaultDatabase
End_Function

Function to Set Default Database

The following function will change the default database.

Procedure SetDefaultDatabase String sDatabaseName
    tConnection Conn
    Handle hConnectionHandle

    Get ConnectionIdInfo of ghoConnection "MssqlOrderID" to Conn
    Get ConnectionDatabaseIdHandle of ghoConnection Conn to hConnectionHandle

    If (hConnectionHandle) Begin
        Set_Attribute DF_DATABASE_DEFAULT_DATABASE of Conn.iDriverIndex hConnectionHandle to sDatabaseName
        Showln (SFormat("Set DF_DATABASE_DEFAULT_DATABASE To %1", sDatabaseName))
    End
End_Procedure

Changing the default database can be used in multi-tenant applications, where multiple databases with identical structures are used.

When changing the default database, opened tables will remain open. After a database change, the opened tables will point to a different database. It is expected that tables in both databases have identical structures.

Example of Changing Default Database

// Let's assume this returns MssqlOrder190
Get GetDefaultDatabase to sOldDefaultDatabase

// Open table SalesP in database MssqlOrder190
Open SalesP

// Find a record in table SalesP in database MssqlOrder190
Clear SalesP
Move "MM" to SalesP.Id
Find Eq SalesP by Index.1

// Switch to a different database
Send SetDefaultDatabase "MssqlOrder182"

// Table SalesP is still open, now points to database MssqlOrder182
// Find will now find a record in table SalesP in database MssqlOrder182
Clear SalesP
Move "MM" to SalesP.Id
Find Eq SalesP by Index.1