• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1287
  • Last Modified:

Copy a blob from one table to another inside a stored procedure

I'm running Firebird 2.1.1 & my applicaton written in .net 2.0
I've become unstuck when trying to write a stored procedure to copy some blob data (a tiff image) from one table to another. For example:

Table 1 contains a field called ImageScan , Type blob , Size 80, Subtype Binary
and row 4  of this contains my image

Table 2 contains a field called FiledScan , Type blob , Size 80 Subtype Binary
and row 6 is where I want the image to be copied to

I dont want to copy the image to the client and have the client reinsert the blob into the new table due to network overhead.

The SP would start with something like this:
Create Procedure CopyBlob (OldImageNumber integer, NewImageNumber integer)
where oldimage is the row number of the existing image and NewImageNumber is the row number of the new image.

Thanks in advance Jon


0
JonR01
Asked:
JonR01
1 Solution
 
dprochownikCommented:
I'm not sure what do you mean by "row number". In databases rows are not identify by  row number but by some IDs. Here you have simple script creates procedure which do what you want, but after I changed "row number" to "row id"

SET TERM ^ ;

CREATE OR ALTER PROCEDURE CopyBlob (
    OldRowID integer,
    NewRowID integer)
as
begin
  Update Table2 b
  set b.fieldscan = (select first 1 a.imagescan from Table1 a where a.id=:OldRowID)
  where b.id = :NewRowID;
end^

SET TERM ; ^

GRANT ALL ON Table1 TO PROCEDURE CopyBlob;

GRANT ALL ON Table2 TO PROCEDURE CopyBlob;

GRANT EXECUTE ON PROCEDURE CopyBlob TO PUBLIC;

Of course I assumed that there is a row with specified ID in Table2, before you use procedure. If not you can use "Insert" or "Insert or update" syntax instead "Update".
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now