Error on CREATE OR ALTER PROCEDURE - FOR WITH RECURSIVE TREE ...
----------------------------------------------------------------
Key: CORE-5036
URL: http://tracker.firebirdsql.org/browse/CORE-5036
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0 RC 1
Environment: Windows 7 Professional x64 Service Pack 1 / HP Compac
8200 Elite SFF PC - Intel(R) Core(TM) i7-2600 CPU - 8 GB RAM
Reporter: Alfred Fehr
I get an error message when I execute the below-mentioned store procedure an
can not recognize the problem..
This procedure run with the Firebird Embedded Version 2.5.3 in a productive
system since more than on year.
Now I've installed the Firebird-3.0.0.32136-0_Win32_RC1 as Embedded Version and
want do test this database with the new Firebird 3 version.
What have i done?
- I made a backup with gbak 2.5.3
- I restored with with gbak 3.0
This procedure run without problems.
As the next step I tried to execute some db-scripts, and with this described
procedure I get an error message.
Then I've created a new database and the domain DS_STR50. The result was the
same then with the productive database - I get the same error message.
Attached the discussed scripts an the error message:
/* Domain --------------------------------------------------*/
CREATE DOMAIN DS_STR50 AS
VARCHAR(50) CHARACTER SET UTF8
COLLATE UNICODE_CI_AI;
COMMENT ON DOMAIN DS_STR50 IS
'General string value with length of 50 chars (nullable)';
/* Procedure with error ------------------------------------*/
CREATE OR ALTER PROCEDURE sp_DDL_GetObjectDependencies
(
ps_DependedOnName DS_STR50,
ps_FieldName DS_STR50 DEFAULT NULL
)
RETURNS
(
sDependentName DS_STR50,
sDependedOnName DS_STR50,
sFieldName DS_STR50
)
AS
-- Get dependent objects
BEGIN
FOR
WITH RECURSIVE TREE AS
(
SELECT rdb$Dependent_Name, rdb$Depended_On_Name, rdb$Field_Name
FROM rdb$Dependencies
WHERE ( UPPER( :ps_DependedOnName ) = rdb$Depended_On_Name )
AND ( :ps_FieldName IS NULL OR UPPER( :ps_FieldName ) =
rdb$Field_Name )
UNION ALL
SELECT rdb$Dependent_Name, rdb$Depended_On_Name, rdb$Field_Name
FROM rdb$Dependencies DEP, TREE T
WHERE T.rdb$Dependent_Name = DEP.rdb$Depended_On_Name
)
SELECT rdb$Dependent_Name, rdb$Depended_On_Name, rdb$Field_Name
FROM TREE
INTO :sDependentName, :sDependedOnName, :sFieldName
DO
SUSPEND;
END
/* Error message
----------------------------------------------------------------*/
Undefined name.
unsuccessful metadata update.
CREATE OR ALTER PROCEDURE SP_DDL_GETOBJECTDEPENDENCIES failed.
Dynamic SQL Error.
SQL error code = -204.
Ambiguous field name between derived table T and table RDB$DEPENDENCIES .
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Go from Idea to Many App Stores Faster with Intel(R) XDK
Give your users amazing mobile app experiences with Intel(R) XDK.
Use one codebase in this all-in-one HTML5 development environment.
Design, debug & build mobile apps & 2D/3D high-impact games for multiple OSs.
http://pubads.g.doubleclick.net/gampad/clk?id=254741911&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
