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 PocketBuilder generates numeric values in the SQL syntax it internally builds in DataWindow objects and reports and sends to your database.
ODBC
In the PocketBuilder 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 a PocketBuilder application script, you must use the following syntax if you are accessing an Oracle database through the ODBC interface. Note the use of three single quotes at the beginning and end of the string:
NumericFormat = ‘‘‘%s,%s’’’
In a PocketBuilder application script, you must use the following syntax if you are accessing an IBM DB2 database through the ODBC interface. Note the use of one single quote at the beginning and end of the string:
NumericFormat = ‘%s,%s’
Parameter |
Description |
|---|---|
' |
Type three single open quotes. PocketBuilder 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. PocketBuilder 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. PocketBuilder substitutes this value with the digits to the right of the decimal when it builds the SQL syntax. |
' |
Type three single closed quotes. PocketBuilder parses the first and second quotes as one single closed quote in the SQL syntax it builds and sends to the database. |
None
When to set NumericFormat In general, you should not need to set the NumericFormat DBParm parameter. Most back-end DBMSs do not require that the driver do special formatting of numeric strings in SQL syntax. However, some databases may 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 PocketBuilder builds in DataWindow objects and reports 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.
This example shows how to specify that you want PocketBuilder to generate two numeric values in the format ‘125,50’ and ‘4,0’. PocketBuilder uses the comma as a decimal separator in the SQL syntax it builds in DataWindow objects and reports 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
PocketBuilder application script Type the following in a PocketBuilder application script:
SQLCA.dbParm = "NumericFormat = '''%s,%s'''"
What happens PocketBuilder internally builds the following SQL INSERT statement in the DataWindow object and sends the syntax to your database. PocketBuilder returns single quotes in the SQL syntax.
INSERT INTO MYTABLE (a, b)
VALUES ('125,50', '4,0')
