Syntax error?

14 Feb 2006 4:00 PM

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.

14 Feb 2006 4:20 PM

Cowboy (Gregory A. Beamer) - MVP

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!
***************************

Show quote

"Joneleth" wrote:

> 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.
>
>

15 Feb 2006 5:00 PM

Joneleth

Cowboy (Gregory A. Beamer) - MVP wrote:

> 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 ;->).

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.

14 Feb 2006 4:38 PM

William (Bill) Vaughn

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.
__________________________________

Show quote

"Joneleth" <Joneleth***@hotmail.com> wrote in message
news:1139932811.148978.224930@o13g2000cwo.googlegroups.com...
> 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.
>