What format are HYDSTRA database tables stored in?
HYDSTRA uses Microsoft FoxPro compatible database tables and indexes. The tables have DBF suffix, the indexes CDX. You can read some of the tables with dBase III and above, but not the indexes. dBase cannot read tables which have memos in them, as the memo format differs between dBase and FoxPro.
Time-series data is not stored in FoxPro tables, it is stored in a proprietary compressed format, and it cannot be accessed by any of the methods described below. However there are simple tools like HYEXTR and HYCSV which allow you get at the time-series data in text format of you need to.
How are the DBF and CDX files related?
The DBF file contains the actual table data, possibly in a random order, and the CDX file is an index which makes the file appear to be in order. If you always manage the data using HYDSTRA then the CDX and DBF will be kept in step. If you make changes to the data outside HYDSTRA it is possible to get the CDX out of step with the DBF. If you think this might have happened the easiest thing is to delete the CDX file and then open the table in its manage. HYMANAGE will reindex the file if the index is missing.
HYDBU has a batch mode for reindexing one table. The INDEXALL option will reindex every registered database table.
Can I access the data using FoxPro?
Yes, but be careful about making any changes. If you make changes outside HYDSTRA you can break the integrity checks which the HYDSTRA manage enforces. For example you might create orphan records, or enter an invalid code into a coded field, or enter lower-case data into a field which requires it to be in upper case. In general we strongly recommend that you use only the appropriate HYDSTRA manage to work with databases.
As a rule of thumb all key fields, coded fields and most short text fields are kept in upper case, while longer descriptive fields should be entered with mixed case.
Can I read the data using ODBC?
Yes, Windows ODBC drivers are available for FoxPro tables. Hence you can read HYDSTRA tables from any Windows program which supports ODBC, including Excel, Reportsmith, Crystal Reports, etc.
Can I read the data using Excel?
Yes, Excel can directly read the tables if you open them as dBase files, but the memo fields come in blank. Under no circumstances should you ever save data back from Excel to HYDSTRA tables. Excel does not keep enough information about the table structure, and when it saves data back it invents a new structure, which will have different field lengths and types. The resulting table cannot be used by HYDSTRA, and you may corrupt data.
If you want to see the data in Excel and you need to see the memos, export the data to CSV format from HYMANAGE and then load the CSV file in Excel.
Can I read the data using Access?
Yes, Access can import FoxPro tables. Be sure to specify that the table is FoxPro rather than dBase, as the dBase memo format is different to the FoxPro memo format. Once you have the data in Access you can use all the query and reporting facilities of Access. Under no circumstances should you ever save data back from Access to HYDSTRA tables.
How do I get text data into a database table?
You need to create either a CSV file or a text file in SDF format, and then import the data using the appropriate manage or HYDBU APPEND. If you have the choice we recommend you use CSV format, as SDF format will gradually be phased out. Memo fields create problems in SDF format.
How do I know what fields are in a table?
You can examine the DBI file in \HYD\SYS\MISC\DBI. For example to discover the structure of the VARIABLE table, look at \HYD\SYS\MISC\DBI\VARIABLE.DBI. The DBI file is a text file in an INI file format, and the first part of the file describes the table format:
[VERSION]
VERSION=1
[FIELDS]
VARNUM=N,4,0
VARNAM=C,40,0
VARUNIT=C,20,0
SHORTNAME=C,20,0
PRECISION=N,8,6
REPORTFORM=C,5,0
UNITCODE=C,4,0
;***keyword-flag*** DBVER%v=L,1,0 ;D
DBVER7=L,1,0 ;Database version info
DATEMOD=D,8,0
TIMEMOD=N,4,0
USERMOD=C,8,0
The field syntax is of the form Field Name=Type,Length,Decimals. Field types include C (character), N (numeric), D (date), L (logical) and M (memo). If a file is N,8,6 it is numeric, 8 characters in length with 6 decimal places, e.g. 1.234567. The decimal point is explicitly included in the data when writing it in SDF or CSV format. The sign, if necessary, is also included in the field length, and precedes the numbers, e.g. -1.23. A missing or blank logical field is interpreted as False.
Most tables have a field with a name of the form DBVERn, which signals the version number of the table. HYDSTRA checks the version number when the table is opened, and will not open tables with an incorrect version. You may need to upgrade the table with HYDBU UPGRADE to bring it to the correct version.
Most tables have fields named DATEMOD, TIMEMOD and USERMOD, and these are used to hold the date, time, and user login of the last person to modify the record. You should set these to today's date and time when importing data form other sources.
What is CSV format?
CSV format is Comma Separated Values format. Fields are separated by commas, with text fields optionally surrounded by double quotes, for example:
10,"Rainfall","Millimetres","Rainfall (mm.)",0.01,"RAIN","MM",,,0,
11,"Rainfall","Inches","Rainfall (in.)",0.01,"RAIN","INCH",,"05/10/1999",952,"TGM"
14,"Dry Day","Days","Dry Day (Days)",0.5,"TOTAL","SCAL",,"24/06/1996",1047,"TMS"
15,"Wet/Dry",,"Wet/Dry",0.1,"LEVEL","SCAL",,"24/06/1996",1047,"TMS"
16,"Dry Day Flow","Cubic Metres","Dry Flow (CuM)",0.001,"TOTAL","M3",,,0,
20,"Rainfall Intensity","mm/hr","Rainfall Intensity",0.1,"RATE","MMH",,,0,
21,"Rainfall Intensity","in/hr","Rain Int. (in/hr)",0.1,"RATE","INH",,"05/01/1998",1921,"JEFF"
etc
In CSV format dates must be constructed in the appropriate local date format, for example DD/MM/YYYY in Australia and MM/DD/YYYY in the USA.
A missing or blank logical field is interpreted as False. Logical fields may use T, F, Y or N to signify true or false. Double quotes are optional.
HYMANAGE supports reading and writing of CSV files with memo fields in them. The memo data is written out a single ASCII string with any control characters replaced by their backslashed decimal equivalent, for example \013 for a carriage return.
What is SDF format?
SDF (Standard Data Format) format is a direct text representation of the database records. Each text field is the exact length and format of the corresponding database field. Records are terminated with CR/LF. Logical fields should have T or F in them, date fields should be 8 character YYYYMMDD (regardless of local date formatting conventions).Memo fields need to handled in a separate file, so leave 10 spaces for the memo in the SDF file.
The Clipper manage and HYDBU support import and export of SDF data. HYMANAGE does not support SDF format.
How do I import memos through SDF format?
Importing memos is quite messy using SDF format. You need to construct a separate file with .MEM suffix which contains the key information, the memo field name, and the memo text. The best way to see the exact format is to use a Clipper manage or HYDBU to export a table with memos to SDF and look at the MEM file. Use the ~ character to indicate a line break within the memo text, which should all be presented on one line.
What tools are available to help me write CSV or SDF files?
We provide a Perl library HYDBASE.PL in RUNPATH with routines to make writing SDF or CSV files fairly simple. The library routine reads the DBI file and parses it, so you simply need to crack your input data, assign values to named variables and write records. You can then use HYDBU APPEND or the appropriate manage to bring the data in.
The Perl library does not have any direct support for memo files in SDF format, you must write them yourself manually. However I does support writing memos in CSV format.
How can I export data from a table for use in other applications?
HYMANAGE has a facility to Export data to CSV format. If necessary you might like to unlink the table and apply a filter before you export. See HYMANAGE. HYDBU and HYDBUTIL also have batch export facilities.
How can I make bulk changes to data?
HYDBU has a REPLACE option which can replace a field with an expression, applying a filter at the same time. Be sure to back up your data before applying any bulk changes, as it is easy to get things wrong!
How can I import data into a single field in a table?
HYDBUTIL has an UPDATE facility which can copy fields from a working table into a production table. The working table needs to have the same structure as the production HYDSTRA table, but need only have the key fields and the field you want to update populated.
What controls whether a manage uses the Clipper manage or the Windows manage?
A number of parameters interact to decide whether the Windows manage is used or not:
At the highest level a HYCONFIG variable WINMNG (default to YES) determines whether to try and run the Windows HYMANAGE at all.
If WINMNG=YES then if the menu entry in the MENUS database for a manage is consulted. If the menu entry has a JOBTYPE of 'N' (New manage) then the manage is a Windows candidate.
If the MNG is a Windows candidate the MNG file is opened to see if Delphi=Yes.
If all of these conditions are met, the Windows manage is used, otherwise the old Clipper manage is used.
