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

cy163 · #1 (**permalink**) 11-22-08, 05:38

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

mike_bike_kite · #2 (**permalink**) 11-22-08, 07:17

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" );

cy163 · #3 (**permalink**) 11-22-08, 07:41

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.

cy163 · #4 (**permalink**) 12-10-08, 06:26

I tested the command

Code:

select "starting proc xxx"

the command caused error "....procedure/function cannot return recordset...."

mike_bike_kite · #5 (**permalink**) 12-10-08, 07:01

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;