I have a MYSQL table that contains two columns; IP, varchar(30) and CONAME, varchar(50). MYSQL is running in an Apache server from a remote location.

The PHP script, so far, returns the following JSON:-

[{"ip":"202.188.71.26","coname":"LEGEND OF THE CONDOR HEROES"}]

I believe I am suppose to prefix the above with {"rows": and suffix with } and have another JSON with the column information.

What are the steps in EWB to call the PHP script and load the JSON values into a dataset so that I can examine the returned data?

I am familiar with using the Database Manager in EWB to load DBISAM tables but I am totally lost with calling the PHP script despite reading the manual many times.

--
Frederick

>>Frederick Chin wrote:

>>I have a MYSQL table that contains two columns; IP, varchar(30) and CONAME, varchar(50). MYSQL is >>running in an Apache server from a remote location.

>>The PHP script, so far, returns the following JSON:-

>>[{"ip":"202.188.71.26","coname":"LEGEND OF THE CONDOR HEROES"}]

>>I believe I am suppose to prefix the above with {"rows": and suffix with } and have another JSON with the >>column information.

>>What are the steps in EWB to call the PHP script and load the JSON values into a dataset so that I can >>examine the returned data?

Frederick, I can tell you what I'm doing. It's not perfect but it does work.


When I'm getting my data rows in php, I do the following:

$rows = array();
 if(mysqli_num_rows($result)) {
   while($row = mysqli_fetch_assoc($result)) {
     $rows[] = array($row);
   }
 }

For me, my php script is called service.php

Hopefully that makes sense.

Then I output the values in JSON format:

header('Content-type: application/json');

   echo json_encode(array('rows'=>$rows));


On the EWB side, this is what I'm doing. In my case the service.php script is local on my server.

var
 
  MyRequest: TServerRequest;

Then you have to execute the TServerRequest which will run the php code

  ShowProgress('Getting Data');
  MyRequest:=TServerRequest.Create(nil);

  MyRequest.URL:='service.php';
  MyRequest.Params.Add('xxx=xxxx')
     MyRequest.OnComplete:=RequestComplete;
  MyRequest.Execute;

Add any parameters you need via the Param.add, like username, password, parameters

Once the request is complete the OnComplete will fire so in my  RequestComplete I have

procedure TFormLogin.RequestComplete(Request: TServerRequest);
var
  strWork: string;
begin
  HideProgress;
  if (Request.StatusCode=HTTP_OK) then
  begin
  strWork := Request.ResponseContent.Text;
  strWork := strReplace(strWork,'[[{','[{');
  strWork := strReplace(strWork,'}]]','}]');
  DSVerifyLoginJSON := strWork;
       DSVerifyLogin.Open;
     DSVerifyLogin.LoadRows(DSVerifyLoginJSON);

  
  end
  else
     ShowMessage('Response Error: '+Request.StatusText);

   
end;

My PHP script is goofing up the data a little bit at the top and bottom of the JSON, and I'm not sure how to fix the php code, so I just strip it out here. If you figure out how to fix the php code so the extra brace is not put in there please let me know so I can not have to do the replace statements.

I have previously dropped and created the DSVerifyLogin which is a dataset with the columns setup that I am returning.

Once the loadRows starts your app will continue to run. You then add  any code you need to execute after the data is loaded into the DataSet. I bind my form fields to the dataset and that works.


Hopefully that helps and doesn't confuse you.

Big Al

Frederick,

<< What are the steps in EWB to call the PHP script and load the JSON values into a dataset so that I can examine the returned data? >>

Your PHP script needs to handle this API:

https://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=API_Reference

and the JSON that it consumes/returns needs to conform to this spec:

https://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=JSON_Reference

You can download some PHP scripts that show how to handle the API portion here:

https://www.elevatesoft.com/forums?action=view&category=ewb&id=ewb_binaries&page=1&msg=45#45

(these are not entirely complete, and are not supported)

Tim Young
Elevate Software
www.elevatesoft.com

Big Al wrote:

/*
  MyRequest.URL:='service.php';
*/

My PHP script is stored in F:\MYPRG\XAMPP\HTDOCS\GSTA17. With Apache running, the PHP script returns the correct JSON values in Chrome. However, when I enter localhost/gsta17/getgstaw.php in the URL property above, I get a 404 error from the internal EWB server. Is it not possible to call the PHP script from another location except the OUTPUT directory?

/*
  strWork := Request.ResponseContent.Text;
*/

When I do a showmessage(strWork), the entire contents of the PHP script is shown instead of the JSON return data.

/*
  DSVerifyLoginJSON := strWork;
*/

