Recent

Author Topic: I can't make a Database project work  (Read 5033 times)

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
I can't make a Database project work
« on: October 06, 2014, 02:03:09 pm »
I 'm trying to work with databases.
I 've made a simple project, a form with 5 controls:   
  SQLite3Connection
  SQLTransaction
  SQLQuery1
  DataSource
  DBGrid1
But it gives me an error (exception class 'EDatabase error' with message: Database not assigned) and shows no record in the grid (the database has 1 record).
What am I doing wrong?

This is the code of the form:
Code: [Select]
unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqlite3conn, sqldb, db, BufDataset, memds, SdfData, dbf,
  FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, DbCtrls, DBGrids;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    DataSource: TDataSource;
    DBEdit1: TDBEdit;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    DBText2: TDBText;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    SQLite3Connection: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction: TSQLTransaction;
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure Label1Click(Sender: TObject);
    procedure StartDatabase;
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation


{$R *.lfm}

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  //SQLiteLibraryName:='/usr/lib/libsqlite3.so.0';
  StartDatabase;
end;



procedure TForm1.StartDatabase;

var createTables:boolean;

begin

  {$IFDEF UNIX}  // Linux

    {$IFNDEF DARWIN}

      SQLiteLibraryName := '/usr/lib/libsqlite3.so.0';

    {$ENDIF}

  {$ENDIF}



  {$IFDEF WINDOWS} // Windows

  SQLiteLibraryName := 'sqlite3.dll';

  {$ENDIF}



  SQLite3Connection.DatabaseName:= 'mydatabase.db';
                                  //         ShowMessage(GetAppConfigDir(false));


  if not FileExists('mydatabase.db') then  // Check if config directory exists





  createTables := not FileExists(SQLite3Connection.DatabaseName); // no file = create new tables

  //FileCreateUTF8('mydatabase.db');                 // if not: create it

  SQLite3Connection.Open;

  SQLTransaction.Active:=true;

                                        //ShowMessage('createTables=' + createTables.ToString;

  if createTables then
    begin
      SQLite3Connection.ExecuteDirect('CREATE TABLE "customers"('+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "category" DateTime,'+
                    ' "selerid" Numeric,'+
                    ' "name" Text,'+
                    ' "shopid" Numeric,'+
                    ' "phone1" Numeric,'+
                    ' "phone2" Numeric,'+
                    ' "NoSoutien" Numeric,'+
                    ' "NoSShorts" Numeric,'+
                    ' "email" Text,'+
                    ' "comments" Text);');
      SQLite3Connection.ExecuteDirect('CREATE INDEX "customers_name_idx" ON "customers"( "name" );');
      SQLite3Connection.ExecuteDirect('CREATE UNIQUE INDEX "customers_id_idx" ON "customers"( "id" );');
      SQLTransaction.Commit;
    end;

  //SQLQuery1.SQL.Text:= 'select * from customers';

  //SQLTransaction.Active:= True;



  SQLite3Connection.DatabaseName:= 'mydatabase.db';
  SQLite3Connection.Connected:= True;
  SQLTransaction.DataBase:= SQLite3Connection;
  SQLQuery1.DataBase:= SQLite3Connection;
  SQLQuery1.SQL.Text:= 'select * from customers';
  SQLQuery1.Open;
  DataSource.DataSet:=SQLQuery1;
  DBGrid1.DataSource:=DataSource;
  DBGrid1.AutoFillColumns:=true;
end;

procedure TForm1.Label1Click(Sender: TObject);
begin

end;

procedure TForm1.DataSourceDataChange(Sender: TObject; Field: TField);
begin

end;

end.
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: I can't make a Database project work
« Reply #1 on: October 06, 2014, 02:24:35 pm »
I'm unfamiliar with SQLLite, so am really only just asking questions that I hope others will find useful.  I admit, the bulk of your code looks good to me...

After you've run the above, does the file mydatabase.db exist?  (I'm wondering if the create part is failing)

