db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: CallableStatement: SYSCS_(S/G)ET_DATABASE_PROPERTY behaviours
Date Mon, 29 Apr 2013 14:21:42 GMT
Guillaume CHAUVET <guillaume.chauvet@qualiformed.com> writes:

> Hello Derby users !
>
> I encountered some difficulties with Derby and CallableStatement. In my case, I try to
save a custom property into DB Derby properties (A database internal version number).
>
> To do that, I use SYSCS_SET_DATABASE_PROPERTY and SYSCS_GET_DATABASE_PROPERTY functions.
>
> Below, the SQL requests successfully executed from the default Netbeans SQL plugin :
>
> ===============================
>
> 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#  ‘1.0.0’                         (the expected result).
>
> Now, I tried to do the same thing in Java (see the attached unitary test) , but nothing
happens as expected…
>
> Attached, please find a maven draft with a unitary test that reproduce the 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 = con.prepareStatement("VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(?)");
        try {
            ps.setString(1, VERSION_OPT);
            ResultSet rs = ps.executeQuery();
            try {
                assertTrue(rs.next());
                assertEquals(EXPECTED_VERSION, rs.getString(1));
            } finally {
                rs.close();
            }
        } finally {
            ps.close();
        }
    }


Hope this helps,

-- 
Knut Anders

Mime
View raw message