Create Database
The buttons in the penultimate line:
OFF
Loads a RTF file with your default editor. This is stored in the directory RTF. It contains this text, which is editable. This is to serve as you experiment with the tutorial that you can take notes.
ON
Load the website with this text. This will probably be updated from time to time, depending on the questions of the users of this tutorial. In this way, a current version of the description is always available.
Pdf
Will contain this text in PDF format to have it available even when offline.
These 3 buttons are page-related and bring each of the information provided for the page on the screen. They will not be mentioned later.
Die Seite "https://www.w3schools.com/" bietet oben rechts in Form einer Weltkugel (neben der Lupe) viele Sprachen an, darunter auch deutsch. Es werden Links zu verschiedenen Seiten angeboten, um die Möglichkeit zu bieten, sich seine Lieblingsseite aussuchen zu können. W3schools bietet auf jeden Fall umfassende Erklärungen zu den SQL Statements.
Create Database
Creates the default database at Settings. The SQL commands to create the tables and to generate the demo data are defined in the 2 editor windows "CREATE DATABASE WITH NOT SET TERM" and "CREATE DATABASE WITH "SET TERM ^;" IN SQL STATEMENT.
In the left editor window, the (data set) table "ADRESS" and "CONTACT" are created in order with "CREATE TABLE ...".
This is followed by "ALTER TABLE CONTACT ...". This SQL command combines the address data ("ADDRESS") with the contact data ("CONTACT"). n this case, "FOREIGN KEY" ensures that there is no data record for the contact data that is not assigned to an address.
FOREIGN KEY english (compact)
Next, the two commands to generate the generators - that is, activities that are executed directly in the database - are executed. "CREATE" GENERATOR GEN_ADRESSID; "creates the generator for generating the consecutive counter for the table" ADDRESS ", the following for the table" CONTACT. "In addition, the generator is assigned the initial value with" SET GENERATOR "at the addresses 143 the contact information 1084.
Next, the demo data is written to the database using "INSERT INTO ADRESS" and "INSERT INTO CONTACT."
This would be the SQL commands of the left text window processed. Now follow the SQL commands of the right window. It is also important to always make sure that the required values are always available. So you can not create an index on a table that does not exist yet. Two TRIGGERs are created here, which supply a new record with a value for the ID column and increment it by 1 in advance. For these SQL commands, we first have to set Adjust Delimiter to "dtSetTerm". This is done here in the program code, or the SQL commands in the right editor window are always executed in dtSetTerm mode, in the left window with dtDefault.
Here we have to go a bit further. In SQL and ISQL (Interbase-SQL) there is the possibility of the default delimiter ";" to change with the command "SET TERM". This is necessary with an SQL sequence like ours:
SET TERM ^ ;
CREATE TRIGGER TRIGADRESSID FOR ADRESS
ACTIVE
BEFORE INSERT
POSITION 0
AS
BEGIN
NEW.ADRESSID = GEN_ID(GEN_ADRESSID, 1);
END ^
SET TERM ; ^
GEN_ID(GEN_ADRESSID, 1) ; END ^
The semicolon here is part of the SQL command, not the end of the SQL command. Normally the creation of the TRIGGER would be aborted here. By "SET TERM ^;" the SQL execution routine is informed that the default delimiter (delimiter between two SQL statements / commands) of ";" is changed to "^". That is, the end of "CREATE PROCEDURE .." is the "END" following "^" instead of the normally used semicolon. The command at the end "SET TERM; ^" reverses this, ie the default delimiter is now ";".
That would not be a big problem, but Firebird did not implement "SET TERM" in its SQL. The common database management programs have solved this internally in the program. But that does not help us here. Thus, the developers of Zeos have integrated into the component TSQLProcessor the ability to change the default delimiter. There are 5 options for setting: Default, dtDelimiter, dtEmptyLine, dtGo and dtSetTerm. "SET TERM" is not used in the SQL command in this case.
For us for our tasks 2 settings are relevant:
dtDefault: if no semicolon is used internally in the SQL command
dtSetTerm: if a semicolon is used internally in the SQL command
Create Empty Database
Creates a new database with no content (no tables and data).
Want to edit SQL before create database?
The two text windows are not editable by default. If you click the button, then you can edit the SQL commands.
To conclude this page
Everything else about database creation has already been explained in the tutorial "Firebird, Lazarus & Zeos I", is in german. This is primarily about working with the database components and a bit of basic knowledge about SQL. Basic knowledge of SQL is a prerequisite for creating a database application with Firebird, regardless of the programming language and the tools used.
Description ZConnection
<>