#Procedure

Post Reply
HaMo

#Procedure

Post by HaMo » Wed Nov 26, 2014 4:05 pm

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?

User avatar
Marco Grawunder
Posts: 272
Joined: Tue Jul 29, 2014 10:29 am
Location: Oldenburg, Germany
Contact:

Re: #Procedure

Post by Marco Grawunder » Wed Nov 26, 2014 4:52 pm

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)

HaMo

Re: #Procedure

Post by HaMo » Wed Nov 26, 2014 6:00 pm

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

HaMo

Re: #Procedure

Post by HaMo » Thu Nov 27, 2014 1:38 pm

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

User avatar
Marco Grawunder
Posts: 272
Joined: Tue Jul 29, 2014 10:29 am
Location: Oldenburg, Germany
Contact:

Re: #Procedure

Post by Marco Grawunder » Thu Nov 27, 2014 2:43 pm

When this error comes, there is a

#RUNQUERY (or #QUERY or #ADDQUERY)

without any following query text ...

HaMo

Re: #Procedure

Post by HaMo » Fri Nov 28, 2014 12:14 pm

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.

User avatar
Marco Grawunder
Posts: 272
Joined: Tue Jul 29, 2014 10:29 am
Location: Oldenburg, Germany
Contact:

Re: #Procedure

Post by Marco Grawunder » Fri Nov 28, 2014 1:06 pm

Yes, this is because the procedures are just copied as text into the current query ... No precompilation, which make it more flexible.

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest