TCB SQL Tutorial

About DataBases

With TCB, a database is a collection of recordsets or tables. It is completely contained in a single SQLite database file (dataset) along with additional information about the dataset. A recordset or table is comprised of data organized in columns and rows where each row represents a discrete data record.

TCB provides several ways to create datasets and tables, populate them, and extract data from them. Execute SQLite.exe from a command line with the database name you want as a parameter, use the makeprc= function, or use the optional TCB RecordSet Master.

The "traditional" method of creating a dataset is with a properly "buttoned" TCB program as described below. Note: a program named runner.mnu is such a program and that is the program we will use for this exercise. Runner.mnu is found in the .\prcs folder. Run the TCB program runner.mnu now to continue with this exercise.

Creating, Populating and Editing a SQL Database File

The commands shown with each step below, pre-supposes there are buttons to run queries and execute commands from a text object and the text object is accessible. Note: the SQLiteviewer, dbManager and some other programs have the needed button text objects already "programmed" but assume the text object is the first or number 1 text object.

To Create a dataset file, (a SQLite database file)
  1. decide on a name with a .db3 extension (any extension will do), no white space, please! Let's call it customer.db3.
  2. In the text object, type in the following:
        sqlite customer.db3 <.quit|db3file=customer.db3|
    Now, click the Execute box contents button. This will create and initialize the data set file and now it is ready to use.

You have now created an empty a SQLite database file.

To Create Tables

Using the SQL create statement. Syntax:    create table [table name](field1, field2, field3......);.
All SQL statements are are usually part of an SQL query. Queries are a SQL statement, or set of SQL statements. SQL statements are terminated separated by a semi-colons However, queries used in the TCB data control must not have a terminating semi-colon or the query will fail.

Execute a SQL create statement from a text object to create a table in the database file with a last name, first name and phone number. Use the rsid field as first field (or column) if you want a TCB compatible table (the way to go).

Clear your text box and paste the statement below in the text box then click the Run SQL Query button.
create table newtable(rsid integer primary key autoincrement, lnam text, fnam text, phone text);

Now you have a database with one empty table in it. Next we are going to populate (put data in) the table.

To create table records

Use the insert statement.  Syntax:     insert into [tablename](field1,field2,field3...) values ('data1', 'data2', 'data3'...); Execute a group of insert statements from a text object. The "rsid" field is automatically updated as each record is inserted
Clear text box 1. Insert the data lines below into text object 1 and click the Run SQL Query button

insert into newtable(lnam,fnam,phone)   values ('Kipper','John','455-8768');
insert into newtable(lnam,fnam,phone)  values ('Jones','Nancy','724-0989');
We now have 2 records in the table. Clear text box 1 and enter the following command-string then click the Execute box contents button:
textclr=2|query2=text2;select * from newtable;|
There it is!

Using Compound Statements and Muti-line Queries

Each function or call in a compound state is terminated with the bar "|" character. A line break or "new-line" is indicated by the character sequence " \n " (Note: and the spaces in the character sequence are required).

In the command line below, the first function clears text object number 2. The second function sends the results of the query in text box 1 to text object 2. Note the "\n .quit" at the end of the query below. This should be appended to every query executed, data control queries excepted, to make sure the query processor closes properly.
Clear text box 1. Insert the data lines below into text object 1 and click the Execute box contents button

textclr=2|query2=text2;.headers on \n select lnam as 'Last Name', fnam as 'First Name', phone as 'Phone Number' from newtable; \n .quit|
The action above will result in the following display in text object 2.
Last Name,First Name,Phone Number
Kipper,John,455-8768
Jones,Nancy,724-0989

The Update Statement

  Syntax:     update [tablename] set field1 = 'Value1', field2 = 'Value2', field3 = 'Value3',... where fldX [=|<>|<|>|like;

Execute an update statement from a text object.
    update newtable set fnam = 'Mary' where lnam like 'jones';

The Delete Statement

.  Syntax:     delete from [tablename] where fld1 [condition] 'value1';

Execute a delete statement from a text object.
    delete from newtable where lnam like 'kipper';

You would have now performed all of the major operations needed to create and update a SQLite recordset or table.

Complex HTML Report

Copy the text below between the dashed lines into text box 2.

---------------------------------------------------------
<html>
<body>
<center><h2>Chart of Accounts Report</h2></center>
<table><tr>


<tr></table>
-------------------------------------------------------------

Now place the cursor into text box 2 just below the <table><tr> line above shown above. Paste the text below into text box 1 and click the text box 1 Execute Box Contents button when done.
    db3file=data\pimsdat.db3|dbload=|query2=text2;qrys\pcoaAccts.sql|

Click the Save to File button above text box 2 and save the contents of text box 2 as a HTML file. The name we are using is myfile.htm. Clear text box 1 and type in: browser=myfile.htm| and click the text box 1 Execute Box Contents button.

There are more queries in the folder qrys. Most (50+) of the files in the qrys folder have an extension of .xql. The rest (<15) of the files in the qrys folder have an extension of .sql. XQL files are simply SQL query files with the path\name of the database file on the first line. The XQL format contains all of the information SQLite needs to process a query.






















Copyright © 1982-2016 Phoenix Information Systems/TM all rights reserved
Contact: Fred Pierce, makpiya@aol.com, makpiya@gmail.com, 831.240.9969