Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: NESTED SELECT not working


This question is answered.


Permlink Replies: 4 - Last Post: Mar 28, 2017 1:45 PM Last Post By: Dmitry Arefiev
Chris Nillissen

Posts: 60
Registered: 11/5/06
NESTED SELECT not working  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 23, 2017 10:39 PM
I have the following code in my Delphi 10.2 Berlin Update 2 project accessing an SQLite database:

    
        LQuery := TFDQuery.Create(nil);
        try
            LQuery.Connection := AConnection;
            LQuery.FetchOptions.CursorKind := ckForwardOnly;
            LQuery.SQL.Text := Format(
                'SELECT * FROM %s WHERE RowId IN (SELECT RowId FROM %s WHERE _SentAt="1970-01-01 00:00:00" LIMIT %d) AND Kind LIKE "%s";',
                [
                    kActivityDataTable,
                    kActivityDataTable,
                    AMarshallLimit,
                    ARecordKind
                ]
                );
            LQuery.Open;
            ...
            ...            
        finally
            LQuery.Free;
        end;


When called, the LQuery.SQL.Text ends up equalling 'SELECT * FROM ActivityData WHERE RowId IN (SELECT RowId FROM ActivityData WHERE _SentAt="1970-01-01 00:00:00" LIMIT 1500) AND Kind LIKE "%Instance";', and as soon as I open the query, I don't get any records returned.

If I use "SQLite Expert Personal" to test the query against the database the correct results are returned.
Bernd Maierhofer

Posts: 161
Registered: 9/27/99
Re: NESTED SELECT not working  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 24, 2017 12:16 AM   in response to: Chris Nillissen in response to: Chris Nillissen
Chris Nillissen wrote:

I have the following code in my Delphi 10.2 Berlin Update 2 project
accessing an SQLite database:

    
        LQuery := TAdsQuery.Create(nil);
        try
            LQuery.Connection := AConnection;
            LQuery.FetchOptions.CursorKind := ckForwardOnly;
            LQuery.SQL.Text := Format(
                'SELECT * FROM ActivityData WHERE RowId IN (SELECT
RowId FROM ActivityData WHERE _SentAt="1970-01-01 00:00:00" LIMIT
1500) AND Kind LIKE "%Instance";',                 [
kActivityDataTable,                     kActivityDataTable,
                    AMarshallLimit,
                    ARecordKind
                ]
                );
            LQuery.Open;
            ...
            ...            
        finally
            LQuery.Free;
        end;


As soon as I open the query, I don't get any records returned.

If I use "SQLite Expert Personal" to test the query against the
database the correct results are returned.

Have a look which SQL finally is issued against the DB. Maybe there is
a problem with datetime formatting.

BTW: I suppose, you know what you are doing - but the LIMIT without an
ORDER BY may give you different results over time.

HTH Bernd

--
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: NESTED SELECT not working  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 27, 2017 9:06 AM   in response to: Chris Nillissen in response to: Chris Nillissen
LQuery := TAdsQuery.Create(nil);

1) Is it your real code ? AFAIK, TAdsQuery is Advantage Database access component. It cannot work with SQLite.
2) Even if it is, then why Format call has no format specifiers, but you are providing 4 format specifier values ?

--
With best regards,
Dmitry
Chris Nillissen

Posts: 60
Registered: 11/5/06
Re: NESTED SELECT not working  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 27, 2017 3:12 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Sorry Dmitry, I did not proof read my snippet. I have updated the original post to better reflect the real code. Within our project TAdsQuery = TFDQuery.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: NESTED SELECT not working
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 28, 2017 1:45 PM   in response to: Chris Nillissen in response to: Chris Nillissen
Please read about double and single quotes:
https://sqlite.org/lang_keywords.html

--
With best regards,
Dmitry

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02