mysql queries

[Omer]

Hello,

Is it possible to store mysql queries and then run one of the stored queries by using an action or trigger via a button?

Thank you

Omer

[shannah]

Dataface 0.6 will allow you to easily add actions to any of the menus on the page, and define handlers for those actions to display whatever you want, including mysql queries.

In the current Dataface 0.5.x you can't easily add actions to the menus, but you can add your own menu to the left column and add links to custom php pages. This post (http://framework.weblite.ca/forum/dataface-users/42) has some information on how to do that.

Hope this helps a little

Best regards

Steve

[Aoirthoir]

Regarding stored queries, does MySQL have the ability to save queries itself, or do you mean for Dataface to store the Query Omar?

Thanks

[shannah]

As far as I know, Mysql does not store queries (unless you create a stored procedure, which is a little more than just a stored query).

We would probably be talking about storing the queries in Dataface for later use.

[Omer]

Hello and thank you for your answers.

Yes, I was thinking of storing the queries within Dataface. Simple stuff for sorting a contact management database. It will be used by volunteers in a non-profit so the GUI needs to be as user friendly as possible. I'll look at your suggestion.

Again thanks.

Omer

[Aoirthoir]

In this case Mr. Omer and Mr. Steve, this relates to my idea on another topic of having a window for SQL statements to be passed from dataface to mysql, similar to how is done in phpMyAdmin. However, to take what Mr. Omer is saying, we could just add a table that would then store those values, so we could reselect them from a list. They could be stored automatically, or you could be required to store them or you could have the option to store.

So for instance to run a query to test you have a window to enter the sql. Then when you are satisified with it you could hit save to save it to the table.

This relates to what I was thinking with a dataface report writer (read your comments on the other topic Mr. Steve and I agree with them)..So that using dataface we could run things we always need to run like end of month sales reports etc...Using the tagging system that Ive been laying out plans for (not programming yet) those tags could link to stored queries. Actually the tags can link any record to any other on the fly with any description we want.

Now in regards to the stored Query...sometimes I think it is as simple as a stored find. So find could have two tabs within itself. One where you see a list of previous finds (and thus previous queries). Another where you have a form to enter your requested find.

Now the only question I would have for myself, is it more efficient (speedier) to do these queries every time in dataface, or to have dataface create stored procedures in mysql and then simply call those. Thus using dataface we could get a list of stored procedures in case we ever create our own in SQL directly... But dataface could also store in a table ITS stored procedures. So if you call one, it attempts to run it, if it gets an error that the procedure does not exist (meaning someone probably deleted it by entering direct SQL) then dataface would just recreate the procedure. If it could not recreate (say because of SQL permissions) then it would just run the query itself. Of course this business of the stored procedures is only good IF it is faster than just having dataface do the query in the first place. (I have other thoughts on that which I will comment on in another post)

[shannah]

Quoted: =========================================================
Now in regards to the stored Query...sometimes I think it is as simple as a stored find. So find could have two tabs within itself. One where you see a list of previous finds (and thus previous queries). Another where you have a form to enter your requested find.
=========================================================
Response:
This sounds like a win-win feature..

Quoted: =========================================================
Now the only question I would have for myself, is it more efficient (speedier) to do these queries every time in dataface, or to have dataface create stored procedures in mysql and then simply call those. Thus using dataface we could get a list of stored procedures in case we ever create our own in SQL directly... But dataface could also store in a table ITS stored procedures. So if you call one, it attempts to run it, if it gets an error that the procedure does not exist (meaning someone probably deleted it by entering direct SQL) then dataface would just recreate the procedure. If it could not recreate (say because of SQL permissions) then it would just run the query itself. Of course this business of the stored procedures is only good IF it is faster than just having dataface do the query in the first place. (I have other thoughts on that which I will comment on in another post)
==========================================================

This sounds like a good feature as an administrator option. I don't think it would be good default behavior to have Dataface creating stored procedures as it might make some DB Admin's upset that their database is getting mucked up with auto-created procedures.

Storing the queries in a separate table seems like it would be a safe addition.

[shannah]

I am working on a similar system in-house to handle volunteer registration for some of our events here. I am using Dataface 0.6 devel version for this, but have been able to accomodate all of the users' requests quite easily in regards to reports and ease of use.

[Aoirthoir]

Ah ok I agree with you. I mean for me, I am administering my databases myself. But if I started messing around with someone else's databases, then that might cause issues.

So by having an option, if someone wants to implement it as a stored procedure they just do it themselves. And the way to call the procedure can be stored in dataface tables. Thus if someone calls a procedure that doesnt exist, ddataface doesnt get in any quandries by trying to make them...

Now the dataface configurator..or perhaps the dataface administrators tool (in a hoped for future...)