[RESOLVED] Copy BLOB data in Query?

[fuzzy1]

Hey All,

Can it be done?
Trying to copy BLOB data between tables, but...while both table.fields are LONGBLOB fields, ONLY the file size is copied i.e [BLOB - 252.6KB]

UPDATE orders, contract_pdfs
SET orders.contract_pdf = contract_pdfs.content
WHERE orders.id = contract_pdfs.record_id

[Roger_Ramjet]

Bit pointless isn't it, duplicating longblob data like that?

[fuzzy1]

Well hey there Roger!:rolleyes:

Silly me -- thinking I'd spare you the lengthy dissertation on why this would be nice to be able to do -- given a particular circumstance. How nice of you to question my motives. The SHORT explanation would be that the objective (the POINT) is to MOVE the data into a more appropriate table, and delete the original table all together, so I guess the answer to your question is ... No???

But thanks for the constructive input just the same:eek:

[Roger_Ramjet]

OK, so you do have a good reason. Just wanted to be sure that the effort of fixing this would not be misplaced: plenty of folks around here would not know any better than to store multiple copies of data instead of joining tables.

Now, on the face of it there is nothing wrong with that query. Thing is, blobs are not actually stored in the table column, they are stored as separate objects and the table contains a pointer to them. I was not aware that your sort of query would not work, I suppose that this is the reason.

One thing I would try would be to select the blob in a subquery to see if this forces the update. Interested to find out the solution myself.

[Bunkermaster]

you might to have a look at:
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-select-into-table.html

good luck

[fuzzy1]

Roger Ramjet wrote:

blobs are not actually stored in the table column, they are stored as separate objects and the table contains a pointer to them. I was not aware that your sort of query would not work, I suppose that this is the reason.

Thanks Roger, BM, this was my initial thought on the matter as well upon discovering that the script I was using to retrieve and open the pdfs would not open from the new (coppied) table locaction. Turns out however that the solution (A solution) lay in the fread script which would read the data from original_table.field but not new_table.new_field. Currious, indeed but haven't the time to investigate further just for knowledge sake.

A few minor adjustments to freed script and all is well.

To reiterate... my initial query worked fine... sort of.

Thanks