SqlDBHowto

From Lazarus wiki
Revision as of 16:10, 18 May 2010 by Abeomega (talk | contribs) (New page: == 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.) ...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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 do I 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 TMy TPQConnection voor PostgreSQL en TMySQL40Connection, TMySQL41Connection en TMySQL50connection voor MySQL-clients met versienummer 4.0, 4.1 of 5.0. De verschillen tussen deze MySQL versies zijn zo groot, dat ze niet door elkaar te gebruiken zijn. Als de MySQL-client library versie 4.1 is geinstalleerd, moet een TMySQL41Connection gebruikt worden, onafhankelijk van de versie van de MySQL-server.

Het kan per database verschillen, maar over het algemeen moeten er vier properties ingesteld worden om met een database-server te kunnen verbinden: de naam van de database, de server-naam of ip-adres, een gebruikersnaam en wachtwoord. Als deze zijn ingesteld kan er een verbinding gemaakt worden met de 'open' methode. Als de verbinding mislukt, komt er een EDatabaseError. Met de property 'connected' kan gekeken worden of er al een verbinding met de database-server is. Met 'close' kan de verbinding weer afgesloten worden.

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.

Als er een exceptie optreed, lees de foutmelding dan zorgvuldig. Het kan zijn dat de gebruikersnaam niet goed is, of bijvoorbeeld het ip-adres. Als in de foutmelding staat dat de client-library niet gevonden kan worden, controleer dan of die client juist geinstalleerd is. Vaak staat in de foutmelding de letterlijke naam van het bestand waarnaar gezocht wordt.

Hoe voer ik direct queries uit/maak ik een tabel?

SqlDB- de naam zegt het al, werkt alleen met database-servers die van SQL gebruik maken. SQL staat voor 'Structured Query Language' en is een taal speciaal ontwikkeld om met relationele databases te werken. Vrijwel iedere database-server heeft zijn eigen dialect, maar een aantal SQL-commando's zijn voor alle database types hetzelfde. Er wordt een verschil gemaakt tussen SQL-commando's die gegevens (een dataset) terug geven, en commando's die dat niet doen. Als je de gegevens die een SQL-commando terugstuurt wilt gebruiken, moet je altijd een TSQLQuery-component gebruiken. (zie hier) Zoniet kan ook de 'ExecuteDirect' methode van een TSQLConnection gebruikt worden.

De meeste typen database-servers kunnen SQL-commando's uitvoeren binnen een transactie. Wil je dat de wijzigingen die je binnen een transactie gemaakt hebt ook beschikbaar komen in de andere transacties, of nog steeds beschikbaar zijn nadat je de transactie hebt afgesloten, dan moet de de transactie 'committen'. Om daar ondersteuning voor te bieden, heeft SqlDB het TSQLTransaction-component. Een SQL-commando dat door SqlDB wordt uitgevoerd, moet altijd binnen een transactie uitgevoerd worden. Zelfs als de database-server geen transacties ondersteund. Er zijn ook typen database-servers die wel transacties ondersteunen, maar waarvan de TSQLConnection (nog) geen transacties ondersteund. Maar ook dan moet een TSQLTransaction-component gebruikt worden.

Wil je dus met TSQLConnection.ExecuteDirect een SQL-commando kunnen uitvoeren, moet je voor de TSQLConnection eerst opgeven welke 'Transaction' hij moet gebruiken. Voor de TSQLTransaction op zijn beurt, moet je opgeven welke TSQLConnection gebruikt moet worden.

Het volgende voorbeeld maakt een tabel 'TBLNAMES' met de velden 'NAME' en 'ID' aan, en voegt twee records toe. Er worden twee SQL-commando's gebruikt die ik hier verder niet uitleg. Voor meer informatie over SQL-queries kan je de documentatie van de database-server raadplegen. De procedure 'CreateConnection' staat hierboven gedefinieerd.

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.

Hoe lees ik data in uit een tabel?

