Blank Java Error when trying to create stored procedure

[regulatre]

I would like to create my first stored procedure, so I have created what I think is an extremely simple (so it should work, right?) procedure that does basically nothing.

My Query (executed through SQL Explorer tool)

create procedure MyFirstProcedure ()
BEGIN
string strHello = new String (20) ;
END

The Response:
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210058
[JDBC OpenEdge Driver]:Error from Java compiler. Compiler messages follow.(10727)

Note that there are no Java errors shown!

1. Where can I go to read the errors? Logfile?
2. Since I've never done this successfully before, I may have something misconfigured... My DB is definitely running and connected, and I can execute other queries against it.


Any help appreciated - Thanks!!!

 

[Casper]

Hi Regulatre,

Did you try some of the code examples given in the Knowledge Base?
For instance KB20617: http://tinyurl.com/d7g9m

Regarding your code: String should be with a capital S, and since you probably want to return the string you can also say something like: String strHello = "Hello World";

Furthermore if you return something then you have to define a RESULT like given in the KB example.

Good luck,

Casper.

 

[regulatre]

No Errors

Thanks for the note. The problem is that no java errors are ever shown... Is this by design? If so then my second problem is that I am not able to create any stored procedure, no matter how simple it is.

The same error is always returned (see below). How can I make sure the java compiler is configured properly on the server?

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210058
[JDBC OpenEdge Driver]:Error from Java compiler. Compiler messages follow.(10727)

 

[regulatre]

I just tried creating a stored proc on my development server and that one returned the java error message - that's good!

My development computer has something that the lab is missing... I think I'm on the right track now so I'll do some more reading in the SQL92 docs.

Is there a specific progress license that is required to define stored procedures, or do I just need the JDK?

Thanks!

 

[Casper]

The JDK is enough (I think... have to look that up)
Is there a $DLC/JDK directory?
What version of Progress are you on?

Casper.

 

[regulatre]

All tests have been on OpenEdge 10.0b,
with Wkgroup license.
There is no %DLC%\JDK directory, but there is a JRE, which I renamed after fixing the java paths through progress.ini and regedit... It's using the Sun JDK now (at least to start adminservice) but doesn't know to use it when it comes time to compiling stored procedure.

on my lab computer I just
uninstalled OpenEdge
rebooted
installed openedge,
rebooted
installed service pack 03 for 10.0b
installed the pup fix
started up my database
tried to create a stored procedure
same problem as before - java error indicated, but no error shown.

I'm going to start from square one and uninstall everything, on the lab computer and then give it another shot.

 

[Casper]

Before you uninstall this:

from KB, I dont know if it applies to OpenEdge as well but it's worth a try I guess:

In a Progress environment the JDK directory is expected on the
following location:
%DLC%\JDK
If this directory is not available, please copy a JDK installation
folder to %DLC%\JDK folder.

In 9.1D I have a jdk and a jre directory

Casper.

 

[regulatre]

Thanks. I was able to get past that error, just to be presented with another.

What I did was modified progress.ini by adding these values to the existing ones:

JDKHOME=C:\Progress\OpenEdge\JDK
JREHOME=C:\progress\openedge\jre
CLASSPATH=C:\progress\openedge\java\prosp.jar;C:\Progress\OpenEdge\jre\lib\i18n.jar;C:\Progress\OpenEdge\jre\lib\tools.jar;C:\Progress\OpenEdge\java\progress.jar;C:\Progress\OpenEdge\java\messages.jar;C:\Progress\OpenEdge\java\rmi.zip;C:\Progress\OpenEdge\java\prowin.jar;C:\Progress\OpenEdge\java\jdbc.jar

Then re-ran ini2reg

After doing so, my procedure compiles and is stored in the DB, but when I try and run it, I get the following error:

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210022
[JDBC OpenEdge Driver]:Failed during dynamic load of Java dynamic library. Reason = [126]The specified module could not be found.
[jvm].


I suspect a problem with a path somewhere, but don't know which path to look at, so I'm tweaking all of them. Also not sure if I have to reboot, or just re-start the DB in order for Progress to re-read changes I've made to PSC registry values, and environ settings.

 

[Casper]

You sure you have the correct JDK version?
Is $JDKHOME/bin in your PATH variable?

What OS you're on, windows? You have have JVM 1.4.1_03 installed?

Casper.

 

[regulatre]

Windows XP Professional.
Sun's JDK 1.5.0 revision 6 (the latest asof today).
I selected this version because it is the latest, and the SQL92 doc I read indicated to use any version after 1.4.something.

Earlier I had installed the JDK into the default C:\program files\java\... folder, but I've since uninstalled it and re-installed it in the %DLC%\jdk folder for simplicity.

I've tried all the suggestions I could find from the Progress Knowledge Center but still stuck with this dynamic library error.

 

[Casper]

You need JVM version 1.4.1_03 and no other version then that. That's what is specified in the product and availability guide.

Casper.

 

[regulatre]

This morning,
I uninstalled Java and OpenEdge 10
installed Java 1.4.1.03 into C:\progress\openedge\jdk
installed OpenEdge 10
installed OE SP 3
Installed pup fix
created a new stored procedure
Ran that procedure
Same error
rebooted
Ran the procedure again
same error

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210022
[JDBC OpenEdge Driver]:Failed during dynamic load of Java dynamic library. Reason = [126]The specified module could not be found.
[jvm].

The procedure:

