Sql Variables Not Working Properly

[Mike_123]

A simple SQL like the following will not calculate properly:

set @rank:=0;

select @rank

will return “(null)”. Also using “@rank:=@rank+1” also does not work correctly. Has anyone else noticed this? It works fine in another MySQL mgr.

Also SQLyog does not like statements that end in “;” so it thinks I did not supply it with any SQL statements. This is a bit annoying because I copy and paste these statements from an SQL script and they all end in “;”.

TIA

Mike

[peterlaursen]

@Mike .. Plase only ONE thread in the Forums per issue. It is easier to discuss if everything is the same place.

1) Of course SQLyog supports the “;” delimiter symbol. Also multiple statements delimited by “;” are supported. Please explain more in detail what porblems you experience!?

2) Your problem with user-variables (@….) most likely is that you are using a HTTP-connection. This is not a SQLyog issue but a limitation with the PHP mysql_connect() method. Refer to:

http://webyog.com/faq/21_103_en.html

You can try to replace mysql_connect() with mysql_pconnect() as described in this FAQ:

http://webyog.com/faq/21_103_en.html.

Maybe it works – maybe not. It depends on the server configuration. Not only PHP but also Webserver must be configured to allow for in. In PHP the php.ini must have the line “persistent = on”

Also note that SQLyog reconnect autmatically if connection is lost. User-variables are connection-specific so after reconnect the user-variables of previous connection are gone.

[Mike_123]

peterlaursen wrote on Nov 20 2006, 11:45 PM:

@Mike .. Plase only ONE thread in the Forums per issue. It is easier to discuss if everything is the same place.

1) Of course SQLyog supports the “;” delimiter symbol. Also multiple statements delimited by “;” are supported. Please explain more in detail what porblems you experience!?

2) Your problem with user-variables (@….) most likely is that you are using a HTTP-connection. This is not a SQLyog issue but a limitation with the PHP mysql_connect() method. Refer to:

http://webyog.com/faq/21_103_en.html

You can try to replace mysql_connect() with mysql_pconnect() as described in this FAQ:

http://webyog.com/faq/21_103_en.html.

Maybe it works – maybe not. It depends on the server configuration. Not only PHP but also Webserver must be configured to allow for in. In PHP the php.ini must have the line “persistent = on”

Also note that SQLyog reconnect autmatically if connection is lost. User-variables are connection-specific so after reconnect the user-variables of previous connection are gone.

Yes, sorry about that but when I first posted the message I got an HTML error and thought the message failed to post properly so I reposted before I lost the text. Then when I saw 2 threads I had no way of deleting the 2nd one.

—-

Ok, I solved it. The problem was between the keyboard and the chair. 🙂 When executing multiple SQL statements in SQLyog I should have been pressing “Execute All Queries” Shift-F5 and not “Execute Query” F5. I would have liked to have seen the Execute All Queries keystroke set to one function key, like F9 because a lot of my queries have multiple SQL statements in them and reaching for Shift-F5 is going to be a pain.

Mike

[Author]

Posts