The only other thing I can think is your sequence of calls.  I would make the following change...
Code: [Select]
  SQLite3Connection.DatabaseName:= 'mydatabase.db';
  //  SQLite3Connection.Connected:= True;   // <---   Move this line
  SQLTransaction.DataBase:= SQLite3Connection;
  SQLQuery1.DataBase:= SQLite3Connection;
  DataSource.DataSet:=SQLQuery1;
  DBGrid1.DataSource:=DataSource;
  DBGrid1.AutoFillColumns:=true;
  SQLite3Connection.Connected:= True;  //  <--- to here

  //And move these two lines to the end
  SQLQuery1.SQL.Text:= 'select * from customers';
  SQLQuery1.Open;

The changes above are minor only.  Moving your SQLQuery1.SQL.Text/SQLQuery1.Open certainly won't make a difference, but the .Connected := True might. 

I'm wondering if perhaps one of the subsequent configuration steps in your original code might close the connection, so I've moved the .Connected := True so it's after all the configuration...
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: I can't make a Database project work
« Reply #2 on: October 06, 2014, 03:41:49 pm »
After you've run the above, does the file mydatabase.db exist?  (I'm wondering if the create part is failing)
The creation of the database works fine, the mydatabase.db gets created.

I tried the changes you suggest but nothing changed.
« Last Edit: October 06, 2014, 03:48:04 pm by panoss »
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: I can't make a Database project work
« Reply #3 on: October 06, 2014, 03:50:26 pm »
In which case we need to play around with your subsequent code.  I note you're calling Calling

Code: [Select]
SQLite3Connection.Open;

  SQLTransaction.Active:=true;

and then later the SQLiteConnection.Connected := True.  I think that SQLiteConnection.Open and SQLiteConnection.Connected := True; probably achieve the same thing.

Which line is the Exception raised on (set a breakpoint, and step through the code)?  Did that line change with the re-ordering of code?

UPDATE:  I also note a discrepancy between the code you posted and the image you posted.  No Labels or DBNavigator on the image you posted.  Not sure how that would be relevant, but just making sure the code you posted is indeed the one you're stepping through...
« Last Edit: October 06, 2014, 03:54:07 pm by Mike.Cornflake »
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: I can't make a Database project work
« Reply #4 on: October 06, 2014, 04:44:12 pm »
The error happens on the line:  SQLTransaction.Active:=true;
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: I can't make a Database project work
« Reply #5 on: October 06, 2014, 04:49:28 pm »
Well, that's before your creation code, so although the file exists I doubt it's correctly populated.  Might be worth deleting it, you never know if it's got corrupted during testing.

Try replacing that line with SQLTransaction.StartTransaction (or it might be SQLTransaction.Start; - sorry I'm away from Lazarus ATM).  I've never seen someone open a Tranaction by setting Active:=True;.

In about 3 hours I'll be in front of Lazarus - I'll try your code then myself and see how I go...
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: I can't make a Database project work
« Reply #6 on: October 06, 2014, 04:51:34 pm »
Try replacing that line with SQLTransaction.StartTransaction
I've never seen someone open a Tranaction by setting Active:=True;.
... but it does work even though StartTransaction IMO is much clearer...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: I can't make a Database project work
« Reply #7 on: October 06, 2014, 04:52:51 pm »
I removed the code that creates the database (and opens connection with db etc) AND IT WORKS!!
Thanks a lot Mike! You really helped me to find it!
(thanks to all of course)
It works! It took me only ( :o) 2 days!!
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: I can't make a Database project work
« Reply #8 on: October 06, 2014, 05:03:39 pm »
:-)  Glad you solved it, but you did it on your own.  I honestly had no idea...
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: I can't make a Database project work
« Reply #9 on: October 06, 2014, 05:15:50 pm »
You gave me the idea to search the error in the code execution of the form.
I thought it was raised before the execution of the code started! That's why I didn't look at it earlier.
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: I can't make a Database project work
« Reply #10 on: October 06, 2014, 05:20:38 pm »
Try replacing that line with SQLTransaction.StartTransaction
I've never seen someone open a Tranaction by setting Active:=True;.
... but it does work even though StartTransaction IMO is much clearer...

Many thanks for this info.
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: I can't make a Database project work
« Reply #11 on: October 07, 2014, 08:59:30 am »
:)

TBH, I've just replaced some .Active:=true calls with .StartTransaction in code that I wrote when I started out with Lazarus ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018