Accessing Databases Open on a page of its own

MaxScript offers an embedded database engine: it is based on tables (where each table is organized in records, and each records in fields) and where each table is a separate disk file.

This greatly simplyfies things: backups are a simply a matter of dragging files from a folder to another. Different databases are in fact simply different folders containing files, grouped under a common root (the DB Root) , and you can access them not only by using DBFree but any other utility capable of managing the DBF format (and you have a massive choiche of them!).

Please notice that some version of DBFree offers ODBC data access through the optional RDD mechanism (replaceable database driver) using the DBMax ODBC Driver (and its SQL statements). The informations of this page refer only to native RDD DBFree Driver - The ODBC RDD Driver is available through thirdy party implementations not supported by DBFree.org - if you want to obtain a Beta copy of the ODBC driver for testing purposes please contact labs@dbfree.org

Furthermore, to use MaxScript's database features in your web pages you simply set the database (choose the folder) to use, open one or more tables and operate on them using simple commands like these:

//-- sets the database to use
setDb("demo")   

//-- open a table 
use "customers"

//-- simple query
list off CUST_NAME, ADDRESS, ZIPCODE, CITY, STATE for STATE="UK"

See the results of these commands (N.B. These two examples are exactly equivalent.)
//-- sets database, retrieves its path
cDbPath := setDb("demo")

//-- sets table to use
cTable := cDbPath + "customers.dbf"
use (cTable) alias MASTER

//-- simple query
list()
Notice that list() is an User Defined Function (see Reference) and not part of standard language command set. (Show an example of a custom list() function)

If the database (or more precisely, its folder) does not exists you can create it with a simple chunk of code:
if not isdir(cDbPath)
   maketree(cDbPath)
