[peterlaursen]

Quote:

Mouse? What is this “mouse” thing you are talking of?

Sorry .. I did not know that your CAD ate it! 😮

[peterlaursen]

looks like Ritesh found a shortcut to the BIGGRIN smiley today! 😀

[peterlaursen]

OR .. if you prefer to use the mouse

'single arrow icon' executes current query

'double arrow icon' executes all queries

[peterlaursen]

@vygi

I think you are right, and if that is the case I'm wrong! <_<

[peterlaursen]

Thanks for the enlightment!

Well, yes that could be useful too.

However you could also use the DATA SYNC tool. Import the .sql to a 'dummy' database and sync the 'dummy' with the 'real' DB. It gives you more options, I think (row filtering, column filtering).

[peterlaursen]

I think you can grant the EXECUTE privilege to user as a GLOBAL privilege or a SCHEMA(database) privilege with “MySQL Administrator”. What you did was to grant the EXECUTE privilege as a SCHEMA OBJECT privilege.

[peterlaursen]

Looks like our postings 'crossed'

[peterlaursen]

My ignorance – what is “full insert statements” ??

BTW: I DO NOT ACCEPT the use of phpmyadmin as a reference … 😛

[peterlaursen]

Quote:

java.sql.SQLException: execute command denied to user 'pir8ped'@'%' for routine 'starfriend.selectByProximity'

I have set up user pir8ped to have all priviledges, and all my java/db tests work, except the one calling the stored procedure.

Can I ask you to try using “MySQL Administrator” http://dev.mysql.com for granting the EXECUTE privilege to user? Or issue the GRANT statment as an SQL statement yourself?

The SQLyog User Management does not handle the EXECUTE privilege yet.

http://www.webyog.com/faq/5_20_en.html

[peterlaursen]

The 'workaround' to this for the next few weeks is to work from RESULT and not the DATA-pane.

Start issuing a statement like

Code:

SELECT column_1, column_2, … , column_n etc.

The SELECT statement should include all columns that you want to edit and omit all columns having defaults that you don't want to overwrite.

Now you can UPDATE etc. from RESULT-pane and the SQL generated by SQLyog when saving will only write to the columns and rows that were included in the original SELECT statement.

[peterlaursen]

Yes this error 1064 is an (in)famous one with MySQL. but it goes wrong here:

Code:

select userName, town, earthRadius * acos( XAxis*CntXAxis + YAxis*CntYAxis + ZA'

After all it tells that.

The use of SELECT as ALIAS is OK as such. And I don't see the error. But maybe the expression is too long to evaluate. Maybe some more paranthesises like

Code:

earthRadius * (acos( ( (XAxis * CntXAxis) + (YAxis * CntYAxis) + (ZAxis * CntZAxis ) ) ) as ….

would do

or it is illegal trigonometrics ..

Also I would simplify things for the start .. drop  the WHERE and ORDER BY until the select works. I would start here

Code:

acos(XAxis*CntXAxi) as proxDistance from location where proxDistance LT 5000

and then one step at a time! Build the SQL interactively from SQLyog, and when it works paste it into your SP!

And

Quote:

So, you have to execute the SP code – thanks!

Yes! There is no other way the server call tell what a client like SQLyog is doing. THE ONLY THING (almost) SQLyog does is executing SQL. SQLyog is a MySQL database CLIENT and database clients communicate with a server by executing SQL.

[peterlaursen]

Well …

You enter the code to create the SP like

Code:

DELIMITER $$;
DROP PROCEDURE IF EXISTS `musik`.`ffff`$$
CREATE PROCEDURE `musik`.`ffff` ()
BEGIN
set @test = 'test';
select * from my_db.my_table;
END$$
DELIMITER;$$

and EXECUTE THAT CODE! For instance by clicking the double arrow icon on the menu bar. When the code is executed the SP is created.

It you do that allready and no SP is created it probably is a problem with your use of delimiters. Read here: http://www.webyog.com/faq/25_49_en.html.

You can use the SQLyog built-in template for creating an SP by right-clicking the SP-folder in the SQLyog Object Browser.

To execute the SP after creation you must call it().

[peterlaursen]

Quote:

SQLyog 9 was able to …

WOW .. where did you get that from? 😀

Maybe it is not a big deal, but it is a 'show stopper'. The sync tools should work. simply! But I think that a similar issue (that struct-sync fails to sync column order) was recorded here recently. And Ritesh promised that it would be fixed soon.

[peterlaursen]

Quote:

what’s the alternate to using views in older mysql?

It depends on why you originally intended to use VIEWS.

Also it depends on the MySQL version and the amount of data and the server resources available to you.

With MySQL 4.1 you might be able to use subqueries to reach your objective. For instance you could use a subquery to create a (temporary) table. For instance CREATE … TABLE … SELECT … WHERE somevariable LIKE (SELECT ….. )

With MySQL <= 4.0 I can only think of splitting your tables into smaller tables.

[peterlaursen]

MySQL 5.1/SQLyog status:

http://www.webyog.com/faq/5_80_en.html

[Author]

Posts