CODEWITHSUNDEEP
c#.netmysqlodbc
Chinjoo
Chinjoo

Reputation: 2832

Memory allocation error from MySql ODBC 5.1 driver in C# application on insert statement

I have a .NET Wndows application in C#. It's a simple Windows application that is using the MySql 5.1 database community edition. I've downloaded the MySql ODBC driver and have created a dsn to my database on my local machine. On my application, I can perform get type queries without problems, but when I execute a given insert statement (not that I've tried doing any others), I get the following error:

{"ERROR [HY001] [MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Memory allocation error"}

I'm running on a Windows XP machine. My machine has 1 GB of memory. Anyone have any ideas? See code below

OdbcConnection MyConn = DBConnection.getDBConnection();
    int result = -1;
    try
    {
        MyConn.Open();
        OdbcCommand myCmd = new OdbcCommand();
        myCmd.Connection = MyConn;
        myCmd.CommandType = CommandType.Text;
        OdbcParameter userName = new OdbcParameter("@UserName", u.UserName);
        OdbcParameter password = new OdbcParameter("@Password", u.Password);
        OdbcParameter firstName = new OdbcParameter("@FirstName", u.FirstName);
        OdbcParameter LastName = new OdbcParameter("@LastName", u.LastName);
        OdbcParameter sex = new OdbcParameter("@sex", u.Sex);
        myCmd.Parameters.Add(userName);
        myCmd.Parameters.Add(password);
        myCmd.Parameters.Add(firstName);
        myCmd.Parameters.Add(LastName);
        myCmd.Parameters.Add(sex);
        myCmd.CommandText = mySqlQueries.insertChatUser;
        result = myCmd.ExecuteNonQuery();
    }
    catch (Exception e)
    {
             //{"ERROR [HY001] [MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Memory  
             // allocation error"} EXCEPTION ALWAYS THROWN HERE 
    }
    finally
    {
        try
        {
            if (MyConn != null) MyConn.Close();
        }
        finally { }
    }

Upvotes: 6

Views: 6542

Answers (3)

Windgate
Windgate

Reputation: 428

This exception also can be raised if you try to insert invalid chars in a VARCHAR field. In example, if the string is generated by a UNIX machine and has end of line characters '\n'. You can just replace that problematic characters to Windows style, or viceversa, or just delete it if you don't want to store end of lines.

You can check the strings, and if any of them has end of line characters, try to repeat the insert deleting them. If it works, the problem are these characters.

Upvotes: 0

Joe
Joe

Reputation: 21

Just for the sake of completeness, Chinjoo's SQL statement would likely be something like this:

mySqlQueries.insertChatUser = "insert into ChatUsers (UserName, Password, FirstName, LastName, sex) values (?,?,?,?,?);";

This is known as a parameterized insert where each question mark represents one of his parameters. In this simple example the order of the parameters in the parameter collection in code must match the order of the column names in the SQL statement.

While less elegant than using a function, the fix for his null problem would look something like this for one of his parameters:

OdbcParameter LastName = new OdbcParameter("@LastName", u.LastName);

is replaced with

// if the value is "null" return DBNull, else just the value
OdbcParameter LastName = new OdbcParameter("@LastName", 
(u.LastName == null) ? System.DBNull.Value : (object)u.LastName);

At least in my code (which is slightly different) the inner cast to type object is required since otherwise the compiler isn't sure what type the ?: operator should return.

Hope this helps anyone who is relatively new to parameterization, etc.

No criticism of Chinjoo implied at all--his posting helped me out! Just thought I'd share for the less-experienced. I'm by no means expert so take everything I say with a grain of salt.

Upvotes: 2

Chinjoo
Chinjoo

Reputation: 2832

It was because some fields accept null, I had passed them as null where they should be passed as DBNull.Value. For all the fields which allow null should be checked for null and if found null, DBNull.Value should be passed.

Upvotes: 9

Related Questions