You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Guillaume CHAUVET <gu...@qualiformed.com> on 2013/04/29 15:45:03 UTC
CallableStatement: SYSCS_(S/G)ET_DATABASE_PROPERTY behaviours
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.
Regards,
Guillaume
RE: CallableStatement: SYSCS_(S/G)ET_DATABASE_PROPERTY behaviours
Posted by Guillaume CHAUVET <gu...@qualiformed.com>.
After reading the PreparedStatement javadoc http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#execute()
It seems that is the good behaviour, even for the inherited CallableStatement class.
RE: CallableStatement: SYSCS_(S/G)ET_DATABASE_PROPERTY behaviours
Posted by Guillaume CHAUVET <gu...@qualiformed.com>.
Hi Knut,
Thank you for your help ! I changed testRetrieveVersionProperty like you have defined and it's okay.
However, in testSaveVersionProperty, I've noticed that "call.execute();" returns false while the corresponding SQL request is well executed in the database. Is it normal ?
Best regards,
Guillaume
Re: CallableStatement: SYSCS_(S/G)ET_DATABASE_PROPERTY behaviours
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Guillaume CHAUVET <gu...@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