Stored Procedures

Check · November 20th, 2009, 8:37 PM

I know that MySQL 5.0+ supports Stored Procedures. Is there anything special I need to do to use these on Pass8?

Thanks

Check · December 1st, 2009, 3:29 PM

Guessing the lack of response means #7263 when it comes to stored procedures.

I worked around it already anyway.

**DewKnight** · December 1st, 2009, 3:38 PM

Sorry that nobody responded here. I'm glad you were able to find a way to work around this. If you have questions like this in the future, be sure to send in a ticket, we will be happy to help! 24x7 Fast, Reliable Support by Surpass Hosting

**Bigjohn** · December 2nd, 2009, 6:18 PM

Quote:

Originally Posted by DewKnight

Sorry that nobody responded here. I'm glad you were able to find a way to work around this. If you have questions like this in the future, be sure to send in a ticket, we will be happy to help! 24x7 Fast, Reliable Support by Surpass Hosting

so what's the answer? enlighten us oh wise DEW DOOOD!

**Kenneth** · December 3rd, 2009, 1:59 PM

You shouldn't have to do anything special to use the stored procedures. What issues were you experiencing? What was your work around?

**Kenneth** · December 3rd, 2009, 2:00 PM

Well, I take that back. You may need privileges added to the user to create routines, or stored procedures. I know you are going to need explicit access given to execute them as well. I remember that now

If you ever need stored procedure access, submit a ticket.

Check · December 3rd, 2009, 10:53 PM

Quote:

Originally Posted by Kenneth

You shouldn't have to do anything special to use the stored procedures. What issues were you experiencing? What was your work around?

I forget the exact error but I'm pretty sure it was permissions based. If I remember right, stored procedures aren't kept in the individual databases in MySql the way they are in MS SQL Server. I think they all get lumped in together. Once I saw that, I started looking for other ways to do what I needed.

I was intending on using a stored procedure with a temporary table to retrieve hierarchical data using the method of each row having an id and a parent_id. I was going to loop through building the hierarchy the way I normally do with MS SQL Server. I got around it by keeping an extra field to store the "lineage" of the row: "parent-parent-parent-currentID" if the item was 4 levels deep, etc.. Made my code a bit more complicated and had some limitations but will probably end up making things run faster in the long run so worked out in the end.

**Kenneth** · December 4th, 2009, 9:37 AM

Ahhh.. Hierarchical data. That's fun

. I just had to do a site for somebody where I needed to use hierarchical data. The only issue was, there was no set max depth what-so-ever. Came up with a "tree" of sorts thanks to a post I found online somewhere, where the table was set up like the following:

id
name
left
right

All children would be held within left and right of it's parent, so after one insertion, it would be:

1 Electronics 0 1

After another insert, it would be:

1 Electronics 0 3
2 TVs 1 2

If we wanted to do a children of TVs, it would be:

1 Electronics 0 5
2 TVs 1 4
3 Plasma 2 3

And so on. I thought it was a pretty good solution.

If you need any more help please let us know

Check · December 4th, 2009, 5:15 PM

Ooof. The nested sets model. Read a lot about it and follow the concept. It just makes my brain hurt thinking about it.

