Any MySQL Experts around?

[garyk]

OK,

banging my head abit with this one. Trying to store the value from a table into a variable and failing. So if I have a table T10Company with 1 row which has the value

MYCO0001

and is the following query:

SELECT 
	MAX(CONVERT(RIGHT(CompanyID, 4), UNSIGNED INTEGER)) 
FROM 
	T10company 
WHERE 
	LEFT(CompanyID, 4) = 'MYCO' 
GROUP BY 
	LEFT(CompanyID, 4) 
ORDER BY 
	RIGHT(CompanyID, 4) limit 1;

I get the return value of 1, as I would expect.

If I now want to store the result of that query in a variable like so:

SELECT 
	MAX(CONVERT(RIGHT(CompanyID, 4), UNSIGNED INTEGER)) INTO @MaxNo
FROM 
	T10company 
WHERE 
	LEFT(CompanyID, 4) = 'MYCO' 
GROUP BY 
	LEFT(CompanyID, 4) 
ORDER BY 
	RIGHT(CompanyID, 4) limit 1;

@MaxNo is always NULL, I cannot understand it!

Help anyone?

Cheers


Gary

 

[TotallySport]

can I ask what your trying to do? according to http://www.w3schools.com/sql/sql_select_into.asp INTO puts from information from one thing into and other, it doesn't hold it as a variable.

 

[yorkukhosting]

Try the following, it should store the result in @MyResult:

Set @MyResult = (SELECT 
 MAX(CONVERT(RIGHT(CompanyID, 4), UNSIGNED INTEGER)) 
FROM 
 T10company 
WHERE 
 LEFT(CompanyID, 4) = 'MYCO' 
GROUP BY 
 LEFT(CompanyID, 4) 
ORDER BY 
 RIGHT(CompanyID, 4) limit 1);

 

[garyk]

@TS you might me looking at INSERT INTO which is completely different

@YorkUK, yep tried that! I'm trying to resist using mssql which I know love and have used for years but this is just 1 of a number of things where mysql is driving me up the wall!

Cheers

Gary

 

[lynxus]

What about using "as"?

Ie: select * from table as name;

 

[TotallySport]

@TS you might me looking at INSERT INTO which is completely different

@YorkUK, yep tried that! I'm trying to resist using mssql which I know love and have used for years but this is just 1 of a number of things where mysql is driving me up the wall!

Cheers

Gary

Thats not whats the links says, but I don't use SQL anymore I use linq, and even then I am not very good at it.

Just to add, but I don't know the answer either good luck

 

[Jeff FV]

Have you tried using SET ?

Try this:

SET @MaxNo := (SELECT 
    MAX(CONVERT(RIGHT(CompanyID, 4), UNSIGNED INTEGER)) 
FROM 
    T10company 
WHERE 
    LEFT(CompanyID, 4) = 'MYCO' 
GROUP BY 
    LEFT(CompanyID, 4) 
ORDER BY 
    RIGHT(CompanyID, 4) limit 1;)

(make sure you put the whole SELECT statement in brackets)

HTH

Jeff

 

[DotNetWebs]

...I don't use SQL anymore I use linq...

Splitting hairs I know but if you are using LINQ [to query a database] you are still using SQL it's just that LINQ is constructing the Query Statement for you behind the scenes.

.. mssql which I know love and have used for years...

Same here and but I use PostgreSQL [RDMS] / SQLite [embedded] / CouchDB [NoSQL]) for my open source stuff so am not experienced in MySQL.

Regards

Dotty

 

[TotallySport]

Splitting hairs I know but if you are using LINQ [to query a database] you are still using SQL it's just that LINQ is constructing the Query Statement for you behind the scenes.

I know, but I haven't written a proper SQL statement in SQL for some time, and I am not yet looking at the Linq to make sure it returns an optimised query, as long as it returns what I want I am happy.

(I know I know I should know better)

 

[garyk]

What about using "as"?

Ie: select * from table as name;

AS is used as an alias not for putting values into variables.

Thanks all anyway looks like Im going to have to go to the cosy confines of MSSQL.

 

[alanc]

I've done this in MySQL, but I did it in a Stored procedure. Here's a fragment:

 DECLARE sellerSKU, prdCode VARCHAR(20);
 DECLARE quantity INT  DEFAULT 0;
 
 DECLARE  delta_cur CURSOR FOR  
 select pd.sku, isc_p.prodcode, sum(pd.quantityChanged)
 from <rest of the select statement>;  
 FETCH delta_cur INTO sellerSKU, prdCode, quantity;

 

