Top Speed Retriever

Usage
Introduction
Top Speed Retriever, or just Retriever, is a utility program that allows you to analyse, query, and extract data from most Clarion native database files (TopSpeed *.TPS).

The program will not retrieve data from Clarion super files (rarely used files with multiple tables inside). Before purchasing Retriever we strongly recommend that you download the free trial version and take all the time you need to ensure that it is the right tool for you. The free trial version is identical to the full Retriever, except that it will only allow you to retrieve every second record from a TopSpeed file. 

Occasionally while using a Clarion program you may need to extract data directly from TPS data files
and handle them from another program like MS Excel. For example, you may want to export all emails sent today,
last week or last month from an EMAILLOG.TPS. However, your program lacks an appropriate report and program
 developer - long since retired. This is when you'll find Top Speed Retriever extremely useful.
All you need to do is simply select the list of fields you want, define a basic query like: 
CUS:EmailDate = TODAY()  And you are ready to go.
After you tune this query, you can save it for use as a Windows Scheduled Task to automatically and
regularly repeat this export.
Usage
Basics
  • If the TPS file is encrypted, specify password in the TPS password prompt, or if not encrypted just leave it blank.
  • Select the TPS file with the lookup button next to the Input TPS Location prompt.
  • Specify either a directory or a CSV file name in the Output CSV Location prompt. In the first instance
    the TPS file name will be used. So Customer.TPS will go into Customer.CSV In the second instance the output
    will always go into the CSV file you specified. In both cases you can append a date stamp to the file name
    with Date Stamp in Export checkbox.
  • Hit the Fetch button. All columns and all records will be output into CSV file. It is a default button so
    you can use your Enter key as a shortcut.
SELECT Box
SELECT text box is optional and has a very small vocabulary. It only recognizes field names fetched from the current
TPS file and the few reserved words: see below. You can combine them any way you like. If you leave Select Box 
blank, all fields in the TPS file will get selected.
  • Fetch Field button (above) allows you to define a list of fields to go into the CSV. Retriever automatically 
    appends a comma. You can use any other separator including spaces. So CUS:PTR,CUS:SNAME,CUSLFNAME
    will select these 3 fields from a CUSTOMER file. For each new TPS file the fetch button lookup will be empty, 
    so you'll need to use the Fetch button to get the field names to populate this lookup. If you use fields 
    in WHERE, code will automatically select them too.
  • TOP N (TOP 3, TOP 50 etc.) will make Retriever take the top N records in the TPS file. It saves time when 
    you fine tune your query as you don't have to wait until all records are processed.
  • BOTTOM N (BOTTOM 3, BOTTOM 50 etc.) will make Retriever take the bottom N records in file. 
    You can use it to process a TPS file in reverse order. Remember to use a number that is bigger than the 
    total number of records. For instance, BOTTOM 1000000 - or any very big number will do the trick.
  • Both Top N and Bottom N commands have 2 special modes. TOP -1 (or BOTTOM -1) will bring just the Header. 
    TOP -2     (or BOTTOM -2) will bring the Header and also column data types with sizes. However, you only need 
    these modes if you're interested in the file structure and want to output just Header or Header and data types 
    into CSV. The Fetch button will populate Header automatically if necessary.
  • STARTAT P (STARTAT 80, STARTAT 100 etc.) Will process file starting from P pointer (internal System ID in all TPS files)
    You can see them in the far left column as CUS:PTR, OWN:PTR etc. You don't need to be too precise! If you say:
    STARTAT 10 and there isn't a record with pointer 10, it will automatically start from the next available pointer, say 12. 
    You can use it in conjunction with TOP N and BOTTOM N. Just remember to put spaces between them.

WHERE Box
The WHERE text box is also optional. Here you can define a query to filter records.  
Shipped with the Retriever are many examples of logical expressions you are able to use in the Queries . 
Feel free to play with them. We also ship a bunch of TPS in .\IN\ for testing.
Don't worry if you stuff them as you can always restore the whole set-up file from .\Backup\ 
The basic rules are: 
Use single quotes with strings, say CUS:NAME > 'M' Quotes are optional with numbers. Both CUS:ID < 232
and CUS:ID < '232' will do.
And here is the really neat thing: because  the WHERE box uses Clarion run-time compiler, you can use many
 built-in Clarion functions. Such as: DAY() MONTH() YEAR() DATE() INSTRING() SUB() TODAY() CLOCK() etc.
You can find many examples in the Queries. Please refer to online Clarion sources to learn more.  

Time/Date
Clarion Time & Date data types are a bit tricky. Clarion supports SQL TIME & DATE but they're rarely used.
If you see these, it is definitely Time & Date and Retriever will automatically mark them as such in the Fetch Field. 
Check "DateTime" query. You will see that these data types are SQL TIME & DATE (Data type as seen from 
Fetch Field is TIME & DATE).
But Clarion native Time & Date are just LONG 4 byte numbers representing "the number of hundredths of a second that have 
elapsed since midnight" and  "the number of days that have elapsed since December 28, 1800" respectively.
(The latter is easy to remember because Charles Goodyear (the tyre guy) was born on the 1st Clarion day - December 29, 1800)
There is therefore no reasonable way to tell if a field is Date or Time or just some other number.
So in the Fetch Field lookup we mark such suspicious fields in the "d?"  and "t?" columns. So it is up to you to mark them
 as "D" and "T" (just tick/untick respective column). 
