[ninadgac]

I have two table at SQLite3 database which I want to join in SQL Query and display display combined result as output. Database have two tables as shown below.

Simple queries like `Select * from userplays` etc.. execute without any problem. But with complex queries like join it gives error like no column, Operation not supported.

CREATE TABLE userplays ( 
        id           INTEGER        PRIMARY KEY
                                    NULL,
        usergroup_id INTEGER        NULL,
        label        VARCHAR( 3 )   NULL,
        description  VARCHAR( 40 )  NULL,
        image        BLOB           NULL,
        orderno      INTEGER        NULL 
    );
    
    
    CREATE TABLE usergroup ( 
        id   INTEGER        PRIMARY KEY AUTOINCREMENT
                            NOT NULL,
        name VARCHAR( 25 )  NULL 
    );

Two table are joined by `usergroup.id = userplays.usergroup_id`

    SQLConnection1.Execute('Select g.name, u.label, u.description, u.image from usergroup g, userplays u where u.usergroup_id = g.id', nil, results);
    
    Error- Exception Class TDBXError with message 'no such column u.usergroup_id' 
    Error2 - 'Operation not supported'

Error with another query format

    SQLConnection1.Execute('Select g.name, u.label, u.description, u.image from usergroup g right outer join userplays u on u.usergroup_id = g.id', nil, results);
    
    Error- 
    Exception Class TDBXError with message 'RIGHT AND FULL OUTER JOINS are not currently supported'

Is it driver related error or there is issue with Query format ?

[majlumbo]

Delphi DocWiki states:

function Execute(const SQL: UnicodeString; Params: TParams; ResultSet: TPSResult = nil): Integer;

ResultSet is a pointer to a variable of type TCustomSQLDataSet

So assuming results is Defined as a TDataSet, You should pass in the address of results not result itself

SQLConnection1.Execute('Select g.name, u.label, u.description, u.image from usergroup g, userplays u where u.usergroup_id = g.id', nil, @results);

[ninadgac]

Originally Posted by majlumbo

Delphi DocWiki states:



So assuming results is Defined as a TDataSet, You should pass in the address of results not result itself

SQLConnection1.Execute('Select g.name, u.label, u.description, u.image from usergroup g, userplays u where u.usergroup_id = g.id', nil, @results);

tried with @results, it is not passing any values and not performing any further operation with results DataSet.

[majlumbo]

Originally Posted by ninadgac

tried with @results, it is not passing any values and not performing any further operation with results DataSet.

How and where is results declared?

[ninadgac]

procedure TForm3.Button1Click(Sender: TObject);
var
    results: TDataSet;
begin
    results := TDataSet.Create(nil);
    SQLConnection1.Params.Add('Database=E:\emp.s3db');
    try
        SQLConnection1.Connected := true;
        SQLMonitor1.Active := True;

     SQLConnection1.Execute('Select g.name, u.label, u.description, u.image from usergroup g, userplays u where g.id = u.usergroup_id', nil, @results);

    except
        on E: EDatabaseError do
          ShowMessage('Exception raised with message' + E.Message);
    end;
    ShowSelectResults(results, dbadvgrid1);
end;

Inside ShowSelectResult procedure

   while not results.EOF do
    begin
       for i := 0 to results.fields.Count - 1 do
      begin
        showmessage('Inside for loop');
        if i=0 then
          sg.AddCheckBox(i, j, false, false)
        else
         sg.cells[i,j] := results.FieldByName(names[i]).AsString;
      end;
      results.Next;
      inc(j);
    end;

With simple query like select * from userplays...it works out perfectly by showing data inside TAdvStringGrid (sg)

But when used with advanced query it dont work. It gives strange error mentioned in first post with operation not supported. I am using Devart Dotconnect for SQLite trial version which has limited features I think that can be reason behind this as advanced queries not supported.

[majlumbo]

I am using Devart Dotconnect for SQLite trial version which has limited features

That would be my guess also, especially since simple queries do work...

Sorry I couldn't help more.