Tim:
I am unable to hit the 'Reply' link on a thread from Sat, Aug 31 2013 7:19 AM titled: How to deploy web database application on external hosting site ( http://goo.gl/cdgzvk )

You said:
<<  I'm working on a PHP layer that  will work with EDB databases (no DBISAM directly because it doesn't have a PHP extension) along with any PDO database drivers (MySQL, etc.). >>

For clients apps with in-house servers I can install EDB and EWB works like a charm! However, most of my clients host their websites on LINUX servers with MySQL databases, as such I need to connect with that db engine also.

Seeing that it is Linux servers, the EWB's ADO would not apply.

I have gone thru just about every message in the forum having searched for 'MySQL' and quite frankly this message and those words are the ones that gave me the most hope.

Has the 'PHP layer that will work ... with any PDO database driver (MySQL etc)' available?

If not, can a SIMPLE example be provided on how to connect to a MySQL database?

I am pretty certain that with a clear walk thru most developers would be able to create their own solutions until a more streamlined solution in implemented within EWB.

thx

To be clear, when I said 'connect', I mean 'connect and work with data' - as in retrieving and posting data.

On 6/8/2015 6:51 PM, Trinione wrote:
> I have gone thru just about every message in the forum having searched for 'MySQL' and quite frankly this message and those words are the ones that gave me the most hope.
> Has the 'PHP layer that will work ... with any PDO database driver (MySQL etc)' available?
> If not, can a SIMPLE example be provided on how to connect to a MySQL database?
> I am pretty certain that with a clear walk thru most developers would be able to create their own solutions until a more streamlined solution in implemented within EWB.

Can i ask if you have a questions about anything specific or on a more
general level ?