[10032012]

Not an MySQL expert and don't use the variables, have you tried the following?

SET @MaxNo = (SELECT MAX(CONVERT(RIGHT(CompanyID, 4), UNSIGNED INTEGER)) FROM T10company WHERE LEFT(CompanyID, 4) = 'MYCO' GROUP BY LEFT(CompanyID, 4) ORDER BY RIGHT(CompanyID, 4) limit 1);

You might have to do a dummy set first like "SET @MaxNo = 0;"

 

[10032012]

Perhaps alan is on to something.

DECLARE MaxNo SMALLINT;
SELECT MAX(CONVERT(RIGHT(CompanyID, 4), UNSIGNED INTEGER)) FROM T10company WHERE LEFT(CompanyID, 4) = 'MYCO' GROUP BY LEFT(CompanyID, 4) ORDER BY RIGHT(CompanyID, 4) LIMIT 1 INTO MaxNo;

 

[garyk]

Perhaps alan is on to something.

No that isnt valid syntax, the INTO has to be after your field specifier.

Thanks all anyway for anyone that uses mysql I find the tools (mysql workbench) far too buggy and unstable, the amount of time it crashes is frustrating.

That and the fact that some things you can do in a proc you cannot just type in a query window, its all soooo easy with mssql.

Cheers


Gary

 

[DotNetWebs]

....its all soooo easy with mssql....

It'a almost toooo easy!

I have some web sites where I have not got around to finishing of the admin system and I just edit them with SQL Server Management System!

Only when that becomes a limitation, e.g when I want to add other admins etc. do I get around to coding it properly!

Regards

Dotty

 

[Mortime Business Software]

Hi Gary. The statement works fine on version 5.0.77 of MySQL. After running the query on a test table with values 'MYCO0001', 'MYCO0002', 'MYCO0003', 'MYCO0004', I get:

mysql> SELECT @MaxNo;
+--------+
| @MaxNo |
+--------+
| 4      | 
+--------+

However, if I run the query after deleting all the values from the table, then I get:

mysql> SELECT @MaxNo;
+--------+
| @MaxNo |
+--------+
| NULL   | 
+--------+
Query OK, 0 rows affected, 1 warning (0.00 sec)

Note that the query is OK, but with one warning.

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed | 
+---------+------+-----------------------------------------------------+

Are you certain that T10company.CompanyID has values before you run the query?

EDIT: I should add that, since the first value of @MaxNo persisted, I used a fresh variable @MaxNo2 for the test on the empty table.

All the best,

Dave

 

[alanc]

No that isnt valid syntax, the INTO has to be after your field specifier.

Funny that, because the code fragment I provided actually works.

 

[garyk]

Doesnt for me Alan but thanks for your time anyway.

Dave thanks for your investigation work!

 

[Mortime Business Software]

garyk:

Dave thanks for your investigation work!

You're welcome, although, after I'd confirmed that your query is syntactically correct, I have to admit that there wasn't much investigating to do because I could think of only one way that the variable could possibly be NULL!

All the best,

Dave

 

[Mortime Business Software]

TotallySport:

according to http://www.w3schools.com/sql/sql_select_into.asp INTO puts from information from one thing into and other, it doesn't hold it as a variable.

W3schools is not a definitive reference for MySQL (or anything else for that matter). If you are in any doubt, then you should look to the official documentation. However, there's nothing wrong with using material from places like w3schools as a quick guide or reminder on something.

All the best,

Dave

 

[10032012]

w3schools is designed for beginners... creating rule of thumb for their usage. MySQL is quite advanced and complicated there is usually several ways of doing a query to get the same result - some better performance than others (I guess that goes with everything though like PHP language).

The w3schools article link talks about SELECT (what) INTO not SELECT [...] INTO [...] there is a major difference, but its correct in regards SELECT INTO which is only separated by the fields used for the query - be it column names or wildcard such as * for all.

The other aspect is w3schools focuses on common SQL... SQL varies slightly (and a lot) between MySQL to other implementations of it.

 

[garyk]

You're welcome, although, after I'd confirmed that your query is syntactically correct, I have to admit that there wasn't much investigating to do because I could think of only one way that the variable could possibly be NULL!

All the best,

Dave

