• Latest

    Monday, November 14, 2016

    Delphi tutorial connect SQLite with ZEOS library


    For this app to create you will need "sqlite3.dll" file which you can download from : https://sqlite.org/
    or you can download file that I used in this example together with exe file:
    https://drive.google.com/open?id=0B_njK7HczCjDcjRrTlNNM0g4R2M

    Just save it in the same folder where your exe file will be, and link to it in you application like I explained in video tutorial.


    The third thing you need is installed ZeosDBO components for Delphi, from ZeosLib Development Group, and the lates source code you can find at http://www.sourceforge.net/projects/zeoslib
    /*****************************************************************************/
    Basically that are tools that you need.
    We start with creating new project , adding necesary components on the form and writting code to achieve what we want.
    In the video you can see all these steps, and in the code for showed example you can see details.

    (code style formatted by http://hilite.me/ )



    unit MainForm;
    
    interface
    
    uses
    Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
    Dialogs, StdCtrls, Grids, DBGrids, DB, ZAbstractRODataset, ZAbstractDataset,
    ZDataset, ZAbstractConnection, ZConnection;
    
    type
    TfrmMain = class(TForm)
    ZConn: TZConnection;
    ZQuery1: TZQuery;
    ZQuery2: TZQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    ComboBox1: TComboBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Edit1: TEdit;
    Label4: TLabel;
    Edit2: TEdit;
    Button3: TButton;
    Label5: TLabel;
    Edit3: TEdit;
    Label6: TLabel;
    Edit4: TEdit;
    Button4: TButton;
    Button5: TButton;
    Label7: TLabel;
    Label8: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    private
    { Private declarations }
    public
    { Public declarations }
    end;
    
    var
    frmMain: TfrmMain;
    
    implementation
    
    {$R *.dfm}
    
    
    procedure TfrmMain.Button1Click(Sender: TObject);
    begin
    ZConn.Protocol:='sqlite-3';
    ZConn.LibraryLocation:=ExtractFilePath(Application.ExeName)+'sqlite3.dll';
    if not FileExists(ZConn.LibraryLocation) then Exit;
    ZConn.Database:=ExtractFilePath(Application.ExeName)+'testdb.s3db';
    //if not FileExists(ZConn.Database) then Exit;
    ZConn.Connect;
    Label2.Caption:='testdb.s3db';
    ComboBox1.Items.Clear;
    ZConn.GetTableNames('',ComboBox1.Items);
    ComboBox1.ItemIndex:=0;
    ComboBox1.OnChange(Self);
    end;
    
    procedure TfrmMain.Button2Click(Sender: TObject);
    begin
    ZQuery1.Close;
    ZQuery1.SQL.Clear;
    ZQuery1.SQL.Add('CREATE TABLE if not exists testtbl(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,'
    +'name VARCHAR(255),surname VARCHAR(255), UNIQUE(id))') ;
    ZQuery1.ExecSQL;
    
    ComboBox1.Items.Clear;
    ZConn.GetTableNames('',ComboBox1.Items);
    ComboBox1.ItemIndex:=0;
    ComboBox1.OnChange(Self);
    end;
    
    procedure TfrmMain.Button3Click(Sender: TObject);
    begin
    if Edit1.Text<>'' then
    begin
    ZQuery1.SQL.Clear;
    ZQuery1.SQL.Add('Insert into testtbl(name,surname) values('+QuotedStr(Edit1.Text)+','+QuotedStr(Edit2.Text)+')');
    ZQuery1.ExecSQL;
    ComboBox1.OnChange(Self);
    end;
    end;
    
    procedure TfrmMain.Button4Click(Sender: TObject);
    begin
    ZQuery1.SQL.Clear;
    ZQuery1.SQL.Add('Update testtbl set name='+QuotedStr(Edit3.Text)+',surname='+QuotedStr(Edit4.Text)
    +' where id='+QuotedStr(Label8.Caption));
    ZQuery1.ExecSQL;
    ComboBox1.OnChange(Self);
    end;
    
    procedure TfrmMain.Button5Click(Sender: TObject);
    begin
    ZQuery2.Delete;
    end;
    
    procedure TfrmMain.ComboBox1Change(Sender: TObject);
    var i :Integer;
    begin
    ZQuery2.Close;
    ZQuery2.SQL.Clear;
    ZQuery2.SQL.Add('Select * from '+ComboBox1.Text);
    ZQuery2.Open;
    
    for I := 0 to DBGrid1.Columns.Count-1 do
    begin
    DBGrid1.Columns[i].Width:=100;
    end;
    end;
    
    procedure TfrmMain.DBGrid1CellClick(Column: TColumn);
    begin
    Edit3.Text:=ZQuery2.FieldByName('name').AsString;
    Edit4.Text:=ZQuery2.FieldByName('surname').AsString;
    label8.Caption:=ZQuery2.FieldByName('id').AsString;
    end;
    
    end.
    

    Fashion

    Beauty

    Travel