endif
Tables can be indexed: indexes must be created togheter with the tables (but if deleted you must use the index-on external utility to rebuild them).
cIndex := cDbPath+"test"
use (cTable) index (cIndex) key CUST_NAME 
Tables can be created at runtime using code (but it's easier to use our Tables Wizard):
cTable := cDbPath + "test.dbf"
if not file(cTable)
   ainit( "aField","inv_no","cust_name","cust_addr","cust_zip","n_rows","inv_amnt","inv_tax","inv_tot")
   ainit( "aType","C","C","C","C","N","N","N","N")
   ainit( "aSize",10,22,80,20,3,8,8,8)
   ainit( "aDec",0,0,0,0,0,2,2,2)
   create (cTable) from aField, aType, aSize, aDec
endif

Showing the internal structure of a table is fairly simple:
cDbPath := setDb("demo")
use (cTable) in 0 alias TEST
if used()
   p( displayStru() )
endif
Of course this instruction makes sense only during development or when inserted into maintenance pages.
This is the result of the code shown on left:

TEST (0 Recs.)

Fld#FieldnameData typeSizeDecimals
01 INV_NO C String of characters 10 0
02 CUST_NAME C String of characters 22 0
03 CUST_ADDR C String of characters 80 0
04 CUST_ZIP C String of characters 20 0
05 N_ROWS N Numeric with decimals 3 0
06 INV_AMNT N Numeric with decimals 8 2
07 INV_TAX N Numeric with decimals 8 2
08 INV_TOT N Numeric with decimals 8 2

The record pointer

The basic concept of the overall DBF table design is that each record has an unique progressive identifying number (assigned automatically by the DB Engine) .
Most operations consist into moving this imaginary record pointer (which establishes where all the commands contained in the web page will end up) to the desired physical record and then apply the actions you want (edit, delete, copy, etc).
This approach makes MaxScript table handling really fast because once you know the record number you don't have to search it from beginning of file but you can instantly jump to record.
use "customers"
customerID := 3401
go (customerID)   //-- jumps to record 3401
The table customers is provided with most versions of DBFree: to see it structure you may use the function displayStru() (
Click here to see displayStru() in action
)

An example of full page
(including all the HTML and ancillary code necessary for a page to work)

<!DOCTYPE html>
<%
************************************* MAX HEADER
path := mspconfigvar("LIBDIR")             //-- the position of the DBFree library to use
set maxlib to ( path + "\free.lib")        //-- load the library
web()                                      //-- we want this to be a web page  
preload("/common/header.h",cSeed,"XYZ")    //-- load an HTML header shared between all pages 
************************************************
print( openWebPage() )                       
cDb := setDb("demo")
cTable := cDb + "CUSTOMERS"
use (cTable)
? "This is the table's content:"    
? list() 
p( closeWebPage() )                 //-- notice that P() is an abbreviation for PRINT() function
%>
The functions openWebPage() and closeWebPage() are a convenient and easy way to put a basic starter code into your page but are not recommended for real-world usage because don't give you all the necessary flexibility: you should build your own code instead.Anyway they greatly simplify the learning, so we will use them often in our examples.

Resulting page

As you see with only five lines of instructions you get a considerable achievement.

Obviously this is only a trivialization of the problems to solve during programming: in real usage your problem won't be how to retrieve existing data from tables but put new data in those tables (conversely changing data is considerably simpler)


Complete examples

You can use these examples in the MaxScript Console (DBFree V41 Control Center/Expert Mode/Command Window) with just copy and paste. The demo data supplied with your Distro will be used. This example is written to be universal: just change the cDb and cTable values and you can use this code to show any table content
Be sure to put this chunk before the examples (to open the table as MASTER):.
cDb := setDb("demo")
cTable := cDb + "CUSTOMERS"
use (cTable) alias MASTER
list()
 

1 - the short way

*******************
function list()
********************
local nnn := 0
go top
do while not eof()
nnn++
   p( showMaster() )
   skip
enddo
return(nnn)
The above code is an example of xBase-style

2 - The long way

***********************
function list()
***********************
<table class="table"
<tr><td>#</td><%
for nnn=1 to afields()
   %><th><%=fieldname(nnn)%></th><%
next
%></tr><%
rrr := 0
do while not eof()
   rrr++
   %><tr><td><%=recno()%></td><%
   for iii=1 to afields()
      %>
      <td>
      <input id="ef<%=zz(rrr)%>-<%=zz(iii)%>" 
      name="EF_<%=fieldname(iii)%>" 
      value="<%=zz(fieldval(iii))%>" 
      size="<%=zz(fieldsize(iii))%>"
      > 
      </td>
      <%
   next
   %></tr><%
   skip
enddo
%>
</table>
 Notice that is written in mixed mode (see Programming Styles) and do not represent the look of a typical MaxScript chunk, that is much clearer than this.

Creating a table

Tables can be created interactively by using the DB Manager of your DBFree installation or by code. The DB Manager offers also a feature to create (or modify) the table interactively and to produce the code chunk to paste in your App to build it at runtime.

Anyway this is the code for creating the demo table CUSTOMERS:

cTable  := cDir + "customers.DBF"
if not file(cTable)
  ainit( "aField","CUST_ID","CUST_NAME","ADDRESS","CITY","ZIPCODE","STATE","CUST_BAL","D_SUBS","D_EXPIR","TOT_AMNT","DUE_AMNT","GRP_ID","USR_NOTES")
  ainit( "aType","C","C","C","C","C","C","N","D","D","N","N","C","M")
  ainit( "aSize",10,35,50,25,12,3,10,8,8,8,8,10,10)
  ainit( "aDec",0,0,0,0,0,0,2,0,0,2,2,0,0)
  create (cTable) from aField, aType, aSize, aDec
endif
Tables can also be created (or creation code generated) on-line using the DBFree.org Tables Wizard.
Notice that this table has been created without any index.For creating an index you should add the following code;

Using standard DBFree indexes:
//-- recreating standard DBFree MTX indexes
cIndex := "customers.mtx"
if not file(cIndex)
  use (cTable) in 0 alias TEMP index (cIndex) key CUST_ID
endif

If you're using DBF DBASE 3 or 5 formats:
//--ALTERNATIVE - FOR DBMAX INDEXES
cIndex := "customers.mdx"
cMindex := strtran(cIndex,".mtx",".mdx")
if not file(cMindex )
  use (cTable) excl
  if used()
     index on CUST_ID tag CUST_ID
     ? "Rebuilt index " + cMindex 
  else
    ? "ERROR could not open exclusive, Index " + cMindex + " not created" html
  endif
endif
use

If you're using Clipper/xBase DBF format:
cIndex := "customers.ntx"
if not file(cIndex )
  use (cTable) in 0 alias TEMP excl
  if used()
     index on CUST_ID to (cIndex)
  endif
endif
use (cTable) in 0 alias MASTER
set index to (cIndex)
set order to 1

Querying tables

You have several choices for querying a table. Let's say we want to query the table parts that is already open:
//-- listing all parts for a car model
set filter to MAKE="VOLVO" and MODEL="S40"
list()
//-- all parts for the ignitions of S40
cMake  := "VOLVO"
cModel := "S40"
cGroup := "ELECTRICAL"
cSub   := "IGNITION" 
set filter to MAKE=cMake and MODEL=cModel and GROUP=cGroup and SUBGROUP=cSub
list()
//-- same as above but below minimum stock
nMinStock := 10
cQuery := "MAKE=" + cMake
cQuery += ".and. MODEL=" + cModel
cQuery += ".and. GROUP=" + cGroup
cQuery += ".and. SUBGROUP=" + cSub 
cQuery += ".and. TOT_QNT =< " + str(nMinStock)
set filter to &cQuery
list()

Father-child relations

The father-child relations in DBFree are implemented by the master-slave standard model.

Each master table can have one or more slave tables (the suggested number is up to 3 but it depends by multiple factors as table size, complexity of the relations, computing power and page TTL server configuration).

Each slave table can be, in turn, the master for other slave tables, so the whole structure - despite its apparent simplicity - can fulfill a wide range of applications.

This example shows a typical usage of master-slave standard model (for more information on this topic consult dedicated publications):

Show the structure of these tables

//-- MASTER TABLE - CAR PARTS CATALOGUE
//   automatically generated by Tables Wizard 1.0
cTable := cDbPath + "car_parts.dbf"
if not file(cTable)
   ainit( "aField","p_id","p_ref","p_maker","p_location","p_descr","p_usage","p_type","sub_p","pcs_req","u_m","curr_stock","min_stock","p_price","min_ord","pre_sold","cur_ord","p_pict","p_loc")
   ainit( "aType","C","C","C","C","C","M","C","L","N","C","N","N","N","N","N","N","C","C")
   ainit( "aSize",12,12,20,20,100,10,10,1,3,4,7,7,7,4,7,7,35,35)
   ainit( "aDec",0,0,0,0,0,0,0,0,0,0,3,3,3,0,3,3,0,0)
   create (cTable) from aField, aType, aSize, aDec
endif
    
cIndex1 := cDbPath + "car_parts_id.mtx"
if not file(cIndex1)
   use (cTable)
   index on P_ID to (cIndex1)
   use
endif
cIndex2 := cDbPath + "car_parts_typ.mtx"
if not file(cIndex2)
   use (cTable)
   index on P_TYPE to (cIndex2)
   use
endif
See table
//-- SLAVE TABLE 1 - CAR MODELS LIST
//   automatically generated by Tables Wizard 1.0
cTable := cDbPath + "car_models.dbf"
if not file(cTable)
   ainit( "aField","p_id","p_ref","car_maker","car_model","mod_descr","mod_year","mod_type","sub_type","pcs_req","u_m","m_pict")
   ainit( "aType","C","C","C","C","M","C","C","C","N","C","C")
   ainit( "aSize",12,12,20,100,10,20,40,40,3,4,35)
   ainit( "aDec",0,0,0,0,0,0,0,0,0,0,0)
   create (cTable) from aField, aType, aSize, aDec
endif
    
cIndex1 := cDbPath + "car_models_id.mtx"
if not file(cIndex1)
   use (cTable)
   index on P_ID to (cIndex1)
   use
endif
cIndex2 := cDbPath + "car_models_ref.mtx"
if not file(cIndex2)
   use (cTable)
   index on P_REF to (cIndex2)
   use
endif
See table
//-- SLAVE TABLE 2 -- CAR MAKERS
cTable := cDbPath + "car_makers.dbf"
if not file(cTable)
   ainit( "aField","maker_name","car_maker","m_desc","tot_mod")
   ainit( "aType","C","C","M","N")
   ainit( "aSize",50,20,10,3)
   ainit( "aDec",0,0,0,0)
   create (cTable) from aField, aType, aSize, aDec
endif
    
cIndex1 := cDbPath + "car_makers.mtx"
if not file(cIndex1)
   use (cTable)
   index on maker_id to (cIndex1)
   use
endif
See table
//-- SLAVE TABLE 3 -- PARTS SUPPLIERS
//   automatically generated by Tables Wizard 1.0
cTable := cDbPath + "part_makers.dbf"
if not file(cTable)
   ainit( "aField","supp_id","p_maker","purch_cond","tot_parts")
   ainit( "aType","C","C","M","N")
   ainit( "aSize",12,20,10,3)
   ainit( "aDec",0,0,0,0)
   create (cTable) from aField, aType, aSize, aDec
endif
    
cIndex1 := cDbPath + "car_makers.mtx"
if not file(cIndex1)
   use (cTable)
   index on p_maker to (cIndex1)
   use
endif
See table
The code above takes lot of precious space if placed at the beginning of the page, and only to be executed once, when you first start the application and tbales still don't exist. Best practices suggest to place this code into an UDF (User Defined Function) at the bottom of the page to improve code readibility.
For more informations on this and other related topics please consult the DBFree Guide

Using the tables

The previous code chunks just creates the tables at runtime, if they don't already exists. To actually use (open and manipulate) the tables you will use this code.
cDbPath := setDb("autoparts")
//-- MASTER TABLE - CAR PARTS CATALOGUE
cTable := cDbPath + "car_parts.dbf" 
cIndex1 := cDbPath + "car_parts_id.mtx"
cIndex2 := cDbPath + "car_parts_typ.mtx"
use (cTable) in 0 index (cIndex1),(cIndex2) alias PARTS

//-- SLAVE TABLE 1 - CAR MODELS LIST
cTable := cDbPath + "car_models.dbf"   
cIndex1 := cDbPath + "car_models_id.mtx"
cIndex2 := cDbPath + "car_models_ref.mtx"
use (cTable) in 0 index (cIndex1),(cIndex2) alias MODELS

//-- SLAVE TABLE 2 -- CAR MAKERS
cTable := cDbPath + "car_makers.dbf"    
cIndex1 := cDbPath + "car_makers.mtx"
use (cTable) in 0 index (cIndex1) alias MAKERS

//-- SLAVE TABLE 3 -- PARTS SUPPLIERS
cTable := cDbPath + "part_makers.dbf"    
cIndex1 := cDbPath + "car_makers.mtx"
use (cTable) in 0 index (cIndex1) alias SUPPLIERS
Notice that in this way all the 4 tables are opened each time the page is loaded, even if you're not accessing all of them. Best practices suggest to open only the tables that you really need.

Using the relation

There are several methods to establish a relation between the tables.
The simplest one is not to establish a relation at all and doing a separate search this way:
cKey := "VOLVO-S40-1234"
select PARTS
set order to 1   //-- using first index (key P_ID)
seek cKey
if found()
   ? "Found part " + parts->P_DESCR
   cKey := parts->P_ID
   select MODELS
   set order to 1
   ? "This part is used in the following models:"
   seek cKey
   if found()
      do while cKey = models->P_ID
         ? parts->P_ID, parts->P_REF, models->CAR_MAKER, models->CAR_MODEL 
         skip
      enddo
   endif
endif   

Automatic relations

Complex relation between tables can be established, but tend to be slower than moving the pointer of each table manually.
Anyway tgere are situations where it is preferably to use a relation to produce code more readable.
This is a complete example that you can directly copy and paste into your MaxScript Console to see how an automatic reationship works:
Setting the dtabase to use (if its folder does not exists will be automatically created)
cDbPath := setDb("test")
Creating the tables
//-- invoices (MASTER table)
cTable := cDbPath + "invoices.dbf"
if not file(cTable)
   ainit( "aField","inv_id","cust_id","cargo_txt","tot_inv")
   ainit( "aType","C","C","M","N")
   ainit( "aSize",10,20,10,7)
   ainit( "aDec",0,0,0,2)
   create (cTable) from aField, aType, aSize, aDec
endif
use (cTable) in 0 alias INV

//-- rows of the invoices (SLAVE TABLE)
cTable := cDbPath + "inv_rows.dbf"
cIndex := cDbPath + "inv_rows.mtx"
if not file(cTable)
   ainit( "aField","inv_id","row_n","cargo_txt","cust_id")
   ainit( "aType","C","N","M","N")
   ainit( "aSize",10,3,10,20)
   ainit( "aDec",0,0,0,2)
   create (cTable) from aField, aType, aSize, aDec
endif
use (cTable) in 0 alias ROWS index (cIndex) key INV_ID
Preloading some demo data
//-- preloading some demo data (1o invoices of 10 rows each)
select INV
if lastrec()=0
   for iii=1 to 10
       select INV
       append blank
       nnn := inv->(recno()) 
       repl inv->INV_ID with "F-"+str(nnn,3,0,"0")
       select ROWS
       for uuu=1 to 12
          append blank
          repl rows->INV_ID with inv->INV_ID
          repl rows->row_n with uuu
       next
   next
endif
Setting the Father-child (master/slave) relation between the two tables
//-- now establishing a relation from INVOICES and ROWS based on
//   the indexed field INV_ID that exists in both tables
select INV
set relation to INV_ID into ROWS
Displaying the content of two tables at once
go top
do while not eof()
   ? "Invoice:" + inv->INV_ID 
   select ROWS
   do while rows->INV_ID = inv->INV_ID 
      ? rows->row_n | rows->INV_ID
      skip
   enddo
   p("<hr>")
   select INV
   skip
enddo
Show this code in action
Of course this is just a simplified presentation of the overall "Databases" topic, that is actually quite complex, and for which we address you to specific publications which can be found in other sections of this site

Back to top