Retreive SQL query results in a web app

[ironphil]

I am running a small standalone web server and I run some SQL queries. I get no error message so I think my queries are executed properly but I do not see the results. How do I get the SQL query results? In a graphical application, a control is linked to the Datasource of the query and I can get the results straight into a dbgrid so I know my queries are good. But how do I access the output of a query in a web application?
Sorry if it is a basic question but I am kind of new to both web apps and databases. I am pretty sure the answer is simple but I feel I am just missing something very important so far.

[rvk]

Show us how you are running those queries.
In html/javascript?
In php?

Show that code (or tell something more about) and we might be able to help.

Without this, it's just guessing for us with what you are working exactly.

[ironphil]

I am running the queries directly in FreePascal like I would in a graphical Lazarus application.

Code: Pascal  [Select][+][-]

1. SQLite3Connection1.Open;
2. SQLTransaction1.Active := true;
3. SQLQuery1.SQL.Text:= 'SELECT Projects.Resp FROM Projects WHERE Projects.Resp = "John"';
4. SQLQuery1.Open;

The results would go into the dbgrid linked to the Datasource linked to SQLQuery1
How could I run a query in html or javascript? And integrate it into my app code?

[ironphil]

I found on the wiki that the query results can be accessed with something like

SQLQuery1.Fields[0].AsString

I can replace 0 with 1, 2 and so on to access everything I requested in the query and do a

while not SQLQuery1.EOF do begin

to get all the results. I can then send it back to the web page with a series of

AResponse.Contents.Add(SQLQuery1.Fields[0].AsString)

Is that the best way to do it? It allows me to do what I want but if there are better ways, I am ready to learn.

[rvk]

Are you creating a cgi module? For apache2 or something?
Or are you programming a webserver itself in FPC.

But yes, you can just do a loop while not eof and output the field values.

https://wiki.freepascal.org/fpWeb_Tutorial

[egsuh]

I'm assuming that you are retrieving dataset from webserver, and want to see it on a client. Otherwise, the question itself is meaningless.

Followings are what I'm doing now. Well, if I can find any better method, I'll use it.

Client side:  sending request.

Dataset here is TBufDataSet in practice.

Code: Pascal  [Select][+][-]

1. uses httpsend;    // uses synapse. But you may use fphttpclient.
2.    
3.  
4. function Taq_r_6.DownloadQuery(SQLDef: TStrings; DataSet: TDataSet): Boolean;  
5. var
6.    QrStream: TStringStream;
7.    ts: string;
8. begin
9.    // SQLDef.SQLText = SQL text.
10.    // Others are params.
11.  
12.    ts := SQLDef.CommaText;
13.    QrStream := TStringStream.Create(ts);
14.    QrStream.Position := 0;
15.    try
16.       if HttpPostBinary(URL + 'GetQuery', QrStream)   // httppostbinary is a function defined in httpsend
17.       then begin
18.          try
19.             QrStream.Position := 0;
20.             if Dataset <> nil
21.                then (DataSet as TCustomBufDataSet).LoadFromStream(QrStream);
22.             Result := True;
23.          except
24.             QrStream.Position := 0;
25.          end;
26.       end
27.       else Result := False;
28.    finally
29.       QrStream.Free;
30.    end;
31. end;
32.  
33.  
34. // An example of calling downloadquery.
35.  
36. function Taq_r_6.GetProjectList(uid: string; DataSet: TDataSet): Boolean;
37. var
38.   tss: TStringList;
39. begin
40.    tss := TStringList.Create;
41.    tss.SkipLastLineBreak:= True;
42.    try
43.       tss.Append('SQLText=select * from Get_Project_List(:UID)');    // here, sqltext
44.       tss.Append('uid=' + uppercase(uid));                                       // and parameters
45.       Result := DownloadQuery (tss, DataSet);
46.    finally
47.       tss.Free;
48.    end;
49. end;
50.  

Server side:  running SQL and sending it as a response.

Code: Pascal  [Select][+][-]

1.  
2. // following procedure is defined in a web-module
3. procedure TwmAQe.GetQuery2Request(Sender: TObject; ARequest: TRequest;
4.    AResponse: TResponse; var Handled: Boolean);
5. var
6.    memstr: TMemoryStream;
7. begin
8.    memstr:= TMemoryStream.Create;
9.    aq_fb6_e.DownLoadQuery(ARequest.Contentfields, memstr);
10.    MemStr.Position := 0;
11.    AResponse.ContentStream := memstr;  
12.    Handled := True;
13. end;
14.  
15.  
16. // and following procedure is stored in a separate datamodule, with database definition, etc.
17. function Taq_fb6_e.DownLoadQuery(SQLDef: TStrings; Data: TStream): boolean;
18. var
19.     xqr: TSQLQuery;
20.     AParam: TParam;
21. begin
22.     xqr := TSQLQuery.Create(nil);   // this is a TSQLQuery, which will connect to a Firebird database.
23.     xqr.DataBase := fbAQ;              
24.     xqr.Transaction := trAQ;
25.     try
26.        xqr.SQL.Text := SQLDef.Values['SQLText'];
27.        For AParam in xqr.Params do
28.            AParam.AsString := SQLDef.VALUES[AParam.name];
29.  
30.        try
31.           xqr.Open;
32.           xqr.SaveToStream(Data);       // the opened dataset is saved to a stream,
33.           xqr.Close;
34.           Result := True;
35.        except
36.           Result := False;
37.        end;
38.     finally
39.        trAQ.CommitRetaining;
40.        xqr.Free;
41.     end;
42. end;
43.