Hi All,

Here's a fun one....

Every now and then a well established program with an insert will occasionally starts throwing errors: "An attempt to place a null value in a non-nullable datatype".

Its reproducible...for a while...The users assure me that the problem goes away the next day all by itself!

So when the problem starts, we put on sc930 and quickly establish that none of the data is null.

The query in question is an insert, the action of which fires a rule which will need to update the row that has just been inserted.
I suspect that the problem is in the rule and is caused in part by the query:
select max(integer_field) into :max_value
from the_table
where date_field = :date_just_inserted

Note that max_value is declared as integer not null not default;

So the rule relies on the fact that at least one row (ie. the one just inserted) will have a matching field for the aggregation. If there are no matching date_fields (which should never occur) then the result of the max() would be a null and this could not be stored in the declared variable :max_value.

As I say the application normally works just fine.

I suspect that when the problem occurs that the JDBC application has confused ingresdates with a timestamp. Certainly other examples using JDBC have shown this confusion creeping in.

As I can't prove any of this, I've simply taken that query and wrapped the aggregation in an ifnull(..., -1) and tested accordingly.

So far the problem hasn't resurfaced.

The question is: Has anyone seen anything like this? Any ideas what may be the source of the problem?

Marty
________________________________________
From: Martin Bowes
Sent: 18 May 2012 14:59
To: Nicholas Goodwin
Subject: RE: more

As I say...I'm grasping at straws...

The problem with appearing repeatable is I suspect due to JDBC on the PCs being whacked with memory leakage or some other usage nasty. I would suggest restarting the PC if the problem occurs and then attempt the repeat of the query.

Marty

-----Original Message-----
From: Nicholas Goodwin
Sent: 18 May 2012 13:31
To: Martin Bowes
Subject: RE: more

Thanks

Your explanation sounds plausible. The only remaining question is why the problem appears repeatable some days and not others. A better error message will definitely help
--
Nicholas Goodwin
Core Programming

________________________________________
From: Martin Bowes
Sent: 18 May 2012 10:13
To: Nicholas Goodwin; Ingres
Subject: RE: more

This is pretty nuts.

About the only thing I can think of is that we are seeing a weird JDBC thing causing the insert RFP to throw a fit.
Specifically in the code that sets the ts_sequence value where a max(ts_sequence) is used. The assumption being that it can always find at least one row (itself) with a matching time_stamp. Ergo the max must always return a value. However if JDBC has somehow confused the date issue I suppose its conceivable that the max could return a null...Yes I am grasping at straws.

The good news is that I can program around this easily and ensure the max always has a value returned 'ifnull(max(ts_sequence), -1)'. Then test if the value is -1, if so raise a more interesting error message. If we see the new message then at least we know where on the right path!

I've loaded the new procedure into mirror, test and live...see if the problem persists.
FYI. The new message will look like: 'Error: Failed to determine ts_sequence details for time_stamp = ' + varchar(:new_time_stamp) + '.';

Marty
From: Nicholas Goodwin
Sent: 17 May 2012 15:54
To: Ingres
Subject: more

Hello both

I doubt any of us has time to look at it but "An attempt to place a null value in a non-nullable datatype" is back again, from insert into c_buddy_worklist (machine_cid, barcode, test_cid, sample_id, project_cid, group_id, category_id) values ( ?, ?, ?, ?, ?, ?, ? ) running against ldbc on vlab
--
Nick