Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread script to support numerous querries for MySQL/JSON connection to EWB client..
Sat, Aug 25 2018 3:48 PMPermanent Link

R&D team @ Environment Canada

greetings fellow EWB experts!

We are looking at converting an existing EWB project from a DBISAM back-end to a MySQL backend. This is because of a corporate desire/requirement to use Linux on the back end – otherwise DBISAM is doing just fine.

This EWB front end project has about 35 different datasets, each with their own parameterized SQL.

We will be using PHP on the back-end to create the MySQL -> JSON script so that our EWB client can connect to the new DB. We have tested this mechanism, it works well, and because of the EWB design, requires ~ no changes on the client side. (kudos to the design/designer of EWB!)

As detailed in the EWB documentation, we pass the ‘method’ (‘columns’ to get table structure, ‘rows’ to retrieve data, and ‘commit’ to make changes), the ‘dataset’ (table name), and then query parameter(s).

The format of our test looks like this:
http://................./ewb_json_generator.php?method=rows&dataset=employees&EmployeeID=2

Being that we have 35 different queries to handle, we are thinking of adding another parameter on the calling method, something like ‘queryID’. Inside our script, the ‘rows’ section would have a ‘case statement’ which would specify the 35 different ways to query and return the data. We need a way to distinguish which SQL that is to be applied (using the column parameters sent along). Using the EWB server as a back-end, that was pretty easy.. we’d just type the SQL right in the editor. Using PHP.. a little more work, and certainly messier.. but quite feasible.

So or new format would look like this:
http://................./ewb_json_generator.php?method=rows&dataset=employees&queryID=31&EmployeeID=2

So, before we jump in to this task.. am I getting this straight? Or any suggestions another way, or best practice?


Regards.. Bruno
Sun, Aug 26 2018 9:10 AMPermanent Link

Ronald

Steven/Phil/Nathan/Bruno @ Environment Canada wrote:

<
So, before we jump in to this task.. am I getting this straight? Or any suggestions another way, or best practice?
>

Hi Bruno,

I hope I understand your question. I think I do it the way you plan it. In the query I specify which query needs to be executed. In fact: the name of the dataset spcifies this. In my EWB module I load the textfile with the slqquery, excute it and return the result. So I have (in your case 35 script) several txt files with the query on the server.
Besides that I check which user is logged in. Then I check what his role is and I only load the textfile if it the user is authorized (has the role). So I rename the textfiles like this: I vae a role "admin". The sqlfiles that that role can execute all start with "admin", like "admin_users.txt".  That is for security.
It works great.

Ronald
Sun, Aug 26 2018 2:45 PMPermanent Link

Bruno Larochelle

Thanks for you comments Ron

I guess we can use the 'dataset' parameter as well.. these are unique in the EWB project**, and so it is then pretty easy to do a case-statement in the PHP which specifies which SQL is to be performed.

Thanks for the tips/techniques on user/security.. our project is internal so this does not matter much, but maybe in future projects.

Regards.. Bruno

** I was mistaking 'dataset' for 'table name'.. that's why i was thinking of introducing another way to uniquely identify the query.

Bruno
Environment Canada

/////////////////////////////////


Ronald wrote:

Steven/Phil/Nathan/Bruno @ Environment Canada wrote:

<
So, before we jump in to this task.. am I getting this straight? Or any suggestions another way, or best practice?
>

Hi Bruno,

I hope I understand your question. I think I do it the way you plan it. In the query I specify which query needs to be executed. In fact: the name of the dataset spcifies this. In my EWB module I load the textfile with the slqquery, excute it and return the result. So I have (in your case 35 script) several txt files with the query on the server.
Besides that I check which user is logged in. Then I check what his role is and I only load the textfile if it the user is authorized (has the role). So I rename the textfiles like this: I vae a role "admin". The sqlfiles that that role can execute all start with "admin", like "admin_users.txt".  That is for security.
It works great.

Ronald
Mon, Aug 27 2018 4:16 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Bruno,

<< So, before we jump in to this task.. am I getting this straight? Or any suggestions another way, or best practice? >>

Yes, you've got it straight.  Even with the EWB Web Server, any parameters that aren't used by EWB are simply treated as pass-through parameters and assigned within the context of any named parameters for the query itself.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Aug 28 2018 5:23 AMPermanent Link

Matthew Jones

Steven/Phil/Nathan/Bruno @ Environment Canada wrote:

> adding another parameter on the calling method, something like ‘queryID’

Given where you are, I think you are probably following the best solution. But just for completeness, I'll suggest that one way that is common is to use a "Remote Procedure Call" style, where instead of having the client know things about the databases, you instead make requests for actions and data, and the server is responsible for doing the actions and returning the right data.

The key here is that you'd do things like "AccountCreate" and "AccountList" rather than TableAddRow(accounts) and TableListRows(accounts). This allows you to have things very much under the server control. You can do it using REST or any such thing, but it separates the client from the underlying databases, and might make the API better re-usable by other things.

--

Matthew Jones
Tue, Aug 28 2018 9:22 AMPermanent Link

R&D team @ Environment Canada

Thanks Ronald, Tim, Matthew. With your expert advice in hand, I'm confident on moving forward with this Smile

Bruno Larochelle
Meteorological Service of Canada
Environment Canada

//////////

"Matthew Jones" wrote:

Steven/Phil/Nathan/Bruno @ Environment Canada wrote:

> adding another parameter on the calling method, something like ‘queryID’

Given where you are, I think you are probably following the best solution. But just for completeness, I'll suggest that one way that is common is to use a "Remote Procedure Call" style, where instead of having the client know things about the databases, you instead make requests for actions and data, and the server is responsible for doing the actions and returning the right data.

The key here is that you'd do things like "AccountCreate" and "AccountList" rather than TableAddRow(accounts) and TableListRows(accounts). This allows you to have things very much under the server control. You can do it using REST or any such thing, but it separates the client from the underlying databases, and might make the API better re-usable by other things.

--

Matthew Jones
Image