These are notes from the “SQL Primer” session from 4D Summit 2007. The speaker was Christophe Visaya a Tech Support Engineer at 4D, Inc.

4D is compliant with SQL92 (SQL2). The good part about SQL is that it’s really well accepted by the industry. In the case of 4D, you should use SQL when it’s the best solution and the 4D syntax when it’s best - each has it’s advantages, depending on the circumstances.

4D Table = SQL Table
4D Field = SQL Column
4D Record = SQL Row

Use /* … */ to comment things out in SQL statements.

SELECT is the foundation of most of what you’ll do in SQL

SELECT [columns] FROM [table]

OR

SELECT [table.column] FROM [table]

Example of 4D code with SQL:

Begin SQLSELECT Name from CustomersINTO <<Box1>>End SQL

OR

Begin SQLSELECT * from CustomersINTO <<Box1>>End SQL

In the last case the * means “all columns/fields”…

Another way is to use EXECUTE IMMEDIATE where you build the SQL statement in a text variable and then just run that SQL query…

Begin SQLEXECUTE IMMEDIATE :$text_tEnd SQL

The other two types are QUERY BY SQL and ODBC Execute…

ODBC Execute treats the internal database as an external SQL database - so everything you know with 4D ODBC will translate…

The INTO statement can take a number of different formats.

Begin SQLSELECT * from CustomersINTO :Test.field1 :Test.field2, :Test.field3End SQL

You’ll notice from the preceding examples that you put a colon before a 4D field or variable and fields are referenced in a dot syntax (:Table.Field).

In the first example <<Box1>> is a list box and the data are put in dynamically - very handy!

ORDER BY is also a staple of what you’ll do.

Examples:

Begin SQLSELECT * from CustomersORDER BY State, Name DESC INTO <<Box1>>End SQL

That does a decending sort on State and Name before returning the results.

WHERE is another item you’ll use alot.

Begin SQLSELECT * from CustomersWHERE State = 'CA' INTO <<Box1>>End SQL

WHERE is the basic query statement. Notice that strings are surrounded by single quotes, not double quotes as in regular 4D syntax.

Operators you can use…

=
!=
<>
>
< >=
<= BETWEEN IN LIKE NOT It’s also put to use math arguments in your SQL statment. The following mathmatical operators are allowed: + Addition - Subtraction * Multiplication / Division BETWEEN

Example…

WHERE Name BETWEEN ‘G’ and ‘M’

That will find items where the first character of Name starts with a letter between G and M.

IN

Example…

WHERE Prod_ID IN (’FOOD002′, ‘FOOD04′)

So IN is basically an OR, but it’s useful when you have a long list of values and an OR would be cumbersome.

LIKE

Example…

WHERE Prod_ID LIKE ‘FOOD%’

Which finds all the items that start with FOOD (”FOOD@” in 4D syntax).

NOT

Example…

Begin SQLSELECT * from CustomersWHERE NOT State = 'CA' INTO <<Box1>>End SQL

This one brought out some discussion since he could have also used the not equal (!=) operator.

JOINS allow you to search across multiple tables. These are put in the WHERE clause.

WHERE table1.field1 = table1.table2

Begin SQLSELECT Customers.Name, Invoices.Invoice_IDFROM Customers, InvoicesWHERE Customers.Cust_ID=Invoices.Cust_IDINTO <<Box1>>End SQL

You can also have multiple JOINs in one statement… This example got a little hairy - you’ll just have to trust that it’s possible… But the basic idea is that you’re joining across multiple tables - to reach out several levels of relations.

It’s important to note that the relationships do not have to be drawn in the 4D structure - explicitly spelling out the JOINs is all you need.

WHERE can also have AND and OR…

WHERE Name BETWEEN ‘G’ and ‘M’
AND Name BETWEEN ‘A’ and ‘C’

If you mix ANDs and ORs in the same statement it will execute the ANDs first. Since this could get confusing - use parentheses to explicity say what you mean.

More Advanced Options…

Aliases - to name something and then refer to it later in the SQL statment.

Aggregate Functions…

COUNT () - The number of records
SUM () - Sums some column/field
AVG () - The mean of a column/field
GROUP BY () - Groups the selection
HAVING () - Lets you refine a GROUP BY
ORDER BY () - General Sort

Keyword Order…

SELECT
FROM WHERE
GROUP BY
HAVING
ORDER BY

Put things in that order, drop the ones you don’t need…

Begin SQLSELECT State, COUNT(Cust_ID) As Customers FROM CustomersGROUP BY StateHAVING COUNT(Cust_ID)>1INTO :Box1End SQL

Subqueries…

Subqueries are queries within queries. They’re more complex than JOINs

SELECT… WHERE(SELECT…)

Begin SQLSELECT Invoice_ID FROM InvoicesWHERE Invoice_ID IN (SELECT Invoice_ID FROM Invoice_Line WHERE Product_ID='FOOD001')INTO <<Box1>>End SQL

Notice the use of IN - which is mandatory before a subquery. The first parent query is the ‘outer query’ and the subquery is the ‘inner query’. Inner queries are evaluated first.

So far all of those examples are about accessing data, not modifying data. INSERT statements are what you use to modify data…

Begin SQLINSERT INTO Customers (Cust_ID, Name, State)VALUES ('COOO21', 'Raleigh', 'TN')End SQL

That will add a record into the Customers table.

Updates allow you up update records (who would have guessed?)

Begin SQLUPDATE Customers SET Name='Chris'WHERE Name='Raleigh'End SQL

So that changes all the people who are named ‘Raleigh’ to ‘Chris’. So this is very useful for mass updates. But you could also use a ID field in the where clause to update one record.

CREATE TABLE is also supported…

Begin SQLCREATE TABLE Suppliers(S_Name VARCHAR(25),S_Specialty VARCHAR(25))End SQL

ALTER TABLE lets you add, modify, and drop columns/fields.

DROP TABLE lets you delete the table.

More on those last bits in the SQL engine session…

Tags: 4D, 4D-summit, 4D-v11, SQL
Categories: Uncategorized