Default values defined in the db

lee · June 14, 2005, 12:02am

Hello all,

I am using servoy 2.2 final, and postgres 7.4.8 as a back end to store my data (sybase contains the repository). Since upgrading, I am having trouble with creating new records. The scenario is thus; in postgres, I have some columns defined as timestamps with a default value of “now()”, so that when a new record is created the database automatically inserts the current timestamp (this is valid postgresql syntax). This was working fine the same solution in 2.1.2, however now when creating a new record that relies on these default values, I get an error dialog saying ‘Error saving data’ and ‘ERROR: null value in column “preorderdate” violates not-null constraint’ in the details. This is not specific to timestamp fields with the default of now().

Running servoy with -DSTACKTRACE=TRUE, I can see the queries being run when I insert a new record (extra carriage returns added by me).

Servoy 2.2:

sql insert into preorders ( preorderid, preorderdate, customerid,
 subtotal, vat, total, preorderstatusid, surcharge, surchargereason, 
deliverycharge, preordernotes, preordertimetodelivery, priority, 
assignedtime, phonefordelivery, isprepaid, creditcardid, iscollect, 
collectlocationid, isstaffmeal, companyid, userid, linkedorder, discount) 
values ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) 
questiondata[0]= 17 ,type: java.lang.Integer
questiondata[1]= NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[2]= 1 ,type: java.lang.Integer
questiondata[3]= NullValue with type: 2 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[4]= NullValue with type: 2 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[5]= 0.0 ,type: java.lang.Double
questiondata[6]= 1 ,type: java.lang.Integer
questiondata[7]= NullValue with type: 2 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[8]= NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[9]= NullValue with type: 2 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[10]= NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[11]= NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[12]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[13]= NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[14]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[15]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[16]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[17]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[18]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[19]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[20]= 1 ,type: java.lang.Integer
questiondata[21]= 'lee' ,type: java.lang.String
questiondata[22]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[23]= NullValue with type: 2 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue

In Servoy 2.1.2 (which works):

sql insert into preorders( preorderid, preorderdate, customerid, 
subtotal, vat, total, preorderstatusid, surcharge, surchargereason, 
deliverycharge, preordernotes, preordertimetodelivery, priority, 
assignedtime, phonefordelivery, isprepaid, creditcardid, iscollect, 
collectlocationid, isstaffmeal, companyid, userid, linkedorder, discount) 
values ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) 
questiondata[0]= 16 ,type: java.lang.Integer
questiondata[1]= 2005-06-14 00:20:05.539 ,type: java.sql.Timestamp
questiondata[2]= 1 ,type: java.lang.Integer
questiondata[3]= 0.0 ,type: java.lang.Double
questiondata[4]= 0.0 ,type: java.lang.Double
questiondata[5]= 0.0 ,type: java.lang.Double
questiondata[6]= 1 ,type: java.lang.Integer
questiondata[7]= 0.0 ,type: java.lang.Double
questiondata[8]= NullValue with type: 2005 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[9]= 0.0 ,type: java.lang.Double
questiondata[10]= NullValue with type: 2005 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[11]= 2005-06-14 00:20:05.539 ,type: java.sql.Timestamp
questiondata[12]= 0 ,type: java.lang.Integer
questiondata[13]= 2005-06-14 00:20:05.539 ,type: java.sql.Timestamp
questiondata[14]= 0 ,type: java.lang.Integer
questiondata[15]= 0 ,type: java.lang.Integer
questiondata[16]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[17]= 0 ,type: java.lang.Integer
questiondata[18]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[19]= 0 ,type: java.lang.Integer
questiondata[20]= 1 ,type: java.lang.Integer
questiondata[21]= 'lee' ,type: java.lang.String
questiondata[22]= NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue
questiondata[23]= NullValue with type: 2 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue

This is the exact same solution, simply exported from 2.1.2 into 2.2. As you can see, in 2.2 a lot of values are null, and I’m not sure why.

(For some reason, the forum isn’t letting me insert a html table, and inserting this as a gif using bbcode img tags is botching the formatting)

