NumericFormat – PB Docs 2021

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:

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:

Default value

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.

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.

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.

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.

See also

DecimalSeparator