Store Procedures Questions

Feb 24, 2011 at 3:42 PM

Good day IglooBone:

Long time since our last conversation... i come to you with a couple of question to see if it's possible to do what i need to do.

Let's put it simple with an example; i have 3 strore procedures, A, B and C. B and C are called inside A; but inside the logic of B there is a condition that trigger a call to A.

- First question: it can be done?

- Second question: if first question is possitive, how can i create them? because there is a check integrity that cannot allow me to create B without create A; And if i create first A using an empty B, i cannot use ALTER to modify B later and add the rest of the code.

Thanks in advance.

Coordinator
Feb 24, 2011 at 3:55 PM

Good Evening Colomaj.

Why you cannot use ALTER?

See http://blog.effiproz.com/2010/10/table-variables-and-recursive-functions.html 

Feb 24, 2011 at 9:11 PM

Good Evening IglooBone:

I saw the example that you gave me and try it...

DROP PROCEDURE IF EXISTS mySP

CREATE PROCEDURE mySP(@myText VARCHAR(32))
SPECIFIC mySP
LANGUAGE SQL
NOT DETERMINISTIC MODIFIES 
SQL DATA
NEW SAVEPOINT LEVEL
BEGIN
	IF @myText IS NULL THEN
		SELECT 'NULL VALUE';
	ELSE	
		SELECT @myText;
	END IF;
END

CALL mySP(NULL); /*This return ... 'NULL VALUE'*/
CALL mySP('My Text'); /*This return ... 'My Text'*/

 

But when i perform the ALTER using the same code in the body, the ide give me an error (see bellow) that were no present when i perform the CREATE. If the body is the same, why the ALTER give me errors? By the way, i paste the code we use to test it next to the error message

Error -5565 (42565): incompatible data type in operation
Failed to execute SQL command.

ALTER SPECIFIC ROUTINE mySP SET BODY 
BEGIN
	IF @myText IS NULL THEN
		SELECT 'NULL VALUE';
	ELSE	
		SELECT @myText;
	END IF;
END

In a second effort, i change the IF sentence but when i perform the ALTER give me another error (see bellow).

Error -458 (S1000): System.NullReferenceException: Object reference not set to an instance of an object.
   at EffiProz.Core.QuerySpecification.CreateResultTable(Session session)
   at EffiProz.Core.QuerySpecification.CreateTable(Session session)
   at EffiProz.Core.QuerySpecification.ResolveTypesPartTwo(Session session)
   at EffiProz.Core.QueryExpression.Resolve(Session session, RangeVariable[] outerRanges, SqlType[] targetTypes)
   at EffiProz.Core.Parsing.ParserDQL.CompileCursorSpecification(Int32 props, Boolean isRoutine, RangeVariable[] outerRanges)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSelectStatement(RangeVariable[] rangeVars, Routine routine)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSQLProcedureStatementOrNull(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSqlProcedureStatementList(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.CompileIf(Routine routine, StatementCompound context, Boolean external)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSQLProcedureStatementOrNull(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSqlProcedureStatementList(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.CompileCompoundStatement(Routine routine, StatementCompound context, QName label)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSQLProcedureStatementOrNull(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.ReadRoutineBody(Routine routine)
   at EffiProz.Core.Parsing.ParserRoutine.CompileAlterSpecificRoutine()
   at EffiProz.Core.Parsing.ParserDDL.CompileAlter()
   at EffiProz.Core.Parsing.ParserCommand.CompilePart(Int32 props)
   at EffiProz.Core.Parsing.ParserCommand.CompileStatements(String sql, Result cmd)
   at EffiProz.Core.Engine.Session.ExecuteDirectStatement(Result cmd) Object reference not set to an instance of an object.
Failed to execute SQL command.

ALTER SPECIFIC ROUTINE mySP SET BODY 
BEGIN
	IF @myText = 'NULL VALUE' THEN
		SELECT 'NULL VALUE';
	ELSE	
		SELECT @myText;
	END IF;
END

I hope you can help me to figure it out, what is wrong.

Thanks in advance.

Coordinator
Feb 25, 2011 at 12:27 AM

Good Morning Colomaj.

Thanks for testing.

We’ll fix the issues in next release. Until then use non-@ parameters with recursive routines.

CREATE PROCEDURE mySP(myText VARCHAR(32))
SPECIFIC mySP
LANGUAGE SQL
NOT DETERMINISTIC MODIFIES
SQL DATA
NEW SAVEPOINT LEVEL
BEGIN
             IF myText IS NULL THEN
                    SELECT 'NULL VALUE';
             ELSE
                    SELECT myText;
             END IF;
END

 

ALTER SPECIFIC ROUTINE mySP SET BODY 
BEGIN
	IF myText IS NULL THEN
		SELECT 'NULL VALUE';
	ELSE	
		SELECT myText;
	END IF;
END



Feb 25, 2011 at 1:18 PM

Good Morning IglooBone:

It's a pleasure for us to be ussefull in the testing process, and you can be sure we'll be doing more test to ensure good performance.

By the way... can you tell us, when do you think the next release will be launch?

Thanks in advance.

Coordinator
Feb 25, 2011 at 1:46 PM

Good Evening Colomaj.

Yet we don't a have a date set for next release.

Coordinator
Mar 11, 2011 at 4:15 AM

Check with 1.6 beta http://www.effiproz.com/downloads/EffiProz_1_6_4087_16609_beta.zip

-thanks