#24 Parameters values not propagated to DB table

[Christopher Bartling]

I have the following code in a NUnit 2.0 test case:

private const string INSERT_SQL =
"INSERT INTO enroll_queue (last_name, first_name,
middle_name, version) " +
"VALUES (@last_name, @first_name, @middle_name,
@version)";

/// <summary>
/// Test case for the retrieving binary data.
/// </summary>
[Test]
public void InsertBinaryDataIntoDatabase() {
byte[] imageData = ImageUtils.GetBinaryData("test.jpg");
Assertion.AssertNotNull("Binary data is null.",
imageData);
Assertion.Assert("Binary data array is empty.",
imageData.Length > 0);

IDbConnection conn = null;
IDbCommand command = null;
try \{
    // Use the MySQL factory to get specific

implementation types
// for the database connection and command.
conn =
MySQLFactory.GetConnection(Constants.NEXUS_DB_CONNECTION_STRING);

    command = conn.CreateCommand\(\);

    command.Parameters.Add\(new

MySQLParameter("@last_name", MySQLDbType.VarChar,
"Bartling"));
command.Parameters.Add(new
MySQLParameter("@first_name", MySQLDbType.VarChar,
"Christopher"));
command.Parameters.Add(new
MySQLParameter("@middle_name", MySQLDbType.VarChar,
"Edward"));
command.Parameters.Add(new MySQLParameter("@version",
MySQLDbType.Int24, 1));

    command.CommandText = INSERT\_SQL;

    int rowsAffected = command.ExecuteNonQuery\(\);
    Assertion.Assert\("Insert failed.", rowsAffected > 0\);
\} finally \{
    if \(conn \!= null\) conn.Close\(\); 
\}

}

My table schema follows:

CREATE TABLE enroll_queue (
id INT NOT NULL
AUTO_INCREMENT,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
middle_name VARCHAR(20) NOT NULL,
raw_facial_photo MEDIUMBLOB NULL,
version INTEGER NOT NULL,
PRIMARY KEY (id)
)

The insertion works, but all the VarChar parameters are
empty strings in the database, instread of the values
that I set when adding the parameters. The version
value is being set and the row is created with a new
autoincremented key value.

[Christopher Bartling]

Logged In: YES
user_id=189992

BTW, this is using version 0.68 of the ByteFX.Data managed
provider. I should also clarify my example above. It is
retrieving byte data but it is not using it in the insert.
Trying to get past the VarChar problem first, so I'm leaving
the raw_facial_photo null when performing the insert.

[Nobody/Anonymous]

Logged In: NO

I believe this is fixed in cvs. You may elect to compile a
fresh copy out of cvs or wait for 0.69 to come out. I'm trying
to get 0.69 out very soon but I haven't had alot of time.

There were significant problems with varchar parms in 0.68

Thanks
Reggie

[Nobody/Anonymous]

Logged In: NO

I get the same thing. If a mysqlcommand object is created,
and then parameters are added, the parameters are ignored
and a table of NULLS is inserted into the database.

e.g.
cmd = new mysqlcommand("INSERT INTO atable (Col1)
VALUES (@Col1)", cn)
cmd.parameters.add("@Col1", "test")
cmd.executenonquery

the above would insert a new row, but with NULL in Col1
If the database did not allow NULL columns, then an
exception will be thrown.