next »

[perkiset]

CScott -

Can I prepare a call to another stored procedure, so that I could effectively have a call to a procedure where the name was not defined at create time? For example:

procedure confirmMembership(theID integer)
begin

   declare procName varchar(64);

   set @vSQL = concat('call gallery_confirmMembership_', theID, '()');
   prepare stmt from from @vSQL;
   execute stmt;
   deallocate prepare stmt;

end;

What I need to do is call stored procedures like gallery_confirmMembership_11() and gallery_comfirmMembership_37() but I don't know the last digits until call time.

And in this case, looking at your code, why are you not required to declare stmt and vSQL?

Thanks!

/p

[perkiset]

Update to my question: the procedure above saves without complaint, and even runs without complaint. However if I call it, with, say, id=31 the gallery_comfirmMembership_31() does not get called... but I am completely unfamiliar with this syntax, so I don't know if I'm stating my intentions correctly.

Thanks again,
/perk

[cafescott]

Perk,

sorry for the delay in responding.  (I kept refreshing page 3, but was unaware that we had moved to page 4.)

"And in this case, looking at your code, why are you not required to declare stmt and vSQL?"

that's kind of a mystery to me, too.  However, there is a difference between these two data types:

vSQL varchar(200);
@vSQL

The one with the @ sign is a user-variable that is unique per connection.  I don't know more than that.  It has different behavior than your traditional varchar variable.  I think of it as similiar to a session variable in VBScript.

If the sql statement were encapsulated in a varchar, yes it would need to be declared before using it.  However, everytime I tried to declare something like this I got an error:

declare @vsql varchar(200);

So I found by process of elimination that you don't declare those puppies before using them.  Strange, huh?

Similarly, the 'stmt' part of 'prepare stmt/execute stmt' is unique in that it is declared by the prepare statement itself.  Again, this is strange and new to me also.

I don't know why you would need to use dynamic sql to call other procedures.  That's a new application of the technique to me.  As i mentioned in my prior posts, typically dynamic sql is used to reference tables that are very similiar in structure from a single stored procedure.

One thing I notice from your code is this statement is wrong:

"prepare stmt from from @vSQL;"

you've got two "from" statements in a row.  You should only have one.

Finally, maybe you need to put the database name in front of the proc?  Like so:

set @vSQL = concat('call MyDB.gallery_confirmMembership_', theID, '()');

[cafescott]

Perk,

btw, i started this thread:
http://forums.mysql.com/read.php?10,228268,228268#msg-228268

so far the best strategy i know of to retrieve the contents of a dynamic sql statement is to write the sql statement to a table, then use phpMyAdmin to "edit" the table record.  In edit mode, you can select the entire statement and then copy and stick it into another stored proc for testing purposes. 

This is kind of a pain in the butt.  However, some of us are pretty desperate to leave the MSSQL world.

thanks for the help,

[perkiset]

CScott - I'm going to respond to this, but I'm currently really slammed and can't give your response it's due and test what you've mentioned to me.

I'll have to catch it later, but thanks so much for the info.

/p

[cafescott]

hey Perk,

I'm moving this to the correct thread.  My apologies, but it seems that the prior thread ran into another person's issue, and I posted there instead of here.

This is the source of the comments that follow:
http://www.perkiset.org/forum/support_threads/phpmyide_bounces_to_phpmyadmin_on_program_load-t979.15.html


Hi Perk,

thanks for the speedy reply.

"After finishing my stored procedures I installed another MySQL data dump.  This wiped out all my existing tables (except for the ones that phpMyIDE) makes, and recreated them.  I tried to make sure that all the routines were retained."

Here's my process so far.  I started out with old tables.  Using phpMyIDE, I created new routines to use with the tables.  (I backed the routines up.)  Then I used phpAdmin to delete all the old tables, minus the ones that were in there from the previous install of phpMyIDE.

Then I loaded in a new set of tables into the database.  Now that I've done this I've suddenly lost ability to work with phpMyIDE.

"I'd go back to the testinstall.php routine and see what it reports."

Here's the output.  It reports everything OK.  (I don't have a better way of sending you this info, since your forum's attachment script doesn't permit a TXT or HTML file):

Testing Includes
OK
Testing: config.php
OK
Testing: source/class.dbconnection.php
OK
Testing: source/class.webrequest2.php
Testing Functions
OK
Testing: json_encode
OK
Testing: json_decode
OK
Testing: mysql_connect
OK
Testing: mysqli_connect
Testing Prompt Handler
OK
Testing: Connection
Testing Connections
OK
Testing: Writeable current-connection
OK
Testing: mbcrazy mySQL
Testing Privileges
Testing: mbcrazy mySQL
OK
show procedure status
OK
drop procedure
OK
create procedure


thanks,

[perkiset]

hmmm... looks like the install program is happy...

when you fire phpMyIDE up, what happens? Do you just get (nothing) in the dbExplorer or scriptExplorer panes?

[cafescott]

Perk,

I've got 'information schema' in a very short text box (i.e., short vertically, so the borders of the box are close to the words).  My database name doesn't appear even if I hit the refresh button.

there's nothing in the second box.

If I click 'information schema', i just barely see 'procedures'.  I click that, and none show up on the right panel.  All three of these panels are still very short vertically.

thanks,

[perkiset]

Scott - can you not resize the top area with the drag knuckles?
They're the 5-knuckle looking things at the bottom of the top panel in the center...

[cafescott]

hey Perk,

i restarted my PC and suddenly phpMyIDE is working fine.  I don't know what was going on beforehand.  For about an hour I thought I had lost many routines, and had another configuration issue.

I appreciate the quick replies, tho.   

[perkiset]

Friggin' Windows  SO VERY glad I fired Microsoft.

Glad it's working again CS.

[cafescott]

yeah, Microsoft is the Devil. 

thanks again, Perk.

[Mithadriel]

Just thought I'd add it's a shame there's no workaround for the json_encode/decode reliance.

A lot of shared web hosts use the Red Hat Network for updates who generally Backport (http://www.redhat.com/security/updates/backporting/) their PHP versions. The current RPM for Red Hat distros is 5.1.6 which doesn't include the json_encode/decode functions sadly

Oh well, lucky I have a VPS to fall back on

[perkiset]

Writing my own encoder/decoder (or including one from someone else) is on the list for the next release as a fallback.

I have a version with just a few little updates that I'm using now, perhaps I should just release this one as an interim update while I work through the newer mods.

I'll give'r a think today, thanks for the bump.