Apparent seriously broken stuff in 2.1b3 and 2.1b4

Classic Servoy
1

I have a solution that I’m developing - started using 2.0, but have moved to 2.1b3 as I needed some of new enhancements. Solution data and repository are in a PostgreSQL database. Mac OS X 10.3.4.

Problem1: db seq not working in 2.1b3

When I create a new record in 2.1b3, in a table where the primary key is assigned a sequence value derived from the backend database (not a servoy seq), the insert fails with the following message:

java.sql.SQLException: ERROR: ExecInsert: Fail to add null value in not null attribute id
ERROR: ExecInsert: Fail to add null value in not null attribute id

What’s happening is that Servoy is not getting the next sequence value and passing it to the db when the record is created, instead the value is null which causes the constraint exception in PostgreSQL. Also, the backend sequence does not increment as it did before. I checked the properties for the various tables in Servoy to make sure that the pk’s were set to be db_seq and referred to the correct sequences in the backend and everything looked OK.

THIS WORKED FINE IN 2.0.

Problem 2) Booleans in backend database now cause data loading error in 2.1b4

When trying to troubleshoot 1) above I downloaded and installed 2.1b4 to see if the db_seq might have been fixed, and couldn’t get that far as I found an even more critical problem. When my solution loads the startup form, I get an error saying that the form data cannot be loaded. The details say:

Bad Integer t Bad Integer t.,

and my form shows no data records!

This is what I think is happening: The PostgreSQL table that is being loaded contains 2 fields of type boolean that contain values of ‘t’ or ‘f’ (usually t). Servoy doesn’t type these fields as boolean, but as integers. These fields aren’t used in my Servoy solution - they are used in backend trigger functions that handle certain types of maintenance tasks. Since I didn’t (and still don’t) access these fields in any form before, I never had any problem with the mistyping. Now I have them with 2.1b4. Actually, it would be nice if Servoy could handle SQL boolean data types, or at minimum go back to how things used to work. Can this be fixed?

Thanks! :?

2

2.1b3 had some problems with sequences yes.

i don’t know why you get that second error because to me ‘t’ or ‘f’ aren’t even integer numbers. but chars in my eyes. Can you give me an simple create table statement with such a boolean field and a few insert statements? So that i can test those and look if we can work around them?

3

Johann:

Thanks for your reply.

Here is a create table statement excerpted from the “real” table that is giving me the problem in 2.1b4:

CREATE TABLE tmp3 (
id text primary key DEFAULT (‘HOLE_’::text || (nextval(‘hole_id_seq’::text))::text),
name text,
loc_from_site boolean DEFAULT FALSE,
hole_depth real CHECK(hole_depth>=0)
);

and here are a few insert statements:

INSERT into tmp3 (name, loc_from_site, hole_depth) values (‘Hole1’,true,150);
INSERT into tmp3 (name, loc_from_site, hole_depth) values (‘Hole2’,true,200);
INSERT into tmp3 (name, loc_from_site, hole_depth) values (‘Hole3’,false,250);
INSERT into tmp3 (name, loc_from_site, hole_depth) values (‘Hole1’,false,300);

I tested these statements in my PostgreSQL database, and they all work fine. The result of a select statement after importing these rows is:

select * from tmp3;

id | name | loc_from_site | hole_depth
------------±------±--------------±-----------
HOLE_50067 | Hole1 | t | 150
HOLE_50068 | Hole2 | t | 200
HOLE_50069 | Hole3 | f | 250
HOLE_50070 | Hole1 | f | 300
(4 rows)

You can see from the select statement that the boolean returns t or f. Note t or f is NOT accepted as input in the insert statement, only true or false (no quotes). I think this is the t that Servoy sees. Since Servoy types the field loc_from_site as an integer, it’s having a problem because is sees the ‘t’.

A couple of things about this -

  1. Obviously I can’t create this table in Servoy, as Servoy has no boolean data type. The “real” tables were created a while back before Servoy was even in the picture.

  2. Before 2.1b4, Servoy didn’t have any problem with this table - it just ignored the problem, so you must have done something different in this beta with respect to checking on data types. The table loaded fine, before. I did have an issue when trying to display the boolean, however - I couldn’t get the boolean to display, and if I tried to edit it in Servoy, I’d get an SQL error from PostgreSQL saying that a boolean field could not accept an integer type. I posted on this before - see http://forum.servoy.com/viewtopic.php?t=1834. At the time you indicated there was a problem with booleans and to use an integer work-around, which I’ve done. My current Servoy solution does not display or edit these fields directly in any form. Again, up until 2.1b4, things worked fine. I can’t abandon the booleans in the backend table entirely, however (not without considerable effort), because of a number of backend functions that refer to them.

Referring to the problem with the sequences - are these now fixed in 2.1b4?

Thanks for your help!

4

The issues with both sequences and data loading where the backend db contains boolean datatypes seems to be resolved in 2.1b5, and function as they did in 2.0. Thanks!

Do you have plans for Servoy to properly accommodate SQL boolean datatypes in the future (e.g. for editing/display)?

5

What do you mean with editing/displaying booleans?

6

Johan:

I refer you again to my post from last April: Displaying/editing boolean fields in a form - Classic Servoy - Servoy Community. The behavior is still the same as I posted back then.

The problem as you stated then is that Servoy does not support PostgreSQL boolean data types (a standard SQL datatype, according to Postgre’s documentation), but instead types the field as integer. As a result, the value of the boolean field will not display anything in a form - either t or f, true or false, 1 or 0, yes or no, or check or no check if a check field type is assigned the data provider on the form. If I try to enter a t or f or similar into the field, Servoy will not accept it as it is expecting an integer, and if I enter a 1 or 0 into the form (either explicitly or via a check box), then I get an error that the form data cannot be saved with details as follows:

java.sql.SQLException: ERROR: column “loc_from_site” is of type boolean but expression is of type integer
You will need to rewrite or cast the expression

For our current application I’ve developed a workaround, by adding new integer fields to our table that mimic the booleans, along with backend functions that keep them in sync. This is a bit awkward, however, so I was wondering if you were planning to add support for the boolean data type in the future…