Sqlite3-Question from a beginner!
lazarus / 16-Databases

----------------------------------------------------------------------
ecitoyen · 2012-05-30 14:23

As support for DBF table will  not be upgraded...
i have to learn another table management...

A very long question... but it should be easy
for a sqlite3 user in lazarus...


My first sqlite3 database was the following
one: a very simple example
Database name: NAMEADDR
Table name:    tablename
field:
aID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
bFIRST TEXT NOT NULL
cLAST TEXT NOT NULL
dADDR1 TEXT NOT NULL
eSTREET TEXT NOT NULL
fCOUNTRY TEXT NOT NULL


1- Wiht Lazarus wich objects i have to use
Surely the SQLdb with the TSQLITE3Connection
for others...
TSQLQuery, TSQLTransaction, TSQLConnector... I don't know?

2 DataAccess???

If I don't want to use the DBGrid...

What I need is the command to
1-Connect to NAMEADDR(Database)
2-Openning The Table(tablename)
3-Count the numbers of records
4 Read sequentially all the record
and put it in a table in memory FIRST[], LAST[],ADD1[]...etc..
4-Close

Later, i want
1-Connect to NAMEADDR(Database)
2-Openning The Table(tablename)
3-Goto to the record no. 5
4-Change the value of ADDR1
5-Commit
6-Close

Later i want to
1add a new name(in a new record)
2-Commit
3-Close

and  later i want
1 delete the record no 10
2-Commit
3-Close

I not found any example for what i want to do...

Thanks a lot

----------------------------------------------------------------------
Leledumbo · 2012-05-30 17:24

These:
http://wiki.lazarus.freepascal.org/SQLite
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery
should get you going.

----------------------------------------------------------------------
ecitoyen · 2012-05-30 22:28

I do an example program to count number of records in the
cat001001 table

The program was compiled and linked... but a
the execution time... i got this error..

SQlite3Connection1: Database connect string
(DatabaseName) not filled in!

Operation cannot be performed on an active dataset
Press OK..... Cancel...

Look at this... what's going wrong

uses
  Classes, SysUtils, sqlite3conn, sqldb, db, FileUtil, Forms,