Is DSVerifyLoginJSON another TDataset with the same structure as DSVerifyLogin? Why is another dataset required to store the returned JSON value before it is loaded to the actual dataset?

--
Frederick

Tim,

/*
Your PHP script needs to handle this API:

https://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=API_Reference

and the JSON that it consumes/returns needs to conform to this spec:

https://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=JSON_Reference
*/

I read both the above sections multiple times but I am still at a loss as to how to get the data from MYSQL.

/*
You can download some PHP scripts that show how to handle the API portion here:

https://www.elevatesoft.com/forums?action=view&category=ewb&id=ewb_binaries&page=1&msg=45#45

(these are not entirely complete, and are not supported)
*/

I will go through the examples to see if I can decipher the steps. I suppose for my purpose, I should be looking at the mysqldatabase.php example?

--
Frederick

Frederick,

<< I will go through the examples to see if I can decipher the steps. I suppose for my purpose, I should be looking at the mysqldatabase.php example? >>

You can look at it for hints, but don't try to complete it.  The mysqli PHP library wasn't robust enough to handle everything generically without serious contortions, so you'll want to simply use what you can and just hard-code anything else.  IOW, just manually write out the JSON where necessary and don't try to go at it in a generic fashion.  Just look at the columnLength method (mysqldatabase.php) to understand why - you can't even get a proper column length for string columns without resorting to hacks.

Tim Young
Elevate Software
www.elevatesoft.com

Frederick Chin wrote:

Big Al wrote:

/*
  MyRequest.URL:='service.php';
*/

>>My PHP script is stored in F:\MYPRG\XAMPP\HTDOCS\GSTA17. With Apache running, the PHP script >>returns the correct JSON values in Chrome. However, when I enter localhost/gsta17/getgstaw.php in the URL >>property above, I get a 404 error from the internal EWB server. Is it not possible to call the PHP script from >>another location except the OUTPUT directory?

I am not using the EWB web server at this point. I am using xampp on my local pc, and apache on my server. so my service.php file is located in the root of my site, in xamppl or apache

>>/*
>>   strWork := Request.ResponseContent.Text;
>>*/

>>When I do a showmessage(strWork), the entire contents of the PHP script is shown instead of the JSON >>return data.

That sounds like your web server is not setup to run a php script

>>/*
>>   DSVerifyLoginJSON := strWork;
>>*/

>>Is DSVerifyLoginJSON another TDataset with the same structure as DSVerifyLogin? Why is another dataset >>required to store the returned JSON value before it is loaded to the actual dataset?

I probably mispoke. DSVerifyLoginJSON is just a string to hold the JSON data until I can load it into the dataset.

Big Al

Tim,

My PHP script has the following code:-

   header('Content-type: application/json');
   echo json_encode($rows);

In Chrome, under the console tab, I see:

Resource interpreted as Document but transferred with MIME type application/json: "http://localhost/gsta17/getgstaw.php?domain=tsw.carat.my".

and in EWB's internal server, I issue the ShowMessage(Request.ResponseContentType) and get:

application/octet-stream

and Showmessage(Request.ResponseContent.Text) gives me the entire content of the PHP script.

I was expecting "application/json for the Request.ResponseContentType and just the JSON data as tested externally using the Chrome browser. What am I doing wrong here?

--
Frederick

After spending a bit of time checking, I have found out that the internal EWB server does not support the "Content-type: application/json" header sent by the PHP script. The internal server keeps returning "Content-type: application/octet-stream".

When I copied the EWB application to a web site using Apache server and ran it, the Request.ResponseContentType returned the intended content type and Request.ResponseContent returned the correct JSON data.

How do I configure the internal EWB server to respond to "application/json" ?

The second problem is to load the rows for the JSON data into my dataset (which I created manually by dropping the TDataset into a TDatabase and adding columns), I have to open it first before calling LoadRows.

However, the following code results in failure.

dbaData.dtsIpadd.Open; // Fails here
dbaData.dtsIpadd.LoadRows(JSONData);

The manual suggests using dbaData.LoadRows(dtsIpadd) but I believe this will fail because dtsIpadd does not have a source.

--
Frederick

Frederick Chin wrote:

> After spending a bit of time checking, I have found ...

Can you be a bit more precise in this please? I don't understand why your PHP is doing anything with the EWB server at all. EWB doesn't run PHP, and PHP would presume a different server. I'm confused.

And the "fails here" is not clear. What is the exact error? In the F12 Chrome debugging, you can trap the error and see the contents of the various variables. I suspect that it hasn't been created if it is null.

--

Matthew Jones