Click here for an html table example/summary

Thus the db rightfully complains when servoy tries to insert a null value into the table.

The only slightly weird thing I’ve done here is, in 2.1.2 the repository was set to postgresql, and for 2.2 I performed a clean install into a new dir and kept the repository in the default sybase db, then exported the solution from 2.1.2 and imported it into 2.2, but I don’t see why that should cause any trouble.

Any ideas what’s going on, and more importantly, how to fix it?

swingman · June 14, 2005, 11:09am

Hi lee,

I’m using PostgreSQL 8 for everything, including the repository, with Servoy 2.2. I haven’t tried setting defaults the way you do them, but I have some triggers working fine when Servoy inserts data. For all I know, you may know more about PostgreSQL than me, but here is some source code to give you an idea what I’m up to:

The function:

CREATE FUNCTION public.trig_people_update () RETURNS trigger AS
' DECLARE
    s TEXT;
  BEGIN
    s:=NEW.person_name_first;
    IF NEW.person_name_first IS NOT NULL AND NEW.person_name_middle IS NOT NULL THEN
        s:=s || $ $;
    END IF;
    s:=s || NEW.person_name_middle;
    IF NEW.person_name_middle IS NOT NULL AND NEW.person_name_last IS NOT NULL THEN
       s:=s || $ $;
    END IF;
    s:=s || NEW.person_name_last;
    NEW.person_name:=s;    
    RETURN NEW;
END ' LANGUAGE 'plpgsql'

And the trigger itself:

CREATE TRIGGER trig_peope_update
	 BEFORE INSERT OR UPDATE ON people FOR EACH ROW
	 EXECUTE PROCEDURE trig_people_update()

I know one could do this with a stored calc in Servoy, but I have an external Application importing data from FileMaker.

One issue is that Servoy needs a refresh to see the new record, so I’m thinking of using both a Servoy calc and a trigger, and putting an IF statement in the function to do nothing if whether the data is updated from Servoy.

Christian

ROCLASI · June 14, 2005, 4:03pm

Interesting approach.
I am not proficient in pgPLSQL but if i read it right why not using column defaults ?
That should also fix your ‘record not loading’ problem.

CREATE TABLE myTable
(
person_name_first varchar(255) NOT NULL DEFAULT 'myValue'::character varying,
person_name_middle varchar(255) NOT NULL DEFAULT 'myotherValue'::character varying
)

Ofcourse you need to also tell Servoy that your auto-enter values are db managed.
That last bit might be Lee’s problem.

lee · June 14, 2005, 4:34pm

ROCLASI:
That last bit might be Lee’s problem.

Yep, that was it. Turns out the issue is in fact with 2.1 and previous versions – I have always had defaults defined in the database, however I hadn’t set the columns in Servoy to “database managed” for “Auto entry” (Tools > Dataproviders > select a table > select a column and hit the Properties button).

Also, I cannot perform the following code without the same error:

forms.preorders.controller.newRecord()
forms.preorders.customerid = customerid
forms.preorders.preorderstatusid = "1"
forms.preorders.companyid = globals.userprefCompany
forms.preorders.userid = globals.curUserName
forms.preorders.controller.saveData()
forms.preorders.controller.show()

where companyid is a column in the database set to not null but also with no default set. So I set the auto-entry for that column to globals.userprefCompany and it all seems to be working.

As ROCLASI has mentioned, Servoy 2.2 seems to be much “stricter” than previous versions; this can only be a good thing as far as I’m concerned, as it forces me to do things ‘properly’.

Topic		Replies	Views
writing database defined default values Classic Servoy	0	1368	October 6, 2005
Major issues and showstoppers with Servoy 2.2rc1 Classic Servoy	5	2605	February 16, 2005
writing database defined default values Classic Servoy	4	2642	October 10, 2005
Servoy's Auto-enter vs. SQL Initial Value Classic Servoy	5	3717	September 14, 2005
Database default data provider property not working in 4.0f Classic Servoy	3	2477	July 21, 2008

Default values defined in the db

Related topics