SqlDBHowto

From Lazarus wiki
Revision as of 17:56, 18 May 2010 by Abeomega (talk | contribs) (Partial translation)
Jump to navigationJump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

Introduction

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, 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 Freepascal. However, the examples are for Freepascal (i.e. they are console applications.)

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. 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.

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. 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.

Program ConnectDB

var AConnection : TSQLConnection;

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

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    writeln('Succesful 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 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.

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. 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?.) 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 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 'Connection' 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.

program CreateTable;

var AConnection : TSQLConnection;
    ATransaction : TSQLTransaction;

procedure CreateTransaction;
begin
  ATransaction := TSQLTransaction.Create;
  ATransaction.Database := AConnection;
end;

begin
  CreateConnection;
  CreateTransaction;
  AConnection.Transaction := ATransaction;
  AConnection.Open;
  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;
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,'Name1');'); 
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,'Name2');'); 
  ATransaction.Commit; 
  AConnection.Close;
  AConnection.Free;
  ATransaction.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 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 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'. 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.


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.

Program ShowData;

var AConnection : TSQLConnection;
    ATransaction : TSQLTransaction;

procedure GetQuery : TSQLQuery;
var AQuery : TSQLQuery;
begin
  AQuery := TSQLQuery.Create;
  AQuery.Database := FConnection;
  AQuery.Transaction := FTransaction;
  Result := AQuery;
end;

var Query : TSQLQuery;

begin
  CreateConnection;
  CreateTransaction;
  Query := GetQuery;
  Query.SQL.Text := 'select * from tblNames';
  AConnection.Open;
  Query.Open;
  while not Query.Eof do
    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. 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 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'. 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

Lazarus has various components to show data from a TDataset on a form. Instead of a While-loop and Writeln statements as use avobe, 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 to '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 only used only in master-detial tables.) 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, 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 mean 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 databse. Only then the database server knows all 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 publish or store the changes.

The below is an example of changing the data in a table, sending the changes to the server and comitting the transaction.

Program EditData;

var AConnection : TSQLConnection;
    ATransaction : TSQLTransaction;
    Query : TSQLQuery;

begin
  CreateConnection;
  CreateTransaction;
  AConnection.Transaction := ATransaction;
  Query := GetQuery;
  Query.SQL.Text := 'select * from tblNames';
  Query.Open;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Edited name';
  Query.Post;
  Query.UpdateMode := upWhereAll;
  Query.ApplyUpdates;
  AConnection.Transaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

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 as follows:

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

Om een wijziging naar de database-server te sturen, moet SqlDB dus zo'n update-query samenstellen. Daarvoor zijn drie dingen nodig:

De tabelnaam
Deze wordt achterhaald door de opgegeven select-query te parsen (dit lukt niet altijd.)
Update- of insert-deel
Dit zijn de velden die gewijzigd worden.
Where-deel
Dit zijn de velden die aangeven welk record er nou eigenlijk gewijzigd moet worden.

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.

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.

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.

Hoe voer je een query uit met een TSQLQuery?

Naast queries die een 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 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)

De volgende procedure maakt een tabel en voegt twee records toe door gebruik te maken van TSQLQuery.

 procedure CreateTable;
 
 var Query : TSQLQuery;
 
 begin
   Query := GetQuery;
   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;
 
   Query.Close;
   Query.Free;
 end;

Hoe maak je gebruik van parameters in een query?

In 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.

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

 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:

 procedure CreateTableUsingParameters;
 
 var Query : TSQLQuery;
 
 begin
   Query := GetQuery;
   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.Params.ParamByName('ID').AsInteger := 1;
   Query.Params.ParamByName('ID').AsString := 'Name1';
   Query.ExecSQL;
 
   Query.Params.ParamByName('ID').AsInteger := 2;
   Query.Params.ParamByName('ID').AsString := 'Name2;
   Query.ExecSQL;
 
   Query.Close;
   Query.Free;
 end;

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)

Een andere reden kan het voorkomen van SQL-injectie zijn, maar in sommige gevallen kan het de code ook een stuk vergemakkelijken.