Problem updating BlOB field

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
MrBishop
Posts: 13
Joined: Tue 31 Oct 2006 19:29

Problem updating BlOB field

Post by MrBishop » Tue 31 Oct 2006 19:48

I just added a MEDIUMBLOB column to my contacts table for saving images. However, when I try to update a record, I get the following exception:

(CoreLab.MySql) Lost connection to MySQL server during query.

I'm trying to save a Byte[] to the field, using this code:

Code: Select all

// Put Byte[] in data field
MemoryStream    stream = new MemoryStream();

pboxMap.Image.Save( stream, 
  System.Drawing.Imaging.ImageFormat.Bmp );
farm.Map = stream.ToArray();

// Create a parameter and add to the DbCommand
DbParameter param = GenerateParameter( "Map", farm );
param.Size = farm.Map.Length;
cmd.Parameters.Add( param );

private DbParameter GenerateParameter(
  string       name,
  FarmInfo  farm
  )
{
  PropertyInfo    property = FARM_TYPE.GetProperty( name );
  DbParameter     parameter = GenericDbFactoryHelper.Parameter;

  parameter.ParameterName = "@" + name;
  parameter.Value = property.GetValue( farm, null );

  return parameter;
}
The update worked fine before I added this field, but I really don't know where to begin with this particular error. I know that the Byte[] is good because I can use it to display the image as such:

Code: Select all

MemoryStream    stream2 = new MemoryStream( farm.Map );
pboxMap.Image = new Bitmap( stream2 );

MrBishop
Posts: 13
Joined: Tue 31 Oct 2006 19:29

Post by MrBishop » Tue 31 Oct 2006 22:38

After posting, I found the thread a few spaces down titled "Net packets out of order / Connection closed during query", which seems very similar to my problem. With regard to these suggestions:
1. Make sure you close all data readers explicitly.
2. If you use Prepare() method of MySqlCommand class, call Dispose() after closing reader.
3. If you use connection pooling, call MySqlConnection.Ping() method before MySqlConnection.Open().
4. If above doesn't help and you use connection pooling, check if you can turn connection pooling off.
1. I'm doing this currently.
2. I don't use Prepare(), but am calling Dispose() on all my Commands (on everything that exposes Dispose, actually).
3. I tried this, but got an exception because the Connection wasn't open. I tried Open'ing the connection first; didn't fix the problem.
4. I'd rather not disable pooling just for this.

I also tried setting Direct to false in the connection string, that didn't help.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 01 Nov 2006 07:31

Please take a look at our Pictures demo project:
%ProgramFiles%\CoreLab\MySQLDirect.NET2\Samples\Pictures\
Last edited by Alexey on Fri 03 Nov 2006 16:01, edited 2 times in total.

MrBishop
Posts: 13
Joined: Tue 31 Oct 2006 19:29

Post by MrBishop » Wed 01 Nov 2006 14:56

Thanks for the reply. I've seen the sample, and it does work. But, it does everything using DataSets. My project is using a custom object to bind one record at a time to a form. I already have the whole project written to use parameterized SQL commands for inserts and updates, I really didn't want to have to rewrite that part using DataSets, I'd prefer to know what's not working with my code.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 02 Nov 2006 06:47

Could you create a small test project to reproduce the problem and send it to the address provided in the Readme file. Include definition of your own database objects. Do not use third party components.

MrBishop
Posts: 13
Joined: Tue 31 Oct 2006 19:29

Post by MrBishop » Thu 02 Nov 2006 18:24

Sent.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 03 Nov 2006 12:08

Your test project works without any problem.
Try to use the latest version of MySQLDirect .NET.

MrBishop
Posts: 13
Joined: Tue 31 Oct 2006 19:29

Post by MrBishop » Fri 03 Nov 2006 23:21

Ok, I got it to work. It turns out that I had my max_allowed_packet_size set too low in MySQL. What was throwing me off was that the sample worked, but my program didn't, so I didn't think it was a problem with the database.

Anyway, Alexey, thanks for the help.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 06 Nov 2006 07:13

Not at all.

Post Reply