0

I have an annoying problem where if I execute the following manually, It works fine.

SELECT * FROM data
WHERE ip = '10.0.0.1'
LIMIT 10

Yet, If i put this in a stored proc. It fails because the WHERE needs to have the quotes?

For example, If i create the following stored PROC.

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetData`(IN `_ownerip` VARCHAR(32) CHARSET utf32)
    NO SQL
SELECT * FROM data
WHERE ip = _ownerip
LIMIT 10

it fails.

Its like the _ownerip needs to be like '_ownerip' rather than without the quotes.

Yet if I quote, it it fails. Ive tried " and backticks `

Cant figure out what to do.

Any thoughts?

Graham Smart
  • 43
  • 1
  • 12
  • According to your message it fails with the quotes, I'm a bit confused, have you tried without quotes? – J_P Mar 20 '18 at 11:48
  • No, it doesnt work whatever I do. If i used the "quoted _ownerip" it just takes that as literal. If i use without quotes, it clearly passes the IP but because its got dots it seems to throw mysql. Its like it just cant read an IP as a parameter. – Graham Smart Mar 20 '18 at 11:49
  • Look at this syntax (in the accepted answer): https://stackoverflow.com/questions/5039324/creating-a-procedure-in-mysql-with-parameters – J_P Mar 20 '18 at 11:50
  • Im not using an @ for the param? Not sure what that link means? Seems a different issue? – Graham Smart Mar 20 '18 at 11:51
  • 2
    What's the error message? Maybe the problem are with charset and not quotes. Try to un-specify charset on stored procedure definition. – Sakura Kinomoto Mar 20 '18 at 12:04
  • It means, in the first answer they don't use quotes, IN nor charset... so you could try that syntax to see if it works. If you keep trying the same code, you'll have the same error again and again. Look in the answer not in the question in that link, – J_P Mar 20 '18 at 12:35
  • I'd expect the error to be "#1172 - Result consisted of more than one row " - the issue is not about adding quotes, its the "limit 10" - try "limit 1" – symcbean Mar 20 '18 at 13:08

0 Answers0