DD numeric definition for SQL validation

[PMM_PxP_User]

Greetings all. Our rule of thumb with Data Dictionary numeric elements is to make them delimited, and we define them using this example in the PxPlus manual:

6.2: 6 represents total length of the field, including explicit signs and decimals, where applicable; 2 represents scaling factor or number of decimal places

So from that, we get that 999.99 is valid, but 9999.99 is too large, because the integer portion of the number is 6 minus 1 (the decimal point) minus 2 (the decimals), which is 3.

But when we consider the checkbox "Force Data Validation on Write/Update," the validation of the integer portion of the number -- using the above example -- appears to max out at 5 digits (decimal point separate), not 3. The documentation references the IOLIST directive for more detail about this data validation.

We're planning to expand our numerics so that, when exported to SQL, they are large enough to not cause an invalid number error of some kind. Does anyone know if the SQL validation follows the PxP validation rules?

I.e. if we have a 6.2 element, then it would accept a 99999.99 (or -99999.99), and by extension 999999.99 would cause an error?

Examples for illustration would be greatly appreciated. TIA,

Paula McKeever
CAI Software

[pmpenchoff]

Hi Paula,

Please find a link to all data types used in SQL.
https://www.w3schools.com/sql/sql_datatypes.asp
Typically I have used the DOUBLE def for numbers.

Mike P.

[PMM_PxP_User]

Thanks Mike; that's a keeper link for sure. Not sure what it means in terms of my question though? We're seeking to potentially use an external SQL db for our Data Dictionary defined tables, and want to make sure that when we write to those tables via our PxPlus code, that the DD definitions for the numerics are of sufficient size and decimal setting. The PxP documentation was for me, on the surface, a little conflicting, but I'm betting there's an explanation out there that will clarify what we would need to do to adapt those DD definitions.

[PMM_PxP_User]

This is sort of a continuation of the original post.... We created a SQL table via PxPlus's database conversion util and have been doing some rudimentary testing to see if we can figure out what kind of rules we need to fashion for defining numeric size/precision in the DD. I.e. we've been setting numeric elements and writing them to the table that's opened via the [ODB] tag.

Let's take a numeric defined as 5.2 in our DD as an example. When we created the table, it showed as
CREATE TABLE SQL_Test
(TEST_KEY varchar(6) NOT NULL,
NUMBER_1 decimal(5,2), etc.

If I set the elem to 123.456789, the write doesn't error, which is what I was expecting. When I look at the data via SQL Svr Mgmt Studio, it shows  AS 123.46 ...rounded.

What's making it round? Is it it something on the PxPlus end or the SQL end?

TIA.

Paula McKeever
CAI Software LLC

[Devon Austen]

I Just tried updating a field in a SQL Server database with a non PxPlus program. The field was defined as decimal(10,2) and I set it as 55.123456789. It is now in the DB as 55.12.

I think this confirms that this is SQL Server behavior or more likely SQL behavior.

[Devon Austen]

I also tried 55.12999 and it becomes 55.13 in the database so SQL Server is rounding to the 2 decimal places.

[PMM_PxP_User]

We've just discovered in the documentation for [ODB] open that the open can have parameters, including CHECK_NUMERICS. (Y/N) That seems to enforce the decimal check on the write. We're going to explore this further.

Still a little fuzzy about the "Force Data Validation on Write/Update" checkbox in DD Maint; not only does the test of the first number (e.g. the "8" in 8.2) seem to include the decimal point in the count, but that first number is for the digits to the left of and including the decimal point, not the total number of digits in the entire number (scale included, decimal point excluded) like for a write to SQL. If I understand correctly, a valid max for an 8.2 is

"Force Data Validation on Write/Update": 1234567.12
[ODB] write to SQL with CHECK_NUMERICS on: 123456.12


Paula McKeever
CAI Software