Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-08-07
9
Medium Priority
?
1,215 Views
Last Modified: 2013-12-17
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!
0
Comment
Question by:calvinklein1
  • 4
  • 2
  • 2
8 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19651363
Try changing this:
sqlParams[0] = new MySqlParameter("?UserName", MySqlDbType.VarChar);
to this:
sqlParams[0] = new MySqlParameter("@UserName", MySqlDbType.VarChar);
0
 

Author Comment

by:calvinklein1
ID: 19651518
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.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19651751
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?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 22

Expert Comment

by:JimBrandley
ID: 19651778
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19651802
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...
0
 

Author Comment

by:calvinklein1
ID: 19653195
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.  :(
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19653230
>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...
0
 
LVL 22

Accepted Solution

by:
JimBrandley earned 2000 total points
ID: 19659510
"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. "

I think most people do it that way to minimize the time the connection is held open. In our database layer, the last thing we do before executing the query is to open the connection. Then we close it as soon as possible after control is returned to the C# code.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question