Recent

Author Topic: Retreive SQL query results in a web app  (Read 670 times)

ironphil

  • Jr. Member
  • **
  • Posts: 56
Retreive SQL query results in a web app
« on: October 01, 2023, 04:55:28 pm »
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

  • Hero Member
  • *****
  • Posts: 5651
Re: Retreive SQL query results in a web app
« Reply #1 on: October 01, 2023, 05:31:49 pm »
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

  • Jr. Member
  • **
  • Posts: 56
Re: Retreive SQL query results in a web app
« Reply #2 on: October 01, 2023, 08:13:47 pm »
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

  • Jr. Member
  • **
  • Posts: 56
Re: Retreive SQL query results in a web app
« Reply #3 on: October 01, 2023, 10:30:41 pm »
I found on the wiki that the query results can be accessed with something like
Quote
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
Quote
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
Quote
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

  • Hero Member
  • *****
  • Posts: 5651
Re: Retreive SQL query results in a web app
« Reply #4 on: October 01, 2023, 10:40:25 pm »
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

  • Hero Member
  • *****
  • Posts: 1220
Re: Retreive SQL query results in a web app
« Reply #5 on: October 02, 2023, 04:21:27 am »
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.  

 

TinyPortal © 2005-2018