next »

[cafescott]

Perkiset,

it's a nice utility.  thanks again for helping me get started with it.

[perkiset]

Most kind, CafeScott - my pleasure.

[cafescott]

Perkiset,

here's another question.  any way to configure phpMyide/index.php so that it requires a password entry?

thanks,

[perkiset]

Currently, the only way is to put password security on the website at the server level - Dink has a rig like that - he will be better to tell you how than me.

Dink! Paging Dink!

[cafescott]

thanks, Perkiset.

btw, what's Time Machine?  every time i click the tab i see nothing.

thanks,

[cafescott]

Perkiset,

another question...

i'm trying to test that an output variable to a stored procedure got the correct value.  In the "command prompt", i try to concatenate three statements using semicolons:

declare @vOutputID int; call sp_My_Stored_Proc(@vOutputID); select @vOutputID;

however, I get this error:

"Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @vOutputID int..."

so, do you have any suggestions of how to test the value of an output parameter using the command prompt?

thanks again,

[perkiset]

I have an issue with the command prompt, which is that "enter" or "return" signals the end of the line, so when you want to put a multi-line statement it's a challenge.

The workaround is to go to the Editor tab and click New Scratchpad - these are just areas for typing anything, there isn't even a way to save anything. So type your whole compound statement into a scratchpad, select all, copy, then click the command tab, paste and return. It's not the most elegant, but on the up side, if you have a problem with 1..[n] of your instructions in the compound statement, you can go back and edit them and reissue.

I'm considering an "Alt-Return" solution for entering multiple commands or some such for the command prompt, but haven't implemented anything yet.

Now - all this said, I have not done almost any testing at all with inline or in-session variables such as you are using, so there may be other issues that I need to address as well. To solve your immediate problem, simply end the stored proc with "select [your variable]" and it will output to the command prompt.

[dink]

Currently, the only way is to put password security on the website at the server level - Dink has a rig like that - he will be better to tell you how than me.

Dink! Paging Dink!

Did you call?

Ya.  I have this instance on a shared hosting server that has cPanel installed.  So, I used the 'password protect directories' button from the control panel.  May not be brilliant, but I didn't stay at a Holliday Inn last night either.

[cafescott]

Perkiset,

thanks for the reply.  i will use your advice and select the output var to see if it has the right value.

[perkiset]

CScott -

The way that the command prompt works is not exactly the way you might expect.

Between "returns" (issuing commands) the session goes away - in other words, if you selected a value into a variable with one command execution, then tried to select that value in another command execution, it would have no value. That sort of thing ONLY works if all the lines are sent in one command execution (multi-line statement). Since there is no way, currently, for the system to hang onto a session and dedicate it to *just you* there's no way to maintain state between commands and, therefore, no way for me (phpMyIDE) to maintain a value in variable for you (the user).

I have looked at creating a daemon that will maintain state on a particular connection and keep it dedicated to (you) but have not gone that direction yet. I think it would be great, but add another level of complexity that I am unsure about how to QA.

So, all that said: issue commands as a mutli-line statement and it SHOULD give you what you are looking for. If it's something that you're going to do a lot of, then you might consider just saving it as a procedure.

Good luck!
/perk

[cafescott]

Perk,

your last sentence helped me figure out how to do it.  I'll just create procs that call other procs.  That will enable me to see what the output variables contain.

thanks for all the help! 

 

[cafescott]

hey Perk,

i'm getting a lot of work done with phpmyide.  however, here's a new question for you. 

I have a few stored procs that use dynamic sql.  (or dynamic mysql.)  However, i'm finding it kind of difficult seeing the value of the var that contains the mysql statement.  In other words, the finish of my stored proc looks like this:

  select @vSQL;

  PREPARE stmt from @vSQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

So, when I call the proc with the 'select @vSQL' statement activated, all that shows up in the in the output of the command prompt tab is the first 63 characters (or so) of the sql statement.   Do you have any suggestions about how a person could see the entire sql statement when using dynamic mysql?

thanks,

[perkiset]

CScott this is great, because you're way beyond me with this stuff.

The truth is, I wrote phpMyIDE so that I'd learn how to write stored procedures. No lie.

So I guess I need to know why you'd do this, as opposed to simply supplying variables to an already-prepared SQL statement... this looks like good stuff for me to know, but I've not done anything with it.

Please post an example usage (and the reason why for me please ) and I'll play with it. (nothing private or dangerous to you of course)

Thanks man! Excellent!
/perk

[cafescott]

hey Perk,

an example of where a dynamic query is helpful is when you've got a group of similiar tables; e.g., tblChicago_Bears, tblMinnesota_Vikings, tblPittsburgh_Steelers, etc.

you want to write a dynamic sql statement that enables an update to a particular table based on what table name is passed in; e.g.,

create procedure Update_NFL_Table
(vTableName varchar(50),
vScore int)
BEGIN
  declare vTablename varchar(30);
  set vTablename = CONCAT('myDatabaseName.', vTableName);
  set @vSQL = CONCAT('update ',vTableName, ' set Score=',CAST(vScore as CHAR));

  select @vSQL;

  PREPARE stmt from @vSQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END

--this way I can use one stored proc to make updates to these tables; e.g.,

call myDatabase.Update_NFL_Table('tblMinnesota_Vikings', 35);
call myDatabase.Update_NFL_Table('tblPittsburgh_Steelers', 15);

etc.

the advantage of working with dynamic sql is i don't have to have an update stored procedure for every table that's part of a group of tables with analogous structures.

thanks for the help,

[perkiset]

CScott I'm going to look at the problem, but right at the moment, you've split my head open on an issue I've had with a bunch of stored procedures and I have to follow that through or lose the thread.

Thanks VERY much for the explanation. I'd like to invite you to break the hell out of my package and let's make it really sing.

I'll try some of this stuff and see what I get in a bit.

Cheers mate, thanks again.

/perk