What I normally do is use a parent child relationship where each row has, for example, a field called item_id and one called parent_item_id. item_id is a system generated one up number. parent_item_id points to its parent or is null (or zero if that's easier) if it doesn't have a parent and is at the top of its tree. This makes entering data and looking at it in the database pretty easy but retrieving it in a hierarchical manner a bit more tricky. We use a variation of the stored procedure below to pull out the hierarchical data:

Code:

CREATE PROCEDURE HierList(@current int) as
set nocount on
declare @lev int
create table #stack (item_id int,item_nm varchar(50), lev int)
create table #stackout (item_id int, item_nm varchar(50), lev int)

insert #stack
  select @current, item_nm, 1
  from item
  where item_id = @current
select @lev = 1
while @lev > 0
begin
  if exists (select * from #stack where level = @lev)
    begin
      select @current = item_id
      from #stack
      where lev = @lev
      order by item_nm desc
      
      insert #stackout
      select item_id, item_nm, lev
      from #stack
      where item_id = @current and lev = @lev
     
      delete from #stack
      where item_id = @current and lev = @lev 

      insert #stack
      select item_id, space((@lev * 2) - 1) + item_nm, @lev +1
      from item
      where parent_item_id = @current
      if @@ROWCOUNT > 0
        select @lev = @lev + 1
    end
  else
    begin
      select @lev = @lev - 1
    end
end
select * from #stackout

The end result is a list of item_id, item_nm, and lev with item_nm indented two spaces for each level deep it is. As I said, retrieval is a bit more complex but we tend to get a lost less lost than I think we would with the nested sets model.

Don't get me wront, I'm not knocking the nested sets model. It's probably superior to the method we use. It just makes my brain hurt when I'm looking at data where their are multiple places where the hierarchy starts (no parent) that go 8 or more levels deep.

November 20th, 2009, 8:37 PM	#1 (permalink)
Check Registered User Fresh Surpasser Joined in Jun 2004 14 posts Gave thanks: 0 Thanked 0 times	Stored Procedures I know that MySQL 5.0+ supports Stored Procedures. Is there anything special I need to do to use these on Pass8? Thanks

December 1st, 2009, 3:38 PM	#3 (permalink)
DewKnight Skittles Super #1 Joined in Aug 2004 Lives in a space ship Hosted on dedi 7,239 posts Gave thanks: 118 Thanked 236 times	Sorry that nobody responded here. I'm glad you were able to find a way to work around this. If you have questions like this in the future, be sure to send in a ticket, we will be happy to help! 24x7 Fast, Reliable Support by Surpass Hosting __________________ Russell P. - Server Analyst I Mountain Dew Knight

December 3rd, 2009, 1:59 PM	#5 (permalink)
Kenneth Surpass FTW Seasoned Poster Joined in Mar 2009 Lives in Altamonte Springs, FL 97 posts Gave thanks: 1 Thanked 8 times	You shouldn't have to do anything special to use the stored procedures. What issues were you experiencing? What was your work around? __________________ Kenneth H. Support Supervisor

December 3rd, 2009, 2:00 PM	#6 (permalink)
Kenneth Surpass FTW Seasoned Poster Joined in Mar 2009 Lives in Altamonte Springs, FL 97 posts Gave thanks: 1 Thanked 8 times	Well, I take that back. You may need privileges added to the user to create routines, or stored procedures. I know you are going to need explicit access given to execute them as well. I remember that now If you ever need stored procedure access, submit a ticket. __________________ Kenneth H. Support Supervisor

December 4th, 2009, 9:37 AM	#8 (permalink)
Kenneth Surpass FTW Seasoned Poster Joined in Mar 2009 Lives in Altamonte Springs, FL 97 posts Gave thanks: 1 Thanked 8 times	Ahhh.. Hierarchical data. That's fun . I just had to do a site for somebody where I needed to use hierarchical data. The only issue was, there was no set max depth what-so-ever. Came up with a "tree" of sorts thanks to a post I found online somewhere, where the table was set up like the following: id name left right All children would be held within left and right of it's parent, so after one insertion, it would be: 1 Electronics 0 1 After another insert, it would be: 1 Electronics 0 3 2 TVs 1 2 If we wanted to do a children of TVs, it would be: 1 Electronics 0 5 2 TVs 1 4 3 Plasma 2 3 And so on. I thought it was a pretty good solution. If you need any more help please let us know __________________ Kenneth H. Support Supervisor

December 1st, 2009, 3:29 PM	#2 (permalink)
Check Registered User Fresh Surpasser Joined in Jun 2004 14 posts Gave thanks: 0 Thanked 0 times	Guessing the lack of response means #7263 when it comes to stored procedures. I worked around it already anyway.

December 4th, 2009, 5:15 PM	#9 (permalink)
Check Registered User Fresh Surpasser Joined in Jun 2004 14 posts Gave thanks: 0 Thanked 0 times	Ooof. The nested sets model. Read a lot about it and follow the concept. It just makes my brain hurt thinking about it. What I normally do is use a parent child relationship where each row has, for example, a field called item_id and one called parent_item_id. item_id is a system generated one up number. parent_item_id points to its parent or is null (or zero if that's easier) if it doesn't have a parent and is at the top of its tree. This makes entering data and looking at it in the database pretty easy but retrieving it in a hierarchical manner a bit more tricky. We use a variation of the stored procedure below to pull out the hierarchical data: Code: CREATE PROCEDURE HierList(@current int) as set nocount on declare @lev int create table #stack (item_id int,item_nm varchar(50), lev int) create table #stackout (item_id int, item_nm varchar(50), lev int) insert #stack select @current, item_nm, 1 from item where item_id = @current select @lev = 1 while @lev > 0 begin if exists (select * from #stack where level = @lev) begin select @current = item_id from #stack where lev = @lev order by item_nm desc insert #stackout select item_id, item_nm, lev from #stack where item_id = @current and lev = @lev delete from #stack where item_id = @current and lev = @lev insert #stack select item_id, space((@lev * 2) - 1) + item_nm, @lev +1 from item where parent_item_id = @current if @@ROWCOUNT > 0 select @lev = @lev + 1 end else begin select @lev = @lev - 1 end end select * from #stackout The end result is a list of item_id, item_nm, and lev with item_nm indented two spaces for each level deep it is. As I said, retrieval is a bit more complex but we tend to get a lost less lost than I think we would with the nested sets model. Don't get me wront, I'm not knocking the nested sets model. It's probably superior to the method we use. It just makes my brain hurt when I'm looking at data where their are multiple places where the hierarchy starts (no parent) that go 8 or more levels deep.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread: