Subject | Re: [firebird-support] Copy row to row in stored procedure |
---|---|
Author | Valeri Mytinski |
Post date | 2009-03-24T09:27:42Z |
Thanks for all suggestions.
Finally I decided to use EXECUTE STATEMENT feature:
CREATE OR ALTER PROCEDURE "ProcName"(
srcKey varchar(16)
, dstKey varchar(16)
)
RETURNS (
...
)
AS
DECLARE VARIABLE fields varchar(32000) = '';
DECLARE VARIABLE vals varchar(32000) = '';
DECLARE VARIABLE aField varchar(31) = '';
BEGIN
...
-- Start fields and values lists with PK for destination row
fields = '"Key"';
vals = '''' || trim(:dstKey) || '''';
FOR
SELECT trim(RDB$FIELD_NAME)
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = 'Object'
AND trim(RDB$FIELD_NAME) <> 'Key' -- All fields except PK
INTO :aField
DO
BEGIN
-- Compose fields and values lists
fields = :fields || ', "' || :aField || '"';
vals = :vals || ', "' || :aField || '"';
END
EXECUTE STATEMENT 'INSERT INTO "Object" (' || trim(:fields)
|| ') SELECT ' || trim(:vals)
|| ' FROM "Object"'
|| ' WHERE "Key" = ''' || trim(:srcKey) || '''';
...
All fields are copied this way except BLOB subtype BINARY that is
always NULL in the new row.
Any idea?
2009/3/23, eMeL <emel@...>:
Finally I decided to use EXECUTE STATEMENT feature:
CREATE OR ALTER PROCEDURE "ProcName"(
srcKey varchar(16)
, dstKey varchar(16)
)
RETURNS (
...
)
AS
DECLARE VARIABLE fields varchar(32000) = '';
DECLARE VARIABLE vals varchar(32000) = '';
DECLARE VARIABLE aField varchar(31) = '';
BEGIN
...
-- Start fields and values lists with PK for destination row
fields = '"Key"';
vals = '''' || trim(:dstKey) || '''';
FOR
SELECT trim(RDB$FIELD_NAME)
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = 'Object'
AND trim(RDB$FIELD_NAME) <> 'Key' -- All fields except PK
INTO :aField
DO
BEGIN
-- Compose fields and values lists
fields = :fields || ', "' || :aField || '"';
vals = :vals || ', "' || :aField || '"';
END
EXECUTE STATEMENT 'INSERT INTO "Object" (' || trim(:fields)
|| ') SELECT ' || trim(:vals)
|| ' FROM "Object"'
|| ' WHERE "Key" = ''' || trim(:srcKey) || '''';
...
All fields are copied this way except BLOB subtype BINARY that is
always NULL in the new row.
Any idea?
2009/3/23, eMeL <emel@...>:
>
>> Do you know easy way(s) for this? Or may be special tools for this
>> exists in FB?
>
> If you can use temp tables, I know a way ;)
>
> Please see it:
> http://www.emel.hu/ibfb/ibfb_002.html
>
> You can copy row(s) to temporary table, modify field(s) of row(s),
> and insert back.
>
> eMeL
>