Retreive SQL query results in a web app
lazarus / 38-Networking and Web Programming

----------------------------------------------------------------------
ironphil · 2023-10-01 14:55

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 · 2023-10-01 15:31

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 · 2023-10-01 18:13

I am running the queries directly in FreePascal like I would in a
graphical Lazarus application.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLite3Connection1.Open;SQLTransaction1.Active :=
true;SQLQuery1.SQL.Text:= 'SELECT Projects.Resp FROM Projects WHERE
Projects.Resp = "John"';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 · 2023-10-01 20:30

I found on the wiki that the query results can be accessed with
something like QuoteSQLQuery1.Fields[0].AsString I can replace 0 with
1, 2 and so on to access everything I requested in the query and do a
Quotewhile not SQLQuery1.EOF do begin to get all the results. I can
then send it back to the web page with a series of
QuoteAResponse.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 · 2023-10-01 20:40

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 · 2023-10-02 02:21

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses
httpsend;    // uses synapse. But you may use fphttpclient. 
  function Taq_r_6.DownloadQuery(SQLDef: TStrings; DataSet: TDataSet):
Boolean;  var   QrStream: TStringStream;   ts: string;begin   //
SQLDef.SQLText = SQL text.   // Others are params.    ts :=
SQLDef.CommaText;   QrStream := TStringStream.Create(ts); 
 QrStream.Position := 0;   try      if HttpPostBinary(URL +
'GetQuery', QrStream)   // httppostbinary is a function defined in
httpsend      then begin         try            QrStream.Position :=
0;            if Dataset <> nil               then (DataSet as
TCustomBufDataSet).LoadFromStream(QrStream);            Result :=
True;         except            QrStream.Position := 0;         end; 
   end      else Result := False;   finally      QrStream.Free; 
 end;end;  // An example of calling downloadquery.  function
Taq_r_6.GetProjectList(uid: string; DataSet: TDataSet): Boolean;var 
tss: TStringList;begin   tss := TStringList.Create; 
 tss.SkipLastLineBreak:= True;   try      tss.Append('SQLText=select *
from Get_Project_List(:UID)');    // here, sqltext     
tss.Append('uid=' + uppercase(uid));                                 
    // and parameters      Result := DownloadQuery (tss, DataSet); 
 finally      tss.Free;   end;end; 
Server side:  running SQL and sending it as a response.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  //
following procedure is defined in a web-moduleprocedure
TwmAQe.GetQuery2Request(Sender: TObject; ARequest: TRequest; 
 AResponse: TResponse; var Handled: Boolean);var   memstr:
TMemoryStream;begin   memstr:= TMemoryStream.Create; 
 aq_fb6_e.DownLoadQuery(ARequest.Contentfields, memstr); 
 MemStr.Position := 0;   AResponse.ContentStream := memstr;   
 Handled := True;end;  // and following procedure is stored in a
separate datamodule, with database definition, etc. function
Taq_fb6_e.DownLoadQuery(SQLDef: TStrings; Data: TStream): boolean;var 
  xqr: TSQLQuery;    AParam: TParam;begin    xqr :=
TSQLQuery.Create(nil);   // this is a TSQLQuery, which will connect to
a Firebird database.    xqr.DataBase := fbAQ;                  
xqr.Transaction := trAQ;    try       xqr.SQL.Text :=
SQLDef.Values['SQLText'];       For AParam in xqr.Params do         
 AParam.AsString := SQLDef.VALUES[AParam.name];        try         
xqr.Open;          xqr.SaveToStream(Data);       // the opened dataset
is saved to a stream,           xqr.Close;          Result := True;  
   except          Result := False;       end;    finally     
 trAQ.CommitRetaining;       xqr.Free;    end;end;