Yes I do have 1 record, like I say without the INTO I do get the correct result. Interesting I notice you are using the command line. Maybe its something iffy with mysql workbench that I'm using (which is very unstable anyway).

I read in mysql you can only put a declare in a begin..end but when I do that it starts saying my query has a syntax error even when I havent changed it!

Thats my biggest frustration, with MSSQL you can freely mix and match your SQL/TransactSQL regardless of if its in a proc. or you are just entering it in the query window. In mysql this seems to not be the case.

Gary

 

[Mortime Business Software]

10032012:

The w3schools article link talks about SELECT (what) INTO not SELECT [...] INTO [...] there is a major difference, ...

I don't understand what you mean. Could you clarify please?

My understanding is that, at least as far as logical processing is concerned, the SELECT clause takes a table as input (produced as output by the last statement in a query), and returns a table consisting of a subset of the columns of the input table. This is standard SQL. I'm not aware of any vendor who has bas.tardised the meaning of an operator based on standard mathematical relational theory, but like I said, I'm not sure what you mean. Are you suggesting that SELECT has more than one meaning or context?

10032012:

w3schools is designed for beginners...

w3schools is one of the last places I would suggest for a beginner.

10032012:

but its correct in regards SELECT INTO which is only separated by the fields used for the query - be it column names or wildcard such as * for all.

And variables - which are treated as columns! We could think of a set of variables as a column set of an anonymous table. Take a look at the outputs in my first post to this thread - message #16, page 2.

I'm not saying that the w3schools article is incorrect. However, it is just a basic tutorial. It is certainly not an academic text (as they like to believe with their silly certificates), nor is it a substitute for proper technical manuals which address specific implementations by different vendors.

I think about web tutorials (such as those provided by w3schools) in roughly the same way as I think about much of the Wikipedia material - highly useful as a quick reference, and sometimes quotable, but to be taken with a pinch of salt if you're not already familiar with a subject. To do otherwise can be very dangerous.

All the best,

Dave

 

[Mortime Business Software]

garyk:

Yes I do have 1 record, like I say without the INTO I do get the correct result. Interesting I notice you are using the command line. Maybe its something iffy with mysql workbench that I'm using (which is very unstable anyway).

That could very well be the case Gary. I have just submitted your query again with just one row in my test table ('MYCO0003'), and the query is still valid.

Have you tried speaking to the organ-grinder rather than a monkey? That is, have you tried running your query from the command line instead of through that Workbench thingy? To bring up the myslq prompt, go to your shell:

[shell] mysql -u user -p
Enter password:

mysql> USE your_database;
mysql>

and run your query.

If you have one record, then @MaxNo should still hold the single value.

The only other reason I can imagine for the error is that you might be using a very old version of MySQL - even earlier than the one I have on my local machine - 5.0.77!

A couple of years ago I remember getting very frustrated about MySQL not parsing my subqueries. I had no idea why. Later, I discovered that, in the version of MySQL that I was using, whilst subqueries were syntactically accepted, they were not yet supported! I guess they were using forward compatibility as they were designing their SQL grammar. Subqueries are now fully supported.

I read in mysql you can only put a declare in a begin..end but when I do that it starts saying my query has a syntax error even when I havent changed it!

Thats my biggest frustration, with MSSQL you can freely mix and match your SQL/TransactSQL regardless of if its in a proc. or you are just entering it in the query window. In mysql this seems to not be the case.

I haven't used a DBMS GUI since I was using Sybase's SQL Anywhere a couple of years ago. Since then I've been using the MySQL monitor (command line mysql program), and, apart from there being no horizontal scroll bar on the terminal window (which means that long table rows are wrapped) I can't remember whether there are any real advantages to using a GUI!

In fact, only the other week I had a play around with phpMyAdmin for the first time, and a couple of days ago, when I was having a look at cPanel on our website, I gave it another go. I have to say that phpMyAdmin is one of the most awful pieces of cripe that I've ever encountered!

Broadly speaking, in my humble opinion, nanny software is for users. I know we all need it to a certain extent, but developers should seek a level of interaction with production software that strikes a good balance between speed of production and quality of learning, where richer learning means faster and more robust production in the long term. In my experience, this is exactly what Microsoft did not offer me, and hence why I jumped off their ship many years ago.

All the best,

Dave

 

[alanc]

I have to say that phpMyAdmin is one of the most awful pieces of cripe that I've ever encountered!

