Re: can't convert tinyint to boolean, a bug in hsqldb?

okay, that explained. Thanks!  My actual query (which used to be working in
1.8) is like below
SELECT (active AND field1 AND NOT field2 ... ) AS status from sometable;

all active, field1, field2 are TINYINT.


On Mon, Feb 8, 2016 at 3:49 PM, Fred Toussi <[email protected]>
wrote:

> The documentation refers to setting values for BOOLEAN columns using 1 or
> 0 in INSERT statements.
>
> In SELECT statements you can of course select the TINYINT:
>
> SELECT active AS status from sometable
>
> But you cannot treat it as a BOOLEAN in a CASE WHEN or WHERE clause.
> Instead of "WHERE active", you need to use "WHERE active = 1".
>
> Fred
>
>
> On Mon, Feb 8, 2016, at 22:00, Lenjoy wrote:
>
> I have to use
> `select active=1 as status from sometable;`
> instead.
>
> On Mon, Feb 8, 2016 at 1:47 PM, Lenjoy <[email protected]> wrote:
>
> According to the hsqldb doc
> http://hsqldb.org/doc/guide/sqlgeneral-chapt.html#sgc_boolean_type
> TINYINT can be converted to boolean
>
> The table is created as
> " active tinyint NOT NULL" +
>
> But got error message when query as below in java code:
> `select active as status from sometable;`
>
>
> java.sql.SQLSyntaxErrorException: data type of expression is not boolean
>     at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>     at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>     at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
>     at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
> ... my own library
>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>     at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>     at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>     at java.lang.reflect.Method.invoke(Method.java:497)
>     at junit.framework.TestCase.runTest(TestCase.java:176)
>     at junit.framework.TestCase.runBare(TestCase.java:141)
>     at junit.framework.TestResult$1.protect(TestResult.java:122)
>     at junit.framework.TestResult.runProtected(TestResult.java:142)
>     at junit.framework.TestResult.run(TestResult.java:125)
>     at junit.framework.TestCase.run(TestCase.java:129)
>     at junit.framework.TestSuite.runTest(TestSuite.java:255)
>     at junit.framework.TestSuite.run(TestSuite.java:250)
>     at
> org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:84)
>     at
> org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:283)
>     at
> org.apache.maven.surefire.junit4.JUnit4Provider.executeWithRerun(JUnit4Provider.java:173)
>     at
> org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:153)
>     at
> org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:128)
>     at
> org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:203)
>     at
> org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:155)
>     at
> org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:103)
>
>
>
> --
> Lenjoy
>
>
>
>
> --
> Lenjoy
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> *_______________________________________________*
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>


-- 
Lenjoy

------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user