Syntax error?

  • Thread starter Thread starter Joneleth
  • Start date Start date
J

Joneleth

Hi,

I'm developing an ASP.NET web application with MySql 5.
I have a little problem: if i create a stored procedure like this one:

CREATE PROCEDURE `spProva` (out retval int, in idemployee int)
BEGIN
UPDATE employee e SET e.Room = 'Milan 41' WHERE e.IdEmployee =
idemployee;
SET retval = idemployee;
END

everything works fine, while if I use a textual query programmatically
created:

string sqlText = "UPDATE employee e SET e.Room = ?Room
WHERE e.IdEmployee=?IdEmployee; ";
sqlText += "SET ?retval = ?IdEmployee; ";

with ?Room, ?IdEmployee and ?retval presetted parameters, an error
raises: #42000You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '0 = 55'.

It seems that the expression is pre-evaluated and the variables
replaced before executing the query, and so an instruction like 'SET 0
=
55' clearly has no sense.
Is there any way to work around that issue, without using stored
procedure?

Thanks.
 
Do you need the retval for anything? I would say no (unless this is a
constraint in sprocs in MySQL).

Think about it this way:

1. Pass in employeeID = 1
2. UPDATE record
3. Pass out retval = 1

This never changes. You do not need this value, as the only way it can be
anything different is in case of error, which will throw an error and not
return anything (unless MySQL is a really strange RDBMS these days ;->).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Did you set the CommandType to stored procedure before executing?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Cowboy said:
This never changes. You do not need this value, as the only way it can be
anything different is in case of error, which will throw an error and not
return anything (unless MySQL is a really strange RDBMS these days ;->).
Click to expand...

In the case of that small example you're right, the retval is quite
useless.
However, if you're going to use an insert statement instead of an
update, for example,
you might need to retrieve the last inserted ID in the same secure
transaction
(not command.Transaction) to avoid potential (concurrency) errors.
Since I'm used to write query with SqlServer, I've never had such a
problem, even using textual query in place of stored procedures.

Regards,
J.
 
Back
Top