SQLite connection
lazarus / 16-Databases

----------------------------------------------------------------------
oniriabot · 2012-03-06 15:43

Hello everybody,

I have a little question about using of SQlite4laz.
I want to use SQLite for database access but I want to put values of
Queries in a StringGrid.

So I put a TSQLite3Connection, a TSQLTransaction, a TSQLQuery and a
StringGrid. When I double click on the StringGrid, I execute :

procedure TFMain.StringGrid1DblClick(Sender: TObject);
var i : integer;
    texte : string;
begin
  // lecture des données
  FMain.SQLite3Connection1.DatabaseName:='D:\hild\Sources\Lazarus\Gest
ion comptes\Gestion V208 SQLite\Databases\finance_2009.s3db';
  FMain.SQLite3Connection1.Connected:=True;
  FMain.SQLTransaction1.Active:=True;
  FMain.StringGrid1.RowCount:=0;
  FMain.SQLQuery1.SQL.Clear;
  FMain.SQLQuery1.SQL.Add('SELECT * FROM operation ;');
  FMain.SQLQuery1.Open;
  FMain.SQLQuery1.First;
  While FMain.SQLQuery1.Eof=False do
    begin
      FMain.StringGrid1.RowCount:=FMain.StringGrid1.RowCount+1;
     
//texte:=FMain.SQLToDate(FMain.SQLQuery1.Fields.Fields[0].AsString);
      texte:=FMain.SQLQuery1.Fields.Fields[0].AsString;
      FMain.StringGrid1.Cells[0,FMain.StringGrid1.RowCount-1]:=texte;
      if FMain.SQLQuery1.Fields.Fields[1].AsString='1'
         then
FMain.StringGrid1.Cells[1,FMain.StringGrid1.RowCount-1]:='X'
         else
FMain.StringGrid1.Cells[1,FMain.StringGrid1.RowCount-1]:='';
      for i:=2 to 6 do
        begin
          FMain.StringGrid1.Cells[i,FMain.StringGrid1.RowCount-
1]:=FMain.SQLQuery1.Fields.Fields.AsString;
        end;
      if FMain.SQLQuery1.Fields.Fields[7].AsFloat=0
         then
FMain.StringGrid1.Cells[7,FMain.StringGrid1.RowCount-1]:=''
         else FMain.StringGrid1.Cells[7,FMain.StringGrid1.RowCount-
1]:=(FMain.SQLQuery1.Fields.Fields[7].AsString);
      if FMain.SQLQuery1.Fields.Fields[8].AsFloat=0
         then
FMain.StringGrid1.Cells[8,FMain.StringGrid1.RowCount-1]:=''
         else FMain.StringGrid1.Cells[8,FMain.StringGrid1.RowCount-
1]:=(FMain.SQLQuery1.Fields.Fields[8].AsString);
      //FMain.id.Add(FMain.SQLQuery1.Fields.Fields[9].AsString);
      for i:=0 to 8 do
          begin
           if FMain.StringGrid1.ColWidths<FMain.StringGrid1.Canvas.Tex
tWidth(FMain.StringGrid1.Cells[i,FMain.StringGrid1.RowCount-1])+10
              then FMain.StringGrid1.ColWidths:=FMain.StringGrid1.Canv
as.TextWidth(FMain.StringGrid1.Cells[i,FMain.StringGrid1.RowCount-
1])+10;
          end;
      FMain.SQLQuery1.Next;
    end;
  FMain.SQLQuery1.Close;
end;                          

The database exist with a table "operation" and this table have these
fields :
date_op, validee, mode_paiement, numero_piece, tiers, description,
categorie, credit, debit, id

When my program arrive at "FMain.SQLQuery1.Open" I have an error :
'EdatabaseError' with message : Unknown record type id.
But my database have 100 records and it have "id" record. Where I am
wrong ?

Someone have an idea ?

Thanks
Best regards
Oniriabot

----------------------------------------------------------------------
ludob · 2012-03-07 08:46

QuoteUnknown record type idHow did you define 'id'? You probably
defined it with a datatype not supported by TSQLite3Connection.

----------------------------------------------------------------------
oniriabot · 2012-03-07 11:01

You right, I create a software that use Mysql but I want to use SQLIte
with it. It works fine with MySQL but nothing works well with SQLite.
So all the table have "id" primary key. I don't understand why a very
simple software can't read my database but SQLAdmin can read it. There
is a ghost in SQLite !

----------------------------------------------------------------------
oniriabot · 2012-03-07 11:05

I define "id" to have a primary key in the database.

----------------------------------------------------------------------
Leledumbo · 2012-03-07 15:15

Try setting the datatype as auto increment. I forgot what it is in
SQLite, from code it's ftAutoInc.

----------------------------------------------------------------------
ludob · 2012-03-07 16:06

QuoteI define "id" to have a primary key in the database. But what is
the data type for id? Can you post the DDL, the create statement, used
to create the table?
Only data types that start with the following strings are supported:
INT, LARGEINT, BIGINT, WORD, SMALLINT, BOOLEAN, REAL, FLOAT, DOUBLE,
TIMESTAMP, DATETIME, DATE, TIME, CURRENCY, VARCHAR, CHAR, NUMERIC,
DECIMAL, TEXT, CLOB, BLOB, NCHAR, NVARCHAR, NCLOB, VARBINARY, BINARY

----------------------------------------------------------------------
Guillermo · 2012-09-22 22:23

These links could be useful:

website: http://sqlite4fpc.yolasite.com
(there are good and simple examples)

more documentation: http://professorcarlos.blogspot.com

the last is a brazilian blog  (en portugués), serch for sqlite4fpc.

Its a little tricky, when you define the database and declare the
index field as [primary autoincrement], it should be automatically
created as an int with auto increment. Is better to create the
database from code (examples in the site, too)

Note: be careful with dates, there is a non-standard implementation in
sqlit4fpc. See the site.

Best luck,
Guillermo.

----------------------------------------------------------------------
BigChimp · 2012-09-23 09:01

@Guillermo, thanks, but the links you pointed to document
sqlitedataset/sqlite3dataset, IIRC - those are different (again IIRC)
from the sqldb TSQL3Connection that the original poster uses...

See e.g. here:
http://wiki.lazarus.freepascal.org/SQLite