Connecting to Your Database
Troubleshooting Your Connection

You can use information from the Database Trace tool to understand what PowerBuilder is doing internally when you work with your database. Examining the information in the log file can help you:

If you are familiar with PowerBuilder and your DBMS, you can use the information in the log to help troubleshoot connection problems on your own. If you are less experienced or need help, run the Database Trace tool before you call Technical Support. You can then report or send the results of the trace to the Technical Support representative who takes your call.

Default contents of the trace file

By default, the Database Trace tool records the following information in the log file when you trace a database connection:

You can opt to include the names of DBI commands and the time elapsed from the last database connection to the completion of processing for each log entry. You can exclude binding and timing information as well as the data from all fetch requests.

Database Trace dialog box selections

The Database Trace dialog box lets you select the following items for inclusion in or exclusion from a database trace file:

Registry settings for DBTrace

The selections made in the Database Trace dialog box are saved to the registry of the machine from which the database connections are made. Windows registry settings for the database trace utility configuration are stored under the HKEY_CURRENT_USER\Software\Sybase\PowerBuilder\22.0\DBTrace key. Registry strings under this key are: ShowBindings, FetchBuffers, Timing, SumTiming, LogFileName, and ShowDialog. Except for the LogFileName string to which you can assign a full file name for the trace output file, all strings can be set to either 0 or 1.

The ShowDialog registry string can be set to prevent display of the Database Trace dialog box when a database connection is made with tracing enabled. This is the only one of the trace registry strings that you cannot change from the Database Trace dialog box. You must set ShowDialog to 0 in the registry to keep the configuration dialog box from displaying.

INI file settings for DBTrace

If you do not have access to the registry, you can use PB.INI to store trace file settings. Add a [DbTrace] section to the INI file with at least one of the following values set, then restart PowerBuilder:

[DbTrace]
FetchBuffers=1
ShowBindings=1
SumTiming=1
Timing=1
ShowDialog=1 
LogFileName=dbtrace.log

The keywords are the same as in the registry and have the same meaning. When you connect to the database again, the initial settings are taken from the INI file, and when you modify them, the changes are written to the INI file.

If the file name for LogFileName does not include an absolute path, the log file is written to the following path, where <username> is your login ID: Users\<username>\AppData\Local\Appeon\PowerBuilder 22.0. If there are no DbTrace settings in the INI file, the registry settings are used.

Error messages

If the database trace utility cannot open the trace output file with write access, an error message lets you know that the specified trace file could not be created or opened. If the trace utility driver cannot be loaded successfully, a message box informs you that the selected Trace DBMS is not supported in your current installation.

The specific content of the Database Trace log file depends on the database you are accessing and the operations you are performing. However, the log uses the following basic format to display output:

COMMAND: (time)
   {additional_information}

Example

The following portion of the log file shows the commands PowerBuilder executes to fetch two rows from a SQL Anywhere database table:

FETCH NEXT: (0.479 MS)
 COLUMN=400
    COLUMN=Marketing
    COLUMN=Evans
FETCH NEXT: (0.001 MS)
 COLUMN=500
    COLUMN=Shipping 
    COLUMN=Martinez

If you opt to include DBI Names and Sum Time information in the trace log file, the log for the same two rows might look like this:

FETCH NEXT:(DBI_FETCHNEXT) (1.459 MS / 3858.556 MS)
 COLUMN=400
    COLUMN=Marketing
    COLUMN=Evans
FETCH NEXT:(DBI_FETCHNEXT) (0.001 MS / 3858.557 MS)
 COLUMN=500
    COLUMN=Shipping 
    COLUMN=Martinez

For a more complete example of Database Trace output, see Sample Database Trace output.

To start the Database Trace tool in the PowerBuilder development environment, edit the database profile for the connection you want to trace, as described in the following procedure.

In a PowerBuilder application that connects to a database, you must specify the required connection parameters in the appropriate script. For example, you might specify them in the script that opens the application.

To trace a database connection in a PowerBuilder script, you specify the name of the DBMS preceded by the word trace and a single space. You can do this by:

For more about using Transaction objects to communicate with a database in a PowerBuilder application, see the section called “Using Transaction Objects” in Application Techniques.

Copying DBMS trace syntax from the Preview tab

One way to start Database Trace in a PowerBuilder application script is to copy the PowerScript DBMS trace syntax from the Preview tab in the Database Profile Setup dialog box into your script, modifying the default Transaction object name (SQLCA) if necessary.

As you complete the Database Profile Setup dialog box in the development environment, PowerBuilder generates the correct connection syntax on the Preview tab for each selected option, including Generate Trace. Therefore, copying the syntax directly from the Preview tab ensures that it is accurate in your script.

Coding PowerScript to set a value for the DBMS property

Another way to start the Database Trace tool in a PowerBuilder script is to specify it as part of the DBMS property of the Transaction object. The Transaction object is a special nonvisual object that PowerBuilder uses to communicate with the database. The default Transaction object is named SQLCA, which stands for SQL Communications Area.

SQLCA has 15 properties, 10 of which are used to connect to your database. One of the 10 connection properties is DBMS. The DBMS property contains the name of the database to which you want to connect.

Reading the DBMS value from an external text file or the registry

As an alternative to setting the DBMS property in your PowerBuilder application script, you can use the PowerScript ProfileString function to read the DBMS value from a specified section of an external text file, such as an application-specific initialization file, or from an application settings key in the registry.

The following procedure assumes that the DBMS value read from the database section in your initialization file uses the following syntax to enable database tracing:

DBMS = trace DBMS_name

Instead of tracing all database commands from the start of a database connection, you can start and end a trace programmatically for specific database queries. To start a trace, you can assign the string value pair "PBTrace=1" to the transaction object DBParm property; to end a trace, you assign the string value pair "PBTrace=0".

For example, if you wanted data to be logged to the trace output for a single retrieve command, you could disable tracing from the start of the connection and then surround the retrieve call with DBParm property assignments as follows:

SQLCA.DBMS = "TRACE ODBC"
SQLCA.DBParm="PBTrace=0"
Connect using SQLCA;
...
SQLCA.DBParm="PBTrace=1"
dw_1.Retrieve ( )
SQLCA.DBParm="PBTrace=0"

When you first connect to a database after setting the DBMS parameter to "Trace DBMSName", a configuration dialog box displays. The configuration parameters that you set in this dialog box are saved to the registry. Configuration parameters are retrieved from the registry when you begin tracing by assigning the DBParm parameter to "PBTrace=1".

You can start and stop the SQL statement trace utility in the same way if you set the DBMS value to "TRS DBMSName" instead of "Trace DBMSName". For information about the SQL statement trace utility, see Using the SQL statement trace utility.

To stop Database Trace in a PowerBuilder application script, you must delete the word trace from the DBMS property. You can do this by:

You must reconnect for the change to take effect.

Editing the DBMS property

Reading the DBMS value from an external text file

As an alternative to editing the DBMS property in your PowerBuilder application script, you can use the PowerScript ProfileString function to read the DBMS value from a specified section of an external text file, such as an application-specific initialization file.

This assumes that the DBMS value read from your initialization file does not include the word trace, as shown in the preceding example in Editing the DBMS property.

You can display the contents of the log file anytime during a PowerBuilder session.

When you use the Database Trace log as a troubleshooting tool, it might be helpful to add your own comments or notes to the file. For example, you can specify the date and time of a particular connection, the versions of database server and client software you used, or any other useful information.

Each time you connect to a database with tracing enabled, PowerBuilder appends the trace output of your connection to the existing log. As a result, the log file can become very large over time, especially if you frequently enable tracing when connected to a database.