Using The Services API

From the configuration page for your Firebird DSN on Windows you have access to a useful graphical management console that is built across the ODBC API and Firebird’s Services API. It gives a database administrator on Windows a user-friendly way to run service utilities that would otherwise be run from a command-line tool. We are using it to introduce this chapter because the source code could be a useful resource for developers looking for ideas about including Services functions in their applications.

Exploring the ODBC Services Console

To use the console, open that configuration page and click the button in the centre, labelled btn:[Services]:

Figure 1. Launching the Services UI on Windows

The console is a tabbed display providing access to many of the Services API functions, with the menu:Backup[] tab on top.

Figure 2. Firebird ODBC Services console — Backup tab

Figure 3. Restore tab

Figure 4. Statistics tab

We selected menu:Header pages[], which produced the gstat -h report for our database. Clicking on the btn:[View Log] button delivers the output to the browser:

Figure 5. Statistics log

Of course, you can have any statistics report, the Firebird log, metadata reports and more.

The menu:Repair[] tab gives easy access to most of the gfix housekeeping functions:

Figure 6. Repair tab

The menu:Users[] tab could be used to maintain accounts in the security database of any version of Firebird prior to version 3.0, although the Services API method was discouraged from V.2.5 onward. The Services API method is still available to maintain users in Firebird 3 databases if they were defined using Legacy_Auth authentication management. It will not work with users defined with the default SRP authentication manager.

Figure 7. Users tab

Click on the appropriate button to add, modify or delete a user. Remember, the user performing these tasks must be SYSDBA or a user with elevated server privileges. The role RDB$ADMIN is not sufficiently elevated.

Figure 8. Add user

Figure 9. Modify user

Figure 10. Delete user

Showing Logs from the Interface

If a log file is available from the execution of a Service API function, the btn:[View Log] button will become active. The UI provides it on demand in HTML format and opens it in your default browser. If you wonder how to go about coding this into your own ODBC application, the source code is a resource that is freely available to you.

Using the Services API

The ODBC/JDBC driver wraps a great many of the Services API functions. The management console built into the Windows DSN interface provides examples of most of them. One thing you cannot do via the console is create databases. Fear not! the driver has it wrapped!

In the Connection chapter is a table of the keywords available to signify the values for attachments via Firebird’s “regular” API. The table below provides the keywords for the KEYWORD=value parameters for connecting to the server and launching a service request. These are additional to the relevant connection parameters. For some cases, the default settings from the DSN, if used, will be correct for Service requests.

Table 1. Keywords for Service Request Attributes
Keyword	Description	More Information
`BACKUPFILE`	Backup file	This is a filesystem path and file name. Unlike a database, a backup path can be a network storage address.
`LOGFILE`	Path and name of the log file for the service	Optional; valid for any service that provides a log file option. The same filesystem rules apply as for backup files.
`CREATE_DB`	Create database	See the examples below for usage
`BACKUP_DB`	Backup database	The path and name of the database backup file, for backups and restores.
`RESTORE_DB`	The network path and name of the database to which a backup is to be restored. This cannot be a network storage address. The file name part can be an alias, provided the alias exists.
`REPAIR_DB`	Repair database	Local path to the database to be repaired or validated. Remote access is invalid.
`COMPACT_DB`	Compact database	Not currently applicable to Firebird databases.
`DROP_DB`	Drop database	Not currently applicable to Firebird databases.

Examples of Services Use

The following samples show how to configure the various service requests.

Creating a Database

SQLConfigDataSource( NULL,
                     ODBC_ADD_DSN,
                     "Firebird/InterBase(r) driver",
                     "ODBC\0"
                     "CREATE_DB = D:\\TestService\\test.fdb\0"
                     "DESCRIPTION = My Firebird database\0"
                     "UID         = SYSDBA\0"
                     "PWD         = masterkey\0"
                     "CHARSET     = NONE\0"
                     "PAGESIZE    = 8192\0"
                     "DIALECT     = 3\0" );

More alternative examples for creating databases are at the end of this chapter.

Backing Up a Database

SQLConfigDataSource( NULL,
                     ODBC_ADD_DSN,
                     "Firebird/InterBase(r) driver",
                     "ODBC\0"
                     "BACKUP_DB = D:\\TestService\\test.fdb\0"
                     "BACKUPFILE = D:\\TestService\\test.fbk\0"
                     "UID         = SYSDBA\0"
                     "PWD         = masterkey\0" );

Restoring a Database

SQLConfigDataSource( NULL,
                     ODBC_ADD_DSN,
                     "Firebird/InterBase(r) driver",
                     "ODBC\0"
                     "RESTORE_DB = D:\\TestService\\testNew.fdb\0"
                     "BACKUPFILE = D:\\TestService\\test.fbk\0"
                     "LOGFILE = D:\\TestService\\test.log\0"
                     "UID         = SYSDBA\0"
                     "PWD         = masterkey\0" );

Repairing a Database

SQLConfigDataSource( NULL,
                     ODBC_ADD_DSN,
                     "Firebird/InterBase(r) driver",
                     "ODBC\0"
                     "REPAIR_DB = D:\\TestService\\test.fdb\0"
                     "UID         = SYSDBA\0"
                     "PWD         = masterkey\0" );

More Ways to Create a Database

Create a database using the ODBC API function SQLConfigDataSource. A convenient method for creating a database that is going to be managed by someone else.

SQLConfigDataSource( NULL,
                     ODBC_ADD_DSN,
                     "Firebird/InterBase(r) driver",
                     "ODBC\0"
                     "CREATE_DB = D:\\TestService\\test.fdb\0"
                     "DESCRIPTION = My Firebird database\0"
                     "UID         = SYSDBA\0"
                     "PWD         = masterkey\0"
                     "CHARSET     = NONE\0"
                     "PAGESIZE    = 8192\0"
                     "DIALECT     = 3\0" );

Create a database using the ODBC API function SQLDriverConnect. Convenient when the job is going to be performed from a user application. The driver will handle errors and continue attempting to create the database until it eventually succeeds in connecting to it. Access is passed to the client upon success.

UCHAR buffer[1024];
SWORD bufferLength;
SQLDriverConnect( connection, hWnd,
                  (UCHAR*)"DRIVER=Firebird/InterBase(r) driver;"
                  "UID=SYSDBA;"
                  "PWD=masterkey;"
                  "PAGESIZE=8192;"
                  "DBNAMEALWAYS=C:\\Temp\\NewDB.fdb", SQL_NTS,
                  buffer, sizeof (buffer), &bufferLength,
                  SQL_DRIVER_NOPROMPT );

Create a database using the ODBC API function SQLExecDirect. This scenario is interesting in that the database is created within the context of an existing client connection. It is not necessary therefore to include "DRIVER=Firebird/InterBase (r) driver;" in the call, since it will be taken from the current connection.

As with the first method that used SQLConfigDataSource, the current user does not get management rights on the database created. For that requirement, SQLDriverConnect should be used instead.

SQLExecDirect( hStmt,
               "CREATE DATABASE \'C:/TEMP/NEWDB00.FDB\'"
               "   PAGE_SIZE 8192"
               "   SET NAMES \'NONE\'"
               "   USER \'SYSDBA\'"
               "   PASSWORD \'masterkey\';",
               SQL_NTS );