Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: how to indicate which statement is executing in a procudure/function

    Hello ALL,

    USually, there are serveral statements in a user-defined SQL procedure/function. Learning which statement in a procedure/function is executing is helpful for debugging. In C/C++, we can insert printf(...) between statements.

    Can I do this in a procedure/function

    Thanks

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You can just use a select statement ie
    Code:
    select "starting proc xxx"
    This can be improved by adding a time at the front so you can see if anything is taking too long to execute. The disadvantage of having just plain select statements is that you'll need to comment them out when the code goes live and, if you suddenly find you need this info again, you'll need to uncomment everything to see the logging. The other disadvantage is that your users will see the logging also.

    Logging can be improved by adding a where clause with a global flag so you can turn debugging on or off at any time. Another improvement is to insert the log info into a log table rather than to the screen so you can read it at any time.

    You could also finish things off nicely by writing a stored procedure to do the logging for you and then have different levels of logging (info, log, warning or error) so only certain messages are stored depending on your global flag.
    Code:
    call db_log_action( in_user_id, 'log', "Re-aligned ids in database" );

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    thanks mike_bike_kite

    Code:
    Logging can be improved by adding a where clause with a global flag so you can turn debugging on or off at any time.
    THis is exactly what I want. Could you please show me how and where to add a 'where clause'.

    Thanks again.
    Last edited by cy163; 11-22-08 at 08:50.

  4. #4
    Join Date
    Apr 2007
    Posts
    130
    I tested the command
    Code:
    select "starting proc xxx"
    the command caused error "....procedure/function cannot return recordset...."

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    If you're calling the procedure via isql etc then it will work fine. However if you're calling it from a program, like PHP, then the program will wonder what it has to do with the string it's suddenly been passed and hence gives you this error.

    My suggestion is to either test the proc by hand in isql. Better still, insert the test data into a log table. You will need to select the data out of the log table to read it.

    Code:
    # create a log table
    create table MyLogTable (
       log_time        datetime,
       log_txt           varchar(200)
    );
    
    # in your procedure have statements like this
    insert MyLogTable values ( now(), "starting proc xxx" );
    
    # later when you want to see what's happened
    select * from MyLogTable order by log_time;

  6. #6
    Join Date
    Apr 2007
    Posts
    130
    mike_bike_kite thanks.

    Also, I found the info on the following link is helpful


    http://gilfster.blogspot.com/2006/03...-in-mysql.html

  7. #7
    Join Date
    Apr 2007
    Posts
    130
    mike_bike_kite thanks.

    Also, I found the info on the following link is helpful


    http://gilfster.blogspot.com/2006/03...-in-mysql.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •