TDataSet
This object is used to access the results of a SQL query.
PAL allows you to directly access the database via SQL queries, and query result sets. Be careful when working with the database directly - although this can be very useful, it is also easy to make mistakes that can corrupt your database or cause you to lose data. Make backups of your database before you start developing and testing new scripts. You should become familiar with SQL to make best use of this object.
This section assumes you have a good knowledge of SQL statements. Teaching SQL is beyond the scope of this document but you will find many good resources on SQL on the web.
SQL parameters
Before we jump into the actual SQL code we first need to explain the usage of SQL parameters inside SQL queries. Usually a query looks something like:
SELECT * FROM songlist WHERE (ID = 123) AND (date_played < '2005-01-01')
Notice how the values "123" and "2005-01-01" are hard-coded into the SQL string. This makes updating SQL queries hard & cumbersome. To solve this PAL introduces SQL parameters which you can think of as variables for SQL.
For example the above query could be implemented like this in PAL:
var Q : TDataSet;
Q := Query('SELECT * FROM songlist WHERE (ID = :ID) AND (date_played < :DatePlayed)',[123,'2005-01-01'],True);
Notice how easy this makes it a simple task to replace the actual values.
For example, let’s use an actual date for the "DatePlayed":
Q := Query('SELECT * FROM songlist WHERE (ID = :ID) AND (date_played < :DatePlayed)',[123,Now],True);
PAL SQL Commands
There are two main SQL commands:
function ExecSQL(SQL: String; Params: Array of Variant): Integer;
unction Query(Sql: String; Params: Array of Variant; ReadOnly: Boolean): TDataSet;
ExecSQL
ExecSQL is used to execute a SQL statement which does not return any data like UPDATE or DELETE queries but instead returns the number of rows affected by the query.
Examples:
{Reset the balance of songs to zero}
var cnt : Integer = 0;
cnt := ExecSQL('UPDATE songlist SET balance = 0',[]);
WriteLn(IntToStr(cnt)+' tracks were updated');
{Delete a certain artist from the songlist}
var cnt : Integer = 0;
cnt := ExecSQL('DELETE FROM songlist WHERE artist LIKE :artist',['Blink182']);
WriteLn(IntToStr(cnt)+' tracks were updated');
Query
The Query command is used exclusively with the SELECT SQL command which always returns a result set. This result set is encapsulated inside a TDataSet class object. Using this object you can browse the resulting rows of data, and even change the data.
Check the PAL Quick Start Guide for more examples and usage. Check out the PAL Script Reference guide for Properties and Methods
Comments