Setting database parameters

In PowerBuilder, you can set database parameters by doing either of the following:

  • Editing the Database Profile Setup dialog box for your connection in the development environment

  • Specifying connection parameters in an application script

Setting database parameters in the development environment

Editing database profiles

To set database parameters for a database connection in the PowerBuilder development environment, you must edit the database profile for that connection.

Character limit for strings

Strings containing database parameters that you specify in the Database Profile Setup dialog box for your connection can be up to 999 characters in length.

This limit applies only to database parameters that you set in a database profile in the development environment. Database strings specified in code as properties of the Transaction object are not limited to a specified length.

Setting database parameters in a PowerBuilder application script

If you are developing an application that connects to a database, you must specify the required connection parameters in the appropriate script as properties of the default Transaction object (SQLCA) or a Transaction object that you create. For example, you might specify connection parameters in the script that opens the application.

One of the connection parameters you might want to specify in a script is DBParm. You can do this by:

  • (Recommended) Copying DBParm syntax from the Preview tab in the Database Profile Setup dialog box into your script

  • Coding PowerScript to set values for the DBParm property of the Transaction object

  • Reading DBParm values from an external text file

Copying DBParm syntax from the Preview tab

The easiest way to specify DBParm parameters in a PowerBuilder application script is to copy the DBParm syntax from the Preview tab in the Database Profile Setup dialog box into your code, modifying the default Transaction object name (SQLCA) if necessary.

As you set parameters in the Database Profile Setup dialog box in the development environment, PowerBuilder generates the correct connection syntax on the Preview tab. Therefore, copying the syntax directly from the Preview tab ensures that you use the correct DBParm syntax in your code.

To copy DBParm syntax from the Preview tab into your code:

  1. On one or more tab pages in the Database Profile Setup dialog box for your connection, supply values for any parameters you want to set.

    For instructions, see Setting database parameters in the development environment.

    For information about the parameters for your interface and the values to supply, click Help.

  2. Click Apply to save your changes to the current tab without closing the Database Profile Setup dialog box.

  3. Click the Preview tab.

    The correct DBParm syntax for each selected option displays in the Database Connection Syntax box.

  4. Select one or more lines of text in the Database Connection Syntax box and click Copy.

    PowerBuilder copies the selected text to the clipboard.

  5. Click OK to close the Database Profile Setup dialog box.

  6. Paste the selected text from the Preview tab into your code, modifying the default Transaction object name (SQLCA) if necessary.

Coding PowerScript to set values for the DBParm property

Another way to specify connection parameters in a script is by coding PowerScript to assign values to properties of the Transaction object. PowerBuilder uses a special nonvisual object called a Transaction object 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 DBParm. DBParm contains DBMS-specific parameters that let your application take advantage of various features supported by the database interface.

To set values for the DBParm property in a PowerBuilder script:

  1. Open the application script in which you want to specify connection parameters.

    For instructions, see the section called “Opening Script views” in Users Guide.

  2. Use the following PowerScript syntax to specify DBParm parameters. Make sure you separate the DBParm parameters with commas, and enclose the entire DBParm string in double quotes.

    SQLCA.dbParm = "parameter_1, parameter_2, parameter_n"

    For example, the following statement in a PowerBuilder script sets the DBParm property for an ODBC data source named Sales. In this example, the DBParm property consists of two parameters: ConnectString and Async.

    SQLCA.dbParm="ConnectString='DSN=Sales; UID=PB; PWD=xyz',Async=1"
  3. Compile the PowerBuilder script to save your changes.

    For instructions, see the section called “Compiling the script” in Users Guide.

Reading DBParm values from an external text file

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

To read DBParm values from an external text file:

  1. Open the application script in which you want to specify connection parameters.

    For instructions, see the section called “Opening Script views” in Users Guide.

  2. Use the following PowerScript syntax to specify the ProfileString function with the SQLCA.DBParm property:

    SQLCA.dbParm = ProfileString ( file, section, key, default )

    For example, the following statement in a PowerBuilder script reads the DBParm values from the [Database] section of the APP.INI file:

    SQLCA.dbParm=ProfileString("APP.INI", "Database", "dbParm","")
  3. Compile the script to save your changes.

    For instructions, see the section called “Compiling the script” in Users Guide.