4D v13.4
Introduction
- 4D SQL Reference
-
- Tutorial
-
- Introduction
- Receiving an SQL query result in a variable
- Using the WHERE clause
- Receiving an SQL query result into arrays
- Using CAST
- Using the ORDER BY clause
- Using the GROUP BY clause
- Using Statistical functions
- Using the HAVING clause
- Calling 4D methods inside the SQL code
- Joins
- Using Aliases
- Subqueries
- SQL code error tracking and debugging
- Data Definition Language
- External connections
- Connection to the 4D SQL engine via the ODBC Driver
Introduction
SQL (Structured Query Language) is a tool for creating, organizing, managing and retrieving data stored by a computer database. SQL is not a database management system itself nor a stand-alone product; however, SQL is an integral part of a database management system, both a language and a tool used to communicate with this system.
The goal of this tutorial is not to teach you how to work with SQL (for this you can find documentation and links on the Internet), nor to teach you how to use and/or program in 4D. Instead, its purpose is to show you how to manage SQL inside 4D code, how to retrieve data using SQL commands, how to pass parameters and how to get the results after a SQL query.
All the examples that will be detailed in this document were fully tested and verified in one of the example databases named "4D SQL Code Samples" that you can download from our ftp server (ftp://ftp-public.4d.fr/Documents/Products_Documentation/LastVersions/Line_12/4D_SQL_Code_Samples.zip).
The structure is as follows:
The MOVIES table contains information about 50 movies, such as the title, the director, the category (Action, Animation, Comedy, Crime, Drama, etc.), the year it was released, whether or not it has subtitles, a brief summary, a picture of its poster, the type of media (DVD, VHS, DivX), whether it is in black and white, a blog saved in a BLOB, and the number of tickets sold.
The ACTORS table contains information regarding the actors of the movies such as an ID, their last and first names, any comments and the ID of the city where the actor was born.
The CITIES table contains information regarding the name and ID of the cities where the actors were born.
The MOVIE_ACTOR table is used to simulate a Many-to-Many relation between the MOVIES and ACTORS tables.
All the information you need to launch every example described in the tutorial is situated in the following main window which you can access by selecting the Demo SQL>Show Samples menu command:
Product: 4D
Theme: Tutorial
