[SOLVED] SQLITE_ERROR - no such table:xxxxxx

[ChrsO]

Newbie here having trouble trying to set up and query a basic SQLite database on my Motorola MC3100 Barcode Scanner (running WinCE 6.0).

I've been trying to use the example found here:
http://translate.google.com.au/translate?sl=pt&tl=en&js=n&prev=_t&hl=en&ie=UTF-8&layout=2&eotf=1&u=http%3A%2F%2Fprofessorcarlos.blogspot.com.au%2Fsearch%2Flabel%2FSQLite

and also tried to emulate the example project provided here:
http://lazarus.freepascal.org/index.php?topic=16852.0

but with both examples i receive the error:

SQLITE_ERROR - no such table:xxxxxx

I created the table using MiniSQLiteViewer, and can view the tables and fields with both the Win32 version, and with the WinCE\ARM version running on the WinCE device. I have attached the SQLite file if that helps.

Is there a way I can test that I am actually connected to the database?
It has occurred to me my problem could be the 'FileName'\Path variable, and that I am not actually pointing to the file correctly. I'm not sure about WinCE directory structures.

here is my code:

Code: [Select]

unit stockscan_barcodesetup;

{$mode objfpc}{$H+}

interface

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

type

  { TfrmBarcodeSetup }

  TfrmBarcodeSetup = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Datasource1: TDatasource;
    Edit1: TEdit;
    SQLite3Connection1: TSQLite3Connection;
    Sqlite3Dataset1: TSqlite3Dataset;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    StringGrid1: TStringGrid;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  frmBarcodeSetup: TfrmBarcodeSetup;

implementation

{$R *.lfm}

{ TfrmBarcodeSetup }

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

procedure TfrmBarcodeSetup.Button2Click(Sender: TObject);
begin
  SQLiteLibraryName:='sqlite3.dll';
  sqlite3DataSet1.FileName:='stockscanDB.sqlite';
  sqlite3DataSet1.TableName:='Barcodes';
  sqlite3DataSet1.PrimaryKey:='BarcodeID';
  sqlite3DataSet1.Active:=true;

  sqlite3DataSet1.Close;
  sqlite3DataSet1.SQL := 'select * from Barcodes';
  sqlite3DataSet1.Open;
  StringGrid1.Clear;
  while not sqlite3DataSet1.EOF do
        begin
         StringGrid1.Row := StringGrid1.RowCount-1;
         StringGrid1.RowCount := StringGrid1.RowCount+1;
         StringGrid1.Cells[0, StringGrid1.Row] := sqlite3DataSet1.Fields[0].AsString;
         StringGrid1.Cells[1, StringGrid1.Row] := sqlite3DataSet1.Fields[1].AsString;
         sqlite3DataSet1.Next;
         end;
  sqlite3DataSet1.Close;
end;

end.
                               
I have also tried using a different DLL 'sqlite3.7.8-wince-arm.dll' rather than the 'sqlite3.dll', which didn't change anything.

Apologies for my ignorance and newbieness. I am trying to re-write some software using lazarus and i am not really understanding everything and on a steep learning curve. Any help \ pointers much appreciated!

I have attached my .sqlite file if that helps.

[irw10062]

i try your db,
its not work to me too,
just create new db file using sqlite expert personal designer
maybe it work

and dont use reserve word, maybe you use it

[ChrsO]

Solved:
My problem was the filename field needed to be the full directory path of the database file
 i.e. '\my documents\example.db' rather than just 'example.db'

[dago71]

Windows CE doesn't have the concept of "current folder", you always have to specify the full path, be it for SQLite, INI or whatever file you need to access.

[mirce.vladimirov]

Under the folder "\My Device" there is a database with the same name as mine, and it has 0 bytes.
The program tries to connect to a database with the specified name but it cant find one, so it creates it, but inside of this newly self-created database file there are no tables at all.
So it is true, IT IS A PATH ISSUE !
I copied my database to that folder and it finaly worked, which proves that it is path-related stuff.
But...
The path to my exe file, my db-file and the sqlite3.dll is the same path, it is '\My Device\Storage Card\distribucija\'. And in my code if i set all of the following

Code: [Select]

  SQLiteLibraryName:='\My Device\Storage Card\distribucija\sqlite3.dll';
  SQLite3Connection1.DatabaseName := '\My Device\Storage Card\distribucija\databazz';
  SQLite3Connection1.Connected := True;   
then it cannot find my database. Now, first i coment the line with the definition of SQLiteLibraryName and it complains about the non-existance of a program's component. Second, I uncomment that line but put a comment on the line with SQLite3Connection1.DatabaseName and it complains about non-existing database, literaly it is

SQLite3 connection : unable to open database file.

[mirce.vladimirov]

To make it easey for everyone :

Code: [Select]

  showmessage('The actual path of your WinCE application is:' + ExtractFilePath( Application.ExeName ));

and set the db filename to

Code: [Select]

  SQLite3Connection1.DatabaseName:=ExtractFilePath( Application.ExeName )+'nameofyourdbfile';