Om data uit een tabel in te lezen maak je gebruik van een TSQLQuery component. Een TSQLQuery moet verbonden worden met een TSQLConnection en een TSQLTransaction om te kunnen werken. Het instellen van de TSQLConnection en TSQLTransaction worden hier en hier besproken.

Als de TSQLConnection, TSQLQuery en TSQLTransaction met elkaar zijn verbonden, moet TSQLQuery nog worden ingesteld. TSQLQuery heeft een 'SQL'-property die een TStrings-object bevat met daarin een SQL-statement dat uitgevoerd moet worden. Als alle gegevens uit een tabel ingelezen moeten worden, stel de query dan in op 'select * from tablename;'. Met 'open' worden de gegevens in de tabel uitgelezen van de server en in de TSQLQuery opgeslagen. De gegevens zijn nu via TSQLQuery te benaderen totdat de query met 'close' weer wordt afgesloten. TSQLQuery is een afgeleide van TDataset en een TDataset heeft een 'Fields' collectie, waarin alle kolommen van de tabel staan. En een TDataset houdt het huidige record bij. Met 'First', 'Next', 'Prior' en 'Last' kan het huidige record gewijzigd worden. 'Eof' en 'Bof' geven aan of het eerste of laatste record bereikt zijn. Om de waarde van een veld in het huidige record op te vragen, moet je eerst het juiste 'TField'-object zoeken en dan de 'AsString', 'AsInteger',... property uitlezen.

Hieronder staat een voorbeeld dat van een tabel zoals die hierboven gemaakt is alle waarden weergeeft.

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.

(De code hierboven is natuurlijk niet helemaal netjes, zo missen er 'try..finally' blokken, maar daar gaat het hier niet om, dus heb ik ze weggelaten.) Als je goed oplet zie je dat ik hier geen gebruik maak van 'TSQLTransaction.StartTransaction'. Dat is ook niet nodig. Als een TSQLQuery uitgevoerd wordt terwijl er geen transactie gestart is, dan wordt er automatisch een transactie gestart, dat hoeft de programmeur niet expliciet te doen. Hetzelfde geld trouwens voor de verbinding van een TSQLConnection. Die wordt ook automatisch geopend als dat nodig is. De regel 'AConnection.Open;' zoals die hierboven staat is dut niet nodig. Als een TSQLTransaction wordt vernietigd, wordt eerst automatisch een 'rollback' uitgevoerd. Eventuele wijzigingen aan de data in die transactie worden dan dus teniet gedaan!

Waarom geeft TSQLQuery.RecordCount altijd 10 terug?

Als je wilt weten hoeveel records er in een dataset zijn, kan je .RecordCount gebruiken. Maar houd er rekening mee dat .RecordCount het aantal records laat zien dat al geladen is van de server. SqlDB leest bij het openen van een TSQLQuery standaard niet alle records in, maar alleen de eerste 10. Pas als er geprobeerd word om naar het 11e record te gaan worden de volgende 10 records geladen enzovoorts. Als .Last wordt aangeroepen worden alle records ingelezen.

Wil je dus weten hoeveel records er echt op de server staan, kan je eerst .last aanroepen en daarna .recordcount uitlezen. Er is echter ook een andere mogelijkheid. Het aantal records dat per keer wordt opgehaald van de server is in te stellen met .Packetrecords. (standaar dus 10) Als hier -1 wordt ingevuld wordt de dataset altijd in een keer ingelezen.

Lazarus

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.

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.

Hoe wijzig ik gegevens in een tabel?

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.

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

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.

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.

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.

Voor een uitleg van UpdateMode, lees hieronder verder.

Hoe stuurt SqlDB de wijzigingen naar de Database-server?

In het voorbeeld voor het wijzigen van gegevens in een tabel, staat de regel

  Query.UpdateMode := upWhereAll;

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:

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:

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 [1] zijn, maar in sommige gevallen kan het de code ook een stuk vergemakkelijken.