Thursday, October 18, 2007

Putting the SQL in 4D v11 SQL

Sergiy Temnikov gives a talk at 4D Summit 2007These are notes from the "Putting the SQL in 4D v11 SQL" session at 4D Summit 2007 presented by Sergiy Temnikov of 4D SAS.

(A lot of what was covered in this session duplicated the SQL Primer for 4D Developers session - so I've just highlighted things that were new subject matter.)

SQL is the industry standard form data retrieval and manipulations, but you can also retrieve structure definition through it as well.

It's important to take a look at it and learn from how it thinks and works. It's a different way of thinking and can be very powerful.

Begin SQL / End SQL is where everything happens...
ARRAY TEXT (arrTitles;0)
Begin SQL
select title from movies into :arrTitles
End SQL
That will populate arrTitles with the title of all movies.

You can also retrieve things like string concatenations, and statistical values like count, min, max, and average.

You can also do multiple selects in each Begin SQL / End SQL and return different values in each select, but remember to put a semicolon after each select statement.

Also realize that carriage returns aren't significant in SQL - you can break up things to make them more readable.

Be careful with delete command - it's powerful...

If you want to put things into fields you'd do something like this...

... INTO :Movies90s.title ...

That puts the value into [Movies90]Title.

To put things into listboxes...

... into listbox :listbox1 ...

If you use the AS clause you can change the titles that appear at the top of each column on the listbox

... select title as 'Title', year_of_movie as 'Year' ...

If you don't do this you'll get the field names at the top of each column.

If you use CAST you can change the type of the values on the fly.

... where id = cast ( :stringVar as int32 ) ...

There is only one database engine in 4D - DB4D - there are now two ways to access that engine - the 4D language and SQL. The query analyzers for the 4D language and SQL are separate - each is optimized for the language they're representing. Each has pretty deep hooks into DB4D.
  • Search with WHERE
  • Sort with ORDER BY
  • Group with GROUP BY - groups become aggregate records and what you can access is the aggregate data (averages, etc), not information on the individual records/row.
  • Search in grouops with HAVING - to enforce conditions within the groups
  • Joins - similar to 4D relations, but it can also be arthmatic expression to arthmatic expression
  • Sub-queries - Use the result of one query inside another, you can have multiple subqueries per query and multiple layers of subqueries - they can be taxing on the database engine
  • FOR UPDATE - add it to selects and it locks all the records returned by the query - the whole command will fail if some of the records are already locked
The exception to not being able to get individual values within a group by is that you can get the value that you group by.

Your WHERE clauses can have complex mathematical or logical statements.

If you don't sort explicitly there's no guarantee of the order - the same query could give results in a different order on subsequent queries.

If you say something like

... ORDER BY 2 DESC ...

what you're saying is sort descending by the second item in your select statement.

LIMIT let's you reduce the end result (it goes between ORDER BY and before INTO). For example

... LIMIT 3 ...

will only return the first three results.

Data Types
  • BYTE - 1 byte integer
  • INT16 - Integer
  • INT32 - LongInt
  • REAL - Real
  • VARCHAR - Text
  • VARCHAR(N) - Alpha(N)
  • TEXT - Text
  • TIMESTAMP - DateTime down to milliseconds
  • DURATION - Time
  • BOOLEAN - Boolean
  • BLOB - BLOB
  • PICTURE - Picture
  • FLOAT - essentially huge reals - not directly supported in 4D language
  • INT64 - essentially huge integers - not directly supported in 4D language
4D will map into 4D variables and fields for FLOAT and INT64, but if the number is too large you will have problems. It's best to stick with SQL to deal with FLOATs and INT64s. The engine supports them, but the 4D language just doesn't support them. Another option is to cast them into string/text and deal with them in 4D that way (say if you need to display them on a form).

Date fields are now datetime/timestamp fields. The time portion is just ignored when using the 4D language.

If you select a timestamp into date and time it will adjust and just give you the date or the time. As a result, if you select the same field twice you can split it into date and time.

If the field is created by SQL TEXT and VARCHAR act differently - one is stored in the record by default and the other outside the record (I didn't catch which was which).

Pictures are just BLOBs from the point of view of SQL.

The DB4D engine supports more types than 4D or SQL supports.

4D expressions can be parameters as well.

:<<My4DVAR1 + My4DVar2>>

The value is calculated before the query.

You can use 4D methods as parameters in SQL queries...

SELECT {fn WordCount ( title ) as int32} as 'Word Count'
WHERE {fn WordCount ( title ) as int32} > 4

This means you could have a 4D method that calls a web service and gets a value and uses that in your SQL statement.

However, you have to explicitly allow method access to the method using the "Available through SQL" method property.

EXECUTE IMMEDIATE is there so you can support user defined queries.

The current record and current selection have no connection to SQL queries. However, Query By SQL allows you to run SQL queries and return them to the current selection (complete with sorts, joins, etc.) One word of caution is that if you're joining on non-unique values you can get duplicated records in your selection (the same record can appear multiple times in your selection). If you stick with joins on unique keys you won't have this issue.

If you use DSN SQL_INTERNAL you can login to your current database using 4D ODBC. The advantage is that you can get things in pages and break down large selections.

Transactions in SQL use START, COMMIT, and ROLLBACK which are the equivalent of START TRANSACTION, VALIDATE TRANSACTION, and CANCEL TRANSACTION. Like 4D transactions they can be nested. There's a "auto-commit" option in the preferences. This wraps things that don't have transactions with hidden transactions. You then just use transactions when you want to do something complicated.

The transaction context is shared within a 4D process. You can start a transaction with the 4D language, do a sub transaction with SQL and then cancel them all with the 4D language.

4D is making the internal protocol publically available. This means you can bypass ODBC if you're writing an non-4D app (if you want).

The old ODBC driver (inbound) was thrown out and it was rewritten from scratch.

For Java apps use an ODBC-JDBC bridge provided by Sun - it works well. It sounds like they might be working on a native JDBC driver, but they didn't say as much (just "we don't have it right now...").

There's also a Flex/Action Script driver for 4D SQL (discussed the other day).

SSL ODBC will work on the Mac in the next release.

Begin/End SQL can also be used to get data from other ODBC data sources (including other 4D databases).

COALESCE - use it to assign a special value to nulls

CASCADES in deletes will delete related many records, RESTRICT in deletes won't delete parent if children records exist.

Special tables available via SQL give you structure information...
  • _USER_TABLES
  • _USER_COLUMNTS
  • _USER_INDEXES
  • _USER_IND_COLUMNS
  • _USER_CONSTRAINTS
  • _USER_CONS_COLUMNS

Labels: , ,

Digg It!  Add to del.icio.us  Add to StumbleUpon  Add to Reddit  Add to Technorati  Add to Furl  Add to Netscape

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home