Controls,
  Graphics, Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Datasource1: TDatasource;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    Edit5: TEdit;
    Edit6: TEdit;
    Edit7: TEdit;
    Edit8: TEdit;
    Edit9: TEdit;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure SQLite3Connection1AfterConnect(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  close;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
 NBITEM: integer;
 REQ: string;
 newrec: LongInt;
 nbfic: integer;
begin
    SQLQuery1.Close;
    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Text:= 'select * from cat001001';
    SQLite3Connection1.Connected:= True;
//    SQLTransaction1.Active:= False;
    SQLQuery1.Open;
    if SQLite3Connection1.Connected = false then
      begin
        SQLite3Connection1.HostName := 'CAT001001';
        SQLite3Connection1.DatabaseName :='cat001001';
        SQLite3Connection1.Username := '';
        SQLite3Connection1.Password := '';
        // Now we've set up our connection, visually show that
        // changes are not possibly any more
      end;
    SQLQuery1.SQL.Text:= 'select * from cat001001';
    SQLite3Connection1.Connected:= True;
//    SQLTransaction1.Active:= True;
//    SQLQuery1.Open;
//    nbfic:=0;
//    SQLQuery1.First;
//    while not SQLQuery1.Eof do
//     begin
//       SQLQuery1.Next;
//       nbfic:=nbfic+1;
//     end;
     SQLQuery1.Close;
     SQLite3Connection1.Close;
     SQLQuery1.Free;
     Edit1.Text:=inttoStr(nbfic);
end;      

procedure TForm1.SQLite3Connection1AfterConnect(Sender: TObject);
begin

end;

----------------------------------------------------------------------
Leledumbo · 2012-05-30 23:44

QuoteSQlite3Connection1: Database connect string
(DatabaseName) not filled in!Isn't this clear enough? Look for a
property named DatabaseName in the object inspector and fill it with a
filename containing the database.
QuoteOperation cannot be performed on an active dataset
Press OK..... Cancel...This could happen when you try to do different
operation before the current one is finished.

You should NEVER do this:
QuoteSQLite3Connection1.Close;
     SQLQuery1.Free;Components dropped from component palette would be
(and should only be) destroyed automatically, along with the
containing form when the program ends.

----------------------------------------------------------------------
cdbc · 2012-05-31 12:01

Hi

I might have some units for you to look at, will be back a little
later, as i have to get out the door now... stay tuned...

regards benny

----------------------------------------------------------------------
ecitoyen · 2012-06-01 00:53

I made a new example for the same table...

var
  AConnection : TSQLite3Connection;
  ATransaction : TSQLTransaction;
  ADatasource: TDatasource;
  Query : TSQLQuery;
  nbitem: integer;
  nbic: integer;
begin
   nbitem:=10;
//   AConnection.Connected:=False;
   if AConnection.Connected = False then
    begin
    AConnection.HostName := 'localhost';
// is HostName = 'localhost' or 'sqlite3.dll'
    AConnection.DatabaseName :='CAT001001';
    AConnection.Username := '';
    AConnection.Password := '';
    AConnection.Transaction:=ATransaction;
    ATransaction.Active:=True;
    Query.Database:=AConnection;
    Query.SQL.Text := 'select * from CAT001001';
    Query.Open;
    nbitem:=0;
    Query.First;
    while not Query.Eof do
     begin
       Query.Next;
       nbitem:=nbitem+1;
     end;
    Query.Close;
    end;
end;

----------------------------------------------------------------------
Leledumbo · 2012-06-01 07:27

Quoteif AConnection.Connected = False thenOOT: Better start using
boolean as boolean, e.g: if not AConnection.Connected then
Quoteis HostName = 'localhost' or 'sqlite3.dll'None, it would be
ignored AFAIK since this SQLite is serverless.

Did you code everything by hand (i.e. not using the form designer/drop
the components from component palette)? If yes, then you have to
manually .Create (with proper arguments) and .Free all objects used
(AConnection, ATransaction, ADataSource and Query).

----------------------------------------------------------------------
cdbc · 2012-06-01 09:19

Hi again

Here are a couple of units, you may find useful.
The project compiles on linux, but you'll get the picture :-)
regards Benny

----------------------------------------------------------------------
ecitoyen · 2012-06-03 18:41

Merci pour les quelques references... elles m'ont ete tres utiles
et comme je veux pouvoir modifier le nom de mes tables dans mon
programme... je dois creer les commandes dans le source et nom
dans les proprietes...

Voici un exemple simple...
Maintenant cela marche bien...
voici un exemple correct:

DATABASE CAT001001
table cat001001
repertoire du programmes...
Ce qu'il fait:
Il compte le nombre de fiches
dans la table puis, par la suite
les affiches dans les lignes Edit.Text.

VOici le programme (unit.pas)
-------------------------------------
Code: [Select]unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqlite3conn, sqldb, db, FileUtil, Forms,
Controls,
  Graphics, Dialogs, StdCtrls;

type
    TStringArray = array of string;
    TIntegerArray = array of Integer;


  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Datasource1: TDatasource;
    Edit1: TEdit;
    Edit10: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    Edit5: TEdit;
    Edit6: TEdit;
    Edit7: TEdit;
    Edit8: TEdit;
    Edit9: TEdit;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
    PLATE: TStringArray;

  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

----------------------------------------------------------------------
BigChimp · 2012-06-03 19:01

Quote from: ecitoyen on June 03, 2012, 08:41:23 pmMerci pour les
quelques references... elles m'ont ete tres utiles
et comme je veux pouvoir modifier le nom de mes tables dans mon
programme... je dois creer les commandes dans le source et nom
dans les proprietes...

Voici un exemple simple...
Maintenant cela marche bien...
voici un exemple correct:
As far as I undertand, he says: Thanks for the references, they've
been very useful.
As I wanted to be able to change the name of my tables in my program,
I must create the commands in the source and the name in the
properties.
Below a simple example. Now it works well.

@ecitoyen: you might want to enclose your code in code tags (the #
button you see on top when editing posts). Makes it easier to copy and
paste, as well as to see what's going on:
Code: [Select]This is an example codeThanks for letting us know
everything works.