Exception while working with SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
Little_Snake · 2013-08-19 09:18

Hello,

I am new to databases in Lazarus and I am working on a small database
app using SQLite.

I do not want to use the visual data controls - instead I would like
to interact with the database programatically.

When I try to activate an SQLQuery, an exception occurs and a message
box pops up with such info:

Project project1 raised exception class 'EDatabaseError' with message:
SQLite3Connection1 : near ".": syntax error

At address 549C88

The code looks like this:

Code: [Select]...
 TForm1 = class(TForm)
    Button1: TButton;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;
...

implementation

const
  dbname = 'cmbase.db'; 
...

procedure TForm1.Button1Click(Sender: TObject);
var
  path: String;
begin
  SQLQuery1.Close;

  path := ExtractFilePath(paramstr(0)) + dbname;

  SQLite3Connection1.DatabaseName := path;
  SQLite3Connection1.Open;

  SQLQuery1.SQL.Text:='SELECT * FROM class';

  SQLQuery1.Open;        // !!! This line causes exception !!!

  SQLite3Connection1.Close;
end;

I have got instances of TSQLite3Connection, TSQLQuery and
TSQLTransaction added to the form from the SQLdb tab.
The following properties are set at design time (in Object Inspector):

SQLite3Connection1.Transaction is set to SQLTransaction1
SQLQuery1.Database                  is set to SQLite3Connection1
SQLQuery1.Transaction               is set to SQLTransaction1
SQLTransaction1.Database          is set to SQLite3Connection1

The database is placed in the project folder - where the executable is
put as well - and I verified that  the computed path is correct.

All goes well untill "SQLQuery1.Open;"

I do not have any idea how to solve the problem and will appreciate
any help.


Best regards,

Little Snake

----------------------------------------------------------------------
BigChimp · 2013-08-19 10:12

Have you checked the SQL is correct - does it work in sqlite
management applications/the sqlite command line application?

What Lazarus/FPC versions are you using? What OS? See signature.

----------------------------------------------------------------------
Little_Snake · 2013-08-19 10:52

Thank you for your reply.

Quote from: BigChimp on August 19, 2013, 12:12:18 pmHave you checked
the SQL is correct - does it work in sqlite management
applications/the sqlite command line application?

Yes, the SQL works in sqllite shell (checked in Windows) and in an
sqlite manager in Linux (I do not remember which one, actually).

Quote from: BigChimp on August 19, 2013, 12:12:18 pmWhat Lazarus/FPC
versions are you using? What OS? See signature.

Right. I forgot to include this info.

The same effect appears in both the following environments:

OS: Linux (Ubuntu 10.4)fpc: 2.6.2Lazarus: 10.0.10OS: Windows 7fpc:
2.6.2Lazarus: 10.0.8
These are quite recent, especially the linux setup  so I do not think
this is about versions.

To add some more info:
The SQLite3Connection1 does not report any problem while opening
connection and, when asked, happily reports
SQLite3Connection1.Connected as true.

Thank you again for your post.

Best regards,
L.S

----------------------------------------------------------------------
BigChimp · 2013-08-19 11:55

No idea what's wrong based on this info, sorry.

You could go project/publish project, and attach a zip with the
resulting source and sqlite db files so we can have a look.

Alternatively, you could adapt the text-based test program at
http://wiki.lazarus.freepascal.org/Database_bug_reporting#FreePascal
and attach it together with the db.

----------------------------------------------------------------------
Little_Snake · 2013-08-19 12:34

Dear BigChimp,

Thank you for your interest and involvement.

I think this is about to be solved. I was just about to post a quick
(pilot) info.

Due to lack of other ideas, I tried to create a new (sample) database
with the command line sqlite shell and substitute it for the database
in the program.

And - surprise - no exception occurred.

I do not know why.

The original database was created with SQLiteMan under Linux.
There should be no problem with it, right?
The base was read correctly with some other sqlite manager under Linux
(I do not recall its name now) and the sqlite shell has not complained
either.

So what was the cause of the exception - I have no idea.
But apparently the base was the problem.



I am going to do some more tests and post the results.

----------------------------------------------------------------------
Little_Snake · 2013-08-19 14:21

OK.
Problem solved.

The culprit was the dot character used in the indeces' names.

( And I thought it would be fine.   )

After changing to underscore, no problem occurs.

Thank you for your time, BigChimp.

Best regards

----------------------------------------------------------------------
BigChimp · 2013-08-19 14:22

Glad it works, snake