You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Anjana Prakash <an...@actian.com> on 2015/12/05 03:38:21 UTC
Writing user defined System procedure
Hi,
Is it possible to write user define System Procedure . I know user define function are possible however we are looking for calling our procedure in same way as
Existing system procedure.
Any doc or any other reference would be useful.
Thanks,
Anjana
RE: Writing user defined System procedure
Posted by Anjana Prakash <an...@actian.com>.
Thanks Rick. Sure will use apache jira forum to raise this request.
Anjana.
-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@gmail.com]
Sent: Tuesday, December 08, 2015 8:21 PM
To: Anjana Prakash <an...@actian.com>
Cc: derby-dev@db.apache.org
Subject: Re: Writing user defined System procedure
On 12/7/15 9:54 PM, Anjana Prakash wrote:
> Hi Rick,
>
> Thanks for follow-up. Please let me know the process of raising an enhancement request.
Hi Anjana,
Nothing fancy. Just log a Derby JIRA (I think you already know how to do this). Set the type field to "Improvement". For this issue, I would set the component field to SQL. In the detailed description box, please describe what you are trying to do and why the existing set of Derby features isn't adequate.
Thanks,
-Rick
> Thanks,
> Anjana.
>
> -----Original Message-----
> From: Rick Hillegas [mailto:rick.hillegas@gmail.com]
> Sent: Monday, December 07, 2015 8:39 PM
> To: derby-dev@db.apache.org
> Cc: Anjana Prakash<an...@actian.com>
> Subject: Re: Writing user defined System procedure
>
> Hi Anjana,
>
> For the moment, your best workaround would be to clone the derby code and put the cloned code into packages outside the org.apache.derby namespace. For security reasons, user code may not call Derby code when running inside the Derby engine.
>
> Exceptions are allowed for code which is in the
> org.apache.derby.optional.* and org.apache.derby.vti packages of Derby's public api:
> http://db.apache.org/derby/docs/10.12/publishedapi/index.html. This policy is enforced by StaticMethodCallNode.bindExpressionMinion().
>
> The DERBY-PROPERTIES error you are seeing is related to a special bulk-import optimization. That optimization can't be invoked from user code either. So you may need to adjust your cloned code.
>
> You are welcome to log an enhancement request for moving the code you need into the public api.
>
> Hope this helps,
> -Rick
>
> On 12/6/15 11:25 PM, Anjana Prakash wrote:
>> More analysis indicates that
>> In scenario where a user defined system procedure calls derby class
>> org/apache/derby/imp l/load/Import. It fails with Class Not found
>> exception Because the Jarloader.java restricts only certain package
>> to be accessible and raises class not found exception for package
>> org/apache/derby/imp l *
>>
>> I tried another alternative where in system procedure are not registered using install_jar instead define as classpath.
>> However this fails with exception " ERROR 42X01: Syntax error: DERBY-PROPERTIES"
>>
>>
>> Need ability where in user defined function can call derby class and reuse the existing functionality.
>>
>> Thanks,
>> Anjana.
>> -----Original Message-----
>> From: Anjana Prakash
>> Sent: Monday, December 07, 2015 9:23 AM
>> To: derby-dev@db.apache.org
>> Subject: RE: Writing user defined System procedure
>>
>> Hi,
>>
>> I verified the version of derby.jar that is used to compiled the
>> stored procedure and used at runtime by ij they both refer the same
>> lib folder. Below is the error I am getting
>>
>> ERROR 38000: The exception 'java.lang.NoClassDefFoundError: org/apache/derby/imp l/load/Import' was thrown while evaluating an expression.
>> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoCla ssDefFoundError'.
>> ERROR XJ001: Java exception: 'org.apache.derby.impl.load.Import: java.lang.Class NotFoundException'.
>>
>>
>> Thanks
>> Anjana.
>>
>> -----Original Message-----
>> From: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
>> Sent: Monday, December 07, 2015 7:34 AM
>> To: derby-dev@db.apache.org
>> Subject: Re: Writing user defined System procedure
>>
>>> My system procedure is accessing some of the derby utility function.
>>> At run time of the procedure (in ij) it fails with exception
>>>
>>> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
>>>
>>> In my stored procedure jar the manifest file has class-path reference as derby.jar.
>> Can you post the full stack trace?
>>
>> NoClassDefFoundError is a fairly unusual failure. In my experience, it generally has to do with CLASSPATH issues, and specifically with cases where there is a subtle difference between the classes that you compiled against, and the classes that you are running with.
>>
>> For example, maybe you compiled your system procedure against a0 new version of Derby, but somehow when you are running you are running with an older version of derby.jar.
>>
>> bryan
>>
>>
>
Re: Writing user defined System procedure
Posted by Rick Hillegas <ri...@gmail.com>.
On 12/7/15 9:54 PM, Anjana Prakash wrote:
> Hi Rick,
>
> Thanks for follow-up. Please let me know the process of raising an enhancement request.
Hi Anjana,
Nothing fancy. Just log a Derby JIRA (I think you already know how to do
this). Set the type field to "Improvement". For this issue, I would set
the component field to SQL. In the detailed description box, please
describe what you are trying to do and why the existing set of Derby
features isn't adequate.
Thanks,
-Rick
> Thanks,
> Anjana.
>
> -----Original Message-----
> From: Rick Hillegas [mailto:rick.hillegas@gmail.com]
> Sent: Monday, December 07, 2015 8:39 PM
> To: derby-dev@db.apache.org
> Cc: Anjana Prakash<an...@actian.com>
> Subject: Re: Writing user defined System procedure
>
> Hi Anjana,
>
> For the moment, your best workaround would be to clone the derby code and put the cloned code into packages outside the org.apache.derby namespace. For security reasons, user code may not call Derby code when running inside the Derby engine.
>
> Exceptions are allowed for code which is in the
> org.apache.derby.optional.* and org.apache.derby.vti packages of Derby's public api:
> http://db.apache.org/derby/docs/10.12/publishedapi/index.html. This policy is enforced by StaticMethodCallNode.bindExpressionMinion().
>
> The DERBY-PROPERTIES error you are seeing is related to a special bulk-import optimization. That optimization can't be invoked from user code either. So you may need to adjust your cloned code.
>
> You are welcome to log an enhancement request for moving the code you need into the public api.
>
> Hope this helps,
> -Rick
>
> On 12/6/15 11:25 PM, Anjana Prakash wrote:
>> More analysis indicates that
>> In scenario where a user defined system procedure calls derby class
>> org/apache/derby/imp l/load/Import. It fails with Class Not found
>> exception Because the Jarloader.java restricts only certain package to
>> be accessible and raises class not found exception for package
>> org/apache/derby/imp l *
>>
>> I tried another alternative where in system procedure are not registered using install_jar instead define as classpath.
>> However this fails with exception " ERROR 42X01: Syntax error: DERBY-PROPERTIES"
>>
>>
>> Need ability where in user defined function can call derby class and reuse the existing functionality.
>>
>> Thanks,
>> Anjana.
>> -----Original Message-----
>> From: Anjana Prakash
>> Sent: Monday, December 07, 2015 9:23 AM
>> To: derby-dev@db.apache.org
>> Subject: RE: Writing user defined System procedure
>>
>> Hi,
>>
>> I verified the version of derby.jar that is used to compiled the
>> stored procedure and used at runtime by ij they both refer the same
>> lib folder. Below is the error I am getting
>>
>> ERROR 38000: The exception 'java.lang.NoClassDefFoundError: org/apache/derby/imp l/load/Import' was thrown while evaluating an expression.
>> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoCla ssDefFoundError'.
>> ERROR XJ001: Java exception: 'org.apache.derby.impl.load.Import: java.lang.Class NotFoundException'.
>>
>>
>> Thanks
>> Anjana.
>>
>> -----Original Message-----
>> From: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
>> Sent: Monday, December 07, 2015 7:34 AM
>> To: derby-dev@db.apache.org
>> Subject: Re: Writing user defined System procedure
>>
>>> My system procedure is accessing some of the derby utility function.
>>> At run time of the procedure (in ij) it fails with exception
>>>
>>> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
>>>
>>> In my stored procedure jar the manifest file has class-path reference as derby.jar.
>> Can you post the full stack trace?
>>
>> NoClassDefFoundError is a fairly unusual failure. In my experience, it generally has to do with CLASSPATH issues, and specifically with cases where there is a subtle difference between the classes that you compiled against, and the classes that you are running with.
>>
>> For example, maybe you compiled your system procedure against a0 new version of Derby, but somehow when you are running you are running with an older version of derby.jar.
>>
>> bryan
>>
>>
>
RE: Writing user defined System procedure
Posted by Anjana Prakash <an...@actian.com>.
Hi Rick,
Thanks for follow-up. Please let me know the process of raising an enhancement request.
Thanks,
Anjana.
-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@gmail.com]
Sent: Monday, December 07, 2015 8:39 PM
To: derby-dev@db.apache.org
Cc: Anjana Prakash <an...@actian.com>
Subject: Re: Writing user defined System procedure
Hi Anjana,
For the moment, your best workaround would be to clone the derby code and put the cloned code into packages outside the org.apache.derby namespace. For security reasons, user code may not call Derby code when running inside the Derby engine.
Exceptions are allowed for code which is in the
org.apache.derby.optional.* and org.apache.derby.vti packages of Derby's public api:
http://db.apache.org/derby/docs/10.12/publishedapi/index.html. This policy is enforced by StaticMethodCallNode.bindExpressionMinion().
The DERBY-PROPERTIES error you are seeing is related to a special bulk-import optimization. That optimization can't be invoked from user code either. So you may need to adjust your cloned code.
You are welcome to log an enhancement request for moving the code you need into the public api.
Hope this helps,
-Rick
On 12/6/15 11:25 PM, Anjana Prakash wrote:
> More analysis indicates that
> In scenario where a user defined system procedure calls derby class
> org/apache/derby/imp l/load/Import. It fails with Class Not found
> exception Because the Jarloader.java restricts only certain package to
> be accessible and raises class not found exception for package
> org/apache/derby/imp l *
>
> I tried another alternative where in system procedure are not registered using install_jar instead define as classpath.
> However this fails with exception " ERROR 42X01: Syntax error: DERBY-PROPERTIES"
>
>
> Need ability where in user defined function can call derby class and reuse the existing functionality.
>
> Thanks,
> Anjana.
> -----Original Message-----
> From: Anjana Prakash
> Sent: Monday, December 07, 2015 9:23 AM
> To: derby-dev@db.apache.org
> Subject: RE: Writing user defined System procedure
>
> Hi,
>
> I verified the version of derby.jar that is used to compiled the
> stored procedure and used at runtime by ij they both refer the same
> lib folder. Below is the error I am getting
>
> ERROR 38000: The exception 'java.lang.NoClassDefFoundError: org/apache/derby/imp l/load/Import' was thrown while evaluating an expression.
> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoCla ssDefFoundError'.
> ERROR XJ001: Java exception: 'org.apache.derby.impl.load.Import: java.lang.Class NotFoundException'.
>
>
> Thanks
> Anjana.
>
> -----Original Message-----
> From: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
> Sent: Monday, December 07, 2015 7:34 AM
> To: derby-dev@db.apache.org
> Subject: Re: Writing user defined System procedure
>
>> My system procedure is accessing some of the derby utility function.
>> At run time of the procedure (in ij) it fails with exception
>>
>> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
>>
>> In my stored procedure jar the manifest file has class-path reference as derby.jar.
> Can you post the full stack trace?
>
> NoClassDefFoundError is a fairly unusual failure. In my experience, it generally has to do with CLASSPATH issues, and specifically with cases where there is a subtle difference between the classes that you compiled against, and the classes that you are running with.
>
> For example, maybe you compiled your system procedure against a0 new version of Derby, but somehow when you are running you are running with an older version of derby.jar.
>
> bryan
>
>
Re: Writing user defined System procedure
Posted by Rick Hillegas <ri...@gmail.com>.
Hi Anjana,
For the moment, your best workaround would be to clone the derby code
and put the cloned code into packages outside the org.apache.derby
namespace. For security reasons, user code may not call Derby code when
running inside the Derby engine.
Exceptions are allowed for code which is in the
org.apache.derby.optional.* and org.apache.derby.vti packages of Derby's
public api:
http://db.apache.org/derby/docs/10.12/publishedapi/index.html. This
policy is enforced by StaticMethodCallNode.bindExpressionMinion().
The DERBY-PROPERTIES error you are seeing is related to a special
bulk-import optimization. That optimization can't be invoked from user
code either. So you may need to adjust your cloned code.
You are welcome to log an enhancement request for moving the code you
need into the public api.
Hope this helps,
-Rick
On 12/6/15 11:25 PM, Anjana Prakash wrote:
> More analysis indicates that
> In scenario where a user defined system procedure calls derby class org/apache/derby/imp l/load/Import. It fails with Class Not found exception
> Because the Jarloader.java restricts only certain package to be accessible and raises class not found exception for package
> org/apache/derby/imp l *
>
> I tried another alternative where in system procedure are not registered using install_jar instead define as classpath.
> However this fails with exception " ERROR 42X01: Syntax error: DERBY-PROPERTIES"
>
>
> Need ability where in user defined function can call derby class and reuse the existing functionality.
>
> Thanks,
> Anjana.
> -----Original Message-----
> From: Anjana Prakash
> Sent: Monday, December 07, 2015 9:23 AM
> To: derby-dev@db.apache.org
> Subject: RE: Writing user defined System procedure
>
> Hi,
>
> I verified the version of derby.jar that is used to compiled the stored procedure and used at runtime by ij they both refer the same lib folder. Below is the error I am getting
>
> ERROR 38000: The exception 'java.lang.NoClassDefFoundError: org/apache/derby/imp l/load/Import' was thrown while evaluating an expression.
> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoCla ssDefFoundError'.
> ERROR XJ001: Java exception: 'org.apache.derby.impl.load.Import: java.lang.Class NotFoundException'.
>
>
> Thanks
> Anjana.
>
> -----Original Message-----
> From: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
> Sent: Monday, December 07, 2015 7:34 AM
> To: derby-dev@db.apache.org
> Subject: Re: Writing user defined System procedure
>
>> My system procedure is accessing some of the derby utility function.
>> At run time of the procedure (in ij) it fails with exception
>>
>> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
>>
>> In my stored procedure jar the manifest file has class-path reference as derby.jar.
> Can you post the full stack trace?
>
> NoClassDefFoundError is a fairly unusual failure. In my experience, it generally has to do with CLASSPATH issues, and specifically with cases where there is a subtle difference between the classes that you compiled against, and the classes that you are running with.
>
> For example, maybe you compiled your system procedure against a0 new version of Derby, but somehow when you are running you are running with an older version of derby.jar.
>
> bryan
>
>
RE: Writing user defined System procedure
Posted by Anjana Prakash <an...@actian.com>.
More analysis indicates that
In scenario where a user defined system procedure calls derby class org/apache/derby/imp l/load/Import. It fails with Class Not found exception
Because the Jarloader.java restricts only certain package to be accessible and raises class not found exception for package
org/apache/derby/imp l *
I tried another alternative where in system procedure are not registered using install_jar instead define as classpath.
However this fails with exception " ERROR 42X01: Syntax error: DERBY-PROPERTIES"
Need ability where in user defined function can call derby class and reuse the existing functionality.
Thanks,
Anjana.
-----Original Message-----
From: Anjana Prakash
Sent: Monday, December 07, 2015 9:23 AM
To: derby-dev@db.apache.org
Subject: RE: Writing user defined System procedure
Hi,
I verified the version of derby.jar that is used to compiled the stored procedure and used at runtime by ij they both refer the same lib folder. Below is the error I am getting
ERROR 38000: The exception 'java.lang.NoClassDefFoundError: org/apache/derby/imp l/load/Import' was thrown while evaluating an expression.
ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoCla ssDefFoundError'.
ERROR XJ001: Java exception: 'org.apache.derby.impl.load.Import: java.lang.Class NotFoundException'.
Thanks
Anjana.
-----Original Message-----
From: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
Sent: Monday, December 07, 2015 7:34 AM
To: derby-dev@db.apache.org
Subject: Re: Writing user defined System procedure
> My system procedure is accessing some of the derby utility function.
> At run time of the procedure (in ij) it fails with exception
>
> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
>
> In my stored procedure jar the manifest file has class-path reference as derby.jar.
Can you post the full stack trace?
NoClassDefFoundError is a fairly unusual failure. In my experience, it generally has to do with CLASSPATH issues, and specifically with cases where there is a subtle difference between the classes that you compiled against, and the classes that you are running with.
For example, maybe you compiled your system procedure against a0 new version of Derby, but somehow when you are running you are running with an older version of derby.jar.
bryan
RE: Writing user defined System procedure
Posted by Anjana Prakash <an...@actian.com>.
Hi,
I verified the version of derby.jar that is used to compiled the stored procedure and used at runtime by ij they both refer the same lib folder. Below is the error I am getting
ERROR 38000: The exception 'java.lang.NoClassDefFoundError: org/apache/derby/imp
l/load/Import' was thrown while evaluating an expression.
ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoCla
ssDefFoundError'.
ERROR XJ001: Java exception: 'org.apache.derby.impl.load.Import: java.lang.Class
NotFoundException'.
Thanks
Anjana.
-----Original Message-----
From: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
Sent: Monday, December 07, 2015 7:34 AM
To: derby-dev@db.apache.org
Subject: Re: Writing user defined System procedure
> My system procedure is accessing some of the derby utility function.
> At run time of the procedure (in ij) it fails with exception
>
> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
>
> In my stored procedure jar the manifest file has class-path reference as derby.jar.
Can you post the full stack trace?
NoClassDefFoundError is a fairly unusual failure. In my experience, it generally has to do with CLASSPATH issues, and specifically with cases where there is a subtle difference between the classes that you compiled against, and the classes that you are running with.
For example, maybe you compiled your system procedure against a0 new version of Derby, but somehow when you are running you are running with an older version of derby.jar.
bryan
Re: Writing user defined System procedure
Posted by Bryan Pendleton <bp...@gmail.com>.
> My system procedure is accessing some of the derby utility function. At run time of the procedure (in ij) it fails with exception
>
> ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
>
> In my stored procedure jar the manifest file has class-path reference as derby.jar.
Can you post the full stack trace?
NoClassDefFoundError is a fairly unusual failure. In my experience,
it generally has to do with CLASSPATH issues, and specifically with
cases where there is a subtle difference between the classes that
you compiled against, and the classes that you are running with.
For example, maybe you compiled your system procedure against a0
new version of Derby, but somehow when you are running you
are running with an older version of derby.jar.
bryan
RE: Writing user defined System procedure
Posted by Anjana Prakash <an...@actian.com>.
Hi Rick,
Thanks for follow up. I am able to create the system procedure following the based on below links. Quick question
My system procedure is accessing some of the derby utility function. At run time of the procedure (in ij) it fails with exception
ERROR XJ001: Java exception: 'org/apache/derby/impl/load/Import: java.lang.NoClassDefFoundError'.
In my stored procedure jar the manifest file has class-path reference as derby.jar.
However still it fails.
I start the ij tool as
java -Dderby.system.home=D:\derby\data -jar %DERBY_INSTALL%\lib\derbyrun.jar ij
Appreciate help on same.
Thanks,
Anjana.
From: Rick Hillegas [mailto:rick.hillegas@gmail.com]
Sent: Saturday, December 05, 2015 9:15 PM
To: derby-dev@db.apache.org
Cc: Anjana Prakash <an...@actian.com>
Subject: Re: Writing user defined System procedure
On 12/4/15 6:38 PM, Anjana Prakash wrote:
Hi,
Is it possible to write user define System Procedure . I know user define function are possible however we are looking for calling our procedure in same way as
Existing system procedure.
Any doc or any other reference would be useful.
Thanks,
Anjana
A Derby user-defined procedure is just a public static void Java method. The syntax for declaring a user-defined procedure may be found here: http://db.apache.org/derby/docs/10.12/ref/rrefcreateprocedurestatement.html More information on writing, calling, and deploying user-defined procedures may be found here: http://db.apache.org/derby/docs/10.12/devguide/cdevspecial.html
Hope this helps,
-Rick
Re: Writing user defined System procedure
Posted by Rick Hillegas <ri...@gmail.com>.
On 12/4/15 6:38 PM, Anjana Prakash wrote:
>
> Hi,
>
> Is it possible to write user define System Procedure . I know user
> define function are possible however we are looking for calling our
> procedure in same way as
>
> Existing system procedure.
>
> Any doc or any other reference would be useful.
>
> Thanks,
>
> Anjana
>
A Derby user-defined procedure is just a public static void Java method.
The syntax for declaring a user-defined procedure may be found here:
http://db.apache.org/derby/docs/10.12/ref/rrefcreateprocedurestatement.html
More information on writing, calling, and deploying user-defined
procedures may be found here:
http://db.apache.org/derby/docs/10.12/devguide/cdevspecial.html
Hope this helps,
-Rick