Database Error with TSqlite3Dataset creating a Table
lazarus / 16-Databases

----------------------------------------------------------------------
OzeCode · 2013-07-31 21:35

I am creating a database using TSqlite3Dataset. On both 64 and 32 bit
compiles I receive an error:

exception class 'EDatabaseError' with message:
SQLITE_ERROR - index AFrm_idx already exists

AT address 647EA7

The error appears when trying to open the MForm Table.
If I run the program a second time it starts correct without any error
message.

What have I out of place to cause this error to be displayed?

Any help appreciated.

Code: [Select]procedure TForm1.FormActivate(Sender: TObject);
begin
  DoDbaseLoc;

  DoTable;

  MForm.Open;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction:
TCloseAction);
begin
  MForm.Close;
end;

procedure TForm1.DoDbaseLoc;
begin
  ForceDirectoriesUTF8(GetAppConfigDirUTF8(False));
  DbLoc:=IncludeTrailingPathDelimiter(GetAppConfigDirUTF8(False)) +
'datas.db';
end;

procedure TForm1.DoTable;
begin
  MForm.FileName:=DbLoc;

  with MForm do
  begin
    if not TableExists then
    begin
      with FieldDefs do
      begin
        Clear;
        Add('FrmID', ftAutoInc);
        Add('HorseName', ftString, 21);
        Add('BigDate', ftLargeInt);
        Add('RNO', ftInteger);
        Add('RDate', ftString, 12);
        Add('DayWk', ftString, 4);
        Add('Fin', ftInteger);
        Add('DLS', ftInteger);
      end;   // with FieldDefs do
      CreateTable;
      // make index
      SQL:='CREATE UNIQUE INDEX AFrm_idx ON AForm(HorseName, BigDate
DESC, RNO)';
      ExecSQL;
    end;
  end;   // with MForm do
end;

Code attached.

----------------------------------------------------------------------
Mike.Cornflake · 2013-07-31 22:10

FormActivate is not a great place to put creation code, it called
whenever your form gets focus, so I'm *guessing* your DoTable is being
called twice.  Sometimes you need to do it though.  When I do I use
the following logic
Code: [Select]...
   Protected
     FActivated : Boolean;

...

TForm1.FormCreate(Sender : TObject);
Begin
  FActivated := False;
End;

TForm1.FormActivate(Sender:TObject);
Begin
  if Not FActivated Then
  Begin
    // Insert Creation/Initialisation code here
   
    FActivated := True;
  End;
End;
Not sure if this will solve your problem as you already have your
logic wrapped in a "If not TableCreated", but give it a go...

----------------------------------------------------------------------
BigChimp · 2013-07-31 22:22

A bit OT but why not use the sqlite driver included by default in
SQLDB? This allows you to switch more easily to other dbs such as
Firebird, PostgreSQL, MySQL
http://wiki.lazarus.freepascal.org/sqlite#Built-in_SQLDB

----------------------------------------------------------------------
OzeCode · 2013-07-31 22:34

Thanks Cornflake. Have also tried working from FormCreate but same
issue. Will look at your code.

BigChimp: The Sqlite3Connection Component is not on the SQLDB tab
(64bit) or I could use it instead of the Dataset.

I have the Sqlite3Connection component on the 32bit Lazarus though!

----------------------------------------------------------------------
BigChimp · 2013-07-31 22:41

Quote from: OzeCode on August 01, 2013, 12:34:00 amBigChimp: The
Sqlite3Connection Component is not on the SQLDB tab (64bit) or I could
use it instead of the Dataset.
Mmm - that's a valid reason  I never really use 64 bit Laz for db
development. I suppose enabling it for 64 bit may be a good idea...
Looks like it's enabled in FPC trunk; so I'll have a look if I can
shoot in a patch for Lazarus..

Could you confirm you have
$(fpcdirectory)\units\x86_64-win64\fcl-db\sqlite3conn.ppu
on your FPC 2.6.2?
(Hope the path is correct for a Laz 1.0.10 install)
If not we'll need a backport for the connector