@echo true;
@autocommit true;
CREATE PROCEDURE HelloWorld6 ()
BEGIN
String s = "testing 123";
END ;
COMMIT;

called with
call HelloWorld6();

from the SQL explorer tool.

The Development computer is returning the same error now so I know it's not a matter of not having the studio license installed.

 

[Casper]

Ok, then 1 more try:

KB P99800 says:

tatus: Unverified
FACT(s) (Environment):
Windows 32 Intel
OpenEdge 10.x
Progress 9.1D
SYMPTOM(s):
SQL-92 stored procedure returns an error when called (executed) from
SQL-Explorer.
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210022

[JDBC OpenEdge Driver]:Failed during dynamic load of Java dynamic
library. Reason = [126]The specified module could not be found.
[jvm].

Running SELECT * from sysprogress.sysprocedures verifies stored
procedure stored in database.
CAUSE:
Java environment is not set properly for executing SQL procedures.
FIX:
To correct the problem do the following steps:
1. Launch Progress proenv window
2. Shut down the database
3. Run sql_env at the proenv prompt:
proenv>sql_env
CLASSPATH=C:\Progress\OpenEdge\java\prosp.jar;C:\Progress\OpenEdge\java\jdbc.jar
;
PATH=C:\Progress\OpenEdge\jre\bin\client;C:\Progress\OpenEdge\BIN;C:\WINDOWS\MS\
SMS\CORE\BIN;C:\PROGRESS\OpenEdge\jre\bin\hotspot;C:\WINDOWS\system32;C:\WINDOWS
;C:\WINDOWS\System32\Wbem;C:\PROGRA~1\ABSOLU~1;C:\Progra~1\Symantec\pcAnywhere\
.
DONE setup the sql environment
4. Restart the database
5. Restart SQL Explorer and call the stored procedure.

Casper

 

[regulatre]

Already reviewed that KB Entry.
All it does is sets environ variables in that DOS instance - not the environment which is used by the DB server. In other words, that KB Entry probably worked for someone who was starting their DBs from that DOS session, but not from progress explorer tool. They both exist in separate environment instances (so the sql_env command wouldn't set the variables in the instance in which the db server would start in).

With that said, I did try adding those environment variables to my system environment and then rebooting. It had no affect.

Note that I do not have any folder called "hotspot" which is referenced in more than one of the KB articles. I suspect this is a version 9 folder, but am not sure. I am on OE10.0b and that folder does not exist within %DLC%.

My classpath is currently:
C:\progress\openedge\java\prosp.jar;C:\Progress\OpenEdge\jdk\lib\tools.jar;C:\Progress\OpenEdge\java\progress.jar;C:\Progress\OpenEdge\java\messages.jar;C:\Progress\OpenEdge\java\rmi.zip;C:\Progress\OpenEdge\java\prowin.jar;C:\Progress\OpenEdge\java\jdbc.jar

Which is a rendition of the path you mentioned from the KB article, with invalid jar files removed (since I'm on OE10 and I think that path came from V9).

Are you on OE10?
If so what is your class path?
And do you have the hotspot folder?

 

[Casper]

I'm on 9.1D06.

In 9.1D there is a hotspot directory in the jre/bin folder. It contains two files: jvm.dll and Xusage.txt

I have 10.0B03 installed on another workplace of mine, butI can't get on that machine now. (Never tried stored procedures on R10 so far...)

Sorry I couldn't help you. When I exprimented with stored procedures it simply worked (also nice once in a while with JDBC/ODBC ;-)

Regards,

Casper.

 

[regulatre]

I think I just got it.

My system path was missing the folder:
C:\Progress\OpenEdge\jre\bin\client
AND after fixing the path, I had to reboot for it to take affect.

Now when I run the stored procedure I get no errors... That leads me to believe it's working. I'll confirm this and then post the results.

Thanks Casper for all your help!!!

 

[regulatre]

It is confirmed.

I just got it up and running on my development computer (Java 1.4.2). Here are my fix notes:

Add a user without spaces in the name (our default DB owner has a name with spaces, this initially caused Java errors when compiling).

GRANT DBA TO SQL;
COMMIT;

Install Sun's Java, version 1.4.1.03 is recommended in the product and availability guide, as found by doing a search on the progress site.

Install OpenEdge 10, OE10-Service pack 3, and pup fix AFTER installing java as specified above (This is what the SQL92 doc says, but I did not confirm that it is really necessary to install OE after JDK).

Modify the following SYSTEM environment variables, via "right click on my computer and select properties, Advanced tab, then select environment variables".
CLASSPATH=C:\progress\openedge\java\prosp.jar;C:\Progress\OpenEdge\jdk\lib\tools.jar;C:\Progress\OpenEdge\java\progress.jar;C:\Progress\OpenEdge\java\messages.jar;C:\Progress\OpenEdge\java\rmi.zip;C:\Progress\OpenEdge\java\prowin.jar;C:\Progress\OpenEdge\java\jdbc.jar
Modify the PATH by adding the following two folders to the path:
C:\Progress\OpenEdge\JRE\Bin\Client
C:\progress\openedge\jdk\bin

Note for these changes to take affect, it is necessary to reboot the computer. I tried restarting the DB and the admin service but it wasn't until I rebooted that the changes took affect.

 

[Casper]

Thanks for the confirmation and the good description, very usefull to me and probably others. Glad it all works now.

Hope to see you around here @ ProgressTalk.

For now: happy new year!

Greetz,

Casper.