How to precisely define numeric datatypes in foxpro table using clarion dictionary

[xxetfxx]

We need to integrate our software into an old foxpro based program. The requirement should be that we could produce a specific required foxpro file that can be read by their program. Right now I am having problems on REAL datatype defined in clarion for that specific foxpro table(REAL in clarion = FLOAT in foxpro counterpart) because it can only accomodate up to 10 maximum digits including decimal. but upon on documentation it can accomodate up to 20 digits (20,18)
If my value writing to specific REAL datatype on the foxpro file exceeds that 10 digit, it gets me a record insert error

Is there a proper way of defining REAL datatype in foxpro tables assigning their maximum digits?

[PurpleEdge2214]

No idea, but could you do it with a decimal definition instead of REAL?

[xxetfxx]

there is no DECIMAL option in the dictionary when the file driver selected is FoxPro, same with DBase files and DOS files

.
I tried creating the table in the source first but I could not be able to identify the errors, so that I put it in the dictionary first.

[Marius_Nel]

Try STRING, maybe that will work?

[PurpleEdge2214]

Ah, that’s a real gotcha! (No pun intended )

Perhaps run the value through a local decimal first and then plug it back into the database REAL?

e.g.

Loc:DecimalNumber = dat:RealNumber
dat:RealNumber = Loc:DecimalNumber

[xxetfxx]

Sorry for misunderstanding, you mean this?

I have predefined my source value from decimal data type

when my value exceeds 10 digits(including decimal and decimal point, not including comma separated) I still get the bad data error, sample pic is the value that i need to put to the foxpro record

I have tried other workaround such as:

• Modifying the actual dbf file created by altering all REAL column data type by increasing their size to be able to accomodate large numeric values using DBF Viewer
  

  
  
  I tried adding large numeric values directly in this dbf viewer, no problem, but clarion still does not allow adding large numeric values exceeding to 10 digits. The only thing that works after altering the table originally produced by my clarion program is it does not produce invalid record declaration error.

• Another workaround I tried is importing that modified dbf file back to clarion dictionary. Handling REAL datatypes defaults to @n10.2 , which is still doesn’t work
  

  

[xxetfxx]

I think this could be my last workaround, but as per the documentation for the integration, they require numeric field instead of alphanumeric for every specific column that requires numeric value.

[PurpleEdge2214]

Try defining your decimal as (10,2) instead of (12,4)

This might not work as I don’t know how a Clarion REAL gets sent to Foxpro, it’s possible that all Clarion REALs are going to be too big?

My theory behind the suggestion is that assigning the value to a decimal 10,2 will trim the output to the REAL to 10 digits. Easy enough to try.

BTW, the picture @N12.4 only affects the screen display, not the size of the data.

[seanh]

Perhaps the foxpro real is only a 4byte real not an 8byte like clarion.
If so maybe all you need to do is round the decimal portion to fit?

[jslarve]

There’s SREAL for that, if that’s the case.

[vitesse]

Not sure if you already have this solved but first check what FoxPro data format they are using as the format evolved from the old dBase format and changed a bit over different versions. Perhaps Clarion supports the format you want or perhaps it doesn’t. If it doesn’t you can simply write the data out yourself using StringTheory or even the DOS driver.

But first you need to find what FP version they are using and get the data format.

[xxetfxx]

using SREAL shows me this error

[xxetfxx]

Not yet, I’m curious about how to create it using stringtheory though as what you have said. Right now I am still waiting for their data format.

[xxetfxx]

I have tried changing my source data to DECIMAL(10,2) and also apply ROUNDING(value,0.1) but still getting the bad data error. Thanks for helping me though.

[Bruce]

I think maybe the FoxPro field is indeed an SREAL, but the Clarion FoxPro driver does not support an SREAL.
In which case you’re likely fresh out of luck.

You might be able to map it as a STRING(4) in the clarion record structure, and manually manipulate the bits to “match” an SREAL bit pattern. That’s a lot of work but might work if the driver doesn’t get too anal about the field type matching. (ie no error47).

[vitesse]

If they can give you an example file that will answer your questions.

See
https://www.dbf2002.com/dbf-file-format.html

You can see the first byte will give you the file format and the header has an array of fields so you see the field types.

Re using ST simply use append and then savefile when done. After writing the header it would be simplest to have a local record buffer with all the fields and then append that once per record.

Similar with the DOS driver but would probably be more work than using ST.

Certainly this is more “old-school” work so only do this if you can’t get the driver working.

Hth

Cheers

Geoff R

Edit#1. A hybrid approach would be to use a string of the required length as Bruce suggested with the data formatted as required and then post-process to change the field type byte in the header.

[seanh]

Why not use an SReal on the Clarion side then at the last bit assign that to a Real. It should have the appropriate bounds at that point.

[vitesse]

I asked Gemini: What is the difference in dbf file between field types F and B.

