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 DataWindow Designer generates numeric values in the SQL syntax it internally builds in DataWindow objects and sends to your database.

Applies to

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 DataWindow Designer generates numeric values in the SQL syntax it builds and sends to the database.)

In DataWindow Designer, 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 ODBC syntax If you are accessing an Oracle database through the 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. DataWindow Designer returns no open quote in the SQL syntax it builds and sends to the database, as required by IBM DB2 databases. Oracle or ODBC syntax Type three single open quotes. DataWindow Designer 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. DataWindow Designer 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. DataWindow Designer 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. DataWindow Designer returns no closed quote in the SQL syntax it builds and sends to the database, as required by IBM DB2 databases. Oracle or ODBC syntax Type three single closed quotes. DataWindow Designer 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 DataWindow Designer 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 DataWindow Designer to generate two numeric values in the format 125,50 and 4,0. DataWindow Designer 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 tab in the Database Profile Setup dialog box:
```
%s,%s
```

Application Type the following in code:

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

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

INSERT INTO MYTABLE (a, b)

VALUES (125,50, 4,0)

Example 2 (Oracle ODBC syntax)

This example shows how to specify that you want DataWindow Designer to generate two numeric values in the format '125,50' and '4,0'. DataWindow Designer 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 tab in the Database Profile Setup dialog box:
```
%s,%s
```