Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of rick.hillegas@oracle.com
 designates 156.151.31.81 as permitted sender)
Message-ID: <517FF11C.3040306@oracle.com>
Date: Tue, 30 Apr 2013 09:28:12 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: Call statement with an SQL request as an input parameter
References: 
 <AC7EE6BD73B6B247A98C4FA5571DBDB91DAECB0C@srv-exchange.qualiformed.lan>
In-Reply-To: 
 <AC7EE6BD73B6B247A98C4FA5571DBDB91DAECB0C@srv-exchange.qualiformed.lan>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

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