Hear! Hear!

But don't knock the GUIs. MS SQL Server Management Studio is very good. it may run slow, but it's quicker than typing out complex SQL all teh time.

 

[Mortime Business Software]

alanc:

But don't knock the GUIs. MS SQL Server Management Studio is very good. it may run slow, but it's quicker than typing out complex SQL all teh time.

Alan. You don't need to type out your queries directly on to the command line. You can use alt+tab and copy'n'paste from files that you control directly on your tree, as shown in your file browser - a *fully justified* and most admirable GUI!

Dave

 

[garyk]

The query seems to work Dave in the console but something still isnt right in the proc as I do a null check, if null set to 1 and then some code to add 1. So basically it must still be returning null, passing the null check, setting to 1 and then adding to 1.

Heres the code, always returns 2

	SELECT
		MAX(CONVERT(RIGHT(CompanyID, 4), UNSIGNED INTEGER)) INTO MaxSuffix
	FROM
		t10company
	WHERE
		LEFT(CompanyID, 4) = Prefix
  GROUP BY
    LEFT(CompanyID, 4)
	ORDER BY
		CompanyID DESC;

  IF MaxSuffix IS NULL THEN
    SET MaxSuffix = 1;
  END IF;

  SET MaxSuffix = MaxSuffix + 1;

I dont understand as well when you use the query window/console variables are prefixed @ but in a proc you cant do this? Its too inconsistent!

Perhaps its the devil you know but having used MSSQL 6.5 since '99 I could have done this in minutes, in fact built the whole solution gone off on holiday while my clone of me struggling with mysql would be at first base!

Your efforts and everyone else's however is greatly appreciated.


Cheers


Gary

 

[Mortime Business Software]

Gary, you'll have to excuse me because the only programming I've done through a DBMS is that which I did on my uni module years ago.

Once again, there doesn't seem to be anything wrong with the code you have shown us here. The only differences between the relevant code you posted and the code I give below are:

* I have shown that the string literal 'MYCO' is saved to the variable Prefix, so you might like to check your code to see whether Prefix is being assigned a string of four characters.

* I have retained MaxNo whereas you have renamed this to MaxSuffix.

Also, note that the parameter list of the procedure declares MaxNo as an 'OUT' variable, and Prefix can be declared as IN, OUT or neither.

Copy and paste the following directly to your mysql prompt:

DELIMITER //

CREATE PROCEDURE IncMaxNo ( OUT MaxNo INT, Prefix CHAR(4) )

BEGIN

SELECT MAX( CONVERT( RIGHT( id, 4 ), UNSIGNED INTEGER ) ) INTO MaxNo
FROM  t2
WHERE LEFT( id, 4 ) = Prefix
GROUP BY LEFT( id, 4 )
ORDER BY id DESC;

IF MaxNo IS NULL THEN
SET MaxNo = 1;
END IF;

SET MaxNo = MaxNo + 1;

END//

DELIMITER ;

Note that if Prefix is NULL, then MaxNo will also be NULL.

Test 1: Call IncMaxNo without setting Prefix to a string. Result: @MaxNo will be set to 2.
Test 2: Call IncMaxNo after setting Prefix = 'MYCO'. Result: @MaxNo will be set to 5.

SET @Prefix = 'MYCO';
CALL IncMaxNo( @MaxNo, @Prefix );

With the same four values that I previously had in my test table, I get the following output:

SELECT @MaxNo;
+--------+
| @MaxNo |
+--------+
| 5      | 
+--------+
1 row in set (0.00 sec)

SELECT @Prefix;
+---------+
| @Prefix |
+---------+
| MYCO    | 
+---------+

Use the following for initialisation or clean-up before running the tests:

DROP PROCEDURE IncMaxNo;
SET @MaxNo = 0;
SET @Prefix = NULL;
SELECT @MaxNo;
SELECT @Prefix;

If IncMaxNo doesn't exist, then you'll receive an error, but this wont affect the tests.

Perhaps its the devil you know but having used MSSQL 6.5 since '99 I could have done this in minutes, ...

Every language has its quirks, and some seem to be more quirky than others - especially when you're not familiar with them. To be honest, this is the first time I've programmed with MySQL. I actually found the difference in variable-naming helpful for distinguishing local variables from 'external' variables.

All the best,

Dave

 

[Mortime Business Software]

Message deleted. Reason: double post.