Clipper Tutorial: a Guide to Open Source Clipper(s)/Working With Databases
Working with Databases
[edit | edit source]Let's return to the Wikipedia entry Database application.
Different kinds of database applications exist as well. If you did store your friend's phone numbers and addresses into a word processor, you would have what someone calls a Free-Form Database (however, a similar expression is an oxymoron in computer science) - myBase®, askSam®, Personal Knowbase®, MyInfo®, Info Select®, and GeneralKB® are a bunch of specialized free-form database application, which actually means PIM (Personal information manager). Now, a word processor lets us search the information, but other operations, such as sorting them in alphabetical or numerical order, cannot usually be done automatically by a word processor.
What about attempting to store it into a spreadsheet? We may use one column for the name, one for the surname, one for the telephone number, one for the city. This quick database, stored in a spreadsheet, may be searched and sorted: for example, we can sort it by city and person's name in alphabetical order. This is a flat database, http://www2.research.att.com/~gsf/man/man1/cql.html: a flat database is a sequence of newline terminated records of delimiter separated fields, and a spreadsheet shows its limits in data entry and reporting (if you did want to use the data in your table to print out addresses on envelopes a spreadsheet is not a good tool). An example is MyDatabase (http://www.pcmag.com/article2/0,2817,760833,00.asp).
Spreadsheets are much better to do accounting: how much harder a book-keeper's work would be if his data were stored in a wordprocessing program? The purpose here is to have our data structured in a certain way: all the costs in a place, all earnings in another.
Before 1970 complex databases were managed using Hierarchical Databases (very little information is needed about them - see for example http://www.extropia.com/tutorials/sql/hierarchical_databases.html and http://people.cs.pitt.edu/~chang/156/14hier.html). An example of a hierarchical database is IBM IMS (Information Management System, which was developed during the mid-1960s for applications in the aerospace industry). Their implementation is based on trees, a hierarchical data structure. Hierarchical Databases and Network Databases together form what today are referred to as Legacy Database Systems. Network databases were born as an extension to the programming language COBOL by the Conference on Data Systems Languages (CODASYL). The network data model is based on data structures called graphs.
Today's standard is the Relational Database (RDBMS), which is "a database with relationships between more than one table of records based on common fields". We will speak of them in some detail, but we will briefly mention the fourth approach: Object Oriented Databases. These databases store objects (in the same sense the word is used in the expression object-oriented programming). They're not much used, mostly because objects are more complex than the simple fields a relational database stores in its tables. More information on the topic at http://www.odbms.org/odmg-standard/.
The Wikipedia entry about DBase reads: «dBase is application development language and integrated navigational database management system which Ashton-Tate labeled as "relational" but it did not meet the criteria defined by Dr. Edgar F. Codd's relational model». Codd's criteria (the so-called 12 rules, which really are 13 because the rule numbered '0' actually exists) are so strict that in practice a true relational database system does not even exist, but the point is that dBase accessed databases in another way, so that it's considered a Navigational Database (which works in a way that simulates relational databases).
http://www.databasedev.co.uk/design_basics.html
DBF Files in Other Languages
[edit | edit source]Because of the great success of dBase and its, the DBF file format became an industry standard. Many other database programs have used them to store data, like Lotus Approach. We also have many little utilities to view and convert to other formats these files. Here is a bunch of URLs: https://dbfview.com/, http://www.alexnolan.net/software/dbf.htm, https://dbfviewer.com/en/, https://www.dbf2002.com/, http://www.whitetown.com/dbf2sql/ («DBF to SQL Converter allows you to convert your dbf files to SQL script. Personal license $29.95», but compare https://www.vlsoftware.net/exportizer/). And is so widely used that interfaces for working with it are available for various languages, for example:
- C++
- https://sourceforge.net/projects/xdb/, http://linux.techass.com/projects/xdb/xbasedocs/xbase_c1.html and http://linux.techass.com/projects/xdb/ Xbase (formerly known as xdb, also formerly known as xBase) is a collection of specifications, programs, utilities and a C++ class library for manipulating Xbase type datafiles and indices.
- Python
- https://sourceforge.net/projects/xbase-py/ A Python interface for managing dbf files
- Java
- https://sourceforge.net/projects/xbasej/ xBaseJ - xBase Engine for Java
- Links for PHP interface:
Well, now we will see how to work with DBF files the way it was intended.
Making a first database and recording some data
[edit | edit source]A verbose way
[edit | edit source] && it was done this way at the Dot Prompt
&& we can type this interactively in hbrun
CREATE TMPNAMES
USE TMPNAMES
APPEND BLANK
REPLACE FIELD_NAME WITH "NAME"
REPLACE FIELD_TYPE WITH "C"
REPLACE FIELD_LEN WITH 15
APPEND BLANK
REPLACE FIELD_NAME WITH "ADDRESS"
REPLACE FIELD_TYPE WITH "C"
REPLACE FIELD_LEN WITH 30
CLOSE
CREATE NAMES FROM TMPNAMES && https://www.itlnet.net/programming/program/Reference/c53g01c/ngc785e.html
ERASE TMPNAMES.DBF && we get rid of the temporary file
The code above created a DBF file, names.dbf, to be used by the following code. It will add a record to the DBF file. It is equivalent to the "First Sample Program" of my old PC GUIDE, which missed a line that is necessary in modern xBase.
CLEAR
? "First Sample Program"
SELECT 1
USE NAMES
APPEND BLANK
REPLACE NAME WITH "MIKE BROWN"
REPLACE ADDRESS WITH "ROME STREET, 56"
CLOSE && this line is missing in my PC GUIDE but is needed in a compiled Harbour program
QUIT
The CLOSE command is equivalent to the dbCloseArea() function, which closes a work area: Pending updates are written, pending locks are released.
A more concise way
[edit | edit source]The short code below does the same work of the two pieces of code of the previous section (it only produces a different file name, namesdb.dbf instead of names.dbf).
local aStruct := { { "NAME", "C", 15, 0 }, ;
{ "ADDRESS", "C", 30, 0 }}
REQUEST DBFCDX
dbCreate( "namesdb", aStruct, "DBFCDX", .t., "NAMESDB" )
&& http://www.fivetechsoft.com/harbour-docs/api.html
USE NAMESDB
NAMESDB->(DbAppend())
NAMESDB->NAME := "MIKE BROWN"
NAMESDB->ADDRESS := "ROME STREET, 56"
This example uses the alias operator, ->. http://www.ousob.com/ng/clguide/ngcf412.php
The alias->field_name notation is used to allow access to fields of databases that are loaded but not active. The alias can be specified with the work area number (e.g. 2->std_id), with the work area alias (e.g. B->std_id), or with the database name (e.g. STUDENTS->std_id).
The result of this code is a file named namesdb.dbf. Informations about how DBF files are can be find at DBF File structure, http://www.dbf2002.com/dbf-file-format.html, where we find this list of Field type:
- C – Character
- Y – Currency
- N – Numeric
- F – Float
- D – Date
- T – DateTime
- B – Double
- I – Integer
- L – Logical
- M – Memo
- G – General
- C – Character (binary)
- M – Memo (binary)
- P – Picture
- + – Autoincrement (dBase Level 7)
- O – Double (dBase Level 7)
- @ – Timestamp (dBase Level 7)
My PC GUIDE showed how a .dbf file is made with the DataBase Utility DBU. Clones of this utility are FiveDBU (with source code) at https://code.google.com/archive/p/fivewin-contributions/downloads, DBF Viewer Plus at http://www.alexnolan.net/software/dbf.htm, CLUT at http://www.scovetta.com/archives/simtelnet/msdos/clipper. Harbour includes its own HbDBU (the source is in \hb32\addons\hbdbu) and a component IdeDBU of HbIDE (the other two components are IdeEDITOR and IdeREPORTS).
From https://code.google.com/archive/p/fivewin-contributions/downloads we can get fivedbu_20130930.zip (New FiveDBU version with enhancements on ADO fields editing). It supports ADO, 3 RDD (DBFNTX, CBFCDX and RDDADS) and 6 languages - select "Bases de datos -> Preferencias -> Lenguaje: Inglés" to have it in English.
Let us see what is in our little file so far.
USE NAMES
LIST DATE(), TIME(), NAME, ADDRESS
Database Design Issue: the First Normal Form (1NF)
[edit | edit source]The work done in the previous section was intended to exactly reproduce the database presented in my PC GUIDE. There are, however, drawbacks: having only one NAME field, this database cannot sort its data on the last name. Also, a careless user might insert the data of some people with the last name first, and some other data with the first name last. When designing a database precautions should be taken of these possibilities. The first normal form (http://www.1keydata.com/database-normalization/first-normal-form-1nf.php, http://www.sqa.org.uk/e-learning/SoftDevRDS02CD/page_14.htm) requires you to define fields whose information cannot be divided into smaller parts. So, instead of a NAME field, we should have a FIRST_NAME and LAST_NAME fields. Complying to the first normal form, our little database would be on the on the right track to being a normalized database.
Designing the database is an essential part of the work and it is not always obvious how it should be done. See https://www.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html.
A graphical device used to help is database design are the Entity-Relationship Diagrams: https://www.lucidchart.com/pages/er-diagrams, https://www.guru99.com/er-diagram-tutorial-dbms.html.
Complicating our simple database
[edit | edit source]Harbour contains a file named test.dbf. Launch hbrun in its directory and type in
use test browse()
At this point, we see that it is a 500 record table. Move around with the cursor keys and, when you're finished, punch the Esc key to quit this interactive table browser and editor. To get the record number of a person called Ted issue:
locate for first="Ted" ? recno()
Something More Elaborate
[edit | edit source]We may now try the use of hbrun interactively to solve an exercise from an ECDL book. ECDL (European Computer Driving Licence, https://www.findcourses.co.uk/inspiration/articles/why-does-ecdl-matter-17580) is a qualification to show that someone can use a computer with different degrees of proficiency. When I got it, many (many) years ago the tasks for the database module very extremely easy. It was the 5th module the one that dealt with databases. I bought a small book and rereading it I found inspiring the 2nd exercise in that module: create a table to manage a movie library. I've done this in Harbour 3.0 under ReactOS 0.4.13, using hbrun almost interactively - which gives it an enjoyable dBase feel. However it is necessary to use some auxiliary files, as some commands are quite difficult - or impossible - to type on a single line. That is not bad, we'll see how to run a piece of code stored in a file from the prompt.
Point 1. Turn on the computer and open the database manager - as absurd as it seems, the first step of every exercise in my workbook is to turn on the computer.
C:\videolib>c:\hb30\bin\hbrun
The first command to issue will set the window to 25×80 characters, so that it looks good on screen and nothing is out of sight.
.setmode(25,80)
At this point we should see more or less this (probably your screen won't be in Italian):
The first line shows the last command hbrun executed, setmode(25,80), and the bottom line features the legendary Dot Prompt.
Point 2. Create a table to manage a movie library with the following fields:
- movie code,
- movie title,
- genre,
- year of production,
- leading actor,
- price in euro.
We can issue all the following commands at the Dot Prompt to create a database with this structure:
.aDbf := {}
.AADD(aDbf, { "MOVIEID", "C", 6, 0 })
.AADD(aDbf, { "TITLE", "C", 30, 0 })
.AADD(aDbf, { "GENRE", "C", 30, 0 })
.AADD(aDbf, { "YEAR", "N", 4, 0 })
.AADD(aDbf, { "LEADACTOR", "C", 30, 0 })
.AADD(aDbf, { "PRICE_EUR", "N", 5, 2 })
.DBCREATE("videolib", aDbf)
or we can store them in a file, for example creadb.prg and invoke from the command line using:.do creadb
Now we can check what is in our working directory:
.dir
this command will show the name of all databases in the current directory. We should have something like:
Database Files # Records Last Update Size videolib.dbf 0 09/15/21 227
and begin using out database:
.use videolib
the first two lines should now read:
PP: use videolib
RDD: DBFNTX | Area: 1 | Dbf: VIDEOLIB | Index: | # 1/ 0 o
Point 3. Create a form to enter data - this we will do later. We will see how it is done: the @...GET command places the input fields on the screen and allow users to enter data. A typical procedure looks like this:
PROCEDURE EnterMovieData
LOCAL MOVIEID, TITLE, GENRE, YEAR, LEADACTOR, PRICE_EUR
@ 5, 10 SAY "Movie Code:" GET MOVIEID
@ 7, 10 SAY "Movie Title:" GET TITLE
@ 9, 10 SAY "Genre:" GET GENRE
@ 11, 10 SAY "Year of Production:" GET YEAR
@ 13, 10 SAY "Leading Actor:" GET LEADACTOR
@ 15, 10 SAY "Price in Euro:" GET PRICE_EUR
// Save data
INSERT INTO videolib VALUES (MOVIEID, TITLE, GENRE, YEAR, LEADACTOR, PRICE_EUR)
RETURN
Point 4. Fill the table with at least ten records with different data.
We will get our sample data from the CSV file "movies.txt" shown below using the command
.append from movies delimited
1,"Jurassic Park","azione",1993,"Jeff Goldblum",35.99 2,"Jumanji","avventura",1995,"Robin Williams",8.49 3,"Navigator","fantascienza",1986,"Joey Cramer",11.39 4,"Mortal Kombat","azione",1995,"Christopher Lambert",10 5,"Karate Kid 4","azione",1994,"Hilary Swank",4.9 6,"Ritorno al futuro","fantascienza",1985,"Michael J. Fox",6.95 7,"2001: Odissea nello Spazio","fantascienza",1968,"Keir Dullea",7.9 8,"Il pianeta proibito","fantascienza",1956,"Leslie Nielsen",9.99 9,"Interstellar","fantascienza",2014,"Matthew McConaughey",6.95 10,"Prometheus","fantascienza",2012,"Michael Fassbender",7
We may check the data currently in our database using:
.browse()
Now we can make some small changes to our sample data. For example the data of the Genre field are in Italian, but we would like to have them in English. The commands to do the substitutions are surprisingly simple:
.replace genre with "science fiction" for genre="fantascienza" .replace genre with "action" for genre="azione" .replace genre with "adventure" for genre="avventura"
In the same way we can translate the Italian movie titles with their English equivalents ("Il pianeta proibito" is "The Forbidden Planet", "2001: Odissea nello spazio" is "2001: A Space Odyssey" - but that was easy to guess -, "Ritorno al futuro" is "Back to the Future").
The movieid field is a simple number, and that's ok but, to spice things up, let's try to change it with this command:
.replace movieid with left(title,4)+right(str(year),2) all
.list movieid, title
At this point we can already try and extract the list of films made before the year 2000:
.cls ; ? ; ? ; ?; list title, leadactor, year for year < 2000
to see how to enter multiple commands at the Dot Prompt, just separate them with a semicolon. Here we can see also how a dBase command is built:
list - a verb title, leadactor, year - an expression list for year < 2000 - a condition
However, those three question marks to make some space to see all the records (the first three lines of hbrun are occupied by other information) are just ugly. Next time we will use setpos().
Now that we think about it the movie code field, Movieid, in record 7 consists only of numbers and in record 8 contains a space, which is just as ugly. We can correct it interactively by entering at the dot prompt:
.browse()
and replace those fields with "Odys68" and "Forb56".
Since we are making corrections, I just remembered that I gave away 2001: A Space Odyssey in change of a better movie, and so we may as well delete it:
.delete record 7
.list title for deleted()
.pack
Now we can browse() again and add a new record by hand: "Miss86", "Mission", "history,drama", "1986", "Jeremy Irons", "6.95".
Point 5. Sort the table by film title.
.index on title to titlesort
Again, .browse() will permit us to make sure things actually changed in the way the file appears.
Point 6. Query the database for films made before the year 2000
We’ve already done this, but this time we have the index on the field Title active:
.cls ; setpos(3,1) ; list title, leadactor, year for year < 2000
What else we can do? We can also also compute the average age of our movies (in years):
.average year(date()) - year to yearavg ; ? yearavg
(maybe i should watch more recent movies...), or see how much we spent on our movie collection:
.sum price_eur to totalprice ; ? totalprice
Point 7. Create a report named "Movie List" with the records of the films in order of genre and title and containing the fields code, genre, title, price in euro.
The DOS dBase had its own Report Generator and Clipper provided an utility, RL.EXE, to create reports and labels. Those programs create a format file used to generate the report, in those ancient times when reports were printed on continuous form paper using dot matrix printers. But how can we create a report today? How to preview it onscreen before sending it to the printer? Probably the most obvious options are to create a PDF or HTML file (which will then need a stylesheet to be watchable, otherwise it'll be too insipid). Since I'm using a vanilla Harbour 3.0 to start we will follow the simplest way: generate an HTML file. We will try a tabular report (this code needs to be corrected, as the HTML it produces although can be interpreted, cannot be validated as many closing tags are missing):
** report.prg
use videolib
index on genre+title to iReport
set printer on
set printer to 'rMovie.html'
?[<html><body>]
?[<h1>],"Movie Report",[</h1>]
?[<table>]
?[<tr><th>Movie Code<th>Genre<th>Title<th>Price in Euro</tr>]
do while .not. eof()
?[<tr>]
?[<td>],movieid
?[<td>],genre
?[<td>],title
?[<td>],price_eur
?[</tr>]
skip
enddo
.do report
This is not much to look at, but a CSS will improve its appearance considerably. Even a messy CSS (this too needs a clean up) like this:
h1 {
display: block;
font-size: 2em;
margin-top: 0.67em;
margin-bottom: 0.67em;
margin-left: 0;
margin-right: 0;
font-weight: bold;
margin: auto;
width: 50%;
border: 3px solid green;
padding: 10px;
text-align: center;
font-family: Arial, Helvetica, sans-serif;
background-color: lightgreen;
}
table, th, td {
border: 1px solid black;
margin: auto;
width: 75%;
border: 3px solid green;
padding: 10px;
}
table.center {
margin-left: auto;
margin-right: auto;
}
th, td {
padding: 10px;
width: 25%;
text-align: center;
}
th {
padding: 10px;
width: 25%;
font-family: Arial, Helvetica, sans-serif;
border: 3px solid blue;
background-color: lightblue;
}
can get us a colorful printed report that does not look dull - albeit unprofessional:
Point 8. Print the "Movie List" report.
Point 9. Close the database and the program. In hbrun these two things are done with these two commands, respectively:
.use
.quit
What have we left within our working directory? .dir *.* reports the following:
iReport ntx 2048 09/15/21 movies txt 605 09/15/21 report prg 414 09/15/21 rMovie htm 1290 09/15/21 styles css 743 09/15/21 titlesor ntx 2048 09/15/21 videolib dbf 1287 09/15/21
Note 1. Using HTML for output is fun, but it is not a good idea, as often a report will be longer than one page of paper and an HTML+CSS table will not handle properly such multiple pages output. Creating a PDF of the report to print, using either libHaru (http://libharu.org/) using the bindings of contrib\hbhpdf (or extras\hbvpdf) would be a much better choice.
Note 2. This example is really basic. We could do just about everything using the Dot Prompt in less than 20 minutes. ECDL has changed a lot during the last few lustra. Now I'm giving a look at the ECDL Advanced Database Syllabus 2.0. It looks good, with some interesting exercises.
Workareas: more than one table at once
[edit | edit source]Let us for a moment think back at the old times when commonly used computers had no mouse and GUI. Nevertheless, if we wanted to use several tables for a task, we had to use some way to tell the computer which tables he should consider. Let us take for example a library. Oversimplifying, we need to use at least three tables to manage it (one for the books, one for the customers and one for the loans).
We might issue these commands:
SELECT 1
USE Books
SELECT 2
USE Customers
SELECT 3
USE Loans
SELECT Books
We might visualize the result as the three windows in the picture below:
Here the command SELECT works like clicking on a window to activate it, and the workareas themselves look like windows having a number (the workarea number) and a name (the workarea alias).
Testdbf.prg
[edit | edit source]Here is the testdbf.prg source from \hb30\tests. It should be discussed in detail. It is a GPL piece of code poorly commented.
/*
* $Id: testdbf.prg 1792 1999-11-10 10:17:19Z bcantero $
*/
function main()
local nI, aStruct := { { "CHARACTER", "C", 25, 0 }, ;
{ "NUMERIC", "N", 8, 0 }, ;
{ "DOUBLE", "N", 8, 2 }, ;
{ "DATE", "D", 8, 0 }, ;
{ "LOGICAL", "L", 1, 0 }, ;
{ "MEMO1", "M", 10, 0 }, ;
{ "MEMO2", "M", 10, 0 } }
REQUEST DBFCDX
dbCreate( "testdbf", aStruct, "DBFCDX", .t., "MYALIAS" )
? "[" + MYALIAS->MEMO1 + "]"
? "[" + MYALIAS->MEMO2 + "]"
? "-"
MYALIAS->( dbAppend() )
MYALIAS->MEMO1 := "Hello world!"
MYALIAS->MEMO2 := "Harbour power"
? "[" + MYALIAS->MEMO1 + "]"
? "[" + MYALIAS->MEMO2 + "]"
MYALIAS->( dbAppend() )
MYALIAS->MEMO1 := "This is a test for field MEMO1."
MYALIAS->MEMO2 := "This is a test for field MEMO2."
? "[" + MYALIAS->MEMO1 + "]"
? "[" + MYALIAS->MEMO2 + "]"
MYALIAS->NUMERIC := 90
MYALIAS->DOUBLE := 120.138
? "[" + Str( MYALIAS->DOUBLE ) + "]"
? "[" + Str( MYALIAS->NUMERIC ) + "]"
? ""
? "Press any key..."
InKey( 0 )
? ""
? "Append 50 records with memos..."
for nI := 1 to 50
MYALIAS->( dbAppend() )
MYALIAS->MEMO1 := "This is a very long string. " + ;
"This may seem silly however strings like this are still " + ;
"used. Not by good programmers though, but I've seen " + ;
"stuff like this used for Copyright messages and other " + ;
"long text. What is the point to all of this you'd say. " + ;
"Well I am coming to the point right now, the constant " + ;
"string is limited to 256 characters and this string is " + ;
"a lot bigger. Do you get my drift ? If there is somebody " + ;
"who has read this line upto the very end: Esto es un " + ;
"sombrero grande rid¡culo." + Chr( 13 ) + Chr( 10 ) + ;
"/" + Chr( 13 ) + Chr( 10 ) + "[;-)" + Chr( 13 ) + Chr( 10 )+ ;
"\"
next
MYALIAS->( dbCommit() )
? "Records before ZAP:", MYALIAS->( LastRec() )
? "Size of files (data and memo):", Directory( "testdbf.dbf" )[1][2], ;
Directory( "testdbf.fpt" )[1][2]
MYALIAS->( __dbZap() )
MYALIAS->( dbCommit() )
? "Records after ZAP:", MYALIAS->( LastRec() )
? "Size of files (data and memo):", Directory( "testdbf.dbf" )[1][2], ;
Directory( "testdbf.fpt" )[1][2]
? "Value of fields MEMO1, MEMO2, DOUBLE and NUMERIC:"
? "[" + MYALIAS->MEMO1 + "]"
? "[" + MYALIAS->MEMO2 + "]"
? "[" + Str( MYALIAS->DOUBLE ) + "]"
? "[" + Str( MYALIAS->NUMERIC ) + "]"
? "Press any key..."
InKey( 0 )
dbCloseAll()
dbCreate( "testdbf", aStruct,, .t., "MYALIAS" )
for nI := 1 to 10
MYALIAS->( dbAppend() )
MYALIAS->NUMERIC := nI
? "Adding a record", nI
if nI == 3 .or. nI == 7
MYALIAS->( dbDelete() )
? "Deleting record", nI
endif
next
MYALIAS->( dbCommit() )
? ""
? "With SET DELETED OFF"
? "Press any key..."
InKey( 0 )
MYALIAS->( dbGoTop() )
do while !MYALIAS->( Eof() )
? MYALIAS->NUMERIC
MYALIAS->( dbSkip() )
enddo
SET DELETED ON
? ""
? "With SET DELETED ON"
? "Press any key..."
InKey( 0 )
MYALIAS->( dbGoTop() )
do while !MYALIAS->( Eof() )
? MYALIAS->NUMERIC
MYALIAS->( dbSkip() )
enddo
? ""
? "With SET DELETED ON"
? "and SET FILTER TO MYALIAS->NUMERIC > 2 .AND. MYALIAS->NUMERIC < 8"
? "Press any key..."
InKey( 0 )
MYALIAS->( dbSetFilter( { || MYALIAS->NUMERIC > 2 .AND. MYALIAS->NUMERIC < 8 }, ;
"MYALIAS->NUMERIC > 2 .AND. MYALIAS->NUMERIC < 8" ) )
MYALIAS->( dbGoTop() )
do while !MYALIAS->( Eof() )
? MYALIAS->NUMERIC
MYALIAS->( dbSkip() )
enddo
SET DELETED OFF
? ""
? "With SET DELETED OFF"
? "and SET FILTER TO MYALIAS->NUMERIC > 2 .AND. MYALIAS->NUMERIC < 8"
? "Press any key..."
InKey( 0 )
MYALIAS->( dbSetFilter( { || MYALIAS->NUMERIC > 2 .AND. MYALIAS->NUMERIC < 8 }, ;
"MYALIAS->NUMERIC > 2 .AND. MYALIAS->NUMERIC < 8" ) )
MYALIAS->( dbGoTop() )
do while !MYALIAS->( Eof() )
? MYALIAS->NUMERIC
MYALIAS->( dbSkip() )
enddo
? "dbFilter() => " + dbFilter()
? ""
? "Testing __dbPack()"
? "Records before PACK:", MYALIAS->( LastRec() )
? "Size of files (data and memo):", Directory( "testdbf.dbf" )[1][2], ;
Directory( "testdbf.dbt" )[1][2]
SET FILTER TO
MYALIAS->( __dbPack() )
MYALIAS->( dbCommit() )
? "Records after PACK:", MYALIAS->( LastRec() )
? "Size of files (data and memo):", Directory( "testdbf.dbf" )[1][2], ;
Directory( "testdbf.dbt" )[1][2]
? "Press any key..."
InKey( 0 )
? "Value of fields:"
MYALIAS->( dbGoTop() )
do while !MYALIAS->( Eof() )
? MYALIAS->NUMERIC
MYALIAS->( dbSkip() )
enddo
? ""
? "Open test.dbf and LOCATE FOR TESTDBF->SALARY > 145000"
? "Press any key..."
InKey( 0 )
dbUseArea( ,, "test", "TESTDBF" )
locate for TESTDBF->SALARY > 145000
do while TESTDBF->( Found() )
? TESTDBF->FIRST, TESTDBF->LAST, TESTDBF->SALARY
continue
enddo
? ""
? "LOCATE FOR TESTDBF->MARRIED .AND. TESTDBF->FIRST > 'S'"
? "Press any key..."
InKey( 0 )
dbUseArea( ,, "test", "TESTDBF" )
locate for TESTDBF->MARRIED .AND. TESTDBF->FIRST > 'S'
do while TESTDBF->( Found() )
? TESTDBF->FIRST, TESTDBF->LAST, TESTDBF->MARRIED
continue
enddo
return nil
Input Mask
[edit | edit source]A simple data base input mask (from the Wikipedia Clipper entry):
USE Customer SHARED NEW
clear
@ 1, 0 SAY "CustNum" GET Customer->CustNum PICT "999999" VALID Customer->CustNum > 0
@ 3, 0 SAY "Contact" GET Customer->Contact VALID !empty(Customer->Contact)
@ 4, 0 SAY "Address" GET Customer->Address
READ
RDDs: What Functions Are Available?
[edit | edit source]http://harbourlanguage.blogspot.de/2010/06/understanding-harbour-rdd.html
ADO RDD: Much Ado About Nothing
[edit | edit source]https://searchsqlserver.techtarget.com/definition/ActiveX-Data-Objects http://cch4clipper.blogspot.com/2009/10/using-adordd-with-harbourxharbour.html
Case Study: Checkbook Balancing
[edit | edit source]This is CHECKD.PRG from x2c-base.zip\Tutor.zip:
Simple checkbook entry and edit program
Use the Checkbook file, indexed on Check number
USE CHECKS
DO WHILE .t.
DONE = 'D'
CLEAR
@ 1,0 SAY "New Check (N), Old Check (O), Done (D), List(L)" ;
GET DONE PICT "!"
READ
DO CASE
CASE DONE='L'
LINE = 2
GOTO TOP
DO WHILE .NOT. EOF()
IF LINE=2
CLEAR
@ 0, 1 say "Date Number Name"
@ 1, 1 say " $$$ Clr Catgy Description"
ENDIF
* DATE NUMBER NAME
* AMT CLR CAT DESC
@ LINE , 1 SAY CHECKDATE && MM/DD/YY
@ LINE ,12 SAY CHECKNO && NNNNNNNN
@ LINE ,22 SAY RECNO() picture "(9999)"
@ LINE ,30 SAY CHECKNAME && CHAR 30
@ LINE+1, 3 SAY CHECKAMT picture "99999.99"
@ LINE+1,14 SAY CHECKCLR && X
@ LINE+1,18 SAY CHECKCAT && XX
@ LINE+1,25 SAY CHECKDESC && Char 30
LINE = LINE + 2
IF LINE>22
Wait "Enter a key to continue"
LINE = 2
ENDIF
SKIP
enddo
IF LINE>0
ACCEPT "Enter a key to continue" TO DONE
ENDIF
LOOP
CASE DONE='D'
USE
EXIT
CASE DONE='O'
REQNO = 0
CLEAR
@ 1, 0 SAY "Record Number: " GET REQNO Picture "###"
READ
IF REQNO>RECCOUNT()
? "Check beyond end of file"
WAIT " Press any key"
LOOP
ENDIF
Goto reqno
LDATE = checkdate
LNO = checkno
LDESC = checkdesc
LNAME = checkname
LAMT = checkamt
LCLR = checkclr
LCAT = checkcat
CASE DONE='N'
APPEND BLANK
LDATE = date()
LNO = ' '
LDESC = space(30)
LNAME = space(30)
LAMT = 0.0
LCLR = "N"
LCAT = ' '
OTHERWISE
? CHR(7)
LOOP
ENDCASE
Now enter Check info or edit it
done = 'N'
DO WHILE done='N'
Make date into editable string
EDATE = DTOC(LDATE)
CLEAR
@ 1, 0 SAY "Check no.:" GET LNO
@ 1, 30 SAY "Date:" GET EDATE Pict "99/99/99"
@ 1, 60 SAY "Record number: "+str(RECNO(), 3)
@ 3, 0 SAY "Check to:" GET LDESC
@ 4, 0 SAY "Check Description:" GET LNAME
@ 6, 0 SAY "Amount of Check:" GET LAMT pict "99999.99"
@ 8, 0 SAY "Check Cleared?" GET LCLR
@ 8, 22 SAY "Check Catagory:" GET LCAT
READ
@ 10, 0 Say "All ok (Yes/No/Cancel) ?" get DONE pict "!"
READ
LDATE = CTOD(EDATE)
ENDDO
IF DONE='Y'
REPLACE checkdate WITH LDATE, checkno WITH LNO, ;
checkdesc WITH LDESC, checkname WITH LNAME, ;
checkamt WITH LAMT, checkclr WITH LCLR, ;
checkcat WITH LCAT
ENDIF
ENDDO
Deleting records
[edit | edit source]? LASTREC()
DELETE RECORD 4
PACK
? LASTREC()
In this piece of code, the command DELETE marks the fourth record for deletion. But the file is not altered, not even by a CLOSE command. The PACK command actually removes the records marked for deletion (and also makes some additional work). The RECALL command removes the deleted flags. The function DELETED() returns .T. if the current record is marked for deletion, .F. if not.
The PACK command, which does the actual deletion of data from the table, PACK requires that the current database be USEd EXCLUSIVEly. If this condition is not met when the PACK command is invoked, CA-Clipper generates a runtime error. Additional work that PACK does is to update indexes on the table it alters (if any).
The commands DELETE ALL and PACK are executed by a single command called ZAP.
&& This example demonstrates a typical ZAP operation in a network
&& environment:
USE Sales EXCLUSIVE NEW
IF !NETERR()
SET INDEX TO Sales, Branch, Salesman
ZAP
CLOSE Sales
ELSE
? "Zap operation failed"
BREAK
ENDIF
An Indexed Example
[edit | edit source]USE Clients NEW
INDEX ON Name TO Clients UNIQUE
Suppose a table containing these data:
FSTNAME LSTNAME John Doe John Doe John Doe Jane Doe
We can create a little index file with this piece of code:
SELECT 1
USE ind
? FILE("ind.ntx")
INDEX ON FstName TO ind
? FILE("ind.ntx") // we verify that a NTX file has been created