C# MySQL Blob field - Cannot copy zip file stored in blob to another table

Question

i have a zip file stored in a table by a third party application which i have no control over. I do however have access to the MySQL DB.

What i want to do is do a SELECT statement to retrieve a blob field and copy this record into another table. But on the other side, i do see the blob field on the far side but it is not a zip file, its a text file that says System.Byte[] and thats it - anyone any ideas on what is causing this and how to fix it?

heres what i have below - again any help greatly appreciated :)

OdbcCommand broadcastSelect = new OdbcCommand("select * from exchange where status='1' and type='UPDATE'", cloud);
                OdbcDataReader DbReader = Select.ExecuteReader();
                int fCount = DbReader.FieldCount;
                String type = "";
                String filename = "";
                byte[] data = null;
                int status = 0;

                while (DbReader.Read())
                {
                    if (DbReader.IsDBNull(0))
                    {
                        type = "BLANK";
                    }
                    else
                    {
                        type = (string)DbReader[0];
                    }
                    if (DbReader.IsDBNull(1))
                    {
                        filename = "BLANK";
                    }
                    else
                    {
                        filename = (string)DbReader[1];
                    }
                    if (DbReader.IsDBNull(2))
                    {
                        data = new byte[1];
                    }
                    else
                    {
                        data = (byte[])DbReader[2];
                    }
                    if (DbReader.IsDBNull(3))
                    {
                        status = 0;
                    }
                    else
                    {
                        status = (int)DbReader[3];
                    }

                    OdbcCommand Copy = new OdbcCommand("INSERT INTO exchange(type,filename,data,status) VALUES('" + type + "','" + filename + "','"
                        + data + "','" + status + "')", local);
                    Copy.ExecuteNonQuery();

                }

ps - know i should parametrise the statement, just havent got around to it yet :) — JazziJeff, Jul 13 '12 at 12:05
If all you want to do is to copy the existing entry to another table, you could just do the following: `OdbcCommand Copy = new OdbcCommand("INSERT INTO exchange(type,filename,data,status) select (type,filename,data,status) from exchange where status='1' and type='UPDATE'", local); Copy.ExecuteNonQuery();` — Bernhard Kircher, Jul 13 '12 at 12:17
problem is that the table exists on another server - if only it were that simple ;) — JazziJeff, Jul 13 '12 at 12:20
Sorry, didn't see that. What exactly do you mean with "a zip file, its a text file"? I suppose all data is stored as byte[] and if you know it's a zip file, you have to use a C# api to translate the raw data (byte[]/stream) to the correct object. EDIT: I am so stupid: see DynamicVarialbe's answer. ths should fix it — Bernhard Kircher, Jul 13 '12 at 12:26
Hi bernard, i saw the zip file was 1kb when i extracted it at the far end, just tried changing the extension to .TXT and i saw the System.Byte[] text :) - trying to figure out how to implement DynamicVariables answer but not quite sure how to yet :) — JazziJeff, Jul 13 '12 at 12:44

Hiren Visavadiya · Accepted Answer · 2012-07-13T14:46:32.173

use sql parameter for inserting the binary data.

OdbcParameter param = new OdbcParameter("@file", SqlDbType.Binary);

---Updated I hope below given code will be helpful to you.

 OdbcCommand broadcastSelect = new OdbcCommand("select * from exchange where status='1' and type='UPDATE'", cloud);
        OdbcDataReader DbReader = Select.ExecuteReader();
        int fCount = DbReader.FieldCount;
        String type = "";
        String filename = "";
        byte[] data = null;
        int status = 0;
        OdbcParameter param = null;
        while (DbReader.Read())
        {
            if (DbReader.IsDBNull(0))
            {
                type = "BLANK";
            }
            else
            {
                type = (string)DbReader[0];
            }
            if (DbReader.IsDBNull(1))
            {
                filename = "BLANK";
            }
            else
            {
                filename = (string)DbReader[1];
            }
            if (DbReader.IsDBNull(2))
            {
                param = new OdbcParameter("@file", SqlDbType.Binary);
                param.DbType = DbType.Binary;
                param.Value = new byte[1];                
                command.Parameters.Add(param); 
            }
            else
            {
                param = new OdbcParameter("@file", SqlDbType.Binary);
                param.DbType = DbType.Binary;
                param.Value = (byte[])dbReader[2];
                param.Size = ((byte[])dbReader[2]).Length; 
                command.Parameters.Add(param); 
            }
            if (DbReader.IsDBNull(3))
            {
                status = 0;
            }
            else
            {
                status = (int)DbReader[3];
            }

            OdbcCommand Copy = new OdbcCommand("INSERT INTO exchange(type,filename,data,status) VALUES('" + type + "','" + filename + "',@file,'" + status + "')", local);
            Copy.ExecuteNonQuery();

excuse my ignorance :) but how exactly would i assign the value from the retrieval to this parameter? thanks — JazziJeff, Jul 13 '12 at 12:40
just figured it out :) well pleased, thanks guys and thaks for the link, i'll have a general read up on this too — JazziJeff, Jul 13 '12 at 12:59
also had to add Parameters.Clear() after the statement executed as this caused me some issues when it would kick back in from a timer :) hope this helps someone else! — JazziJeff, Jul 13 '12 at 14:54

C# MySQL Blob field - Cannot copy zip file stored in blob to another table

1 Answers1