Page 1 of 1

#Procedure

Posted: Wed Nov 26, 2014 4:05 pm
by HaMo
Hi,
tried to test (stored) Procedure functionality with a simple open on a MySQL DB.
The only parameter should be the connectionname

Code: Select all

#PROCEDURE openDB
con
BEGIN
#RUNQUERY
	CREATE DATABASE CONNECTION ${con}
		AS mysql 
		TO odysseus 
		AT X.X.X.X : 3306 
		WITH USER <user> 
		PASSWORD <password>
		NO_LAZY_CONNECTION_CHECK
END
tried to start 'openDB' with

Code: Select all

#PARSER PQL
#RUNQUERY
#EXECUTE openDB(con2)
Error is
'Parsing and Executing Query' has encountered a problem.
Script Execution Error: Stored procedure openDB does not exist

Think, that because of my empty Stored Procedure Repository, Parser didn't find openDB.
How do I register openDB in my Stored Procedure Repository?

Re: #Procedure

Posted: Wed Nov 26, 2014 4:52 pm
by Marco Grawunder
It seems to be a problem with lower and upper case. Will be fixed in the next version.

Please use CON instead of in con in the query definition.

You should also use #PARSER CQL inside the procedure

Code: Select all

#PROCEDURE openDB
CON
BEGIN
   #PARSER CQL
   #RUNQUERY
   CREATE DATABASE CONNECTION ${CON}
      AS mysql 
      TO odysseus 
      AT X.X.X.X : 3306 
      WITH USER <user> 
      PASSWORD <password>
      NO_LAZY_CONNECTION_CHECK
END
and call with

Code: Select all

#EXECUTE openDB(con2)

Re: #Procedure

Posted: Wed Nov 26, 2014 6:00 pm
by HaMo
OK,
Marco Grawunder wrote: Please use CON instead of in con in the query definition.
You should also use #PARSER CQL inside the procedure
openDB is registered, but by the way the refresh of the stored procedure window does not function, had to close and reopen it :( for making openDB visible

Thanks for the hints, going ahead testing this functionality

Re: #Procedure

Posted: Thu Nov 27, 2014 1:38 pm
by HaMo
Dear Mr. Grawunder,
sorry but
Marco Grawunder wrote: and call with

Code: Select all

#EXECUTE openDB(con2)
leads to the following console output

Code: Select all

10168906 ERROR StandardExecutor  - Could not add query '#EXECUTE DropProc(openAPlus)
' - de.uniol.inf.is.odysseus.planmanagement.executor.standardexecutor.StandardExecutor.addQuery(StandardExecutor.java:639) 
de.uniol.inf.is.odysseus.core.server.planmanagement.QueryParseException: Parsing Odysseus script failed:Exception in query validation 
Encountered empty query
	at de.uniol.inf.is.odysseus.script.parser.OdysseusScriptParser.processScriptException(OdysseusScriptParser.java:823)
	at de.uniol.inf.is.odysseus.script.parser.OdysseusScriptParser.parse(OdysseusScriptParser.java:795)
	at de.uniol.inf.is.odysseus.planmanagement.compiler.standardcompiler.StandardCompiler.translateQuery(StandardCompiler.java:252)
	at de.uniol.inf.is.odysseus.planmanagement.executor.standardexecutor.StandardExecutor.createQueries(StandardExecutor.java:246)
	at de.uniol.inf.is.odysseus.planmanagement.executor.standardexecutor.StandardExecutor.addQuery(StandardExecutor.java:617)
	at de.uniol.inf.is.odysseus.planmanagement.executor.standardexecutor.StandardExecutor.addQuery(StandardExecutor.java:610)
	at de.uniol.inf.is.odysseus.planmanagement.executor.standardexecutor.StandardExecutor.addQuery(StandardExecutor.java:594)
	at de.uniol.inf.is.odysseus.rcp.editor.text.commands.RunQueryCommand$1.run(RunQueryCommand.java:153)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: de.uniol.inf.is.odysseus.script.parser.OdysseusScriptException: Encountered empty query
	at de.uniol.inf.is.odysseus.script.keyword.AbstractQueryPreParserKeyword.validate(AbstractQueryPreParserKeyword.java:56)
	at de.uniol.inf.is.odysseus.script.parser.PreParserStatement.validate(PreParserStatement.java:56)
	at de.uniol.inf.is.odysseus.script.parser.OdysseusScriptParser.validate(OdysseusScriptParser.java:188)
	at de.uniol.inf.is.odysseus.script.parser.OdysseusScriptParser.execute(OdysseusScriptParser.java:157)
	at de.uniol.inf.is.odysseus.script.parser.OdysseusScriptParser.parseAndExecute(OdysseusScriptParser.java:140)
	at de.uniol.inf.is.odysseus.script.parser.OdysseusScriptParser.parse(OdysseusScriptParser.java:788)
	... 7 more

Re: #Procedure

Posted: Thu Nov 27, 2014 2:43 pm
by Marco Grawunder
When this error comes, there is a

#RUNQUERY (or #QUERY or #ADDQUERY)

without any following query text ...

Re: #Procedure

Posted: Fri Nov 28, 2014 12:14 pm
by HaMo
Hallo Marco,
thanks for the helping hint:
Marco Grawunder wrote:When this error comes, there is a

#RUNQUERY (or #QUERY or #ADDQUERY)

without any following query text ...
Has been an error in the called stored procedure and therefore hard to detect.

Re: #Procedure

Posted: Fri Nov 28, 2014 1:06 pm
by Marco Grawunder
Yes, this is because the procedures are just copied as text into the current query ... No precompilation, which make it more flexible.