#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    42
    Rep Power
    1

    TDBXError :- Operation Not Supported


    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.

    Code:
    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`


    Code:
        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

    Code:
        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 ?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    344
    Rep Power
    6
    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
    Code:
    SQLConnection1.Execute('Select g.name, u.label, u.description, u.image from usergroup g, userplays u where u.usergroup_id = g.id', nil, @results);
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    42
    Rep Power
    1
    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
    Code:
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    344
    Rep Power
    6
    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?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    42
    Rep Power
    1
    Code:
    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


    Code:
       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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    344
    Rep Power
    6
    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.

IMN logo majestic logo threadwatch logo seochat tools logo