Solve the Bug of storing Blob images in MySql: an error occurs when the index exceeds the array limit.

After leaving the network for more than a week, I finally returned to the group. Some netizens raised the following questions:

CYQ. Data framework test: mysql database test (problems may occur when storing blob-type Data). Test the mysql database: If the blog field is updated, the System. Byte [] is always saved.

 

Since the netizens raised the question, try again locally:

First, a local Mysql instance is installed, a navicat management tool is installed, and a new project is created for testing:
A simple test method is to put a button, click it, read the remote image, save it to the database, and then read it and display it on the interface.
The management tool creates a table "D1" at will and obtains three fields: id, img, and name.

The sample code is as follows. Because of the test, the Code is not too standard:

Protected void btnTest_Click (object sender, EventArgs e)
{
String url = "http://www.baidu.com/img/baidu_jgylogo3.gif ";
Bool result = false;
Int id = 0;
String conn = "host = 127.0.0.1; Port = 3306; Database = test; uid = root; pwd = 123456 ";
// String conn = "server =.; database = demo; uid = SAS; pwd = 123456 ";
Using (MAction action = new MAction ("d1", conn ))
{
WebClient wc = new WebClient ();
Try
{
// If (action. Fill (2 ))
//{
Byte [] data = wc. DownloadData (url );
Action. Set ("img", data );
Action. Set ("name", "ldf ");
Result = action. Insert (InsertOp. ID );
Id = action. Get <int> ("id ");
//}
}
Catch (Exception err)
{
Log. WriteLogToTxt (err );
}
}
If (result) // open another read.
{
Using (MAction action = new MAction ("d1", conn ))
{
If (action. Fill (id ))
{
Response. BinaryWrite (action. Get <byte []> ("img "));
}
}
}
}

 

Solution and debugging:

After local testing and debugging, it is found that an exception is thrown when a parameter of the blob type is assigned: The index exceeds the limit of the array.
The exception was too confusing. I searched the internet and some people said that the Mysql. dll version was a problem. I spent a csdn point for a version 6.2.3, but it was still unavailable.
Then debug it unconsciously and estimate my luck. I found out the problem. I can only say that I am lucky.

Take a look at a small piece of code and assign values to parameters. This method is similar when multiple databases support parameters:

DbParameter para = _ fac. CreateParameter (); // obtain the current database type parameter para. ParameterName = parameterName through the factory advertisement; para. Value = value = null? DBNull. Value: value; para. DbType = dbType;

If (size>-1) // set the length. {Para. Size = size;} para. Direction = direction;

This code is actually very simple:

A parameter is generated, and the parameter name, parameter value, parameter type, parameter size, and parameter direction are assigned.
Mysql has some bugs in dealing with the parameter Size assignment problem.

When I skip the value assignment to para. Size during debugging, it turned out to be normal.

The code is changed to the following:

If (dbType! = DbType. Binary & size>-1) // mysql cannot set the length. Otherwise, an error occurs when the index exceeds the array limit.

Then I tested other databases and found that the specified size is normal. Finally, I came to the conclusion:

After tests, it is normal for other database types to assign values to the Size value. Only Mysql is faulty. Therefore, when writing a framework that supports multiple databases, many problems may occur from time to time, don't tangle, always solve it.

 

Address: http://www.cyqdata.com/cyq1162/article-detail-54329