SqlDBHowto/nl

From Lazarus wiki
Revision as of 11:44, 13 September 2008 by Loesje (talk | contribs) (Added part about executing queries/creating tables)
Jump to navigationJump to search

Introductie

Omdat er heel veel vragen over SqlDb zijn, en zo weinig documentatie beschikbaar is, heb ik besloten toch het een en ander op te gaan schrijven. Ik doe het in het Nederlands, omdat dat me nu eenmaal veel beter afgaat. Later kunnen anderen het altijd nog naar het Engels vertalen. Vooralsnog is het een proef, ik weet niet of ik dit helemaal af ga maken. Misschien niet, en verwijder ik het binnenkort weer. Maar misschien is het wel heel nuttig. De tijd zal het leren.

Ik heb gekozen voor een soort 'howto'-opzet. Ik wil een voor een vragen beantwoorden, uitleggen hoe je iets doet. Al die vragen achter elkaar moeten wel ook een verhaal vormen, die als een soort tutorial doorlopen kan worden.

Ik zal proberen het zo te verwoorden dat het duidelijk is voor zowel Lazarus als Freepascal, maar de voorbeelden zijn voor Freepascal. (daarmee bedoel ik dat het console-applicaties zijn)

Hoe maak ik een verbinding met een database-server?

SqlDB maakt niet direct verbinding met de database server, maar maakt gebruik van de client die bij de besbetreffende database-server hoort. SqlDB stuurt de commando's naar de client-library, die vervolgens de daadwerkelijke verbinding maakt. Dat betekent dus dat deze library altijd op de computer geinstalleerd moet zijn, wil je een verbinding kunnen maken. In Windows is dat meestal een dll, in Linux een .so en voor OS/X een .dylib-bestand.

Als de client-library goed geinstalleerd is, kan je een verbinding maken met een database-server met een TSQLConnection-component. Er zijn verschillende TSQLConnection componenten voor de verschillende database-servers. Voorbeelden zijn TIBConnection om met een Firebird/Interbase client-library te verbinden. 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.

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 typen databases 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 CreateTransaction;
begin
  ATransaction := TSQLTransaction.Create;
  ATransaction.Database := AConnection;
end;

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!