Stored Procedure Help

Frunkie · #1 (**permalink**) 11-09-07, 18:27

Hey everyone.

I would like to create a stored procedure in mysql. I am using 5.0.42. The only decent reference I have been able to find so far is here but it is over my head. I am afraid that because SPs are so new to mysql that my chances of finding someone who knows how to use them are going to be slim to none. It also looks like there are others that need help and can't find what they need on mysql's site

I would be grateful if someone would please give me a hand with this.

Thanks.

mike_bike_kite · #2 (**permalink**) 11-09-07, 19:36

Procedures look like this :

Code:

drop procedure if exists db_log_action;
$

create procedure db_log_action(
        IN      in_user_id      int,
        IN      in_logType      varchar( 20 ),
        IN      in_logMsg       varchar( 200 )
)
begin
        if in_logType != 'test' and in_logMsg != 'Searching for logs' then
                insert  db_Log ( id, logType, updTime, msgTxt )
                values ( in_user_id, ifnull(in_logType,'error'),
                        now(),ifnull( in_logMsg,'EMPTY') );
        end if;
end
$

Functions work well too:

Code:

drop function if exists db_return_hello
$

create function db_return_hello(
        in_name          varchar(250) ) returns varchar(250)
begin
        declare v_str   varchar(250);

        set v_str = concat( 'Hello ', in_name );

        return v_str;
end;
$

You need to remember to alter the delimiter character from ";" to something different (here it's "$") otherwise it will try to run each command before you get to the end of the sproc so put this at the top of your file:

Code:

delimiter $

Mike

Frunkie · #3 (**permalink**) 11-10-07, 01:07

Thanks Mike. Now if I could only figure out how to use it with my code.

It looks a little harder than I had thought. I was under the impression that I could use standard sql for this.

I probably should have mentioned in my first post but it slipped my mind so I'll ask now.

What I have are about 4 inserts that need need to happen at the same time using a transaction. I am having a major problem with my php code and the transaction playing "nice".

Table A
Table B
Table C
Table D

Table A is the parent and B, C and D are the children. I need to get the surrogate from table A by way of a php function that will grab the last auto-inc. value from table A to insert FKs into tables B C and D. The problem is, when I use BEGIN to start the transaction I can't get the id value from table A because A has not yet been committed.

I believe that a sproc would help me out with this but I don't know how to implement it.

Can I use the code you provided to do this with? I know of course that it would need to be modified to suite my needs but is that code for mysql?

EDIT: Would a function help me in lieu of a sproc?

mike_bike_kite · #4 (**permalink**) 11-10-07, 06:19

Code:

delimiter $

drop procedure if exists add_my_thing;
$

create procedure add_my_thing(
        IN      in_tab_a_key       int,
        IN      in_tab_b_data      int,
        IN      in_tab_c_data      int,
        IN      in_tab_d_data      int )
begin
        declare v_id            int;

        start transaction;
             insert TABLE_A ( key ) values ( in_tab_a_key );
             set v_id = last_insert_id();

             insert TABLE_B ( data, tab_a_id ) 
             values ( in_tab_b_data,v_id  );

             insert TABLE_C ( data, tab_a_id ) 
             values ( in_tab_c_data,v_id  );

             insert TABLE_D ( data, tab_a_id ) 
             values ( in_tab_d_data,v_id  );
      commit;
end
$

call add_my_thing( 'tab A key', your data goes here );
$

In your example you're using a non standard database engine (InnoDB), transactions, FKs and finally splitting all the logic between PHP and mysql. All of these things work fine but remember it's best to always keep coding as simple as possible because it's quicker to write, has fewer bugs and generally just works.

Note: I don't have your tables etc to hand so I can't compile/test etc the code. You'll need to add your own fields from your tables to the above code. I don't have an InnoDB database up and running to check the transaction code. Transactions only work in InnoDB.

Quote:

EDIT: Would a function help me in lieu of a sproc?

No - I only put it in for completeness.
Mike

r937 · #5 (**permalink**) 11-10-07, 07:40

"non standard database engine (InnoDB)"

what's non-standard about it?

mike_bike_kite · #6 (**permalink**) 11-10-07, 08:25

I only meant "not the one that comes out the box" - InnoDB is a fine engine and it offers transactions which is what he appears to want.

I understand he's been working for quite some time on this system and doing VERY long hours - I was hoping to point out that if he simplfied his approach to these problems then he might be able get a bit more sleep.

I wasn't knocking InnoDB - I promise.

Mike

Frunkie · #7 (**permalink**) 11-10-07, 19:17

Thanks Mike. Yes, I need to have transactions because the app will mainly be used in a multi-user env.

This is the 8th month on this project and I am "cooked". The temptation to cut out all of the constraints and RI is overwhelming for sure but I'm almost done and I haven't cut any corners yet so I don't want to start now.

What I need is a shot of demerol and some clean sheets.

Internet Services

Web Development

Digital Marketing

Consumer Tech