Errors in the simplest procedures in Progress

Posted by jwiseheart on 08-Aug-2012 18:17

I have been using OpenEdge Archtitect for a while to access our database, and manage ODBC access to it. Now I am trying for the first time to figure out how to get stored procedures to work. I followed all the instructions to make sure Java was installed and environment variables set properly.

When I try to create a procedure I get an error:

Error:
[DataDirect]OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL Statement at or about "END" (10713)

All I did was copy-and-paste the "Hello world" example from the "Progress OpenEdge 10 - Data Management_SQL Development_10.2B" book, which I did alter slightly, so that it would use an existing table instead of polluting the database with new test tables. Here's the original, straight from the book:

CREATE PROCEDURE HelloWorld ()
BEGIN
SQLIStatement Insert_HelloWorld = new SQLIStatement (
"INSERT INTO HelloWorld(fld1) values (’Hello World!’)");
Insert_HelloWorld.execute();
END;

Here's my modified version, which shouldn't be a problem (I also read in a different post that the line break in the SQL statement coul dhave been the problem, but that didn't fix it):

CREATE PROCEDURE SprocTest ()
BEGIN
SQLIStatement Select_SprocTest = new SQLIStatement ("SELECT ItemNo, SellPrice FROM Item") ;
Select_SprocTest.execute() ;
END;

Upon searching Google, I found someone who had the same problem, but the solution lead to a link that is now dead: http://www.dbforums.com/other/1663151-errors-simplest-procedures-progress.html

I am trying to run these statements in OpenEdge Architect 10.1C SP4. We are in a Windows 7 / Windows Server 2008 environment.

Please help!

All Replies

Posted by Roberta Miller on 09-Aug-2012 11:47

Hi Joel,

The problem is specific to running stored procedures and triggers in OpenEdge Architect. Java uses the semicolon as the Java statement separator. By default, Architect uses the semicolon as the SQL statement separator. When Architect sees semicolons in the Java code for stored procedures or triggers, it interprets them as SQL statement separators, which causes the errors.

The solution is to use a different character for the SQL statement separator. Article By default, Architect uses the semicolon as the SQL statement separator. Article 000029457, "Error 10713 compiling Java stored procedure or trigger in OpenEdge Architect SQL Editor" contains instructions for how to change the SQL statement separator in Architect and then modify the stored procedure code to use the new separator. You can find this article in our public knowledge base at http://knowledgebase.progress.com, by searching on the article number or the title in the OpenEdge product group. For your convenience, here is a direct link: http://knowledgebase.progress.com/articles/Article/P135184?retURL=%2Fapex%2Fprogresskbsearch&popup=false

The other Communities post that the outside link referenced is still here, but the address changed. You can find the post at http://communities.progress.com/pcom/message/72666#72666. It gives the same solution as the KB article.

HTH,

Roberta

Posted by jwiseheart on 09-Aug-2012 12:59

Roberta,

     Thanks, that fixed it!   

This thread is closed