Copying Binary data from one table to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

normanp - 06 Mar 2008 05:18 GMT

Hi,

I have these tables with binary data stored in tinyblobs and blobs in MySql.
In my application I sometimes need to copy them to another table. Not all the
data, maybe just a column or two. So I first select the data into a record
set and then loop and insert it (its the only way can do it)

Eg:
Select blah blah

Do while not rs.eof
insert here
rs.movenext
loop

I thought this would be easy, but its not. No matter what I do I get:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch

I suppose copying binary data is not easy. So just how do I do this? Whats
the right way to copy Binary Data from one table to another.

Most of you might have done this before. The problem is with the binary
stuff. Thats the thing that causes the error. Please can you help.

Thanx

Reply to this Message

Bob Barrows [MVP] - 06 Mar 2008 11:53 GMT

> Hi,
>
[quoted text clipped - 3 lines]
> select the data into a record set and then loop and insert it (its
> the only way can do it)

I've never worked with mysql, but this statement would make me absolutely
reject it as a database-of-choice if I was ever considering it. Are you sure
this is the only way? Can't you do a simple "INSERT INTO tablename (<column
list>) SELECT <column list> FROM othertable" in MySQL?

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

normanp - 06 Mar 2008 12:08 GMT

Hi Bob,

Thanks. Unfortunately there is no other way for me to do this. I have to
select then insert. Because sometimes i may need a few columns and sometime
others. Its hard for me to put it in words. But I think you'll understand.

By why will binary stuff not work?

Norman

> > Hi,
> >
[quoted text clipped - 8 lines]
> this is the only way? Can't you do a simple "INSERT INTO tablename (<column
> list>) SELECT <column list> FROM othertable" in MySQL?

Reply to this Message

Evertjan. - 06 Mar 2008 12:23 GMT

=?Utf-8?B?bm9ybWFucA==?= wrote on 06 mrt 2008 in
microsoft.public.inetserver.asp.db:

>> > Hi,
>> >
[quoted text clipped - 9 lines]
>> simple "INSERT INTO tablename (<column list>) SELECT <column list>
>> FROM othertable" in MySQL?

[Please do not toppost on usenet]

> Thanks. Unfortunately there is no other way for me to do this. I have
> to select then insert. Because sometimes i may need a few columns and
> sometime others. Its hard for me to put it in words. But I think
> you'll understand.
>
> By why will binary stuff not work?

However:

>>> Error Type:
>>> Microsoft VBScript runtime (0x800A000D)
>>> Type mismatch

seems to suggest, the error is not database related, but vbs code.

Start debugging!

Signature

Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Reply to this Message

normanp - 06 Mar 2008 12:38 GMT

Debug what? Heres the testing thing i've broken it down to.