----------------------------------------------------------------------
BigChimp · 2013-07-31 22:54

Looking in
$(lazarusdir)\components\sqldb\registersqldb.pas (Laz trunk version),
sqlite3connection is enabled for 64 bit builds so should be on the
tab.

Laz 1.0.10 indeed switches this connector off for win64:
http://svn.freepascal.org/svn/lazarus/tags/lazarus_1_0_10/components/s
qldb/registersqldb.pas

you could change this
Code: [Select]{$IFNDEF win64}
{$DEFINE HASMYSQL4CONNECTION}
{$DEFINE HASORACLECONNECTION}
{$DEFINE HASPQCONNECTION}
{$DEFINE HASSQLITE3CONNECTION}
{$ENDIF}

to
Code: [Select]{$IFNDEF win64}
{$DEFINE HASMYSQL4CONNECTION}
{$DEFINE HASORACLECONNECTION}
{$DEFINE HASPQCONNECTION}
{$ENDIF}
// Always use sqlite3connection, also on win64
{$DEFINE HASSQLITE3CONNECTION}

Warning: air code, not tested.

If fpc 2.6.2 does contain the 64 bit sqlite3connector, a save amd
recompile of Lazarus should be enough to get it to show.
A quick glance at
http://svn.freepascal.org/svn/fpc/tags/release_2_6_2/packages/fcl-
db/src/sqldb/
seems to indicate FPC should have the connector - hopefully you can
confirm by looking if the ppu is there as asked above.

Of course, you could also try Laz trunk+FPC trunk but that may have
other sideeffects...

----------------------------------------------------------------------
OzeCode · 2013-08-01 00:18

BigChimp:

Yes, all those files and contents were there.

Did the changes in the registersqldb.pas and
then started Lazarus and in the Tools Menu selected Build Lazarus with
Profile: Normal IDE.

Lazarus compiled and restarted.
The TSlite3Connection is NOW on the SQLdb Tab.

Thank you.

----------------------------------------------------------------------
OzeCode · 2013-08-01 00:20

Mike:

Still had the same problem with the changes you suggested.

However, with a little change I have it working. though still not
absolutely sure why other than it was the first table to be made in
the database.

Code: [Select]procedure TForm1.FormActivate(Sender: TObject);
begin
  // creates folder
  if not DirectoryExistsUTF8(GetAppConfigDirUTF8(False)) then
  begin
    ForceDirectoriesUTF8(GetAppConfigDirUTF8(False));
  end;
  // sets Database Name
  MForm.FileName:=IncludeTrailingPathDelimiter(GetAppConfigDirUTF8(Fal
se)) + 'datas.db';

  with MForm do
  begin
    if not TableExists then
    begin
      with FieldDefs do
      begin
        Clear;
        Add('FrmID', ftAutoInc);
        Add('HorseName', ftString, 21);
        Add('BigDate', ftLargeInt);
        Add('RNO', ftInteger);
        Add('RDate', ftString, 12);
        Add('DayWk', ftString, 4);
        Add('Fin', ftInteger);
        Add('DLS', ftInteger);
      end;
      // creates database and First Table
      CreateTable;
      // make index
      Open;     // Open Table b4 INDEX --->
      SQL:='CREATE UNIQUE INDEX AFrm_idx ON
AForm(HorseName,BigDate,RNO)';
      ExecSQL;
    end
    else
    begin
      MForm.Open;
    end;
  end;
end;

----------------------------------------------------------------------
BigChimp · 2013-08-01 07:28

Quote from: OzeCode on August 01, 2013, 02:18:45 amYes, all those
files and contents were there.

Did the changes in the registersqldb.pas and
then started Lazarus and in the Tools Menu selected Build Lazarus with
Profile: Normal IDE.

Lazarus compiled and restarted.
The TSlite3Connection is NOW on the SQLdb Tab.
Thanks for checking!

I've submitted a request to backport this to the next Lazarus release:
http://wiki.lazarus.freepascal.org/Lazarus_1.0_fixes_branch#Submitted_
by_others

thanks for the help.