Link to home
Start Free TrialLog in
Avatar of calvinklein1
calvinklein1

asked on

Can not correctly call mysql stored procedure via .net C#

Hi.

I'm use to writing stored procedures in sql server and not venturing into mysql.  But i'm getting this error when I call the sproc from my .net code:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use"
BUT if I call my sproc from mysql browser it works fine.   Here's my create script for the sproc:
**********
DELIMITER $$

DROP PROCEDURE IF EXISTS `uspValidateUser`$$

CREATE   PROCEDURE `uspValidateUser`
(
    `@UserName` VARCHAR(50)
)
BEGIN

IF Not Exists (SELECT `UserId`
             FROM   `Users`
             WHERE  `UserName` = `@UserName`) THEN
        SELECT 0 As `Error`;
    ELSE

        /********************************************
        * UPDATE LastLoginDate FIELD
        ********************************************/

        UPDATE  `Users`
           SET  `LastLoginDate` = NOW()
         WHERE  `UserName` = `@UserName`;

        /********************************************
        * GET return
        ********************************************/

        SELECT
                U.`UserId`,
                U.`UserName`,
                U.`Password`,
                U.`Salt`,
                U.`UserDemographicsId`,
                R.`Role`,
                U.`LastLoginDate`,
                U.`LastUpdatedDate`,
                U.`LastUpdatedBy`,
                U.`CreatedDate`,
                U.`CreatedBy`
        FROM    `Users` U
        JOIN    `Roles` R
          ON    U.`RoleId` = R.`RoleId`
        WHERE   U.`UserName` = `@UserName`;

    END IF;

END$$
**********

Now here is my C# code (using the .net connector supplied by Mysql):
**********
        public User ValidateUserCredentials(string username)
        {
            User user = null;
            const string PROC_NAME = "uspValidateUser";

            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("?UserName", MySqlDbType.VarChar);
            sqlParams[0].Direction = ParameterDirection.Input;
            sqlParams[0].Value = username;

            using(MySqlDataReader sqlReader = MySqlHelper.ExecuteReader(_ConnectionString, PROC_NAME, sqlParams))
            {
                if (sqlReader.Read())
                {
                    user = new User(sqlReader.GetInt32(sqlReader.GetOrdinal("UserId")),
                                        sqlReader.GetString(sqlReader.GetOrdinal("UserName")),
                                        sqlReader.GetString(sqlReader.GetOrdinal("Password")),
                                        sqlReader.GetString(sqlReader.GetOrdinal("Salt")),
                                        sqlReader.GetString(sqlReader.GetOrdinal("Role")),
                                        sqlReader.GetInt32(sqlReader.GetOrdinal("UserDemographicsId")),
                                        sqlReader.GetDateTime(sqlReader.GetOrdinal("LastLoginDate")),
                                        sqlReader.GetString(sqlReader.GetOrdinal("CreatedBy")),
                                        sqlReader.GetDateTime(sqlReader.GetOrdinal("CreatedDate")),
                                        sqlReader.GetString(sqlReader.GetOrdinal("LastUpdatedBy")),
                                        sqlReader.GetDateTime(sqlReader.GetOrdinal("LastUpdatedDate")));
                    return user;
                }
            }

            return null;
        }
**********

I have tried different alterations of the param syntax within the c# code but yet nothing is producing results.    I don't think my sproc code is wrong because I'm able to successfully run it in the mysql browser but maybe someone knows something I don't.  thanks for your help!
Avatar of JimBrandley
JimBrandley
Flag of United States of America image

Try changing this:
sqlParams[0] = new MySqlParameter("?UserName", MySqlDbType.VarChar);
to this:
sqlParams[0] = new MySqlParameter("@UserName", MySqlDbType.VarChar);
Avatar of calvinklein1
calvinklein1

ASKER

yeah I have tried that before and just did it now but still no luck.   I have tried for the params these alterations:
sqlParams[0] = new MySqlParameter("@UserName", MySqlDbType.VarChar);
sqlParams[0] = new MySqlParameter("?@UserName", MySqlDbType.VarChar);
sqlParams[0] = new MySqlParameter("?UserName", MySqlDbType.VarChar);

and still no luck :(

Do you see any issues with my sproc?   It runs fine in mysql browser but .net doesn't like it.
Sorry - I do not see anything else that looks wrong to me - proc looks good and the code to invoke it. You could try setting the parameter.Size = 50, but I do not think that's required for in params. Is this the first time you have tried invoking a stored proc?
I have used stored procs with both Oracle and SQL Server 2005, but never MySQL. Maybe someone who knows this server will stop by and spot what I have been unable to see.

Jim
Avatar of Guy Hengel [angelIII / a3]
I don't see any issues so far.
just to try out, could you make the procedure with just the SELECT statement, to see if that works, ie if the IF EXISTS and/or the UPDATE are the "problem" to get the results back...
So in the SQL Server world we have a tool called SQL Profiler that allows a DBA to see what SQL commands are being sent to the database.   Do we have any tool like that for MySQL 5?   It would be nice to see if .net is screwing up the SQL for calling the sproc or not.   I also don't see a lot of examples on the web with this method of calling a mysql sproc.  A lot of them create a MysqlCommand first and then open a connection with that command.  I haven't tried that yet but I would hate to have to do that because I wouldn't be able to use my Using statement.  :(
>Do we have any tool like that for MySQL 5?
the MySQL Administrator tool can see the current queries.
however, that only works for long-runing queries, but you cannot copy the sql...
ASKER CERTIFIED SOLUTION
Avatar of JimBrandley
JimBrandley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial