NumericFormat database parameter
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.
Controls
-
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:
|
1 |
<span>NumericFormat</span>='%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:
|
1 |
<span>NumericFormat</span>='''%s,%s''' |
|
Parameter |
Description |
|---|---|
|
‘ “‘ |
IBM DB2 syntax Type a single open quote. PowerBuilder returns no open quote Oracle, JDBC, or ODBC syntax Type three single open quotes. PowerBuilder parses the second |
|
%s |
Represents one or more digits to the left |
|
, |
Represents the decimal separator character |
|
%s |
Represents one or more digits to the right |
|
‘ ”’ |
IBM DB2 syntax Type one single closed quote. PowerBuilder returns no closed Oracle, JDBC, or ODBC syntax Type three single closed quotes. PowerBuilder parses the first |
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
profileType the following in the Numeric Format box on the Syntax
page in the Database Profile Setup dialog box:1%s,%s -
Application
Type the following in code:
1SQLCA.DbParameter="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.
|
1 |
INSERT INTO MYTABLE (a, b) |
|
1 |
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
profileType the following in the Numeric Format box on the Syntax
page in the Database Profile Setup dialog box:1%s,%s -
Application
Type the following in code:
1SQLCA.DbParameter="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.
|
1 |
INSERT INTO MYTABLE (a, b) |
|
1 |
VALUES ('125,50', '4,0') |