October 9th, 2013, 11:10 AM
-
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 ?
October 9th, 2013, 04:02 PM
-
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);
October 10th, 2013, 02:11 AM
-
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.
October 10th, 2013, 08:17 AM
-
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?
October 10th, 2013, 02:23 PM
-
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.
October 10th, 2013, 02:34 PM
-
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.