The original table
[code]
CREATE TABLE `binary_a` (
`org_a` tinyblob NOT NULL,
`org_b` blob NOT NULL,
`org_c` tinyint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
[/code]

The one i'm trying to copy to
[code]
CREATE TABLE `binary_b` (
`copy_a` tinyblob NOT NULL,
`copy_b` blob NOT NULL,
`copy_c` tinyint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
[/code]

The Sql to create the binary stuff:
[code]
insert into binary_a (org_a,org_b,org_c) values
(aes_encrypt('norman','mongo'),aes_encrypt('mink','mongo'),1)
[/code]

The ASP:
[code]
set original = Cecexe.execute("select org_a,org_b,org_c from binary_a")

if original.eof and original.bof then
Response.Write("Did not find data to print")
else
do while not original.eof
'Response.Write original("org_a")&" "&original("org_b")&" "&original("org_c")
CecExe.Execute("insert into binary_b (copy_a,copy_b,copy_c) values
('"&original("org_a")&"', '"&original("org_b")&"', "&original("org_c")&") ")
original.MoveNext
loop

End If
[/code]

> =?Utf-8?B?bm9ybWFucA==?= wrote on 06 mrt 2008 in
> microsoft.public.inetserver.asp.db:
[quoted text clipped - 31 lines]
>
> Start debugging!

Reply to this Message

Bob Barrows [MVP] - 06 Mar 2008 12:37 GMT

Actually, you've still lost me.
This is one of the few situations where I can recommend dynamic sql (but
only if you use parameters for user inputs - in other words don't ask the
user for a list of column names via a textbox - have him select them from a
listbox which uses column index as the value submitted, and use the
sebmitted index values to get the column names from an array in your
server-side code).
So you need col1 and col 3:

sql = "insert into table (col1,col3) select col1,col3 from othertable"
cn.execute sql,,129

If you need col 2, col3,col4 ... well, hopefully, you get the idea.

Or, if you need to update existing rows, use similar logic to build your
update statements. I'm not sure of the syntax used in MySQL to update a
table with values retrieved from another table, but you should be able to
find it in the documentation.

If you insist on using an inefficient cursor, you may need to look into
using AppendChunk. Look it up in the ADO documentation:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx

And, as Evertjian says: the error is a vbscript error, not an ADO error. You
need to narrow down which statement in your code is actually throwing that
error.

> Hi Bob,
>
[quoted text clipped - 26 lines]
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

normanp - 06 Mar 2008 12:53 GMT

This is the part that throws up the error.

CecExe.Execute("insert into binary_b (copy_a,copy_b,copy_c) values
('"&original("org_a")&"', '"&original("org_b")&"', "&original("org_c")&") ")

Actually i'm using these 3 cols only for testing. its a lot more complex in
the real thing.

> Actually, you've still lost me.
> This is one of the few situations where I can recommend dynamic sql (but
[quoted text clipped - 53 lines]
> >> I don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"

Reply to this Message

Bob Barrows [MVP] - 06 Mar 2008 14:09 GMT

> This is the part that throws up the error.
>
> CecExe.Execute("insert into binary_b (copy_a,copy_b,copy_c) values
> ('"&original("org_a")&"', '"&original("org_b")&"',
> "&original("org_c")&") ")

Jeez, how about a little whitespace, if only for readability! :-)

> Actually i'm using these 3 cols only for testing. its a lot more

Assign the sql statement to a variable and write the value of the
variable to Response so you can see what your concatenation is actually
doing. Your goal is to create a sql statement that can be copied from
the browser window and executed as-is using whatever the native query
execution tool for MySQL is.

Incidently, you should absolutely change your coding practice here: by
default, ADO expects the sql statement being sent to the database to
return a resultset, so behind the scenes, it creates a recordset object
to retrieve that result. You need to tell ADO that you are not expecting
a resultset so it does not waste cpu and memory creating an object that
will not be used. You do this by passing 129 as the third argument to
the Execute statement. This of course means you need to get rid of the
unneeded parentheses around your method argument:

sql="insert into binary_b (copy_a,copy_b,copy_c) values ('" & _
original("org_a") & "', '" & original("org_b") & "'," & _
original("org_c") & ")"
Response.Write sql
CecExe.Execute sql,,k129

Hmmm ... now that I think about it, I doubt that your binary data is
getting successfully converted to a string to be used in your dynamic
sql statement. The Response.Write should confirm this. I think if you
insist on using this approach (read data from recordset and use it in an
insert statement), that you are going to need to use parameters and an
explicit Command object instead of dynamic sql. Something like this:

dim sql,cmd,arParms
sql="insert into binary_b (copy_a,copy_b,copy_c) values (?,?,?)"
arParms=array(original("org_a"),original("org_b"), _
original("org_c"))
set cmd=createobject("adodb.command")
set cmd.activeconnection = CecExe
cmd.commandtext = sql
cmd.commandtype=1
cmd.execute ,,128

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

Bob Barrows [MVP] - 06 Mar 2008 14:17 GMT

> dim sql,cmd,arParms
> sql="insert into binary_b (copy_a,copy_b,copy_c) values (?,?,?)"
[quoted text clipped - 5 lines]
> cmd.commandtype=1
> cmd.execute ,,128

Oops - I hit Send too soon. If this fails, then you are going to need to
explicitly create parameters and probably use GetChunk to get the binary
data from the recordset and AppendChunk to put the binary data into the
binary parameter ... are you sure you don't want to bypass using the
recordset?

Oh! and one more thing. I think in this case you need to be more
explicit:

sql="insert into binary_b (copy_a,copy_b,copy_c) values (?,?,?)"
arParms=array(original("org_a").value, _
original("org_b").value, _
original("org_c").value)

Signature

Reply to this Message

normanp - 06 Mar 2008 15:21 GMT

Gosh. Thats a lot of information and effort you've given/put in. Many thanks
indeed.

I somehow came up with another idea.

I converted all tinyblob and blob cols to tinytext and text, and the i'll
encrypt the thing and hex it and insert it. That way the binary data will be
text data, and I can easily copy between tables. No issues.

What do you say? :-)

Norman

> > dim sql,cmd,arParms
> > sql="insert into binary_b (copy_a,copy_b,copy_c) values (?,?,?)"
[quoted text clipped - 19 lines]
> original("org_b").value, _
> original("org_c").value)

Reply to this Message

Bob Barrows [MVP] - 06 Mar 2008 16:31 GMT

> Gosh. Thats a lot of information and effort you've given/put in. Many
> thanks indeed.
[quoted text clipped - 9 lines]
>
> Norman

What can I say? It's your project. :-)

Signature

Reply to this Message

Webmaster Forum / ASP / Database Access / March 2008

Copying Binary data from one table to another