Hi guys,

I have loads of SQL Server experience, but not a lot of mySql.

I have created this proc:

DROP PROCEDURE IF EXISTS
`up_GetLatestFirmwareVersion_SelectBy_TargetDevice Id`;
DELIMITER $$
CREATE PROCEDURE up_GetLatestFirmwareVersion_SelectBy_TargetDeviceI d
(IN inTargetDeviceId VARCHAR(4),
IN inVariant VARCHAR(5))
BEGIN
SELECT
firmware_version_id,
version_major,
version_minor,
version_variant,
version_pre_release,
version_variant IS NULL AS variant_isnull,
version_pre_release IS NULL AS pre_release_isnull
FROM firmware_versions fv
INNER JOIN target_device td ON td.target_device_id =
fv.target_device_id
WHERE td.devid = inTargetDeviceId
AND ((version_variant = inVariant) OR (version_variant IS NULL AND
inVariant IS NULL))
ORDER BY
version_major DESC,
version_minor DESC,
variant_isnull ASC,
pre_release_isnull DESC
LIMIT 1;
END$$
DELIMITER ;


I call it like this:
CALL up_GetLatestFirmwareVersion_SelectBy_TargetDeviceI d ('1579',
'S');

And it works well.

But when I call it lik this:
CALL up_GetLatestFirmwareVersion_SelectBy_TargetDeviceI d
(inTargetDeviceId='1579', inVariant='S');

It fails with "Unknown column 'inTargetDeviceId' in 'field list'"

My .Net application needs to pass the names:

MySqlCommand command = _conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText =
"up_GetLatestFirmwareVersion_SelectBy_TargetDevice Id";
command.Parameters.Add(new
MySqlParameter("inTargetDeviceId", 1));
command.Parameters.Add(new MySqlParameter("inVariant",
""));

But it seems the proc doesn't want me to name my parameters. What am I
doing wrong?

Thanks.

In article <5b43af12-f7cd-423c-9508-ffb473109655 (AT) z34g2000pro (DOT) googlegroups.com>,
Cralis <craiglister74 (AT) gmail (DOT) com> wrote:
AFAIK, MySQL does not support named parameters.

Well all the examples I just found by googling (which anyone can do!)
show the paramater names prefixed with a question mark, e.g.

MySqlParameter("?inTargetDeviceId", 1)

Perhaps the ? is a .NET way of telling it to suppress the naming of
the parameters in the procedure call. Try it!

Disclaimer: I know nothing about .NET

Cheers
Tony

--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

El 04/10/2010 22:28, Cralis escribió/wrote:
I presume it's just trying to evaluate the boolean expression. As far as
I know, argument values must be provided in the same order the procedure
defines them; there isn't an alternative syntax.


I can't speak C# or whatever lang it is. But all DB libraries with named
parameters support I've ever used provide it as a mean to inject custom
values into SQL queries. Despite the name, it has nothing to do with the
parameters of stored routines.

E.g., you write this query:

SELECT id, name
FROM users
WHERE login=:login

.... and you use the library to feed parameter "login" with the
appropriate value. I bet you have to do something similar:

-- Ignore the syntax, it's just a guess!
CALL up_GetLatestFirmwareVersion_SelectBy_TargetDeviceI d
(:inTargetDeviceId, :inVariant)



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--