Hello,
I'd like to program a service for xdata that returns the results of a very complex query (with subqueries, group by and having). For this I currently create a new TUniConnection in the service function, create and link an TUniQuery to this connection and execute then the query. After execution I free all these components again.
Is there a possibility to get the UniConnection from IDBConnection like from
TXDataOperationContext.Current.GetConnectionPool.GetConnection?
At the moment I only see that I get an IDBConnection but this interface is not able to be connected to a TUniQuery which could execute my complex query. Or is there a possibility to execute complex queries inside of XData itself?
Regards
Harald
Hi Harald, this is a way to execute custom SQL queries using IDBConnection, maybe that's what you are looking for?
Wagner R. Landgraf2018-01-17 17:38:09
Hello Wagner,
thanks again for this great support! This is exactly what I was looking for.
Regards
Harald
Hello,
there is still one more question regarding this topic:
I have a simple joined query like this
lSQL := SELECT * FROM profil p INNER JOIN profil_studium pst ON (p.id = pst.pid) WHERE pst.klasse = 241
lStatement := TXDataOperationContext.Current.GetManager.Connection.CreateStatement;
lStatement.SetSQLCommand(lSQL);
lResultSet := lStatement.ExecuteQuery;
This does not work. The errormessage tells me that there is no column "*". I have a column "id" in both tables and I do not find a way to export these columns. The following code does not work
SELECT p.id... -> not found
SELECT id... -> ambiguous
SELECT profil.id... -> unknown column
SELECT a.id... -> unknown column (I think I found something like this in aurelius documentation)
Do You have any advice for me?
Kind regards
Harald
and one more question:
If I am successfull in creating the query how do I reference this column then in the resultset?
lResultSet.GetFieldValue('XXX')
What do I have to insert for XXX?
p.id
id
profil.id
a.id
Regards
Harald
In the meantime I discovered that the errors were produced in my export
function and not in the sql. Therefore here is the full source code for
my experiments and then the final question:
I call the following function with my browser
http://localhost:2001/v1/api/studentservice/fieldsbycohort?mcid=241&mfields='p.id,pst.id'
// mFields is a string with fieldnames commadelimeted
function TStudentService.FieldsByCohort(mCid: integer; mFields: string): TStream;
begin
lStatement := TXDataOperationContext.Current.GetManager.Connection.CreateStatement;
lSQL := 'SELECT ' + mFields
+ ' FROM profil p INNER JOIN profil_studium pst ON (p.id = pst.pid)'
+ format(' WHERE pst.klasse = %d', [mCid]);
lStatement.SetSQLCommand(lSQL);
lResultSet := lStatement.ExecuteQuery;
i := 0;
while lResultSet.Next do
begin
CodeSite.SendVariant('id', lResultSet.GetFieldValue('id')); // SUCCESS: p.id
// but how can I get the pst.id?
CodeSite.SendVariant('profil.id', lResultSet.GetFieldValue('profil.id')); // ERROR
CodeSite.SendVariant('p.id', lResultSet.GetFieldValue('p.id')); // ERROR
CodeSite.SendVariant('p_id', lResultSet.GetFieldValue('p_id')); // ERROR
CodeSite.SendVariant('a.id', lResultSet.GetFieldValue('a.id')); // ERROR
CodeSite.SendVariant('a_id', lResultSet.GetFieldValue('a_id')); // ERROR
// the following lines do actually work - but how can I access the Value by name???
CodeSite.SendVariant(lResultSet.GetFieldValue(i));
inc(i);
end;
...
So the question remains - and I need it for producing a json-result:
How can I get the names AND the values of the resultset to produce a Json-Result like this?
Is there a way to reference the resultset by name?
[
{ "p.id": 1234, "pst.id": 5678 },
{ "p.id": 2345, "pst.id": 6789 },
{ "p.id": 3456, "pst.id": 7890 },
...
]
Regards
Harald
This is out of Aurelius scope. It's just SQL and your database-access component. When you perform such an SQL using TUniQuery directly, how do you access those values? It's exactly the same.
Usually it's recommended to use "as" to give fields alias:
p.id as p_id, pst.id as pst_id
Also note that what you're doing is not safe at all, as you can suffer from SQL injection.
Hello Wagner,
thank You for Your response and the advice. Maybe
You are right. ;-) It's not such a good idea to do it the way I
intended. The danger of sql injection is too big. I'm looking for
another solution.
Just to complete this issue, maybe for others
that are having a similar problem, here is the solution that I found for
the inpredictable fieldnames in the resultset:
The key is to get the fieldnames from the input in mFields.
function TStudentService.FieldsByCohort(mCid: Integer; mFields: string): TStream;
var
i: Integer;
lFields: TStringList;
lFieldname: string;
lObj: ISuperObject;
lResult: ISuperObject;
lResultSet: IDBResultSet;
lSQL: string;
lStatement: IDBStatement;
begin
lFields := TStringList.Create;
try
lFields.Delimiter := ',';
lFields.DelimitedText := mFields;
lStatement := TXDataOperationContext.Current.GetManager.Connection.CreateStatement;
lSQL := 'SELECT ' + mFields
+ ' FROM profil p INNER JOIN profil_studium pst ON (p.id = pst.pid)'
+ format(' WHERE pst.klasse = %d', [mCid]);
lStatement.SetSQLCommand(lSQL);
lResultSet := lStatement.ExecuteQuery;
lResult := SA([]);
while lResultSet.Next do
begin
lObj := SO;
for i := 0 to lFields.Count - 1 do
begin
// replace the dot in json keys - otherwise SuperObject takes it as sub-object
lFieldname := [lFields.Replace('.', '_').Trim;
// get the Value by Index and not by Fieldname - get Fieldnames from the input mFields/lFields
lObj.S[lFieldname] := lResultSet.GetFieldValue(i);
end;
lResult.AsArray.Add(lObj);
end;
Result := TStringStream.Create(lResult.AsJSon(true, false));
TXDataOperationContext.Current.Response.Headers.SetValue('content-type', 'application/json');
finally
lFields.Free;
end;
end;
Regards
Harald