Difference between revisions of "SqlDBHowto"

From Lazarus wiki
Jump to navigationJump to search
m
(→‎Example: Fix another usage of the undefined CreateQuery() function.)
 
(92 intermediate revisions by 26 users not shown)
Line 1: Line 1:
 
{{SqlDBHowto}}
 
{{SqlDBHowto}}
  
==Introduction==
+
{{Infobox databases}}
  
This page is a translation of [[SqlDBHowto/nl]]. The Dutch text is leading, even though I took the liberty to make some corrections (minor corections in my opinion.)
+
This text is setup as a 'how-to'. I want to answer a number of questions one by one, and explain how you can use the various classes. All those questions are put one after the other and form a sort of tutorial.  
  
This text is setup as a 'how-to'. I want to answer a number of questions one by one, explain how you can use the various classes. All those questions are put one after the other and form a sort of tutorial.  
+
I will try to word it in such a way that the text can be used for Lazarus as well as Free Pascal. However, the examples are for FreePascal (i.e. they are console applications.)
  
I will try to word it in such a way that the text can be used for Lazarus as well as Freepascal. However, the examples are for Freepascal (i.e. they are console applications.)
+
== Where can I find official documentation? ==
  
==How to connect to a database server?==
+
Please see the official documentation at [http://www.freepascal.org/docs-html/fcl/sqldb/index.html SQLDB documentation].
  
SqlDB doesn't connect to a database server directly but uses a client that corresponds to the used database server. SqqlDb sends the commands to the client library, the client library connects to the database and and transfers the commands. This means that a client library must be installed on the computer to make a connection to a database. Under Windows a client is usually a .dll, under Linux a .so and under OS/X a .dylib.
+
== How to connect to a database server? ==
  
When the client library is installed properly you can connect to a database server using a TSQLConnection component. Various TSQLConnection components are available for different database servers. For example, use TIBConnection to connect to a Firebird/Interbase database; TPQConnection for PostgreSQL and TMySQL40Connection, TMySQL41Connection and TMySQL50Connection for MySQL databases with version number 4.0, 4.1 and 5.0 respectively.
+
SqlDB doesn't connect to a database server directly but uses a client that corresponds to the used database server. SqlDB sends the commands to the client library; the client library connects to the database and and transfers the commands. This means that a client library must be installed on the computer to make a connection to a database. Under Windows a client is usually a .dll, under Linux an .so and under OS/X a .dylib.
The differences between the MySQL client versions are big, to the extend that the clients and connections cannot be interchanged. If a MySQL client library version 4.1 is installed, you have to use a TMySQL41Connection. This is not related to the MySQL server.
 
  
Although details differ for the various database, in general you need to set four properties to connect to a database server: the name of the database, the server name or ip address, the username and password. When these properties are set, you can create a connection with the 'open' method. If the connection fails, a EDatabaseError exception is thrown. Use the property 'connected' to test if a connection has been made with the database server. Use the 'close' method to end the connection with the server.
+
When the client library is installed properly you can connect to a database server using a TSQLConnection component. Various TSQLConnection components are available for different database servers (see [[SQLdb_Package]]):
  
Program ConnectDB
+
* Firebird/Interbase: [[TIBConnection]]
 +
* MS SQL Server: [[TMSSQLConnection]] (available since FPC 2.6.1)
 +
* MySQL v4.0: [[TMySQL40Connection]]
 +
* MySQL v4.1: [[TMySQL41Connection]]
 +
* MySQL v5.0: [[TMySQL50Connection]]
 +
* MySQL v5.1: [[TMySQL51Connection]] (available since FPC version 2.5.1
 +
* MySQL v5.5: [[TMySQL55Connection]] (available since Lazarus 1.0.8/FPC version 2.6.2
 +
* MySQL v5.6: [[TMySQL56Connection]] (available in Lazarus 1.2.4/FPC version 2.6.4
 +
* ODBC: [[TODBCConnection]] (see [[ODBCConn#TODBCConnection]])
 +
* Oracle: [[TOracleConnection]] (see [[Oracle]])
 +
* PostgreSQL: [[TPQConnection]] (see [[postgresql#SQLDB]])
 +
* Sqlite3: [[TSQLite3Connection]] (available since FPC version 2.2.2, see [[SQLite#Built-in_SQLDB]])
 +
* Sybase ASE: [[TSybaseConnection]] (available since FPC 2.6.1, see [[Lazarus_Database_Overview#Lazarus_and_MSSQL.2FSybase|Lazarus and MSSQL/Sybase]])
 +
 
 +
Note for MySQL - There are many differences between the client versions to the extent that the clients and connections cannot be interchanged. If a MySQL client library version 4.1 is installed, you have to use a TMySQL41Connection. This is not related to the MySQL server; using the MySQL 4.1 client library you can probably connect to a MySQL 5.0 server (see MySQL documentation regarding what combinations are supported).
 +
 
 +
Although details differ for the various databases, in general you need to set four properties to connect to a database server:
 +
 
 +
* the server name or IP address
 +
* the name of the database
 +
* the username
 +
* the password
 +
 
 +
When these properties are set, you can create a connection with the 'open' method. If the connection fails, a EDatabaseError exception is thrown. Use the property 'connected' to test if a connection has been made with the database server. Use the 'close' method to end the connection with the server.
 +
 
 +
<syntaxhighlight lang=pascal>
 +
Program ConnectDB;
 +
 
 +
{$mode objfpc}{$H+}
 +
 
 +
uses
 +
  IBConnection;
 +
 
 +
function CreateConnection: TIBConnection;
 +
begin
 +
  result := TIBConnection.Create(nil);
 +
  result.Hostname := 'localhost';
 +
  result.DatabaseName := '/opt/firebird/examples/employee.fdb';
 +
  result.UserName := 'sysdba';
 +
  result.Password := 'masterkey';
 +
end;
 
   
 
   
var AConnection : TSQLConnection;
+
var  
+
  AConnection : TIBConnection;
Procedure CreateConnection;
+
 
begin
+
begin
  AConnection := TIBConnection.Create(nil);
+
  AConnection := CreateConnection;
  AConnection.Hostname := 'localhost';
+
  AConnection.Open;
  AConnection.DatabaseName := '/opt/firebird/examples/employee.fdb';
+
  if Aconnection.Connected then
  AConnection.UserName := 'sysdba';
+
    writeln('Successful connect!')
  AConnection.Password := 'masterkey';
+
  else
end;
+
    writeln('This is not possible, because if the connection failed, ' +
+
            'an exception should be raised, so this code would not ' +
begin
+
            'be executed');
  CreateConnection;
+
  AConnection.Close;
  AConnection.Open;
+
  AConnection.Free;
  if Aconnection.Connected then
+
end.
    writeln('Succesful connect!')
+
</syntaxhighlight>
  else
+
 
    writeln('This is not possible, because if the connection failed, an exception should be raised, so this code would not be executed');
+
If an exception is thrown, read the error message carefully. It may be that the database server is not running, the user name or password are incorrect or the database name or IP address are typed incorrectly. If the error message states that the client library cannot be found, then check if the client is installed correctly. Often the error message states literally the name of the file looked for.
  AConnection.Close;
+
 
  AConnection.Free;
+
== How to execute direct queries/make a table? ==
end.
 
  
If an exception is thrown, read the error message carefully. It maybe the database server is not running, the user name or password is incorrect or the database name or ip address are typed incorrectly. If the error message states that the client library cannot be found, then check if the client is installed correctly. Often the error message states literaly the name of the file looked for.
+
SqlDB - the name says it all - only works with database server that make use of SQL. SQL stands for 'Structured Query Language' SQL is a language developed to allow working with relational databases. Virtually every database system has its own dialect, but a large number of SQL statements are the same for all database systems.
  
==How to execute direct queries/make a table?==
+
In FPC, there is a difference between:
 +
* SQL statements that return information (a dataset). For this, you have to use the TSQLQuery component; see [[#How to read data from a table?]].
 +
* statements that do not return information but do something else, e.g. update data. For this, you may also use the 'ExecuteDirect' method of a TSQLConnection. (You can also use this if you get a dataset back but are not interested in the results, e.g. in a selectable stored procedure).
  
Sqldb - the name says it all - only works with database server that make use of SQL. SQL stands for 'Structured Query Language' SQL is a language developed to allow working with relational databases. Virtually every database system has its own dialect, but a large number of SQL statements are the same for all database systems. We can make a difference between SQL statements that return information and statements that do not return information. If you want to use the information that is returned by the SQL statement, you have to use the TSQLQuery component (see [[#How to read data from a table?]].)
+
Most database system execute SQL statements within a transaction. If you want changes made within a transaction available in other transactions, or have those changes available even after closing the transaction(!), then you have to 'commit' the transaction.  
If you do not expect to use the information returned by the SQL statement, then you may also use the 'ExecuteDirect' method of a TSQLConnection.  
 
  
Most database system execute SQL statements within a transaction. If you want changes made within a transaction available in other transactions, or have those changes available even after closing the transaction, then you have to 'commit' the transaction. To support transactions Sqldb contains the TSQLTransaction component. A SQL statement that is executed by Sqldb must always be executed within a transaction. Even if the database system does not support transactions. Also, there are database sustems that do support transaction for which TSQLConnection does not (yet) support transaction. Even then, you must use the TSQLTransaction component.
+
To support transactions Sqldb contains the TSQLTransaction component. A SQL statement that is executed by Sqldb must always be executed within a transaction, even if the database system does not support transactions. Also, there are database systems that do support transaction for which TSQLConnection does not (yet) support transaction. Even then, you must use the TSQLTransaction component.
  
To use <tt>TSQLConnection.ExecuteDirect</tt> to execute a SQL statement you must specify which 'Transaction' must be used. In turn, to use TSQLTransaction you must specify which 'Connection' must be used.
+
To use <tt>TSQLConnection.ExecuteDirect</tt> to execute a SQL statement you must specify which 'Transaction' must be used. In turn, to use TSQLTransaction you must specify which TSQLConnection component must be used.
  
The following example creates a table 'TBLNAMES' with fields 'NAME' and 'ID' and inserts two records. The used SQL statements are not explained. For more information about the SQL statements, their use and syntax, please refer to the database system documentation. The procedure 'CreateConnection' is defined in the code example in [[#How to connect to a database server?]] above.
+
The following example creates a table 'TBLNAMES' with fields 'NAME' and 'ID' and inserts two records. This time using SQLite. The used SQL statements are not explained. For more information about the SQL statements, their use and syntax, please refer to the database system documentation. Note that this example does not attempt to catch any errors, thats a bad thing! Look into [https://www.freepascal.org/docs-html/current/ref/refch17.html#x226-24800017 Exceptions].
  
program CreateTable;
+
<syntaxhighlight lang=pascal>
 +
program CreateTable;
 +
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
 +
uses
 +
  sqldb, sqlite3conn;  
 
   
 
   
var AConnection : TSQLConnection;
+
var  
    ATransaction : TSQLTransaction;
+
  AConnection : TSQLite3Connection;
 +
  ATransaction : TSQLTransaction;
 
   
 
   
procedure CreateTransaction;
+
begin
begin
+
  AConnection := TSQLite3Connection.Create(nil);
  ATransaction := TSQLTransaction.Create;
+
  AConnection.DatabaseName := 'test_dbase';
  ATransaction.Database := AConnection;
 
end;
 
 
   
 
   
begin
+
  ATransaction := TSQLTransaction.Create(AConnection);
  CreateConnection;
+
  AConnection.Transaction := ATransaction;
  CreateTransaction;
+
  AConnection.Open;
  AConnection.Transaction := ATransaction;
+
  ATransaction.StartTransaction;
  AConnection.Open;
+
  AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));');  
  ATransaction.StartTransaction;
 
  AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));');
 
 
 
  // Some database-server types need a commit before you can use a newly created table. (Firebird)
 
  // With .Commit you also close the transaction
 
  ATransaction.Commit;  
 
 
   
 
   
  ATransaction.StartTransaction;
+
  ATransaction.Commit;
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,'Name1');');  
+
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,'Name2');');  
+
  ATransaction.StartTransaction;
  ATransaction.Commit;  
+
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,''Name1'');');  
  AConnection.Close;
+
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,''Name2'');');  
  AConnection.Free;
+
  ATransaction.Commit;  
  ATransaction.Free;
+
  AConnection.Close;
end.
+
  ATransaction.Free;
 +
  AConnection.Free;
 +
end. </syntaxhighlight>
  
==How to read data from a table?==
+
== How to read data from a table? ==
 +
Use the TSQLQuery component to read data from a table. A TSQLQuery component must be connected to a TSQLConnection component and a TSQLTransaction component to do its work. Setting the TSQLConnection and TSQLTransaction is discussed in [[#How to connect to a database server? ]] and [[#How to execute direct queries/make a table?]].
  
Use the TSQLQuery component to read data from a table. A TSQLQuery component must be connected to a TSQLConnection component and a TSQLTransaction component to do its work. Setting the TSQLConnection and TSQLTransaction is discussed in [[#How to make a connection to a database server?]] and [[#How to execute direct queries/make a table?]].  
+
When the TSQLConnection, TSQLTransaction and TSQLQuery are connected, then TSQLQuery needs to be further configured to work.  TSQLQuery has a 'SQL' property containing a TStrings object. The 'SQL' property contains a SQL statement that must be executed. If all data from a table <tt>tablename</tt> must be read, then set the 'SQL' property to:
 +
<syntaxhighlight lang="sql">'SELECT * FROM tablename;'</syntaxhighlight>.
  
When the TSQLConnection, TSQLTransaction and TSQLQuery are connected, then TSQLQuery needs more settings. TSQLQuery has a 'SQL' property containing a TStrings object. The 'SQL' property contains a SQL statement that must be executed. If all data from a table must be read, then set the 'SQL' property to 'SELECT * FROM tablename;' Use 'open' to read the table from the server and put the data in the TSQLQuery dataset. The data can be access through TSQLQuery until the query is closed using 'close'.
+
Use 'open' to read the table from the server and put the data in the TSQLQuery dataset. The data can be accessed through TSQLQuery until the query is closed using 'close'.
TSQLQuery is a subclass of TDataset. TDataset has a 'Fields' collection that contains all columns of the table. The TDataset also keeps track of the current record. Use 'First', 'Next', 'Prior' and 'Last' to change the current record. 'Eof' returns 'True' if the first record is reached, and 'Bof' returns 'True' if the last record is reached. To read the value of a field in the current record, first find the right 'TField' object and then use 'AsString', 'AsInteger', etc.
 
  
 +
TSQLQuery is a subclass of TDataset. TDataset has a 'Fields' collection that contains all columns of the table. The TDataset also keeps track of the current record. Use 'First', 'Next', 'Prior' and 'Last' to change the current record. 'Bof' returns 'True' if the first record is reached, and 'Eof' returns 'True' if the last record is reached. To read the value of a field in the current record, first find the right 'TField' object and then use 'AsString', 'AsInteger', etc.
  
Below is an example that displays all values of the table as it was made in [[#How to execute direct queries/make a table?]] above.
+
=== Example: reading data from a table ===
 +
Below is an example that displays all values of the table as it was made in CreateTable example above. Copy the test_database file into the ShowData working directory. Note, again, no error checking has taken place !
  
Program ShowData;
+
<syntaxhighlight lang=pascal>
 +
Program ShowData;
 +
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
 +
uses
 +
  DB, Sysutils, sqldb, sqlite3conn;
 +
 
 +
var
 +
  AConnection  : TSQLConnection;
 +
  ATransaction : TSQLTransaction;
 +
  Query        : TSQLQuery;
 
   
 
   
var AConnection : TSQLConnection;
+
begin
    ATransaction : TSQLTransaction;
+
  AConnection := TSQLite3Connection.Create(nil);
+
  ATransaction := TSQLTransaction.Create(AConnection);
procedure GetQuery : TSQLQuery;
+
  AConnection.Transaction := ATransaction;
var AQuery : TSQLQuery;
+
  AConnection.DatabaseName := 'test_dbase';
begin
+
  Query := TSQLQuery.Create(nil);
  AQuery := TSQLQuery.Create;
+
  Query.SQL.Text := 'select * from tblNames';
  AQuery.Database := FConnection;
+
  Query.Database := AConnection;
  AQuery.Transaction := FTransaction;
+
  Query.Open;
  Result := AQuery;
+
  while not Query.Eof do
end;
+
  begin
+
    Writeln('ID: ', Query.FieldByName('ID').AsInteger, 'Name: ' +
var Query : TSQLQuery;
+
                                  Query.FieldByName('Name').AsString);
+
    Query.Next;
begin
+
  end;
  CreateConnection;
+
  Query.Close;
  CreateTransaction;
+
  AConnection.Close;
  Query := GetQuery;
+
  Query.Free;
  Query.SQL.Text := 'select * from tblNames';
+
  ATransaction.Free;
  AConnection.Open;
+
  AConnection.Free;
  Query.Open;
+
end.
  while not Query.Eof do
+
</syntaxhighlight>
    begin
 
    Writeln('ID: ', Query.FieldByName('Name').AsInteger, 'Name: '+Query.FieldByName('Name').AsString);
 
    Query.Next;
 
    end;
 
  Query.Close;
 
  AConnection.Close;
 
  Query.Free;
 
  ATransaction.Free;
 
  AConnection.Free;
 
end.
 
  
(The code above of course is not quite finished, it misses 'try...finally' blocks. However, the above code intends to show the database code and thus the finishing touches are left out.) Please note that 'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the SQL statement is executed and if no transaction is available then a transaction is automatically started. The programmer does not need to start the transaction explicitly. The same applies for the connection maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not really required.
+
(The code above of course is not quite finished, it misses 'try...finally' blocks. However, the above code intends to show the database code and thus the finishing touches are left out.)
If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. Possible changes to data contained in the transaction will be lost.
+
Please note that 'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the SQL statement is executed and if no transaction is available then a transaction is automatically started. The programmer does not need to start the transaction explicitly.
 +
The same applies for the connection maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not really required.
 +
If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. '''Possible changes to data contained in the transaction will be lost.'''
  
===Why does TSQLQuery.RecordCount always return 10?===
+
=== Why does TSQLQuery.RecordCount always return 10? ===
 +
To count the records in a dataset, use '.RecordCount'. However, notice that '.RecordCount' shows the number of records that is already loaded from the server. For performance reasons, SqlDB does not read all records when opening TSQLQuery by default, only the first 10. Only when the eleventh record is accessed will the next set of 10 records be loaded, etc. Using '.Last', all records will be loaded.
  
To count the records in a datase, use '.RecordCount'. However, notice that '.RecordCount' shows the number of records that is already loaded from the server. Sqldb does not read all records when opening TSQLQuery by default, only the first 10. Only when the eleventh record is accessed then the next set of 10 records is loaded. Using '.Last' all records will be loaded.  
+
When you want to know the real number of records on the server you can first call '.Last' and then call '.RecordCount'.  
  
When you want to know the real number of records on the server you can first call '.Last' and then call '.RecordCount'. An alternative is available. The number of records returned by the server is set by the '.PacketRecords' property. The default value is 10, if you make it -1 then all records will be loaded at once.
+
An alternative is available. The number of records returned by the server is set by the '.PacketRecords' property. The default value is 10; if you make it -1 then all records will be loaded at once.
  
===Lazarus===
+
In current stable FPC, '.RecordCount' does not take filters into account, i.e. it shows the unfiltered total.
  
Lazarus heeft verschillende componenten om gegevens uit een TDataset op een formulier te tonen. In plaats van de while-loop en WriteLn zoals in het voorbeeld hierboven, kan je van deze componenten gebruik maken om de data in de tabel te laten zien. Plaats daarvoor de juiste TSQLConnection, TSQLTransaction en TSQLQuery op een formulier, stel ze goed in en verbind ze met elkaar. Verder heb je een TDatasource nodig, geef als TDatasource.Dataset de TSQLQuery op. (Doe dit niet andersom, TSQLQuery heeft een 'Datasource' property, maar gebruik die alleen als je met master-detail tabellen werkt) Vervolgens kan je bijvoorbeeld een TDBGrid op het formulier zetten en de datasource daarvan instellen op de TDatasource die je net gemaakt hebt.  
+
If you need the exact number of records, it often is a better idea to directly query the number of records in a query using another SQL query, but you would have to do that in the same transaction, as other transactions may have changed the number of records in the meanwhile.
  
Om te kijken of het werkt, kan je in de Lazarus-IDE de property 'Connected' van de TSQLConnection op 'True' zetten. Dan wordt er meteen geprobeerd om een verbinding met de database-server te maken. Als dat gelukt is kan je TSQLQuery.Active op 'True' zetten. Als alles goed is gedaan, zie je - in de IDE - meteen alle gegevens in de tabel op je scherm verschijnen.
+
=== Lazarus ===
 +
Lazarus has various components to show data from a TDataset on a form. Instead of a While-loop and Writeln statements as used above, you can use the components to show the data in a table. Place the right TSQLConnection, TSQLTransaction and TSQLQuery components on a form, then connect them and set them properly. In addition you will need a TDatasource; set the 'TDatasource.Dataset' property to the TSQLQuery component you used. ('''Note''' do not set the 'TSQLQuery.Datasource' property to the TDatasource compnent you used. The 'TSQLQuery.Datasource' property is used only in master-detail tables - see [[MasterDetail]]) Subsequently you may put a TDBGrid onto the form and set the 'Datasource' property of the grid to the TDatasource component you added before.
  
== Hoe wijzig ik gegevens in een tabel? ==
+
To see if it all works, set the 'Connected' property of the TSQLConnection to 'True' in the Lazarus IDE. The IDE will try to connect to the database server immediately. If this works you can set the 'TSQLQuery.Active' property to 'True'. If everything is right, you will see - within the IDE - all data from the table immediately on the screen.
  
Om de gegevens in een record te wijzigen, moet de TDataset (waarvan TSQLQuery een afgeleide is) in edit-modus gezet worden. Dat gebeurt met de 'Edit', 'Insert' of 'Append' methodes. Met 'Edit' is het huidige record te wijzigen, 'Insert' en 'Append' maken een nieuw record aan voor het huidige record of helemaal aan het eind van de dataset. Daarna zijn de gegevens via de 'Fields' property te wijzigen. Met 'Post' wordt er eerst gecontroleerd of de nieuwe gegevens wel geldig zijn en als dat zo is dan verlaat de dataset de edit-modus. Als je naar een ander record gaat (bijvoorbeeld met 'Next') en de dataset is in de edit-modus, dan wordt altijd eerst 'Post' aangeroepen. Met de methode 'Cancel' kan je de wijzigingen die je sinds de aanroep van 'Post' in de dataset hebt gemaakt weer ongedaan maken en wordt de edit-modus ook weer verlaten.
+
== How to change data in a table? ==
 +
To change the data in a record (or records), the general process is get TSQLQuery to search for the records you wish to change, make the changes there and then push them back to the database. The [[TDataSet]] (from which TSQLQuery is derived) must be set to edit mode. To enter edit mode call the '.Edit', '.Insert' or '.Append' methods. Use the '.Edit' method to change the current record. Use '.Insert' to insert a new record before the current record. Use '.Append' to insert a new record at the end of the table. In edit mode you can change field values through the 'Fields' property. Use 'Post' to validate the new data, if the data is valid then the edit mode is left. If you move to another record - for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. Use '.Cancel' to discard all changes you made since the last '.Post' call and leave the edit mode.
  
Query.Edit;
+
<syntaxhighlight lang=pascal>
Query.FieldByName('NAME').AsString := 'Edited name';
+
Query.Edit;
Query.Post;
+
Query.FieldByName('NAME').AsString := 'Edited name';
 +
Query.Post;</syntaxhighlight>
  
Dit is echter nog niet het hele verhaal. TSQLQuery is afgeleid van TBUFDataset, dat gebruik maakt van gebufferde-updates. Dat houdt in dat nadat 'Post' is aangeroepen, de wijzigingen in de dataset in het geheugen direct zichtbaar zijn, maar dat ze nog niet naar de database-server worden gestuurd. Wat er wel gebeurd is dat er een changelog wordt bijgehouden met alle wijzigingen. Als de methode 'ApplyUpdates' wordt aangeroepen, worden alle wijzigingen in het changelog een voor een naar de database gestuurd. Pas daarna weet de database-server van de wijzigingen. De wijzigingen worden naar de database verstuurd binnen de transactie van de TSQLConnection. Vergeet dus niet om die in te stellen voordat 'ApplyUpdates' aangeroepen wordt. Daarna moet er natuurlijk ook nog een commit uitgevoerd worden om de wijzigingen publiek te maken en/of op te slaan.
+
The above is not the complete story yet. TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible immediately, but they are not sent to the database server. What does happen is that the changes are maintained in a change log. When the '.ApplyUpdates' method is called, then all changes in the change log are sent to the database. Only then will database server know of the changes. The changes are sent to the server within a transaction of TSQLTransaction. Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the database server.
  
Hieronder een voorbeeld van het wijzigen van gegevens in de database, het versuren van de wijzigingen naar de server en het committen van de transactie.
+
The below is an example of changing the data in a table, sending the changes to the server and committing the transaction. Again, no error checking, again, thats bad!
  
Program EditData;
+
<syntaxhighlight lang=pascal>
 +
Program EditData;
 +
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
 +
uses
 +
    db, sqldb, sqlite3conn;
 +
var
 +
  AConnection : TSQLConnection;
 +
  ATransaction : TSQLTransaction;
 +
  Query : TSQLQuery;
 
   
 
   
var AConnection : TSQLConnection;
+
begin
    ATransaction : TSQLTransaction;
+
  AConnection := TSQLite3Connection.Create(nil);
    Query : TSQLQuery;
+
  ATransaction := TSQLTransaction.Create(AConnection);
+
  AConnection.Transaction := ATransaction;
begin
+
  AConnection.DatabaseName := 'test_dbase';
  CreateConnection;
+
  Query := TSQLQuery.Create(nil);
  CreateTransaction;
+
  Query.DataBase := AConnection;
  AConnection.Transaction := ATransaction;
+
  Query.SQL.Text := 'select * from tblNames where ID = 2';
  Query := GetQuery;
+
  Query.Open;
  Query.SQL.Text := 'select * from tblNames';
+
  Query.Edit;
  Query.Open;
+
  Query.FieldByName('NAME').AsString := 'Name Number 2';
  Query.Edit;
+
  Query.Post;
  Query.FieldByName('NAME').AsString := 'Edited name';
+
  Query.UpdateMode := upWhereAll;         // defined in db
  Query.Post;
+
  Query.ApplyUpdates;
  Query.UpdateMode := upWhereAll;
+
  ATransaction.Commit;
  Query.ApplyUpdates;
+
  Query.Free;
  AConnection.Transaction.Commit;
+
  ATransaction.Free;
  Query.Free;
+
  AConnection.Free;
  ATransaction.Free;
+
end.                  
  AConnection.Free;
+
</syntaxhighlight>
end.
 
  
Voor een uitleg van UpdateMode, lees hieronder verder.
+
The actual works starts with the SQL statement "select * from tblNames where ID = 2" identifying the record (or records) you wish to change. If you leave out the "where ID = 2" bit, the TSQLQuery apparently sets ID (and other integer fields?) to 1. And therefore will operate on lines where ID=1 only. For a discussion of 'UpdateMode' continue reading.
  
== Hoe stuurt SqlDB de wijzigingen naar de Database-server? ==
+
== How does SqlDB send the changes to the database server? ==
  
In het voorbeeld voor het [[#Hoe wijzig ik gegevens in een tabel?|wijzigen van gegevens in een tabel]], staat de regel
+
In the code example in [[#How to change data in a table?]], you will find the line
  Query.UpdateMode := upWhereAll;
+
<syntaxhighlight lang=pascal>Query.UpdateMode := upWhereAll;</syntaxhighlight>
waarvan niet uitgelegd is wat de regel doet. De beste manier om daar achter te komen is door de regel weg te laten. Als je dat doet en je hebt deze howto precies hebt gevolgd, dan krijg je de volgende foutmelding:
+
without explanation of what it does. The best way to find out what that line does is to leave it out. If you leave out the statement and the followed this howto precisely, then you will receive the following error message:
 
  No update query specified and failed to generate one. (No fields for inclusion in where statement found)
 
  No update query specified and failed to generate one. (No fields for inclusion in where statement found)
Om te begrijpen wat er hier fout gaat is het nodig om precies te begrijpen hoe wijzigingen naar de database-server gestuurd worden. De enige manier om een gegevens op een SQL-server te wijzigen is door een SQL-query uit te voeren. Er zijn drie type queries voor de drie verschillende manieren waarop een record gewijzigd kan worden: Een record kan nieuw zijn, verwijderd of gewijzigd waarvoor respectievelijk een insert-, delete- of update-query voor uitgevoerd moet worden. Een update-query kan er als volg uitzien:
+
To understand what went wrong, you must understand how changes are sent to the database server. The only way to get data in a SQL server is by executing SQL queries. SQL has three types of queries for three different ways of manupulating a record. To create a new record, change or delete a record insert, update and delete statements are executed respectively. An update statement may be as follows:
update TBLNAMES set NAME='Edited name' where ID=1;
+
<syntaxhighlight lang="sql">update TBLNAMES set NAME='Edited name' where ID=1;</syntaxhighlight>
Om een wijziging naar de database-server te sturen, moet SqlDB dus zo'n update-query samenstellen. Daarvoor zijn drie dingen nodig:
+
To send a change to the database server, Sqldb must assemble an update query. To assemble the query, three things are needed:
; De tabelnaam : Deze wordt achterhaald door de opgegeven select-query te parsen (dit lukt niet altijd.)
+
; The name of the table : The table name is retrieved from parsing the select query, although this doesn't always work.  
; Update- of insert-deel : Dit zijn de velden die gewijzigd worden.
+
; <tt>UPDATE</tt> or <tt>INSERT</tt> clause : These contain the fields that must be changed.
; Where-deel : Dit zijn de velden die aangeven welk record er nou eigenlijk gewijzigd moet worden.
+
; <tt>WHERE</tt> clause : This contains the fields that determine which records should be changed.
Ieder veld (TField) heeft een property ProviderFlags. Alleen de velden met pfInUpdate in de ProviderFlags worden in het update- of insert-deel van de query opgenomen. Standaard hebben alle velden dat.
+
 
 +
Every field (each ''TField'' in ''Fields'') has a ProviderFlags property. Only fields with '''pfInUpdate''' in ''ProviderFlags'' will be used in the update or insert cluase of a query. By default all fields have ''pfInUpdate'' set in their ''ProviderFlags'' property.
 +
 
 +
Which fields are used in the <tt>WHERE</tt> clause depends on the ''UpdateMode'' property of the query and the ''ProviderFlags'' property of the fields. Fields with ''pfInkey'' in their ''ProviderFlags'' are always used in the <tt>WHERE</tt> clause. A field will have the ''pfInKey'' flag set automatically if the field is part of the primary key of the table and 'TSQLQuery.UsePrimaryKeyAsKey' returns 'True'.
 +
 
 +
The default value for ''UpdateMode'' of the query is ''upWhereKeyOnly''. In this update mode only fields with ''pfInkey'' in their ''ProviderFlags'' property are used in the <tt>WHERE</tt> clause. If none of the fields have their ''pfInKey'' flag set, then no fields are available for the <tt>WHERE</tt> clause and the error message from the beginning of this section will be returned. You can solve the issue by:
 +
* Adding a primary key to the table and set ''TSQLQuery.UsePrimaryKeyAsKey'' to 'True', or
 +
* Setting the ''pfInkey'' flag for one or more fields in code.
 +
 
 +
The '''UpdateMode''' property knows two more possible values. 'upWhereAll' can be used to add all fields with the 'pfInWhere' flag set to the <tt>WHERE</tt> clause. By default all fields have this flag set. 'upWhereChanged' can be used to add only those fields that have the 'pfInWhere' flag set '''and''' that are changed in the current record.
 +
 
 +
==How to handle Errors==
 +
Run time errors are unavoidable, disks may fill up, necessary libraries or helper apps may not be available, things go wrong and we need to allow for that.
 +
The FPC detects and handles run time errors quite well. It usually gives you a concise and reasonable explanation of what went wrong. However, you will want
 +
to monitor and handle errors yourself for a number of reasons -
 +
* You probably don't want the programme to teminate at the first sign of trouble.
 +
* If we do keep going, lets make sure any memory allocated in the problem area is recovered, we don't want any memory leaks.
 +
* If we are going to go under however, lets give the user a context sensitive explanation.
 +
 
 +
The following bit of code is based on the above examples but this time it DOES check for errors in critical places. Key is the try...finally...end and try...except...end blocks. You can test it by doing things like uninstalling SQLite3, putting a dummy file in place of the test_dbase database and so on.
  
Welke velden in het where-gedeelte worden opgenomen hangt af van de UpdateMode van de query en de ProviderFlags van de betreffende velden. De velden met pfInKey in hun ProviderFlags komen altijd in het where-gedeelte terecht. Een veld krijgt de pfInKey vlag automatisch als het veld deel uitmaakt van de primary key van de tabel en TSQLQuery.UsePrimaryKeyAsKey waar is.
+
<syntaxhighlight lang=pascal>
 +
program DemoDBaseWithErrors;
 +
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
 +
uses
 +
  DB, Sysutils, sqldb, sqlite3conn;
 +
var
 +
    Connect : TSQLite3Connection;
 +
    Trans : TSQLTransaction;
  
De standaard waarde voor de UpdateMode van de query is upWhereKeyOnly. In deze UpdateMode zijn de velden met pfInKey in de ProviderFlags de enige velden die gebruikt worden in het where-gedeelte. Als er geen enkel veld de pfInKey vlag heeft en de UpdateMode is upWhereKeyOnly dan zijn er geen velden beschikbaar zijn voor het where-deel van de query en treedt de foutmelding op van het begin van deze sectie. Dit kan dan opgelost worden door een primary key aan de tabel toe te voegen en TSQLQuery.UsePrimaryKeyAsKey op 'true' in te stellen of door handmatig de pfInKey vlag in te stellen op voor of meer van de velden van de tabel.
 
  
Er zijn nog twee andere UpdateMode's. Met UpWhereAll worden naast de velden met pfInKey alle velden met de pfInWhere flag opgenomen in het where-deel van de query. Standaard heeft ieder veld deze vlag ingesteld staan. Als de UpdateMode UpWhereChanged is dan worden alleen die velden met pfInWhere opgenomen die in het huidige record gewijzigd zijn.
+
procedure WriteTable (Command : string);
 +
begin
 +
Connect.ExecuteDirect(Command);
 +
    Trans.Commit;
 +
end;
  
== Hoe voer je een query uit met een TSQLQuery? ==
+
procedure ReadTable ();
 +
var
 +
  Query : TSQLQuery;
 +
  Count : smallint;
 +
begin
 +
    Count := 0;
 +
    try
 +
        Query := TSQLQuery.Create(nil);
 +
        Query.DataBase := Connect;
 +
        Query.SQL.Text:= 'select * from tblNames';
 +
        Query.Open;          // This will also open Connect
 +
        while not Query.EOF do begin
 +
            writeln('ID: ', Query.FieldByName('ID').AsInteger, '  Name: ' +
 +
                              Query.FieldByName('Name').AsString);
 +
            Query.Next;
 +
            Count := Count + 1;
 +
        end;
 +
    finally
 +
        Query.Close;
 +
        Query.Free;
 +
    end;
 +
    writeln('Found a total of ' + InttoStr(Count) + ' lines.');
 +
end;
  
Naast queries die een [[#Hoe lees ik data in uit een tabel?|dataset teruggeven]] zijn er ook queries die dat niet doen. Bijvoorbeeld de 'update', 'insert' en 'delete' queries. Deze queries kunnen niet alleen uitegvoerd worden met [[#Hoe voer ik direct queries uit/maak ik een tabel?|TSQLConnection.ExecuteDirect]] maar ook door een TSQLQuery te gebruiken. Dit doe je door TSQLQuery.ExecSQL aan te roepen in plaats van TSQLQuery.Open. (Wat je gebruikt om een tabel die je met een query opvraagt te openen)  
+
procedure FatalError(ClassName, Message, Suggestion : string);
 +
begin
 +
    writeln(ClassName);
 +
    writeln(Message);
 +
    writeln(Suggestion);
 +
    Connect.Close;              // Its possibly silly worrying about freeing
 +
    Trans.free;                // if we are going to call halt() but its
 +
    Connect.Free;              // a demo, alright ?
 +
    halt();
 +
end;
  
De volgende procedure maakt een tabel en voegt twee records toe door gebruik te maken van TSQLQuery.
+
begin
 +
    Connect := TSQLite3Connection.Create(nil);
 +
    Trans := TSQLTransaction.Create(Connect);
 +
Connect.Transaction := Trans;
 +
    Connect.DatabaseName := 'test_dbase';
 +
    try
 +
        if not fileexists(Connect.DatabaseName) then begin
 +
            Connect.Open;  // give EInOutError if (eg) SQLite not installed
 +
            Trans.StartTransaction;
 +
            WriteTable('create table TBLNAMES (ID integer Primary Key, NAME varchar(40));');
 +
            Trans.Commit;
 +
        end;
 +
        Connect.open;
 +
        Trans.StartTransaction;
 +
        WriteTable('insert into TBLNAMES (NAME) values (''AName1'');');
 +
        WriteTable('insert into TBLNAMES (NAME) values (''AName2'');');
 +
    except
 +
        on E : EDatabaseError do
 +
            FatalError(E.ClassName, E.Message, 'Does the file contain the correct database ?');
 +
        on E : EInOutError do
 +
            FatalError(E.ClassName, E.Message, 'Have you installed SQLite (and dev package)?');
 +
        on E : Exception do
 +
            FatalError(E.ClassName, E.Message, 'Something really really bad happened.');
 +
    end;
 +
    ReadTable();
 +
    Connect.Close;
 +
    Trans.Free;
 +
    Connect.Free;
 +
end.               
 +
</syntaxhighlight>
  
  procedure CreateTable;
+
== How to execute a query using TSQLQuery? ==
 +
Next to statements that return a dataset (see [[#How to read data from a table?]]) SQL has statements that do not return data. For example <tt>INSERT</tt>, <tt>UPDATE</tt> and <tt>DELETE</tt> statements do not return data. These statements can be executed using ''[[#How to execute direct queries/make a table?|TSQLConnection.ExecuteDirect]]'', but TSQLQuery can also be used. If you do not expect return data use ''TSQLQuery.ExecSQL'' instead of ''TSQLQuery.Open''. As mentioned earlier, use ''TSQLQuery.Open'' to open the dataset returned by the SQL statement.
 +
 
 +
The following procedure creates a table and inserts two records using TSQLQuery.
 +
 
 +
<syntaxhighlight lang=pascal>
 +
procedure CreateTable;
 
    
 
    
   var Query : TSQLQuery;
+
var
 +
   Query : TSQLQuery;
 
    
 
    
   begin
+
begin
     Query := GetQuery;
+
  Query := TSQLQuery.Create(nil);
 +
   try
 +
     Query.Database := AConnection;
 +
 
 
     Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
 
     Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
 
     Query.ExecSQL;
 
     Query.ExecSQL;
 
+
     Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (1,'Name1');';
+
     Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (1,''Name1'');';
 
     Query.ExecSQL;
 
     Query.ExecSQL;
 
    
 
    
     Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (2,'Name2');';
+
     Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (2,''Name2'');';
 
     Query.ExecSQL;
 
     Query.ExecSQL;
    
+
   finally
    Query.Close;
 
 
     Query.Free;
 
     Query.Free;
 
   end;
 
   end;
 +
end;</syntaxhighlight>
 +
 +
== How to use parameters in a query? ==
 +
In the code example of [[#How to execute a query using TSQLQuery?]] the same query is used twice, only the values to be inserted differ. A better way to do this is by using parameters in the query.
 +
 +
The syntax of parameters in queries is different per database system, but the differences are handled by TSQLQuery. Replace the values in the query with a colon followed by the name of the parameter you want to use. For example:
 +
<syntaxhighlight lang=pascal>Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';</syntaxhighlight>
 +
 +
This query will create two parameters: 'ID' and 'NAME'.
 +
To determine the parameters, the query is parsed at the moment the text of ''TSQLQuery.SQL'' is assigned or changed. All existing parameters will be removed and the new parameters will be added to the 'TSQLQuery.Params' property. Assigning a value to a parameter is similar to assigning a value to a field in the dataset:
 +
 +
<syntaxhighlight lang=pascal>
 +
Query.Params.ParamByName('Name').AsString := 'Name1';
 +
</syntaxhighlight>
 +
 +
You can't tell from the query what kind of data must be stored in the parameter. The data type of the parameter is determined at the moment a value is first assigned to the parameter. By assigning a value using '.AsString', the parameter is assigned the data type 'ftString'. You can determine the data type directly by setting the 'DataType' property. If an incorrect datatype is assigned to the parameter, then problems will occur during opening or executing the query.
 +
See [[Database field type]] for more information on data types.
 +
 +
=== Select query ===
 +
 +
An example of a select query with parameters would be to change something like this:
 +
 +
<syntaxhighlight lang=pascal>
 +
  Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = '''+Edit1.Text+''' ORDER BY NAME ';
 +
</syntaxhighlight>
  
== Hoe maak je gebruik van parameters in een query? ==
+
to something like this:
  
In [[#Hoe voer je een query uit met een TSQLQuery?|dit voorbeeld]] wordt twee keer dezelfde query uitgevoerd met alleen verschillende parameters. Een betere manier om dit te doen is door een query met parameters te gebruiken.  
+
<syntaxhighlight lang=pascal>
 +
  Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = :NAMEPARAM ORDER BY NAME ';
 +
  Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;
 +
</syntaxhighlight>
  
De syntax van parameters in queries is per Database-server verschillend maar dat wordt door TSQLQuery afgehandeld. Vervang de parameter in de query met een dubbele punt, gevolgd door de naam die je de parameter wilt geven. Bijvoorbeeld
+
=== Example ===
  Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
 
maakt twee parameters aan, 'ID' en 'NAME'.
 
Om te bepalen wat de parameters zijn wordt de query geparsed als hij aan TSQLQuery.SQL wordt toegewezen of gewijzigd. Alle bestaande parameters worden dan verwijderd en de nieuwe worden toegevoegd aan de TSQLQuery.Params property. Om een waarde aan een parameter toe te wijzen lijkt op de manier waarop de waarde van een veld wordt gewijzigd:
 
  Query.Params.ParamByName('Name').AsString := 'Name1';
 
Het is aan de query zelf niet te zien wat voor soort data de parameter moet opslaan. Daarom wordt het datatype pas bepaald op het moment dat er voor het eerst een waarde aan de parameter wordt toegewezen. Door .AsString te gebruiken bijvoorbeeld, krijgt de parameter het datatype 'ftString'. Het datatype is ook direct in te stellen door de 'DataType' property te gebruiken. Als het verkeerde datatype aan een parameter toegewezen wordt, kan dat tot problemen leiden tijdens het openen of uitvoeren van de query.
 
  
Het volgende voorbeeld maakt dezelfde tabel aan als in het vorige voorbeeld maar dan door gebruik te maken van parameters:
+
The following example creates the same table as the previous example, but now parameters are used:
  
  procedure CreateTableUsingParameters;
+
<syntaxhighlight lang=pascal>
 +
procedure CreateTableUsingParameters;
 
    
 
    
   var Query : TSQLQuery;
+
var
 +
   Query : TSQLQuery;
 
    
 
    
   begin
+
begin
     Query := GetQuery;
+
  Query := TSQLQuery.Create(nil);
 +
   try
 +
     Query.Database := AConnection;
 +
 
 
     Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
 
     Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
 
     Query.ExecSQL;
 
     Query.ExecSQL;
 
+
 
 
     Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
 
     Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
 +
    Query.Prepare;
 
    
 
    
 
     Query.Params.ParamByName('ID').AsInteger := 1;
 
     Query.Params.ParamByName('ID').AsInteger := 1;
     Query.Params.ParamByName('ID').AsString := 'Name1';
+
     Query.Params.ParamByName('NAME').AsString := 'Name1';
 
     Query.ExecSQL;
 
     Query.ExecSQL;
 
    
 
    
 
     Query.Params.ParamByName('ID').AsInteger := 2;
 
     Query.Params.ParamByName('ID').AsInteger := 2;
     Query.Params.ParamByName('ID').AsString := 'Name2;
+
     Query.Params.ParamByName('NAME').AsString := 'Name2';
 
     Query.ExecSQL;
 
     Query.ExecSQL;
 
+
 
 +
    //Query.UnPrepare; // no need to call this; should be called by Query.Close
 
     Query.Close;
 
     Query.Close;
 +
  finally
 
     Query.Free;
 
     Query.Free;
 
   end;
 
   end;
 +
end;
 +
</syntaxhighlight>
 +
 +
Notice that this example requires more code than the example without the parameters. Then what is the use of using parameters?
 +
 +
Speed is one of the reasons. The example with parameters is faster, because the database server parses the query only once (in the .Prepare statement or at first run).
 +
 +
Another reason to use prepared statements is prevention of [http://en.wikipedia.org/wiki/SQL_injection SQL-injection] (see also [[Secure programming]].
 +
 +
Finally, in some cases it just simplifies coding.
 +
 +
== Troubleshooting: TSQLConnection logging ==
 +
 +
You can let a TSQLConnection log what it is doing. This can be handy to see what your Lazarus program sends to the database exactly, to debug the database components themselves and perhaps to optimize your queries.
 +
NB: if you use prepared statements/parametrized queries (see section above), the parameters are often sent in binary by the TSQLConnection descendent (e.g. TIBConnection), so you can't just copy/paste the logged SQL into a database query tool.
 +
Regardless, connection logging can give a lot of insight in what your program is doing.
 +
 +
Alternatives are:
 +
 +
# you can use the debugger to step through the database code if you have built FPC (and Lazarus) with debugging enabled.
 +
# if you use ODBC drivers (at least on Windows) you could enable tracelog output in the ODBC control panel.
 +
# many databases allow you to monitor all statements sent to it from a certain IP address/connection.
 +
 +
If you use TSQLConnection logging, two things are required:
 +
 +
# indicate which event types your TSQLConnection should log
 +
# point TSQLConnection at a function that receives the events and processes them (logs them to file, prints them to screen, etc.).
 +
That function must be of type TDBLogNotifyEvent (see sqldb.pp), so it needs this signature:
 +
 +
<syntaxhighlight lang=pascal>
 +
TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String) of object;
 +
</syntaxhighlight>
 +
 +
=== FPC (or: the manual way) ===
 +
 +
A code snippet can illustrate this:
 +
 +
<syntaxhighlight lang=pascal>
 +
uses
 +
...
 +
TSQLConnection, //or a child object like TIBConnection, TMSSQLConnection
 +
...
 +
var
 +
type
 +
  TMyApplication = class(TCustomApplication); //this is our application that uses the connection
 +
...
 +
  private
 +
    // This example stores the logged events in this stringlist:
 +
    FConnectionLog: TStringList;
 +
...
 +
  protected
 +
    // This procedure will receive the events that are logged by the connection:
 +
    procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
 +
...
 +
  procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
 +
    EventType: TDBEventType; const Msg: String);
 +
  // The procedure is called by TSQLConnection and saves the received log messages
 +
  // in the FConnectionLog stringlist
 +
  var
 +
    Source: string;
 +
  begin
 +
    // Nicely right aligned...
 +
    case EventType of
 +
      detCustom:  Source:='Custom:  ';
 +
      detPrepare:  Source:='Prepare: ';
 +
      detExecute:  Source:='Execute: ';
 +
      detFetch:    Source:='Fetch:  ';
 +
      detCommit:  Source:='Commit:  ';
 +
      detRollBack: Source:='Rollback:';
 +
      else Source:='Unknown event. Please fix program code.';
 +
    end;
 +
    FConnectionLog.Add(Source + ' ' + Msg);
 +
  end;
 +
 +
...
 +
  // We do need to tell our TSQLConnection what to log:
 +
    FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
 +
    // ... and to which procedure the connection should send the events:
 +
    FConnection.OnLog:=@Self.GetLogEvent;
 +
...
 +
  // now we can use the connection and the FConnectionLog stringlist will fill with log messages.
 +
</syntaxhighlight>
 +
 +
You can also use TSQLConnection's GlobalDBLogHook instead to log everything from multiple connections.
 +
 +
=== Lazarus (or: the quick way) ===
 +
 +
Finally, the description above is the FPC way of doing things as indicated in the introduction; if using Lazarus, a quicker way is to assign an event handler to the TSQLConnection's OnLog event.
  
Wat meteen opvalt is dat dit voorbeeld meer code heeft dan het voorbeeld dat geen gebruik maakt van parameters. Waarom zou je dan parameters gebruiken? Een van de redenen is dat het voorbeeld met parameters sneller is. Dat komt omdat de query niet twee keer geparsed hoeft te worden. Niet alleen door TSQLQuery, maar ook door de database server. De meeste database-servers ondersteunen parameters. Als eenzelfde query meerdere keren wordt aangeroepen met steeds verschillende waarden als parameter, dan hoeft de database server maar een keer een plan te maken voor het uitvoeren van de query. Dat maakt het sneller. (Met TSQLQuery.Prepare kan je zelf bepalen wanneer de query door de database-server geparsed en gepland moet worden. Met .UnPrepare zorg je ervoor dat de query de volgende keer dat hij uitgevoerd wordt, altijd eerst opnieuw wordt voorbereid door de database-server)
+
== See also ==
  
Een andere reden kan het voorkomen van [http://en.wikipedia.org/wiki/SQL_injection SQL-injectie] zijn, maar in sommige gevallen kan het de code ook een stuk vergemakkelijken.
+
* [[Working With TSQLQuery]]
[[Category:Databases]]
 

Latest revision as of 23:26, 10 December 2020

Deutsch (de) English (en) español (es) français (fr) 日本語 (ja) Nederlands (nl) polski (pl) 中文(中国大陆)‎ (zh_CN)

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

This text is setup as a 'how-to'. I want to answer a number of questions one by one, and explain how you can use the various classes. All those questions are put one after the other and form a sort of tutorial.

I will try to word it in such a way that the text can be used for Lazarus as well as Free Pascal. However, the examples are for FreePascal (i.e. they are console applications.)

Where can I find official documentation?

Please see the official documentation at SQLDB documentation.

How to connect to a database server?

SqlDB doesn't connect to a database server directly but uses a client that corresponds to the used database server. SqlDB sends the commands to the client library; the client library connects to the database and and transfers the commands. This means that a client library must be installed on the computer to make a connection to a database. Under Windows a client is usually a .dll, under Linux an .so and under OS/X a .dylib.

When the client library is installed properly you can connect to a database server using a TSQLConnection component. Various TSQLConnection components are available for different database servers (see SQLdb_Package):

Note for MySQL - There are many differences between the client versions to the extent that the clients and connections cannot be interchanged. If a MySQL client library version 4.1 is installed, you have to use a TMySQL41Connection. This is not related to the MySQL server; using the MySQL 4.1 client library you can probably connect to a MySQL 5.0 server (see MySQL documentation regarding what combinations are supported).

Although details differ for the various databases, in general you need to set four properties to connect to a database server:

  • the server name or IP address
  • the name of the database
  • the username
  • the password

When these properties are set, you can create a connection with the 'open' method. If the connection fails, a EDatabaseError exception is thrown. Use the property 'connected' to test if a connection has been made with the database server. Use the 'close' method to end the connection with the server.

Program ConnectDB;

{$mode objfpc}{$H+}

uses
  IBConnection;

function CreateConnection: TIBConnection;
begin
  result := TIBConnection.Create(nil);
  result.Hostname := 'localhost';
  result.DatabaseName := '/opt/firebird/examples/employee.fdb';
  result.UserName := 'sysdba';
  result.Password := 'masterkey';
end;
 
var   
  AConnection : TIBConnection;

begin
  AConnection := CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    writeln('Successful connect!')
  else
    writeln('This is not possible, because if the connection failed, ' +
            'an exception should be raised, so this code would not ' +
            'be executed');
  AConnection.Close;
  AConnection.Free;
end.

If an exception is thrown, read the error message carefully. It may be that the database server is not running, the user name or password are incorrect or the database name or IP address are typed incorrectly. If the error message states that the client library cannot be found, then check if the client is installed correctly. Often the error message states literally the name of the file looked for.

How to execute direct queries/make a table?

SqlDB - the name says it all - only works with database server that make use of SQL. SQL stands for 'Structured Query Language' SQL is a language developed to allow working with relational databases. Virtually every database system has its own dialect, but a large number of SQL statements are the same for all database systems.

In FPC, there is a difference between:

  • SQL statements that return information (a dataset). For this, you have to use the TSQLQuery component; see #How to read data from a table?.
  • statements that do not return information but do something else, e.g. update data. For this, you may also use the 'ExecuteDirect' method of a TSQLConnection. (You can also use this if you get a dataset back but are not interested in the results, e.g. in a selectable stored procedure).

Most database system execute SQL statements within a transaction. If you want changes made within a transaction available in other transactions, or have those changes available even after closing the transaction(!), then you have to 'commit' the transaction.

To support transactions Sqldb contains the TSQLTransaction component. A SQL statement that is executed by Sqldb must always be executed within a transaction, even if the database system does not support transactions. Also, there are database systems that do support transaction for which TSQLConnection does not (yet) support transaction. Even then, you must use the TSQLTransaction component.

To use TSQLConnection.ExecuteDirect to execute a SQL statement you must specify which 'Transaction' must be used. In turn, to use TSQLTransaction you must specify which TSQLConnection component must be used.

The following example creates a table 'TBLNAMES' with fields 'NAME' and 'ID' and inserts two records. This time using SQLite. The used SQL statements are not explained. For more information about the SQL statements, their use and syntax, please refer to the database system documentation. Note that this example does not attempt to catch any errors, thats a bad thing! Look into Exceptions.

program CreateTable;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
  sqldb, sqlite3conn; 
 
var 
  AConnection : TSQLite3Connection;
  ATransaction : TSQLTransaction;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  AConnection.DatabaseName := 'test_dbase';
 
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.Open;
  ATransaction.StartTransaction;
  AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));'); 
 
  ATransaction.Commit;
 
  ATransaction.StartTransaction;
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,''Name1'');'); 
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,''Name2'');'); 
  ATransaction.Commit; 
  AConnection.Close;
  ATransaction.Free;	
  AConnection.Free;
end.

How to read data from a table?

Use the TSQLQuery component to read data from a table. A TSQLQuery component must be connected to a TSQLConnection component and a TSQLTransaction component to do its work. Setting the TSQLConnection and TSQLTransaction is discussed in #How to connect to a database server? and #How to execute direct queries/make a table?.

When the TSQLConnection, TSQLTransaction and TSQLQuery are connected, then TSQLQuery needs to be further configured to work. TSQLQuery has a 'SQL' property containing a TStrings object. The 'SQL' property contains a SQL statement that must be executed. If all data from a table tablename must be read, then set the 'SQL' property to:

'SELECT * FROM tablename;'

.

Use 'open' to read the table from the server and put the data in the TSQLQuery dataset. The data can be accessed through TSQLQuery until the query is closed using 'close'.

TSQLQuery is a subclass of TDataset. TDataset has a 'Fields' collection that contains all columns of the table. The TDataset also keeps track of the current record. Use 'First', 'Next', 'Prior' and 'Last' to change the current record. 'Bof' returns 'True' if the first record is reached, and 'Eof' returns 'True' if the last record is reached. To read the value of a field in the current record, first find the right 'TField' object and then use 'AsString', 'AsInteger', etc.

Example: reading data from a table

Below is an example that displays all values of the table as it was made in CreateTable example above. Copy the test_database file into the ShowData working directory. Note, again, no error checking has taken place !

Program ShowData;
 {$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
  DB, Sysutils, sqldb, sqlite3conn;

var
  AConnection  : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query        : TSQLQuery;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.DatabaseName := 'test_dbase';
  Query := TSQLQuery.Create(nil);
  Query.SQL.Text := 'select * from tblNames';
  Query.Database := AConnection;
  Query.Open;
  while not Query.Eof do
  begin
    Writeln('ID: ', Query.FieldByName('ID').AsInteger, 'Name: ' +
                                  Query.FieldByName('Name').AsString);
    Query.Next;
  end;
  Query.Close;
  AConnection.Close;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

(The code above of course is not quite finished, it misses 'try...finally' blocks. However, the above code intends to show the database code and thus the finishing touches are left out.) Please note that 'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the SQL statement is executed and if no transaction is available then a transaction is automatically started. The programmer does not need to start the transaction explicitly. The same applies for the connection maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not really required. If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. Possible changes to data contained in the transaction will be lost.

Why does TSQLQuery.RecordCount always return 10?

To count the records in a dataset, use '.RecordCount'. However, notice that '.RecordCount' shows the number of records that is already loaded from the server. For performance reasons, SqlDB does not read all records when opening TSQLQuery by default, only the first 10. Only when the eleventh record is accessed will the next set of 10 records be loaded, etc. Using '.Last', all records will be loaded.

When you want to know the real number of records on the server you can first call '.Last' and then call '.RecordCount'.

An alternative is available. The number of records returned by the server is set by the '.PacketRecords' property. The default value is 10; if you make it -1 then all records will be loaded at once.

In current stable FPC, '.RecordCount' does not take filters into account, i.e. it shows the unfiltered total.

If you need the exact number of records, it often is a better idea to directly query the number of records in a query using another SQL query, but you would have to do that in the same transaction, as other transactions may have changed the number of records in the meanwhile.

Lazarus

Lazarus has various components to show data from a TDataset on a form. Instead of a While-loop and Writeln statements as used above, you can use the components to show the data in a table. Place the right TSQLConnection, TSQLTransaction and TSQLQuery components on a form, then connect them and set them properly. In addition you will need a TDatasource; set the 'TDatasource.Dataset' property to the TSQLQuery component you used. (Note do not set the 'TSQLQuery.Datasource' property to the TDatasource compnent you used. The 'TSQLQuery.Datasource' property is used only in master-detail tables - see MasterDetail) Subsequently you may put a TDBGrid onto the form and set the 'Datasource' property of the grid to the TDatasource component you added before.

To see if it all works, set the 'Connected' property of the TSQLConnection to 'True' in the Lazarus IDE. The IDE will try to connect to the database server immediately. If this works you can set the 'TSQLQuery.Active' property to 'True'. If everything is right, you will see - within the IDE - all data from the table immediately on the screen.

How to change data in a table?

To change the data in a record (or records), the general process is get TSQLQuery to search for the records you wish to change, make the changes there and then push them back to the database. The TDataSet (from which TSQLQuery is derived) must be set to edit mode. To enter edit mode call the '.Edit', '.Insert' or '.Append' methods. Use the '.Edit' method to change the current record. Use '.Insert' to insert a new record before the current record. Use '.Append' to insert a new record at the end of the table. In edit mode you can change field values through the 'Fields' property. Use 'Post' to validate the new data, if the data is valid then the edit mode is left. If you move to another record - for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. Use '.Cancel' to discard all changes you made since the last '.Post' call and leave the edit mode.

Query.Edit;
Query.FieldByName('NAME').AsString := 'Edited name';
Query.Post;

The above is not the complete story yet. TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible immediately, but they are not sent to the database server. What does happen is that the changes are maintained in a change log. When the '.ApplyUpdates' method is called, then all changes in the change log are sent to the database. Only then will database server know of the changes. The changes are sent to the server within a transaction of TSQLTransaction. Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the database server.

The below is an example of changing the data in a table, sending the changes to the server and committing the transaction. Again, no error checking, again, thats bad!

Program EditData;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
    db, sqldb, sqlite3conn;
var 
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query : TSQLQuery;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.DatabaseName := 'test_dbase';
  Query := TSQLQuery.Create(nil);
  Query.DataBase := AConnection;
  Query.SQL.Text := 'select * from tblNames where ID = 2';
  Query.Open;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Name Number 2';
  Query.Post;
  Query.UpdateMode := upWhereAll;         // defined in db
  Query.ApplyUpdates;
  ATransaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

The actual works starts with the SQL statement "select * from tblNames where ID = 2" identifying the record (or records) you wish to change. If you leave out the "where ID = 2" bit, the TSQLQuery apparently sets ID (and other integer fields?) to 1. And therefore will operate on lines where ID=1 only. For a discussion of 'UpdateMode' continue reading.

How does SqlDB send the changes to the database server?

In the code example in #How to change data in a table?, you will find the line

Query.UpdateMode := upWhereAll;

without explanation of what it does. The best way to find out what that line does is to leave it out. If you leave out the statement and the followed this howto precisely, then you will receive the following error message:

No update query specified and failed to generate one. (No fields for inclusion in where statement found)

To understand what went wrong, you must understand how changes are sent to the database server. The only way to get data in a SQL server is by executing SQL queries. SQL has three types of queries for three different ways of manupulating a record. To create a new record, change or delete a record insert, update and delete statements are executed respectively. An update statement may be as follows:

update TBLNAMES set NAME='Edited name' where ID=1;

To send a change to the database server, Sqldb must assemble an update query. To assemble the query, three things are needed:

The name of the table
The table name is retrieved from parsing the select query, although this doesn't always work.
UPDATE or INSERT clause
These contain the fields that must be changed.
WHERE clause
This contains the fields that determine which records should be changed.

Every field (each TField in Fields) has a ProviderFlags property. Only fields with pfInUpdate in ProviderFlags will be used in the update or insert cluase of a query. By default all fields have pfInUpdate set in their ProviderFlags property.

Which fields are used in the WHERE clause depends on the UpdateMode property of the query and the ProviderFlags property of the fields. Fields with pfInkey in their ProviderFlags are always used in the WHERE clause. A field will have the pfInKey flag set automatically if the field is part of the primary key of the table and 'TSQLQuery.UsePrimaryKeyAsKey' returns 'True'.

The default value for UpdateMode of the query is upWhereKeyOnly. In this update mode only fields with pfInkey in their ProviderFlags property are used in the WHERE clause. If none of the fields have their pfInKey flag set, then no fields are available for the WHERE clause and the error message from the beginning of this section will be returned. You can solve the issue by:

  • Adding a primary key to the table and set TSQLQuery.UsePrimaryKeyAsKey to 'True', or
  • Setting the pfInkey flag for one or more fields in code.

The UpdateMode property knows two more possible values. 'upWhereAll' can be used to add all fields with the 'pfInWhere' flag set to the WHERE clause. By default all fields have this flag set. 'upWhereChanged' can be used to add only those fields that have the 'pfInWhere' flag set and that are changed in the current record.

How to handle Errors

Run time errors are unavoidable, disks may fill up, necessary libraries or helper apps may not be available, things go wrong and we need to allow for that. The FPC detects and handles run time errors quite well. It usually gives you a concise and reasonable explanation of what went wrong. However, you will want to monitor and handle errors yourself for a number of reasons -

  • You probably don't want the programme to teminate at the first sign of trouble.
  • If we do keep going, lets make sure any memory allocated in the problem area is recovered, we don't want any memory leaks.
  • If we are going to go under however, lets give the user a context sensitive explanation.

The following bit of code is based on the above examples but this time it DOES check for errors in critical places. Key is the try...finally...end and try...except...end blocks. You can test it by doing things like uninstalling SQLite3, putting a dummy file in place of the test_dbase database and so on.

program DemoDBaseWithErrors;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
  DB, Sysutils, sqldb, sqlite3conn;
var
    Connect : TSQLite3Connection;
    Trans : TSQLTransaction;


procedure WriteTable (Command : string);
begin
	Connect.ExecuteDirect(Command);
    	Trans.Commit;
end;

procedure ReadTable ();
var
   Query : TSQLQuery;
   Count : smallint;
begin
    Count := 0;
    try
        Query := TSQLQuery.Create(nil);
        Query.DataBase := Connect;
        Query.SQL.Text:= 'select * from tblNames';
        Query.Open;          // This will also open Connect
        while not Query.EOF do begin
            writeln('ID: ', Query.FieldByName('ID').AsInteger, '  Name: ' +
                              Query.FieldByName('Name').AsString);
            Query.Next;
            Count := Count + 1;
        end;
    finally
        Query.Close;
        Query.Free;
    end;
    writeln('Found a total of ' + InttoStr(Count) + ' lines.');
end;

procedure FatalError(ClassName, Message, Suggestion : string);
begin
    writeln(ClassName);
    writeln(Message);
    writeln(Suggestion);
    Connect.Close;              // Its possibly silly worrying about freeing
    Trans.free;                 // if we are going to call halt() but its
    Connect.Free;               // a demo, alright ?
    halt();
end;

begin
    Connect := TSQLite3Connection.Create(nil);
    Trans := TSQLTransaction.Create(Connect);
	Connect.Transaction := Trans;
    Connect.DatabaseName := 'test_dbase';
    try
        if not fileexists(Connect.DatabaseName) then begin
            Connect.Open;   // give EInOutError if (eg) SQLite not installed
            Trans.StartTransaction;
            WriteTable('create table TBLNAMES (ID integer Primary Key, NAME varchar(40));');
            Trans.Commit;
        end;
        Connect.open;
        Trans.StartTransaction;
        WriteTable('insert into TBLNAMES (NAME) values (''AName1'');');
        WriteTable('insert into TBLNAMES (NAME) values (''AName2'');');
    except
        on E : EDatabaseError do
            FatalError(E.ClassName, E.Message, 'Does the file contain the correct database ?');
        on E : EInOutError do
            FatalError(E.ClassName, E.Message, 'Have you installed SQLite (and dev package)?');
        on E : Exception do
            FatalError(E.ClassName, E.Message, 'Something really really bad happened.');
     end;
    ReadTable();
    Connect.Close;
    Trans.Free;
    Connect.Free;
end.

How to execute a query using TSQLQuery?

Next to statements that return a dataset (see #How to read data from a table?) SQL has statements that do not return data. For example INSERT, UPDATE and DELETE statements do not return data. These statements can be executed using TSQLConnection.ExecuteDirect, but TSQLQuery can also be used. If you do not expect return data use TSQLQuery.ExecSQL instead of TSQLQuery.Open. As mentioned earlier, use TSQLQuery.Open to open the dataset returned by the SQL statement.

The following procedure creates a table and inserts two records using TSQLQuery.

procedure CreateTable;
  
var 
  Query : TSQLQuery;
  
begin
  Query := TSQLQuery.Create(nil);
  try
    Query.Database := AConnection;

    Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
    Query.ExecSQL;
 
    Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (1,''Name1'');';
    Query.ExecSQL;
  
    Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (2,''Name2'');';
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;

How to use parameters in a query?

In the code example of #How to execute a query using TSQLQuery? the same query is used twice, only the values to be inserted differ. A better way to do this is by using parameters in the query.

The syntax of parameters in queries is different per database system, but the differences are handled by TSQLQuery. Replace the values in the query with a colon followed by the name of the parameter you want to use. For example:

Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';

This query will create two parameters: 'ID' and 'NAME'. To determine the parameters, the query is parsed at the moment the text of TSQLQuery.SQL is assigned or changed. All existing parameters will be removed and the new parameters will be added to the 'TSQLQuery.Params' property. Assigning a value to a parameter is similar to assigning a value to a field in the dataset:

Query.Params.ParamByName('Name').AsString := 'Name1';

You can't tell from the query what kind of data must be stored in the parameter. The data type of the parameter is determined at the moment a value is first assigned to the parameter. By assigning a value using '.AsString', the parameter is assigned the data type 'ftString'. You can determine the data type directly by setting the 'DataType' property. If an incorrect datatype is assigned to the parameter, then problems will occur during opening or executing the query. See Database field type for more information on data types.

Select query

An example of a select query with parameters would be to change something like this:

  Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = '''+Edit1.Text+''' ORDER BY NAME ';

to something like this:

  Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = :NAMEPARAM ORDER BY NAME ';
  Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;

Example

The following example creates the same table as the previous example, but now parameters are used:

procedure CreateTableUsingParameters;
  
var 
  Query : TSQLQuery;
  
begin
  Query := TSQLQuery.Create(nil);
  try
    Query.Database := AConnection;

    Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
    Query.ExecSQL;

    Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
    Query.Prepare;
  
    Query.Params.ParamByName('ID').AsInteger := 1;
    Query.Params.ParamByName('NAME').AsString := 'Name1';
    Query.ExecSQL;
  
    Query.Params.ParamByName('ID').AsInteger := 2;
    Query.Params.ParamByName('NAME').AsString := 'Name2';
    Query.ExecSQL;

    //Query.UnPrepare; // no need to call this; should be called by Query.Close
    Query.Close;
  finally
    Query.Free;
  end;
end;

Notice that this example requires more code than the example without the parameters. Then what is the use of using parameters?

Speed is one of the reasons. The example with parameters is faster, because the database server parses the query only once (in the .Prepare statement or at first run).

Another reason to use prepared statements is prevention of SQL-injection (see also Secure programming.

Finally, in some cases it just simplifies coding.

Troubleshooting: TSQLConnection logging

You can let a TSQLConnection log what it is doing. This can be handy to see what your Lazarus program sends to the database exactly, to debug the database components themselves and perhaps to optimize your queries. NB: if you use prepared statements/parametrized queries (see section above), the parameters are often sent in binary by the TSQLConnection descendent (e.g. TIBConnection), so you can't just copy/paste the logged SQL into a database query tool. Regardless, connection logging can give a lot of insight in what your program is doing.

Alternatives are:

  1. you can use the debugger to step through the database code if you have built FPC (and Lazarus) with debugging enabled.
  2. if you use ODBC drivers (at least on Windows) you could enable tracelog output in the ODBC control panel.
  3. many databases allow you to monitor all statements sent to it from a certain IP address/connection.

If you use TSQLConnection logging, two things are required:

  1. indicate which event types your TSQLConnection should log
  2. point TSQLConnection at a function that receives the events and processes them (logs them to file, prints them to screen, etc.).

That function must be of type TDBLogNotifyEvent (see sqldb.pp), so it needs this signature:

TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String) of object;

FPC (or: the manual way)

A code snippet can illustrate this:

uses
...
TSQLConnection, //or a child object like TIBConnection, TMSSQLConnection
...
var
type 
  TMyApplication = class(TCustomApplication); //this is our application that uses the connection
...
  private
    // This example stores the logged events in this stringlist:
    FConnectionLog: TStringList;
...
  protected
    // This procedure will receive the events that are logged by the connection:
    procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
...
  procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
    EventType: TDBEventType; const Msg: String);
  // The procedure is called by TSQLConnection and saves the received log messages
  // in the FConnectionLog stringlist
  var
    Source: string;
  begin
    // Nicely right aligned...
    case EventType of
      detCustom:   Source:='Custom:  ';
      detPrepare:  Source:='Prepare: ';
      detExecute:  Source:='Execute: ';
      detFetch:    Source:='Fetch:   ';
      detCommit:   Source:='Commit:  ';
      detRollBack: Source:='Rollback:';
      else Source:='Unknown event. Please fix program code.';
    end;
    FConnectionLog.Add(Source + ' ' + Msg);
  end;

...
  // We do need to tell our TSQLConnection what to log:
    FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
    // ... and to which procedure the connection should send the events:
    FConnection.OnLog:=@Self.GetLogEvent;
...
  // now we can use the connection and the FConnectionLog stringlist will fill with log messages.

You can also use TSQLConnection's GlobalDBLogHook instead to log everything from multiple connections.

Lazarus (or: the quick way)

Finally, the description above is the FPC way of doing things as indicated in the introduction; if using Lazarus, a quicker way is to assign an event handler to the TSQLConnection's OnLog event.

See also