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 (nike.apache.org: domain of knut.hatlen@oracle.com
 designates 156.151.31.81 as permitted sender)
From: Knut Anders Hatlen <knut.hatlen@oracle.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: CallableStatement: SYSCS_(S/G)ET_DATABASE_PROPERTY behaviours
References: 
 <AC7EE6BD73B6B247A98C4FA5571DBDB91DAEC0E7@srv-exchange.qualiformed.lan>
Mail-Copies-To: never
Mail-Followup-To: "Derby Discussion" <derby-user@db.apache.org>
Date: Mon, 29 Apr 2013 16:21:42 +0200
In-Reply-To: 
 <AC7EE6BD73B6B247A98C4FA5571DBDB91DAEC0E7@srv-exchange.qualiformed.lan>
	(Guillaume CHAUVET's message of "Mon, 29 Apr 2013 13:45:03 +0000")
Message-ID: <wjo8txmph0y1.fsf@khepri41.no.oracle.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Guillaume CHAUVET <guillaume.chauvet@qualiformed.com> writes:

> Hello Derby users !
>
> I encountered some difficulties with Derby and CallableStatement. In my c=
ase, I try to save a custom property into DB Derby properties (A database i=
nternal version number).
>
> To do that, I use SYSCS_SET_DATABASE_PROPERTY and SYSCS_GET_DATABASE_PROP=
ERTY functions.
>
> Below, the SQL requests successfully executed from the default Netbeans S=
QL plugin :
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
>
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('softrev', '1.0.0');
>
>>Executed successfully in 0,001 s, 0 rows affected.
>
>>Line 1, column 1
>
> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('softrev');
>
>>Executed successfully in 0 s.
>
>>Line 2, column 1
>
>>1#  =E2=80=981.0.0=E2=80=99                         (the expected result).
>
> Now, I tried to do the same thing in Java (see the attached unitary test)=
 , but nothing happens as expected=E2=80=A6
>
> Attached, please find a maven draft with a unitary test that reproduce th=
e unexpected behaviour.

Hi Guillaume,

It looks like your Java program used CALL for calling the
SYSCS_GET_DATABASE_PROPERTY function, whereas you used VALUES in the
NetBeans SQL plugin. Derby doesn't support calling functions with a CALL
statement, so I believe your test case needs to be changed to something
like this (untested code):

    @Test
    public void testRetrieveVersionProperty() throws Exception {
        PreparedStatement ps =3D con.prepareStatement("VALUES SYSCS_UTIL.SY=
SCS_GET_DATABASE_PROPERTY(?)");
        try {
            ps.setString(1, VERSION_OPT);
            ResultSet rs =3D ps.executeQuery();
            try {
                assertTrue(rs.next());
                assertEquals(EXPECTED_VERSION, rs.getString(1));
            } finally {
                rs.close();
            }
        } finally {
            ps.close();
        }
    }


Hope this helps,

--=20
Knut Anders