Call statement with an SQL request as an input parameter

[Guillaume CHAUVET <gu...@qualiformed.com>]

Hi,

Below, please find a small SQL script intended to inject a value selected from an SQL request as a input call statement parameter :
SELECT center FROM APP.SETTINGS WHERE ID = 1
>Return a varchar(128) string, for example: 'test'

Now, I try to use this value as a input parameter in a call statement :
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('center', (SELECT center FROM APP.SETTINGS WHERE ID = 1));
>Error code -1, SQL state XJ001: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC: java.lang.NullPointerException  XJ001.U
>Line 1, column 1
>[This SQL request reset the JDBC connection]

According to you, is it the expected behaviour or a bug ?

--
Regards,
Guillaume

Re: Call statement with an SQL request as an input parameter

[Knut Anders Hatlen <kn...@oracle.com>]

Guillaume CHAUVET <gu...@qualiformed.com> writes:

> Hi Rick,
>
> Thank you very much, I understand my mistake now : I wanted to make a single SQL request  instead of two requests.
> By cons I have to write a Java program to do that, but this is not a problem in my case.

Hi Guillaume,

Even though Derby doesn't allow sub-queries as arguments to procedure
calls, I think it's a bug that it fails with a NullPointerException.
I've filed https://issues.apache.org/jira/browse/DERBY-6212 to make it
fail more gracefully.

Thanks,

-- 
Knut Anders

RE: Call statement with an SQL request as an input parameter

[Guillaume CHAUVET <gu...@qualiformed.com>]

Hi Rick,

Thank you very much, I understand my mistake now : I wanted to make a single SQL request  instead of two requests.
By cons I have to write a Java program to do that, but this is not a problem in my case.

Thanks again :)

--
Regards,
Guillaume

Re: Call statement with an SQL request as an input parameter

[Rick Hillegas <ri...@oracle.com>]

On 4/30/13 8:51 AM, Guillaume CHAUVET wrote:
>
> Hi,
>
> Below, please find a small SQL script intended to inject a value 
> selected from an SQL request as a input call statement parameter :
>
> *SELECT center FROM APP.SETTINGS WHERE ID = 1 *
>
> >Return a varchar(128) string, for example: ‘test’
>
> Now, I try to use this value as a input parameter in a call statement :
>
> *CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('center', (SELECT center 
> FROM APP.SETTINGS WHERE ID = 1)); *
>
> />Error code -1, SQL state XJ001: DERBY SQL error: SQLCODE: -1, 
> SQLSTATE: XJ001, SQLERRMC: java.lang.NullPointerException XJ001.U /
>
> />Line 1, column 1 /
>
> />[This SQL request reset the JDBC connection] /
>
> According to you, is it the expected behaviour or a bug ?
>
> -- 
>
> Regards,
>
> Guillaume
>
Hi Guillaume,

Both arguments to that system procedure should be strings. In your 
example, the second argument is not enclosed in single quotes, so the 
parser does not interpret it as a string. That's why you get the error 
you are seeing.

But it sounds as though you don't want to bind that SELECT string as the 
value of the "center" key. Instead, what you want to bind to "center" is 
the result of running that SELECT. You will need to run 2 statements:

1) Run the SELECT and retrieve its string result.

2) In a separate statement, bind that result to the "center" key by 
calling the system procedure.

Hope this helps,
-Rick