NumericFormat

Description

If supported by the DBMS or back-end database, setting NumericFormat tells the driver to do special formatting of numeric strings in SQL syntax. This formatting affects how PowerBuilder generates numeric values in the SQL syntax it internally builds in DataWindow objects and sends to your database.

Applies to

JDB JDBC
ODBC

Syntax

The syntax you use depends on the back-end DBMS you are accessing and how you want to format the numeric string.

The following are typical syntax examples for Oracle databases that format a numeric string with a comma as the decimal separator. (See the Examples section for information about how PowerBuilder generates numeric values in the SQL syntax it builds and sends to the database.)

In the PowerBuilder development environment, the Database Profile Setup dialog box inserts special characters (quotes) where needed, so you can specify just the NumericFormat value (%s in this example).

In code, you must use the following syntax:

IBM DB2 syntax If you are accessing an IBM DB2 database through the ODBC interface, use the following syntax for NumericFormat. Note the use of one single quote at the beginning and end of the string:

NumericFormat='%s,%s'

Oracle JDBC or ODBC syntax If you are accessing an Oracle database through the JDBC or ODBC interface, use the following syntax for NumericFormat. Note the use of three single quotes at the beginning and end of the string:

NumericFormat='''%s,%s'''

Parameter	Description
' "'	IBM DB2 syntax Type a single open quote. PowerBuilder returns no open quote in the SQL syntax it builds and sends to the database, as required by IBM DB2 databases. Oracle, JDBC, or ODBC syntax Type three single open quotes. PowerBuilder parses the second and third quotes as one single open quote in the SQL syntax it builds and sends to the database.
%s	Represents one or more digits to the left of the decimal in the numeric string. PowerBuilder substitutes this value with the digits to the left of the decimal when it builds the SQL syntax.
,	Represents the decimal separator character (in this case a comma).
%s	Represents one or more digits to the right of the decimal in the numeric string. PowerBuilder substitutes this value with the digits to the right of the decimal when it builds the SQL syntax.
' '''	IBM DB2 syntax Type one single closed quote. PowerBuilder returns no closed quote in the SQL syntax it builds and sends to the database, as required by IBM DB2 databases. Oracle, JDBC, or ODBC syntax Type three single closed quotes. PowerBuilder parses the first and second quotes as one single closed quote in the SQL syntax it builds and sends to the database.

Default

None

Usage

When to set NumericFormat In general, you should not need to set the NumericFormat parameter. Most back-end DBMSs do not require that the driver do special formatting of numeric strings in SQL syntax. However, some databases might require special formatting, such as an IBM DB2/MVS database server configured to use a comma as the decimal separator.

In these cases, setting NumericFormat allows you to generate numeric values with special formatting in the SQL syntax that PowerBuilder builds in DataWindow objects and sends to your database. For example, if the decimal separator for your DBMS is a comma, you might want to set NumericFormat as shown in the Examples section below to use a comma as the decimal delimiter in the SQL syntax sent to your database.

Examples

Example 1 (IBM DB2 syntax)

This example shows how to specify that you want PowerBuilder to generate two numeric values in the format 125,50 and 4,0. PowerBuilder uses the comma as a decimal separator in the SQL syntax it builds in DataWindow objects and sends to an IBM DB2 database.

Database profile Type the following in the Numeric Format box on the Syntax page in the Database Profile Setup dialog box:
```
%s,%s
```

Application Type the following in code:
```
SQLCA.DBParm="NumericFormat='%s,%s'"
```

What happens PowerBuilder internally builds the following SQL INSERT statement in the DataWindow object and sends the syntax to your database. PowerBuilder returns no quotes in the SQL syntax.

INSERT INTO MYTABLE (a, b)

VALUES (125,50, 4,0)

Example 2 (Oracle, JDBC, or ODBC syntax)

This example shows how to specify that you want PowerBuilder to generate two numeric values in the format '125,50' and '4,0'. PowerBuilder uses the comma as a decimal separator in the SQL syntax it builds in DataWindow objects and sends to an Oracle database.

Database profile Type the following in the Numeric Format box on the Syntax page in the Database Profile Setup dialog box:
```
%s,%s
```

Application Type the following in code:

SQLCA.DBParm="NumericFormat='''%s,%s'''"

What happens PowerBuilder internally builds the following SQL INSERT statement in the DataWindow object and sends the syntax to your database. PowerBuilder returns single quotes in the SQL syntax.

INSERT INTO MYTABLE (a, b)

VALUES ('125,50', '4,0')