October 9th, 2013, 12:10 PM
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.
CREATE TABLE userplays (
id INTEGER PRIMARY KEY
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
name VARCHAR( 25 ) NULL
Two table are joined by `usergroup.id = userplays.usergroup_id`
Error with another query format
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'
Is it driver related error or there is issue with 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);
Exception Class TDBXError with message 'RIGHT AND FULL OUTER JOINS are not currently supported'
October 9th, 2013, 05:02 PM
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);
October 10th, 2013, 03:11 AM
Originally Posted by majlumbo
tried with @results, it is not passing any values and not performing any further operation with results DataSet.
October 10th, 2013, 09:17 AM
How and where is results declared?
Originally Posted by ninadgac
October 10th, 2013, 03:23 PM
procedure TForm3.Button1Click(Sender: TObject);
results := TDataSet.Create(nil);
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);
on E: EDatabaseError do
ShowMessage('Exception raised with message' + E.Message);
Inside ShowSelectResult procedure
With simple query like select * from userplays...it works out perfectly by showing data inside TAdvStringGrid (sg)
while not results.EOF do
for i := 0 to results.fields.Count - 1 do
showmessage('Inside for loop');
if i=0 then
sg.AddCheckBox(i, j, false, false)
sg.cells[i,j] := results.FieldByName(names[i]).AsString;
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.
October 10th, 2013, 03:34 PM
That would be my guess also, especially since simple queries do work...
Sorry I couldn't help more.