Recursive queries with sqlite

[sandy771]

Has anyone else had a problem using recusive queries using sqlite?

I emailed Devart a couple of days ago and got told that this is supported in the later version of sqlite - but when I replied (twice) witha demo project and explained that I was using the latest version 3.8.6 I have had no reply back from them.

I can only assume from their reply that these queries are supported but for some reason I get an error.

with recursive path as (select id, from_visit from visits where visits.id=27585 union all select visits.id, visits.from_visit from path join visits on (path.from_visit = visits.id) ) select * from path;

I get an error - "near "all": syntax error

The query works fine at the sqlite command line

Cheers

[AlexP]

Hello,

We cannot reproduce the problem. You query is executed with no errors. Please try running the following code and let us know the results.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils, Uni, SQLiteUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ConnectString := 'Provider Name=SQLite;Data Source=:memory:';
    UniConnection.Connect;
    WriteLn('SQLite version: ' +  UniConnection.ClientVersion); //3.8.5
    WriteLn('UniDAC Version: ' + UniDACVersion); //5.5.12
    UniConnection.ExecSQL('create table visits(id integer, from_visit integer)');
    UniConnection.ExecSQL('create table path(id integer, from_visit integer)');
    UniConnection.ExecSQL('insert into visits values(27585, 1)');
    UniConnection.ExecSQL('insert into path values(1, 1)');
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := 'with recursive path as (select id, from_visit from visits where visits.id=27585 union all select visits.id, visits.from_visit from path join visits on (path.from_visit = visits.id) ) select * from path';
      UniQuery.Open;
      WriteLn('Record Count: ' + IntToStr(UniQuery.RecordCount)); //1
    finally
      UniQuery.Free;
    end;
  finally
    UniConnection.Free;
    readln;
  end;
end.

[sandy771]

Thanks Alex

I am away from my programming machine at the moment. Your code has reminded me that I downgraded to UniDac 5.2.7 (becuase of what seemed to be a conflict with a third party application) - would using this version affect a recursive query?

Thanks

[AlexP]

If you are working with the SQLite3.dll library, then this functionality doesn't depend on the UniDAC version (it is built in the library). This functionality is supported in the Direct mode since UniDAC 5.2.6.

[luksmolders]

Hi,

I tried the example above and got following error:
EUniError error raised, with message : near "WITH" : syntax error.

SQLite version : 3.7.2
UniDAC Version : 5.5.12

I think the version of sqlite is the problem ?

(I'm using Delphi Starter Edition XE6).

Luk

[sandy771]

Sorry about the delay replying - that worked with your example - but still fails in my test program

[AlexP]

>luksmolders
This functionality appeared in SQLite 3.8.3, You should update your library version.

>sandy771
Please send me your project (including the database file) to alexp*devart*com - and we will check this query on your sample.

[sandy771]

Thanks Alex - I sent my program twice and as I said I am using 3.8.6

The immediate need for this has now gone though

[AlexP]

Unfortunately, we received no your letter, please send it once more to support*devart*com .