This website uses cookies for visitor traffic analysis. By using the website, you agree with storing the cookies on your computer.More information

Using Boolean Datatype with Oracle and JDBC

Oracle doesn't use/define the SQL Type Boolean. You can't define a column in a table as Boolean. It's possible to use boolean in PL/Sql e.g. for procedures or functions but you can't call the function from SQL.

Example functions

CREATE OR REPLACE FUNCTION afunc(pIn BOOLEAN) RETURN BOOLEAN IS
BEGIN
  RETURN pIn;
END afunc;
/

CREATE OR REPLACE FUNCTION bfunc(pIn VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN pIn;
END bfunc;
/

It's possible to call:

SELECT bfunc('Hello') FROM dual;

but it's not possible to call:

SELECT afunc(true) FROM dual;

or

SELECT afunc(1) FROM dual;

(1 = true, 0 = false; boolean is mapped to integer in Oracle)

If you want to call a procedure or function which defines boolean parameters, it's not possible without tricks with Oracle JDBC driver. This is annoying.

We solved the problem in JVx 2.4 and it's now possible to call functions or procedures without tricks. Here's a "more complex" example. We define a function with an input parameter and a procedure with an input/output parameter in a package:

CREATE OR REPLACE PACKAGE TestBoolean IS
 
  FUNCTION test(pOutput BOOLEAN) RETURN BOOLEAN;
  PROCEDURE testBoolOut(pOutput IN OUT BOOLEAN);
 
END;
/

CREATE OR REPLACE PACKAGE BODY TestBoolean IS

  FUNCTION test(pOutput BOOLEAN) RETURN BOOLEAN IS
  BEGIN
    IF (pOutput) THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END;

  PROCEDURE testBoolOut(pOutput IN OUT BOOLEAN) IS
  BEGIN
    IF (pOutput) THEN
      pOutput := FALSE;
    ELSE
      pOutput := TRUE;
    END IF;
  END;

END;
/

Now we use JVx' DBAccess to call the function and procedure.

//DB connection
DBAccess dba = new OracleDBAccess();
dba.setUrl("jdbc:oracle:thin:@oravm:1521:XE");
dba.setUsername("test");
dba.setPassword("test");
dba.open();

//Function call
Assert.assertEquals(Boolean.TRUE, dba.executeFunction("TESTBOOLEAN.TEST", Types.BOOLEAN, Boolean.FALSE));

//Procedure call
InOutParam param = new InOutParam(Types.BOOLEAN, Boolean.FALSE);
dba.executeProcedure("TESTBOOLEAN.TESTBOOLOUT", param);

Assert.assertEquals(Boolean.TRUE, param.getValue());

The whole type mapping was done in OracleDBAccess and it's invisible for you. Simply use the API to call procedures or functions.

categories Development | rjahn | 18/12/2015 | comments Comments (0)

categories ,

eTV in Action

Some days ago, I wrote about our eTV project. The blog post had some pictures but no more details. I want to tell you some details about this project because it's simply awesome.

The project was a research project of our framework team and was started just for fun. The use-case was simple: Our room had 4 walls and 3 were full with pictures and world maps. Only one wall was empty. Why not using a flat TV for showing different content like livestreams, comic strips, images.

The idea was great and some days later, the last wall was full. A nice 43" flat TV was mounted.

We thought that a RaspberryPi could bring the content to the TV because it's a small device and fits behind the TV. Java works without problems on the Pi and JVx as well.

After all hardware pieces were ready, it was time to think about the software because a sort of control center was needed to implement some features. The plan was to write a simple server which has a set of commands like: open website, show image, play livestream, execute command, upload file, download file.

The server was implemented as simple socket server, with JVx. It executes pre-configured commands and has some important control features: take screenshot, next - previous window, get window list, close window. The server has no GUI and is more or less a window/process manager.

The server has no GUI, but we need a GUI to control the server. We wrote a simple JVx application as remote control. The remote control application was deployed on a Jetty application server (on the RasPi, with vaadin UI) and as mobile JavaFX application. Jetty runs fine on the RasPi and our vaadin UI as well.

The TV with RaspberryPi, streaming a Video from YouTube:

eTV YouTube Stream

eTV YouTube Stream

After we were ready with the server, we tried to create a simple JVx demo application to demonstrate JVx on embedded devices. It was funny to use eTV for live streams or to show images, but what about real-world use-cases?

The idea was about a JavaFX application, running on the RasPi. The application could be a monitoring app for a warehouse with a nice looking frontend and a backoffice view for administration. It's usual to have a nice looking frontend and a not so nice looking backoffice part.

We've implemented an application for a big assembly hall. The application is a styled JVx application but still 100% JVx. We've used VisionX to create the UI:

VisionX design mode

VisionX design mode
TV mode

TV mode

The application in VisionX is not 100% the same as on the TV because of the screen resolution, but it's the same source code and VisionX works with this application. The application on the TV hides the application frame and only shows the screen content, but this is a supported feature.

The UI technology is not JavaFX! We tried to use JavaFX but it wasn't possible because the RasPi had performance problems with the amount of nodes. It wasn't possible to reduce the amount of used nodes with standard JavaFX controls. Overclocking the Pi didn't solve the problem.

We simply switched to Swing and didn't have any performance problems. So, the UI technology is good old Swing. It works great in combination with the RasPi and we think the result is also nice!

The application is a monitoring application for different events, like performance, effort, pressure, temperature, aerodynamics, alerts. We did connect a temp sensor and two buzzers to get a better real-world experience and because it was easy to support with a RasPi. Initial setup:

Initial setup

Initial setup

The backoffice/backend was deployed as web application (Jetty on RaspberryPi, JVx vaadin UI) because it should be possible to use it on tablets or smartphones without native apps:

Backend view

Backend view

The same UI on mobile phones:

Mobile view

Mobile view
Mobile view (no menu)

Mobile view (no menu)

The application is a 100% JVx application with Swing UI and vaadin UI. Everything runs directly on the RaspberryPi.

We've used the whole eTV system as showcase application at DOAG conference in November:

eTV @ DOAG 2015

eTV @ DOAG 2015

The results of our "research project" are awesome and eTV is a ready-to-use product. We didn't code one line of code to support different UI technologies and didn't have problems with resolutions of tablets, smartphones or the TV (#responsive).

Thanks to JVx it was super easy to create an amazing application.

categories Development | rjahn | | comments Comments (0)

categories , , , ,

© SIB Visions GmbH