Menu

#344 (ok 2.11) Working with procedures and Functions

closed-accepted
1
2007-08-21
2007-02-01
No

hi,
sorry for not having been of any help for the last few years. Now without having a devaccount anymore and not having had a look at the source for a very long time, i am not sure of all the best practices to use, but neverteless i need something to find my procedures so i did a few lines...
what it does:
- creates a table showing all the defined procedures and functions of a database
- icon allows deleting of procedures and functions
- icon allows calling of procedures. (not of functions as i haven't yet found a simple way of knowing how many parameters they need.
- icon tries to show you the DEFINION

now this last thing fails badly, which is whery i'd appreciate some help:

1) i'd like to include a delimiter before the actual definition but delimiter seems to be a known problem
2) the defintion is a longtext field and the pma function seems to ignore that, and i haven't read deep enough into the source to find out how to circumvent it.

how to apply:
1) put the php-file into the libraries-directory
2) add a require to call it into db_details_structure.php. A good line in the current stable version is 500, which is just above the comment
/**
* Work on the database
* redesigned 2004-05-08 by mkkeck
*/
for example:
// table of procedures
require './libraries/db_details_procedures.inc.php';

i will also need three new strings for the column headers of the table:
$strProcedureName
$strProcedureType
$strProcedureReturnType

cheers
Mike

Discussion

  • Anonymous

    Anonymous - 2007-02-01

    new include File to handle procedures

     
  • Marc Delisle

    Marc Delisle - 2007-02-03
    • assigned_to: nobody --> lem9
     
  • Marc Delisle

    Marc Delisle - 2007-02-03

    Logged In: YES
    user_id=210714
    Originator: NO

    Hi Mike,
    thanks for contributing again! I'm having a look at your module. We will have to avoid executing the definition, I'm testing some ideas for this, stay tuned.

     
  • Marc Delisle

    Marc Delisle - 2007-02-04

    db_procedures.inc.php (show definition works)

     
  • Marc Delisle

    Marc Delisle - 2007-02-04

    Logged In: YES
    user_id=210714
    Originator: NO

    File Added: db_procedures.inc.php

     
  • Marc Delisle

    Marc Delisle - 2007-02-04

    Logged In: YES
    user_id=210714
    Originator: NO

    Mike,
    the new db_procedures.inc.php fixes the definition showing. Please try it.

    I am not completely happy with it because the user might be tempted to click Go on the page where the definition is displayed, either to try the routine or to save a modification he makes on the interface (of course, this is the ultimate goal).

    Maybe we need to fit this into a new Routines tab where a user can create a procedure but I am not sure, because currently, in the SQL tab, a user can choose his own delimiter via the Delimiter box and input his own procedure.

    From http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html , "routines" seem to be the generic term chosen by MySQL to talk about stored procedures and functions.

    Filename changed to fit into our trunk's naming since 2.10.

     
  • Anonymous

    Anonymous - 2007-02-04

    Logged In: YES
    user_id=509287
    Originator: YES

    hi Marc,
    thanks for your fast response. Seems that your version will only work with the current SVN-Version? at least i get a 404 on tbl_sql.php (and didn't find it myself in my pma-root on the version 2.9.2 i am currently using here in the office. I will put the SVN-Version on my server at home and have a go.
    Is the tbl_sql.php the reason it works for you or did i miss something else?
    I am not quite sure where to go from here myself yet - apart from agreeing that yes, ROUTINES seems to be a better word.
    But trying to use functions there doesn't make much sense. So i thought that we have this central table where i can work on the definition of and delete all kinds of routines, but then the functions should somehow be integrated into the SQL-Box. e.g. on the right where you have the list of columns of the current table there might be a second list with all functions defined for this db. Easy... but what to do with the parameters that the functions might need? First of all, as stated before, i haven't yet seen a simple way to find out what parameters are necessary, Second i am not sure how best to ask for them. Using a javascript-box when the function is chosen?
    Also i am not sure if the normal SQL-Box is the ideal place to edit stored procedures. Something more specialized would
    a) not require the user to set a different delimiter himself (if he initially creates a procedure)
    b) would not try to show him columns of one table that happens to be active
    c) instead help him with the procedure - especially when creating a new one. I am thinking of an assistant, at least with the functionality of the MySQL Administrator (that is create some template for function or procedure) but possibly we could also ask for the kind of input and return parameters and allready create those lines.
    if we go there, than we should really have a seperate tab 'routines'.
    how much would we be missing if, instead of using the existing sql-box and sql.php we would start with a clean, new file? most of the stuff that we have there might be more of a nuisance than a help i am afraid, as currently sql.php is trying to understand what kind of query it has and act accordingly, so we'd have to bypass most parts of this code anyway... on the other hand of course we would still want the syntaxcoloring...

     
  • Marc Delisle

    Marc Delisle - 2007-02-07

    Logged In: YES
    user_id=210714
    Originator: NO

    Hello Mike,
    yes, my version has been tested with the latest development version in svn (trunk). tbl_sql.php is important here, to avoid the execution of the stored procedure (this is what sql.php does). I used tbl_sql.php just as a quick hack.

    I agree that it would be best to use a specialized box for procedures editing. Syntax coloring is not a priority for procedures, IMO. And an assistant would be much work. Hmm, maybe we can use tbl_sql.php after all, sending it some parameters to avoid the list of variables.

    Let's have a basis for 2.11 that lets users define, edit, execute and delete procedures, this would a good step forward.

     
  • Marc Delisle

    Marc Delisle - 2007-02-22

    Logged In: YES
    user_id=210714
    Originator: NO

    Mike,
    did you retest with the latest dev version in svn?

     
  • Marc Delisle

    Marc Delisle - 2007-03-17
    • priority: 5 --> 1
    • summary: Working with procedures and Functions --> (ok 2.11) Working with procedures and Functions
    • status: open --> open-accepted
     
  • Marc Delisle

    Marc Delisle - 2007-03-17

    Logged In: YES
    user_id=210714
    Originator: NO

    I reworked the code to fit with our new IO functions; added DROP and CREATE in the query box; automated the delimiter box. Work in progress...

     
  • Marc Delisle

    Marc Delisle - 2007-08-21
    • status: open-accepted --> closed-accepted