Look for clues. Firstly: It is obvious that the word "Date" in a field name is a date. This also applies to time. 
The second clue refers to field values. For instance, 9/01/2020 is number 80000 
(80000 days elapsed since December 28, 1800. So Mr. Goodyear would be 80000 days old!). 
Therefore,  if most of the numbers in a column are in the vicinity 0f 70,000-80,000, and the Type is LONG, it is probably a Date.
Ideally, time has to stay in the range 0 - 8,640,000 (11:59:59.99 PM; 1 is midnight, 0 - not set).
This is what we use to mark columns as possible Dates. Unfortunately, this method is not always 100% reliable. This is 
because Clarion sometimes inserts corrupted records with weird numbers. So be aware of this and don't be fooled!
Once you have marked a column as a date you can use any formatting for the Time/Date fields with the combos on the top right.
You can't use anything like '31-12-2019' or '23:45:59' in logical expressions. Instead you need to use the Time/Date functions:
TIME(23,45,59) or DATE(12,31,2019)  (month first!). Say CUS:StartTime < TIME(23,45,59) or 
CUS:FirstDate  < DATE(12,31,2019) There are many examples in the Queries.
The only exception to this is when you use FORMAT() : FORMAT(CUS:FirstDate,@D06) = '31/12/2018' See "Format" example query.
@D06 is called Picture. You can find this in clipboard once you select Date or Time Format.
This allows you to paste it and use in FORMAT() as the second parameter.
The 3 Clarion date functions - DAY(CUS:FromDate) MONTH(CUS:FromDate) YEAR(CUS:FromDate) - you may also find useful.
So if CUS:FromDate is '31-12-2019' they would return 31, 12, and 2019 respectively.
Clarion does not have functions to process Time. So we've built them for you. If you feed HOUR() MINUTE() SECOND() with
a Clarion Time field, say  HOUR(CUS:StartTime) and CUS:StartTime = '23:45:59', they return 23, 45 and 59 correspondingly. 
We've also built a function similar to Clarion DATE() - TIME() It expects (HH,MM,SS) in military format - 23:45:59. 
So TIME(23,45,59) will return Clarion Time of 11:45:59 PM.
Both TIME() HH, MM, SS & DATE() MM, DD accept positive out of range numbers. Therefore there is nothing wrong with 
TIME(23,61,95) - 11:61:95 PM = 00:02:35 AM Or DATE(2,30,2018) - 30 February. It will produce 2nd of March 2018. 
Two other useful Clarion built-in functions are CLOCK() and TODAY() to return System Time & Date.
You can use them in logical expressions like:
CUS:StartTime < CLOCK() OR CUS:FirstDate < TODAY() - 7

Commandline
Syntax for calling a program with a command line is as follows:
C:\Retriever\retriever.exe \auto=1 \query="Encrypted File" \select="ENC:PTR,ENC:NAME" \where="ENC:NAME > 'D'"
The C:\Retriever\retriever.exe \auto=1 part is essential. All other flags are optional. 
You may specify a predefined query with \query="Encrypted File" . If omitted, the no-name default query will be called.
If you use spaces in the query name, remember to always wrap the name in double quotes: \query="Encrypted File"
If there are no spaces in the query name, you can omit the double quotes: \query=Encrypt. 
The same applies to the other 2 flags.
Use \select= & \where= to override query Select & Where. The original query Select & Where will be ignored in this case.
Always leave space in front of and after a flag and use only back slashes \. 
The last flag doesn't need a space after it.

Miscellaneous
New Button. Retriever automatically saves the last query when you close the program; when you 
select a new query;  when you choose another TPS file;  or when you hit the New button. 
If there is no query name in the Query prompt it still gets saved as default.
Fields By Name checkbox will sort fields in the Fetch Field lookups alphabetically.
Null Empty String checkbox will insert NULL instead of empty strings (Space, char N# 32)
No Preview checkbox will run Fetch in light mode (no Preview). Just the CSV file will be generated.
Commandline mode \auto=1 always runs like this.
Arrays Clarion supports arrays. So if you see many fields with names like CUS:AGE-1 CUS:AGE-2 CUS:AGE-2... 
these are the first 3 members of CUS:AGE[] array. See "Arrays" query.
Errors. If you use the wrong password for an encrypted TPS file, Fetch will come up with "Invalid Data File".
Superfile will throw "File Not Found" Error. We ship one super file - Super#.TPS - so you can see it.
Memos are usually an unstructured chunk of text. The method we use cannot read them. 
So they will come up with the value "unavailable".
BLOBs are OK, but only if file does not also include MEMO(s). The presence of a MEMO blocks access to BLOB as well
Please refer to our query "BlobIsOK".
CSV files Retriever produces standard CSV files. All fields are wrapped in double-quotes. If the field itself has 
double-quote(s) inside, each will be escaped with another double-quote So ---"--- would become "---""---" in CSV file.
Clarion is not case sensitive. So you can write expressions in either case. So CUS:TIME = CUS:time, DAY() = Day() etc.

Copyright © 2025 Vivid Help Systems Pty Ltd