I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

April Edition
Subscribe now! It's Free!
Enter your email:

   ► KBDesktop Data...Paradox & Ob...Paradox Tables   Print This     
  From the April 2015 Issue of Prestwood eMag
 
Paradox Tables:
Paradox Table Specifications
 
Posted 17 years ago on 11/19/2002 and updated 7/12/2009
Take Away:

Covers ALL Paradox tables from version 4 on including a description of the various Paradox table structures, limits, and specifications.

KB100060



Paradox Standard Table Specifications

There are ONLY four Paradox table formats. The Paradox 1-3x, 4.x (or standard), 5 and 7. By default, the BDE creates Paradox 4 tables and automatically upgrades your tables to Paradox 5 or 7 if you use a new feature that requires the newer Paradox formats.

Also known as Paradox 4 table structure.

The Paradox standard table format was introduced in Paradox for DOS version 4. Other products that use the standard format include Paradox for DOS version 4.5, ObjectVision 2.1, and Paradox for Windows versions 1.0 and 4.5.
Earlier versions of the Paradox table type are referred to as the Compatible table type. In the BDE Configuration Utility, the level option for the Paradox driver dictates what default table type is created by Paradox for Windows. Use 3 for Compatible tables, 4 for Standard tables (the default). Following are the specifications for standard Paradox tables.

  • 256MB file size limit if the table is in Paradox format and using a 4K block size.
    Up to 255 fields per record.
    Up to 64 validity checks per table.
    A primary index can have up to 16 fields.
    Tables can have up to 127 secondary indexes.
    Up to two billion records per file. Because of the 256MB file size limit and other factors such as block size, however, the limit is much smaller. Tables of 190,000 records are easily achievable (and you can have more if you don't use up the 1,350-bytes-per-record limit for a keyed table). Tables with close to a million records are common. 
     
  • Block size can be 1024, 2048, 3072, or 4096. Paradox stores data in fixed records. Even if part or all of the record is empty, the space is claimed. Knowing the interworkings can save you disk space. Paradox stores records in fixed blocks of 1024, 2048, 3072, 4096 in size.
     
    After a block size is set for a table, that size is fixed, and all blocks in the table will be of that size. To conserve disk space, you want to try to get your record size as close to a multiple of block size as possible (minus 6 bytes, which are used by Paradox to manage the table).
     
  • Record size. 1,350 for keyed tables and 4,000 for unkeyed tables. When figuring out the size (the number of bytes or characters) of a table, remember that Alpha fields take up their size (for example, an A10 = 10 bytes), numeric field types take up 8 bytes, short number field types take up 2 bytes, money takes up 8, and dates take up 4 bytes.
     
  • Memos, BLOBs, and so on take 10 bytes plus however much of the memo is stored in the .DB. For example, M15 takes 25 bytes.

Paradox 5 Table Specifications

The Paradox 5 table format was introduced in Paradox for Windows version 5. Following are the specifications for Paradox 5 tables.

  • Up to two billion records per file.
     
  • File size is limited to two gigabytes.
     
  • Up to 255 fields per record.
     
  • Record size: Up to 10,800 bytes per record for indexed tables and 32,750 bytes per record for nonindexed tables. When figuring out the size (the number of bytes or characters) of a table, remember that Alpha fields take up their size (for example, an A10 = 10 bytes), numeric field types take up 8 bytes, short number field types take up 2 bytes, money takes up 8, and dates take up 4 bytes.

    Memos, BLOBs, and so on take 10 bytes plus however much of the memo is stored in the .DB. For example, M15 takes 25 bytes.
     
  • Up to 64 validity checks per table for Paradox for Windows tables.

    A primary index can have up to 16 fields.
    Tables can have up to 127 secondary indexes.
    Block size can be from 1K to 32K in steps of 1K. For example, 1024, 2048, 3072, 4096, 5120...32768.

Paradox 7 and above Table Specifications

The Paradox 7 table format was introduced in Paradox version 7 for Windows 95/NT. The Paradox 7 table format has all the same specifications as the Paradox 5 table format with two additions. Following are the specification additions for the Paradox 7 table format.

  • Added descending secondary indexes.
  • Added unique secondary indexes

Paradox Field Types

Alpha (A)

Paradox 3.5, 4, 5, and 7 field type that can contain up to 255 letters and numbers. This field type was called Alphanumeric in versions of Paradox before version 5. It is similar to the Character field type in dBASE.

Autoincrement (+)

Field type introduced in the Paradox 5 table format that adds one to the highest number in the table whenever a record is inserted. The starting range can from  -2,147,483,647 to 2,147,483,647. Deleting a record does not change the field values of other records.

BCD (#)

Paradox 5 and 7 field type which is provided only for compatibility with other applications that use BCD data. Paradox correctly interprets BCD data from other applications that use the BCD type. When Paradox performs calculations on BCD data, it converts the data to the numeric float type, then converts the result back to BCD. When this field type is fully supported, it will support up to 32 significant digits.

Binary (B)

Paradox 1, 5, and 7  field type that can store binary data up to 256MB per field.

Bytes (Y)

Paradox 5 and 7 field type for storing binary data up to 255 bytes. Unlike binary fields, bytes fields are stored in the Paradox table (rather than in the separate .MB file), allowing for faster access.

Date (D)

Paradox 3.5, 4, 5, and 7 as well as dBASE III+, IV, and V. dBASE tables can store dates from January 1, 100, to December 31, 9999. Paradox 5 tables can store from 12/31/9999 B.C. to 12/31/9999 A.D.

Formatted Memo (F)

Paradox 1, 4.5, 5, and 7 field type is like a memo field except that you can format the text. You can alter and store the text attributes of typeface, style, color, and size. This rich text document has a variable-length up to 256MB per field.

Graphic (G)

Paradox 1, 5, and 7 field type can contain pictures in .BMP (up to 24 bit), .TIF (up to 256 color), .GIF (up to 256 color), .PCX, and .EPS file formats. Not all graphic variations are available. For example, currently you cannot store a 24-bit .TIF graphic. When you paste a graphic into a graphic field, Paradox converts the graphic into the .BMP format.

Logical (L)

Paradox 5 and 7 and dBASE III+, IV, and V field type can store values representing True or False (yes or no). By default, valid entries include T and F (case is not important).

Memo (M)

Paradox 4, 5, and 7 as well as dBASE III+, IV, and V field. A Paradox field type is an Alpha variable-length field up to 256MB per field. dBASE Memo fields can contain binary as well as memo data.

For Paradox tables, the file is divided into blocks of 512 characters. Each block is referenced by a sequential number, beginning at zero. Block 0 begins with a 4-byte number in hexadecimal format, in which the least significant byte comes first. This number specifies the number of the next available block. It is, in effect, a pointer to the end of the memo file. The remainder of Block 0 isn't used.

Money ($)

Paradox 3.5, 4, 5, and 7 field type, like number fields, can contain only numbers. They can hold positive or negative values. Paradox recognizes up to six decimal places when performing internal calculations on money fields. This field type was called Currency in previous versions of Paradox.

OLE (O)

Paradox 1, 5, and 7 as well as dBASE V field type that can store OLE data.

Number (N)

Paradox 3.5, 4, 5, and 7 as well as dBASE III+, IV, and V field type can store up to 15 significant digits -10307 to + 10308 with up to 15 significant digits.

dBASE number fields contain numeric data in a Binary Coded Decimal (BCD) format. Use number fields when you need to perform precise calculations on the field data. Calculations on number fields are performed more slowly but with greater precision than are calculations on float number fields. The size of a dBASE number field can be from 1 to 20. Remember, however, that BCD is in Paradox 5 and 7 only for compatibility and is mapped directly to the Number field type.

Short (S)

Paradox 3.5, 4, 5, and 7 field type that can contain integers from -- 32,767 through 32,767 (no decimal).

Time (T)

Paradox 5 and 7 field type that can contain time times of day, stored in milliseconds since midnight and limited to 24 hours.

This field type does not store duration which is the difference between two times. For example, if you need to store the duration of a song, use an Alpha field. Whenever you need to store time, make a distinction between clock time and duration. The Time field type is perfect for clock time. Duration can be stored in an Alpha field and manipulated with code.

TimeStamp (@)

Paradox 5 field type comprised of both date and time values. Rules for this field type are the same as those for date fields and time fields.

Paradox Table File Types (.DB. .PX, .TV, .FAM, etc.)

The following are some notes on the various Paradox table files I've collected since 1992.

[6/8/2008: I have lot's of notes on each of these files and will add to these notes as I have time.-MP]

.DB

Main data file. This contains all of the tables data except for what is in the MB file.

The DB file is the central file of a Paradox table. It contains most of the data (except BLOb data which is in the MB file) as well as critical header information.

DB Header File:

Byte Offset

Use

0-1h

Record buffer size

2-3h

Block Size

4h

We believe this is Table type (00 for paradox)

6-7h

Number of records

21h

Number of fields in the table

49-50h

Next AutoIncrement Value


.FAM

Database Desktop's listing of related files (like a table's .TV file). (not used by the BDE).

.LCK

Lock file. Created as needed by the BDE to lock either specific records in a table (a record lock) or the whole table (a table lock).

.MB

BLOb file. This is were a BLOb data is kept for Memo, Formatted Memo, Graphic, OLE and Binary field types.

.PX

Primary Key

Just like a DB file the PX, Xnn, Ynn, XGn and Ygn index files have a header block and data blocks. If the header is corrupt the BDE complains about invalid sort order or other index-related problems. Just like with DB files index file blocks can become garbled resulting in an error, garbage in returned records or a freeze.  Like DB files index file blocks are connected by pretty much the same double-link scheme using the first four bytes of each block and can suffer the same link problems. In addition indexes can get "out of date" with the data file, like if the DB file is written to disk but the computer is turned off before all of the index files are updated.

.TV

Table view settings used by Paradox for Windows (not used by BDE)

.VAL

Validity checks and referential integrity.

Sometimes It's Okay to Delete the .VAL Files
Referential integrity and other settings stored in .VAL files sometimes interfere with the development of a project. This particularly is a problem when you are restructuring a child table in a referential integrity link. If this happens to you and you don't mind losing the validity checks for a table or for a set of tables, go ahead and delete or, better yet, rename them.

.Xnn/.Ynn

Secondary single-field index. nn=number of index. Always found in pairs (.X01 and .Y01).

.XGn/.XYn

Composite secondary index. n=number of index. Always found in pairs (.XG1 and .YG1).



More Info

Tip:  Deleting .Val files is sometimes okay and necessary.
Code:  ObjectPAL File Extensions
Article:  Power: Chapter 02, Tables and Developing

Linked Message Board Threads

 Edit .VAL Values?? in Paradox Tables MB Topic (3 replies)
 paradox file extensions in Paradox Setup, Vista, etc. MB Topic (1 replies)

Comments

1 Comments.
Share a thought or comment...
First Comment
Comment 1 of 12

"Paradox Standard Table Specifications" 

The section under number is displaying the range as -10307 to +10308.  I think you mean from -10 to the power 307  to +10 to the power 308.

Thanks for the article - I couldnt remember the max record size for a Paradox 4 table, and I didnt know that it had been expanded in a Paradox 5 table.

Posted 11 years ago

Comment 2 of 12
Good catch. Thanks. We've migrated this article several times and we lost the superscript. It's fixed now. Thanks again.
Posted 11 years ago

Comment 3 of 12

Is there any documentation on the lock file format? Specifically the pdoxusers.lck file. We are having some issues on Windows 2003 with this file being 'locked' by a random user and blocking other users from updating the file...

Posted 8 years ago

Comment 4 of 12

"Is there any documentation on the lock file format?"

I wrote a utility to monitor the BDE locks, which might give you what you need. Either google 'lockwise bde' or go here:

http://home.comcast.net/~j.breveleri/lockwise.html [[NO LONGER AVAILABLE. --MP]]

Read the included file "lockinfo.txt" for a good description.

-Al.

Posted 8 years ago

Comment 5 of 12

Thanks, I'll check it out.. I also discovered that we had a few users logging onto to multiple terminal servers and running the same app. I am assuming that BDE gets confused since the user name is store in the lock file...

Posted 8 years ago

Comment 6 of 12

The BDE has no trouble keeping track of multiple sessions with the same user net name.  The locking architecture allows up to 300 simultaneous sessions, and as far as the BDE is concerned, all 300 could have the same net name.  (Note: I haven't actually tested this.)

A single instance of the MS Windows OS will only run a single instance of the BDE.  Normally, each Paradox or BDE executable will open one session.  When one of my users runs N of my DB applications simultaneously, his name appears N times in PDOXUSRS.NET with different session IDs.

The lesson to take away is that the application administrator and the users become confused long before the BDE does.

-Al.

Posted 8 years ago

Comment 7 of 12

I would like to contact Mike concerning the Blob data file in Paradox. I program database programs in Vb. I started with Access files and now busy on SQL. I don't know much of delphi. I end up a few weeks back finding myself in trouble because I have a paradox database that have blob images stored. Now i am trying to retrieve that images and to store it back into SQL. Now with the ODBC I can retrieve the data but not the blob. I do have the BDE administrator on my system. I also have some database tool that I downloaded from the internet. If it connects through my BDE administrator I can see all my fields even the blob field is included but i can't do anything with it, I can only view the data. So I thought if there is a way that i can connect with Vb straight through the BDE then I should be able to access the blob field. Now I don't know how to do it. With SQL it is easy because atleast i have a Active X control that I can use to connect but with BDE I don't know if there is something like that availabe or I don't know which DLL to use. Is there a way that you can help.

I am from South Africa

My name is Hendrik

My email is haneef.vdm@gmail.com or haneef.vdm@eject.co.za

If there is a way that you can help me i would appreciate that.

Thanks

---
Hendrik
Posted 8 years ago

Comment 8 of 12

Note to Hendrix: You and may others are in the same boat. I have found a few programs which claim to be able to extract object as well as text data, but none have succeeded.

Tony McGuire was developing this solution for graphics, which he concluded were stored in binary files onhttp://www.tek-tips.com/viewthread.cfm?qid=1189318  I haven't gave it a quick run for documents, but I didn't have time to play with it.

 var
tc   tcursor
img  image
bn   binary
endvar

tc.open(":alias:images.db")
scan tc :
  img=tc."fieldname" ; field containing graphic
  img.writetofile("path\\imagename.ext") ; .ext likely .jpg/.gif/.etc
endscan
tc.close()

Another clever Mac User suggested this work around:

http://tek-tips.com/viewthread.cfm?qid=1668297

Mike Prestwood, if you are addressing the issue, I too would like to hear about it.

Posted 7 years ago

Comment 9 of 12

Hi Mike, I wonder if you can help? I am using paradox 9 (although some of my tables are from earlier versions).  I want to make a table of 2.9 million records.  At the moment I am struggling to query linked  tables of 1.5 million records (130mb) & 156,000 records (56 mb).  I have changed the block size which was at 16384 to 32768 but I have read this caused problems in earlier versions.  I have added the data to new tables.  So far queries are still slow though I am now not getting table full message.  could it be that the default table size is set for 256 mb?  how could i change this?  all the best Claire

---
Claire
Posted 6 years ago

Comment 10 of 12

correction!  I am still getting full table message...

---
Claire
Posted 6 years ago

Comment 11 of 12

I setup a Paradox file years ago to handle part number descriptions in the mower trade. This has increased in size and complexity over the years and I've split it up into a separate file for each manufacturer.
The Honda file has the following BDE data:
Paradox 7, Block Size: 32, Rec.Size: 1840, No.of Recs: 194269, Fields: 95
Drive: Paradox ASCII, Code Page: 850.
This worked fine until I added the last update of 250 records to it to take it up to the 194,269 record total at which point I get a Corrupt Header message if I try to "Locate" any record numbers above 138,500.
The cursor goes to 138,500 ok but the data it shows is up in the Zs area which is close to the end which should be around 194,000. If I page down to arrive at the end of the data the cursor goes down to the end but registers 138,515 (nearly 60,000 short) and a page down after that alters the record number to 194,259 and any further attempts to click in the data results in a blank page.
The index is on the first field which is 80c long and I get the impression that the creation of the index file (.px) is cut short during its creation.
I've tried various methods of copying, restructuring, re-indexing, table repairing on various computers' C: drives in the office including network drives and in every case the file works fine unindexed but as soon as I apply the 80c index I run into trouble.
I don't understand why it has worked fine until now but the Honda file is the largest so I'm drawn to the problem being some sort of issue relating to file size.
Has anyone got any ideas?

Thanks and regards,
Bernard Norrie

Posted 6 years ago

Latest Comment
Comment 12 of 12

I missed the Long Integer in the 'Paradox Field Types' section.

:-)

Posted 22 months ago
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1222A1
Enter key:
KB Post Contributed By Mike Prestwood:

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Visit Profile

 KB Article #100060 Counter
50450
Since 4/2/2008
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech