views:

686

answers:

4

I'm used to MSSQL's Query Analyzer. Needing to convert some SP's for a hobby project, I'm having trouble making the transition to the mysql query browser, particularly when it comes to using variables. I'm essentially trying to simulate a procedure before it's a procedure.

So in Query Analyzer i'd write something like this...

delcare @var1 int
declare @var2 varchar(30)

set @var1 = 17 --some thing i'd normally pass to the SP
set @var2 = 'something else id pass to the SP'

SELECT * 
FROM table 
WHERE id = @var1 
OR textcolumn = @var2

Then I'd play around with the query (cause it's way more complex that the example one) until I got it right, or I'd substitute the values for the variables cause they're used like 100 times in the body of the query and that's a lot of retyping.

So my question is how to get that to work in MySQL's query browser.

I understand that it's only executing whatever statement is highlighted (the cursor is on that line or block of text). And I think i understand that the "Begin Transaction" button should be used somehow, but I can't get it to go. Here's what I have so far

DELIMITER $$

begin
declare var1 varchar(1) default 'W';
  select count(*) from gamestatspitchers where wls = var1;
end$$

delimiter ;

Thanks for any help. I can't seem to sort this out.

A: 

How about using a script tab instead of a resultset tab? That way you can execute more than one statement.

Greg
Script tabs do not allow you to see the results of SELECTs.
Daniel Schneller
+1  A: 

Usually MySQL Query Browser will use a separate connection for each statement that you execute from a query tab. This includes auto-commit semantics. That means, if you INSERT a record into a table, immediately all other clients connected to the same server can see the new record, once your statement finished.

If you hit the "Start Transaction" button, this will be different. For as long as you do not hit the "Rollback" or "Commit" buttons, everything you do in that query tab will be in a single transaction.

To try this, connect two clients at the same time, at least one being the Query Browser. In the other client do a SELECT COUNT(*) FROM testtable, assuming such a table exists.

Now, in the Query Browser hit the "Start Transaction" button. Insert a new record into the testtable. Do not yet hit the commit button, but instead, in that same query browser, do the same SELECT COUNT(*) FROM testtable. You will see a value that is one higher than in the other client, because this SELECT is in the same transaction as the INSERT you just did. Re-execute the SELECT in the other client and see that you still get the same value, because you cannot see the uncommitted INSERT yet.

In Query Browser now hit the "Commit" button and re-run the SELECT in the other client a third time. Now you will see the increased row-count there, too.

I have not tried using variables, but I assume this should work just fine, too.

Hope that helps!

Daniel Schneller
+1  A: 

Delimit the queries with semicolons like this:

SELECT  @var1 := 17, @var2 := 'somethingelse';
SELECT  @var1, @var2;

The query will be highlighted when you put a cursor over it.

Then just press Ctrl-Enter to execute this very query you want to execute.

Each tab keeps its own session, so the values of @var will remain defined after you execute the first query. You can rerun the second query as many times as you want.

Quassnoi
To clarify: This is only true, if the start transaction button was pressed first. Only then will the second statement show the variables' assigned values! Otherwise the second query will return oth as NULL.
Daniel Schneller
`@Daniel Schneller`: No, it is not true. The variables are session-bound, not transaction bound. Just try it.
Quassnoi
That is what I did before I commented. See here: http://picasaweb.google.com/lh/photo/ELAIKb0BCCRw1DC1i2TIMQ?feat=directlink and here: http://picasaweb.google.com/lh/photo/47es8mXWyGNNNUBO8mjg9A?feat=directlink
Daniel Schneller
It's something weird with your `MySQL` setup or the `Query Browser` settings. I just checked on two machines and three `MySQL` instances, it works fine everywhere. Could you please post your `MySQL` version and try to reproduce it on a command-line client?
Quassnoi
I just did what D Schneller did, and got the same result. first query gives results.... second query gives nulls. turn on the txn, and both give results. odd.... i've seen them called "session variables" when you use the '@'. oh well. maybe QB is starting and stopping sessions with ctrl-enter.
Jody
`@Jody`: could you please post your `QB` and `MySQL` version, and try to reproduce the same using command line client?
Quassnoi
A: 

Doesn't look like what I want is really possible. I'm just using two tabs - one script tab to edit the query, and a results tab to call it. It works, but it's not as easy as MSSQL Query Analyzer for this.

Jody