In a .dbf (xBase) file, both F and B field types are primarily used to handle floating-point numbers, but they differ significantly in their internal byte storage and the database dialects that support them.

​Here is the technical breakdown:

​1. Field Type F (Float)

• ​Storage Format: ASCII Text (String)
• ​Size: 1 to 20 bytes (defined in the field descriptor).
• ​How it works: Just like the standard N (Numeric) type, an F field stores the number as plain, human-readable ASCII text, right-justified and padded with spaces. For example, 123.45 is physically written into the file as the text string " 123.45".
• ​History: Introduced in dBASE IV. In legacy dBASE, F was used to instruct the engine to use floating-point math internally, whereas N used Binary Coded Decimal (BCD) math. On disk, however, F and N are structurally identical.

​2. Field Type B (Double / Binary)

​The B type changes meaning depending on the specific engine that created the .dbf file:

• ​In Visual FoxPro (Double Precision):

  • ​Storage Format: Raw Binary

  • ​Size: Fixed at 8 bytes.

  • ​How it works: Unlike F, it stores the number natively as a 64-bit IEEE double-precision floating-point value (little-endian) directly in the file. Because it avoids ASCII string conversion, it requires less disk space for high-precision numbers and is much faster for the engine to calculate, though the raw bytes are unreadable in a text editor.

  .

  • ​In other dBASE variants (Binary Memo):
    • ​Storage Format: Integer Pointer (typically 4 or 10 bytes).
    • ​How it works: It acts as a reference pointing to a Binary Large Object (BLOB) stored in a separate, external memo file (like a .dbt or .fpt), rather than storing the actual binary data inside the .dbf record itself.

  ​Summary for Parsing Data

  ​If you are writing software to parse a .dbf file:

  • ​When you encounter an F, read the specified number of bytes as an ASCII string and cast/evaluate it to a float.
  • ​When you encounter a B, you must check the DBF header (byte 0) to determine the database version. If it is a Visual FoxPro table (e.g., 0x30), read those 8 bytes directly into a 64-bit float variable. If it is a legacy dBASE file, treat it as a block pointer to an external memo file.

[xxetfxx]

I have received their actual dbf file and for now I will use that as a template for generating data because of their urgency compliance
DBF files.zip (953 Bytes)
I have uploaded the two versions of files that I need to create.
the Actual_Daily_File.DBF is their sample file provided while Clarion_Daily_File.DBF is the one created by clarion using CREATE() command.

I have used this for now as a template format and use COPY() command instead of directly creating the file using CREATE(). Using their file and writing the required data leaves me no error, while using the clarion created file gives the bad data error. I will try Bruce and vitesse suggestions and will post the solution here once I successfully created the dbf file using string theory.

Thanks for helping.

[vitesse]

Hello again. I am travelling with only my phone and no computer so it is hard for me to have a look. However, I ask Claude this time and he/it said this:

DBF FILE LAYOUTS
================

Both files are dBase III (version byte 0x03, no memo file), header length
705 bytes, with the same 21 fields in the same order. The only difference
is the width and decimal places of the numeric fields.

Actual = Actual_Daily_File.DBF
Clarion = Clarion_Daily_File.DBF

 #  Field       Type  Actual  Clarion
--  ----------- ----- ------- -------
 1  TRANDATE    Date  8       8      
 2  OLDGT       Num   20,5    9,2    
 3  NEWGT       Num   20,5    9,2    
 4  DLYSALE     Num   20,5    9,2    
 5  TOTDISC     Num   20,5    9,2    
 6  TOTREF      Num   20,5    9,2    
 7  TOTCAN      Num   20,5    9,2    
 8  VAT         Num   20,5    9,2    
 9  TENTNAME    Char  10      10     
10  BEGINV      Num   20,5    9,2    
11  ENDINV      Num   20,5    9,2    
12  BEGOR       Num   20,5    9,2    
13  ENDOR       Num   20,5    9,2    
14  TRANCNT     Num   20,5    9,2    
15  LOCALTX     Num   20,5    9,2    
16  SERVCHARGE  Num   20,5    9,2    
17  NOTAXSALE   Num   20,5    9,2    
18  RAWGROSS    Num   20,5    9,2    
19  DLYLOCTAX   Num   20,5    9,2    
20  OTHERS      Num   20,5    9,2    
21  TERMNUM     Num   20,5    9,2    
--  ----------- ----- ------- -------
    Rec length        399     190    
    Records           2       0      

NOTES
-----

1. In Actual_Daily_File, record 1 (dated 20260101, TMB) is flagged as
   deleted (*). Record 2 (20260902, DENIM HUB) is live.

2. The Clarion file's N(9,2) fields cannot hold values in the real data.
   For example, OLDGT is 16,546,141.96 in record 2, which needs at least
   11 characters at 2 decimal places. N(9,2) tops out at 999,999.99.

3. Byte 29 (the language driver) is 0x00 in both files, so no codepage
   is declared.