Problem with query with SQLite

[aducom]

I can connect to a sqlite database and use the ttable component. However if I connect a TQuery every select is generating error:

sql logic error or missing database

I can use create table with tquery though. Anybody experiencing same problem? It useually is an sqlite api issue, but I'm not sure. It's been a while for me.

I'm using the components in Lazarus by the way.

[mmvisual]

I have the same Problem with Lazarus for MAC, but in Windows, it works.

MacOS 10.4, Lazarus SVN 1.1.41557, Zeos SVN 7.1.0 2286

- Creat new App/Form
- Put TZConnection and TZReadOnlyQuery and set sqlite-3/existing File/Connection/SQL "SELECT * FROM adr"
- Open the Query >> Error

In Windows comes no error.

Edit: On my MacOS (V10.4) is a sqlite version 3.1.3 installed. Witch minimum version of sqlite require zeos?

[EgonHugeist]

mmvisual,

On my MacOS (V10.4) is a sqlite version 3.1.3 installed. Witch minimum version of sqlite require zeos?

Since V3.x it should work, i think.

So i think there happens something with the Path-variables. SQLite expects allways UTF8 Strings for the Database path's. I've no clue how i can help here.


aducom,
Which OS you're using?

[aducom]

I'm running under Windows 8. Long time ago I wrote my own Delphi components for sqlite, and I recognize this message. It has something to do with calling the api out-of-sequence. I wasn't able to debug, but I'm not sure I'm the only one with the problem.

I tested the readonly version, but this gives the same errormessage.

[mmvisual]

I have make a small test:
Lazarus V1.1.41121/32
ZEOS V7.0.3
Win8/64
New Application, put TZConnection, TZQuery, TDatasource, TDBGrid. A existing Database, SQLite and "SELECT * FROM adr", set in designer as "TZQuery.Active" an I see the Data in the designer.
It works good with Win8.

Type this in your TZQuery.SQL:
SELECT sqlite_version()
And take then "open"

[EgonHugeist]

aducom,

can you post your TZConnection settings, please?

[aducom]

Database is set to the path and name of the database. Connectiontype is sqlite or sqlite-3 (both give same error). If I use a ttable or use create table with tquery it works well, but if I use select statements I get the error. If there are fellows who do not have issues with Zeos on lazarus as mmvisual then it looks like I have some kind of mismatch. I can try to reinstall Lazarus/Zeos. But before doing that I liked to know if people have a working instance.

I will uninstall /reinstall lazarus and see if I keep the same issues. Will report here.

Albert

[EgonHugeist]

aducom,

you're welcome. Let's see what happens.

[aducom]

Ok, I uninstalled everything, including zeos. Reinstalled Lazarus. Installed Zeos. Recompiled IDE. Error persits. In the next code the database and tables are created, but as soon as the select is executed then the error comes up:

Code: Select all

        'create table if not exists phsp_pref ('+
        'sectionId varchar(32),'+
        'prefId varchar(32),'+
        'prefValue varchar(255),'+
        'primary key (sectionId, prefId))'
   ) ;
begin
  DM.DBRepos.Database :=  DEWork.Text+'\phspeed.db';
  DM.DBRepos.Connected := true;

  for i := 0 to QCount do begin
    QRepos.Close;
    QRepos.SQL.Text := Queries[i];
    QRepos.ExecSQL;
  end;

  QRepos.Close;
  QRepos.SQL.Text := 'select * from phsp_project where projectdId=:p';
  QRepos.Params[0].AsInteger :=0;
  QRepos.Open;
end;      

[aducom]

Ok, I should have tried this immediately. I just created a small project and guess what? Things are working. That means that I am doing something stupid in my project.... but realy don't know what....

However, it seems not to be a zeos problem :-)

Sorry for the trouble.

[aducom]

Ok, I need to reopen this. If you create a simple application things work. Now I have a main program and a datamodule. In the datamodule I have a db and a query object, linked together. then I have a separate module to create a database if it does not exists. In the main menu you can select this module and it is created dynamically. In this routine I create the database using create tables and execsql using the datamodule. This goes well. Then I reuse the component for a select and I get the same problem. SQL Logic error or missing database. I probabely be missing something, but I don't think I'm doing things wrong here. Do I need to reset the component somehow?

Code: Select all

DM.ZDB.Database := DEWork.Text + '\phspeed.db';
  DM.ZDB.Connected := True;

  for i := 0 to QCount do
  begin
    DM.ZQ.Close;
    DM.ZQ.SQL.Text := Queries[i];
    DM.ZQ.ExecSQL;
  end;

  DM.ZQ.Close;
  DM.ZQ.SQL.Clear;
  DM.ZQ.SQL.Add('select * from phsp_project where projectdId=:p');
  DM.ZQ.Params[0].AsInteger := 0;
  DM.ZQ.Open;  

[aducom]

This error occurs when:

Each invocation of sqlite_step returns an integer code that indicates what happened during that step. This code may be SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or SQLITE_MISUSE.

Useually things get messed up if some kind of nesting occurs. I think that the create table statement is not being released somehow.

[aducom]

I haven't been able to put the finger on it, but it looks like it's going wrong if you combine execsql where no cursor is needed and selects where a cursor is needed. This error occurs when the dll is not finalized correctly, but it's been a while since I worked with the code. In other words, couln't find the location where it's going wrong. Still working on it, but pfff, long time...

[EgonHugeist]

aducom,

As i made the SQLite CAPI prepared statments i was running into the same issue:

if a fetch isn't finalized and another statement wants to update a row on the table: sql logic error or missing database

The clue is: both statements do prepare with the same connection handle. You can execute the update if you've a second connection (i think). So my impression was: SQLite works sequential here. That's why we fetch the whole table on executing a select, just to prevent this case.

Maybe you've some more findings?

[aducom]

Using another connection does not work. I tried that too. It's because sqlite is not suitable for multiconnections afaik. I wrote my own components for sqlite a long time ago which had similar issues then. It had to do with finalizing the connection. I am not giving up, still working on it. My problem is that I've not been folowing the zeoscode for a long time and need to find the concept again.

Buit in my case I first create tables, then do a select. This is sequential by default. Still I get this error. But using a ttable seems to work (...). Now I don't get it. They are derived from the same basics?