JSON specification is in the manual (Tim is still updating it but
basically this :
http://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=DataSet_JSON_Reference).

All your PHP side has to do is return data in the proper JSON format for
queries and process incoming JSON for transactions (insert/update/delete.

Let's start with the easier part - querying for data:

1. To have PHP act like EWB web server then you need to respond to
couple of specific http queries :


1a. Columns :

http://<your host>/<your app
path>datasets?method=columns&dataset=<DataSet Name>

for example http://localhost/datasets?method=columns&dataset=Albums

1b. Actual row data :

http://<your host>/<your app path>datasets?method=rows&dataset=<DataSet
Name>

for example http://localhost/datasets?method=rows&dataset=Albums

the example is based on the on the sample that comes with EWB but
generally it can simply represent you MySQL table name.

2. in your PHP side you just to connect to MySQL  (i think these days
using either mysqli or PDO_MySQL), get the table and just iterate over
columns or rows populating the JSON response.

Most of this is covered by every single PHP/MySQL tutorial out there
(connect to MySQL and get data) so the only part that is unique to EWB
is writing it in JSON format into the response. The JSON format is very
straightforward though.

Anyways, this is getting bit long so i'll skip the transaction part
which is bit more complex but not a lot (you need to parse the incoming
JSON and then based on what's there connect top sql and execute the
changes).


Raul

Hi Raul:
Thanks for your response.

<< Can i ask if you have a questions about anything specific or on a more general level ? >>
I am asking on a general level. Sort of like a 'hello world' walk thru example.


<< All your PHP side has to do is return data in the proper JSON format for queries and process incoming JSON for transactions (insert/update/delete.
Let's start with the easier part - querying for data:
1. To have PHP act like EWB web server then you need to respond to
couple of specific http queries :
1a. Columns :
http://<your host>/<your app
path>datasets?method=columns&dataset=<DataSet Name>
for example http://localhost/datasets?method=columns&dataset=Albums
1b. Actual row data :
http://<your host>/<your app path>datasets?method=rows&dataset=<DataSet
Name>
for example http://localhost/datasets?method=rows&dataset=Albums
the example is based on the on the sample that comes with EWB but
generally it can simply represent you MySQL table name. >>

This is what is hard to follow. Where is it done? Where do the links get called? When is it assigned to the EWB dataset/components?


<< 2. in your PHP side you just to connect to MySQL  (i think these days
using either mysqli or PDO_MySQL), get the table and just iterate over
columns or rows populating the JSON response.

Most of this is covered by every single PHP/MySQL tutorial out there
(connect to MySQL and get data) so the only part that is unique to EWB
is writing it in JSON format into the response. The JSON format is very
straightforward though. >>

Yes, this is the simplest part since getting the JSON data MySQL is pretty standard.


Raul, thanks for your response. However, it is in keeping with the information provided in various parts of the forum. As such, I am no closer to understanding the how to use MySQL (on Linux) as the data source.

EWB is not a common product and I believe a step-by-step on using EWB in a public Linux/MySQL web hosting environment would open up this fine product to a much wider market.

One of the biggest challenges many face is spending an inordinate amount of time 'figuring things out'. I have found that EWB via the product and this forum has removed a lot of that from the equation.

Later on today I shall do is create a simple MySQL database and post it here and hopefully it will serve as a resource on how this can be done.

Thanks again.

On 6/9/2015 7:47 AM, Trinione wrote:
> This is what is hard to follow. Where is it done? Where do the links get called? When is it assigned to the EWB dataset/components?

Nothing changes in EWB code when you're using PHP vs EWB Web Server so
if you have your PHP code respond to same URL scheme then all the work
is done on PHP side (your EWB remains unchanged) and you switch
back-ends without EWB ever knowing what is serving it.

For example in EWB calling "Database.LoadRows(Albums)" will result in
EWB automatically generating a request with the appropriate URL (for
example http://localhost/datasets?method=rows&dataset=Albums ) so as
long as your back-end responds with properly formatted JSON EWB will
"just work".


> Raul, thanks for your response. However, it is in keeping with the information provided in various parts of the forum. As such, I am no closer to understanding the how to use MySQL (on Linux) as the data source.
> EWB is not a common product and I believe a step-by-step on using EWB in a public Linux/MySQL web hosting environment would open up this fine product to a much wider market.

I agree and believe Tim has this as one of the things he will provide
once he has time - unfortunately his time is still in short supply so
this might be a while.

Anyways i have not done PHP in years but if you do have any questions
then please post here and we'll all try do help as much as we can.

Raul

<< For example in EWB calling "Database.LoadRows(Albums)" will result in  EWB automatically generating a request with the appropriate URL (for  example http://localhost/datasets?method=rows&dataset=Albums ) so as
long as your back-end responds with properly formatted JSON EWB will "just work". >>

Ahhhh... Got it! LOL. Maybe its the words 'will just work' that made me understand it all.

Doesn't matter the source, just feed it the properly formatted JSON. Maybe place this in a datasets folder and the default index.php file to handle the parameters for 'method' and 'dataset'. So, a function call to say my albums.php file would be called by Albums function... or something like that. I will see if I can figure it out. Thanks!

Shall work with a sample MySQL database and post stuff on this thread.


<< I agree and believe Tim has this as one of the things he will provide  once he has time - unfortunately his time is still in short supply so  this might be a while. >>

I expect it is one of those things it would be great for the community to help out with. I am hoping this thread would result in something he can include in his manual. I think sufficient numbers of developers would wantI to know how to use the EWB/MySQL/Linux combination.

<< Anyways i have not done PHP in years but if you do have any questions then please post here and we'll all try do help as much as we can. >>

I find myself going back to it rather than node.js, angular and a lot of modern technologies which, quite frankly, I find add more weight and heaviness to the simpler direction web development should be heading. EWB falls squarely in the simpler and powerful direction IMO.

On 6/9/2015 9:59 AM, Trinione wrote:
> Ahhhh... Got it! LOL. Maybe its the words 'will just work' that made me understand it all.

In that case the transaction part will also "just work"

Ok - that does require bit more work but starting with query side really
helped me understand this better when we built in the json support into
our existing app/web server.

> Doesn't matter the source, just feed it the properly formatted JSON. Maybe place this in a datasets folder and the default index.php file to handle the parameters for 'method' and 'dataset'. So, a function call to say my albums.php file would be called by Albums function... or something like that. I will see if I can figure it out. Thanks!

Yes. Or you can always do url rewriting in apache so that .php is not
even required (depends on your web side design).

EWB defaults the URL base to "datasets" but if you need it be something
else then you can override this using the Database.BaseURL setting.

For example this :

   Database.BaseURL := 'datasets.php';
   Database.LoadRows(Albums);

results in this http query :

http://localhost/datasets.php?method=rows&dataset=Albums

Note that this is still relative to where you loaded your app from so if
your app was in /myapp/myapp.html then this would generate
http://localhost/myapp/datasets.php?method=rows&dataset=Albums)


However you can do this :

   Database.BaseURL := '/datasets.php';
   Database.LoadRows(Albums);

and now the query should always refer to

http://localhost/datasets.php?method=rows&dataset=Albums

so now you're basically always referring to same php script for any
database activity.

or if you're doing with the datasets folder and default index.php then
you can do Database.BaseURL := '/datasets/';

and you should end up with

http://localhost/datasets/?method=rows&dataset=Albums

Raul

<< I am unable to hit the 'Reply' link on a thread from Sat, Aug 31 2013
7:19 AM titled: How to deploy web database application on external hosting
site ( http://goo.gl/cdgzvk ) >>

It's the link earlier in the thread - it causes the browser to exceed the
width of the "page".

<< Has the 'PHP layer that will work ... with any PDO database driver (MySQL
etc)' available? >>

It's available, but only for EDB, and not for PDO drivers or MySQL (didn't
get that part finished).  You can find it in the following installation
directories (EWB 2):

\examples\phpdataset
\examples\phpmultimedia

You can find descriptions here:

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=Example_Applications

(at the bottom).

Tim Young
Elevate Software
www.elevatesoft.com

In order to understand the JSON to be used as input, I have created a file named 'products.json' which contains the json in the help file.

The file looks like:
--------------------------------------------------------------------------------------------
{ "rows": [
{ "ProductID": "9V-BATTERY-12PK",
 "Description": "12-pack of 9-volt batteries",
 "ListPrice": 20, "Shipping": 2 },
{ "ProductID": "9V-BATTERY-4PK",
 "Description": "4-pack of 9-volt batteries",
 "ListPrice": 4.5, "Shipping": 1.5 },
....... (all records from example in the manual)
--------------------------------------------------------------------------------------------

I have created a new form, dropped a dataset object on it, and am attempting to load the 'products.json' file into it with the following:

procedure TForm1.Form1Create(Sender: TObject);
begin                                 
  Database.BaseURL := 'datasets/products.json';
  Database.LoadRows(dsProducts);

//ShowMessage(IntToStr(dsProducts.Columns.Count));
//ShowMessage(IntToStr(dsProducts.RowCount));
end;
--------------------------------------------------------------------------------------------

The dsProducts.Column.Count returns '3', as expected.

However, dsProducts.Rowcount returns '0'. Apparently no rows are added, but the columns are???

What am I missing here?
//

On 6/11/2015 3:53 PM, Trinione wrote:
> In order to understand the JSON to be used as input, I have created a file named 'products.json' which contains the json in the help file.
> What am I missing here?
> //

I will take a wild guess and say you have defined one of the BeforeX
events and are not returning a true hence cancelling it.

For example do you have a handler for BeforeLoad ?
if you do then make sure you actually do "result := true;" in there or
you're cancelling the load.

How did you get the columns in - manually ?

You cannot call LoadRows without there being any columns present -
you'll get an exception saying something like "at oleast one column must
be defined".


Here's a simple way to test things :

I'm just using the built-in IDE web server but i can confirm that
loading columns and then loading rows works just fine.

My setup :
- using the IDE and built-in web server with sample datasets loaded
- one TDataset dropped on form called "ds" with DataSetName set to "Albums"

- when i call

  Database.LoadColumns(ds);

- i end up with 10 columns (ds.ColumnCount property)

and then calling

   ds.Open;
   Database.LoadRows(ds);

i end up with 10 columns and 7 data rows  (ds.RowCount property)


if you want to see the actual JSON leave the app running in IDE and use
browser to hit :

http://localhost/datasets?method=columns&dataset=Albums

http://localhost/datasets?method=rows&dataset=Albums


Raul