Hello folks,

recently (it could be already a few weeks) we are getting a problems with a dataSource validation and dataSource commit functions.

We are getting a messages line "Cannot inset NULL into columns ...." even, when a value is populated in datasource! Please see below debug messages.

Code:

api_debug.log('FS_PJ.stazky_jizdyCommit before commit ='||' PJ_STAZKY_JIZDYDETAILS1.stazka_id='||api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stazka_id')); 
api_datasource.doCommit('PJ_STAZKY_JIZDYDETAILS1');
--commit;
api_debug.log('FS_PJ.stazky_jizdyCommit after commit ='||' PJ_STAZKY_JIZDYDETAILS1.stazka_id='||api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stazka_id'));

Debug result:

USER : 23-02-2015 11:38:42 FS_PJ.stazky_jizdyCommit before commit = PJ_STAZKY_JIZDYDETAILS1.stazka_id=13273248
ERROR : 23-02-2015 11:38:42 pj_stazky_jizdyDetailsBottomPanel.btn-pridaj ( Button ) : ButtonPress => pj_stazky_jizdyPridej : fs_pj.stazky_jizdyPridej (  ) =>  Error while committing Datasource "PJ_STAZKY_JIZDYDETAILS1": ORA-01400: cannot insert NULL into ("DEV"."PJ_STAZKY_PRUBEH_JIZDY"."STAZKA_ID")

A datasource 'PJ_STAZKY_JIZDYDETAILS1' is based on table 'PJ_STAZKY_PRUBEH_JIZDY', DML operations are allowed.

A problem is, that this behavior does not occur all the time, but quite often. Any idea what could cause it?

I have no clue, where could be a problem. Tomas

asked 23 Feb '15, 06:08

Tomeo's gravatar image

Tomeo
162249
accept rate: 3%


I have replaced a api_datasource.doCommit with below code (which reads data from dataSource and row which I'm trying to commit) and it works fine.

         --commit changes
         api_debug.log('FS_PJ.stazky_jizdyCommit before commit ='||' PJ_STAZKY_JIZDYDETAILS1.stazka_id='||api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stazka_id'));

         -- tu to blbne !!!
         --api_datasource.doCommit('PJ_STAZKY_JIZDYDETAILS1');
         if p_insert_flag = 1 then 
             insert into pj_stazky_prubeh_jizdy
               (object_id, timestamp, datum_zmeny, zmenil, stazka_id, ridic, akce, stat, datum, stav_km, misto, tonaz, objed_nv_id, spotreba)
             values
               (api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.object_id'), 
                api_datasource.getColumnValueDT('PJ_STAZKY_JIZDYDETAILS1.timestamp'), 
                api_datasource.getColumnValueDT('PJ_STAZKY_JIZDYDETAILS1.datum_zmeny'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.zmenil'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stazka_id'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.ridic'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.akce'), 
                api_datasource.getColumnValueTX('PJ_STAZKY_JIZDYDETAILS1.stat'), 
                api_datasource.getColumnValueDT('PJ_STAZKY_JIZDYDETAILS1.datum'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stav_km'), 
                api_datasource.getColumnValueTX('PJ_STAZKY_JIZDYDETAILS1.misto'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.tonaz'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.objed_nv_id'), 
                api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.spotreba')
                );
         else
             update pj_stazky_prubeh_jizdy
                set datum_zmeny = api_datasource.getColumnValueDT('PJ_STAZKY_JIZDYDETAILS1.datum_zmeny'),
                    zmenil = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.zmenil'),
                    stazka_id = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stazka_id'),
                    ridic = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.ridic'),
                    akce = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.akce'),
                    stat = api_datasource.getColumnValueTX('PJ_STAZKY_JIZDYDETAILS1.stat'),
                    datum = api_datasource.getColumnValueDT('PJ_STAZKY_JIZDYDETAILS1.datum'),
                    stav_km = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stav_km'),
                    misto = api_datasource.getColumnValueTX('PJ_STAZKY_JIZDYDETAILS1.misto'),
                    tonaz = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.tonaz'),
                    objed_nv_id = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.objed_nv_id'),
                    spotreba = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.spotreba')
              where object_id = api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.object_id');

         end if;
  commit;
  api_debug.log('FS_PJ.stazky_jizdyCommit after commit ='||' PJ_STAZKY_JIZDYDETAILS1.stazka_id='||api_datasource.getColumnValueNR('PJ_STAZKY_JIZDYDETAILS1.stazka_id'));
link

answered 23 Feb '15, 06:39

Tomeo's gravatar image

Tomeo
162249
accept rate: 3%

Hi Tomas,

This problem is very difficult to track like this. You need to send us enough information so that we can replicate the problem here. Maybe you want to email me?

Kind Regards,

Yalim

(24 Feb '15, 05:38) Yalim ♦♦

Hello folks,

could you please send me details regarding api_datasource.doCommit background?

Additionally, how can I query list of all datasources which are related to table including table name?

Thanks, Tomas

link

answered 28 Feb '15, 10:04

Tomeo's gravatar image

Tomeo
162249
accept rate: 3%

Hi Tomas,

What do you mean by the "details regarding the api_datasource.doCommit background"?

I don't have access to the Formspider database right now but off the top of my head: To get the datasources that issue DML to a specific table you can query the Formspider repository. T_BDF_DATASOURCEDEFINITION table contains all datasource definitions. T_BDF_DBOBJECTACCESS contains all datasources. Column names are clear enough that you should be able to consturct an SQL for what you are looking for relatively easily.

(28 Feb '15, 15:34) Yalim ♦♦

Hi Yalim,

thanks for pointing me to right tables.

Regarding api_datasource.doCommit background I mean: what operations are you performing there? At some point there is a bug, but it's difficult to identify it without seeing code.

Unfortunately, I can not simply reproduce a problem - as i think it has to do something with multiple sessions, etc...

(28 Feb '15, 17:18) Tomeo

Hi Tomas,

doCommit does what you expect it to do. It loops over the datasources (using the commit order attribute to decide in which order to process the datasources), it looks if the datasource requires a DML operation. It skips datasources that can't be committed (such as datasources that are instances of query based datasource definitions etc...) If there is a need to issue a DML than the API simply prepares the INSERT, UPDATE and DELETE statements and runs them. In the end, it issues a database commit if the DML commands are executed without any exceptions.

This API has been working without any issues for a long time now. If it is a bug, you are probably running into an edge case. I understand that it will be difficult to replicate in a sample app but this is the only way we can look at the situation. We only provide other options for support (such as remote desktop support etc... ) only to professional support subscribers.

Kind Regards, Yalim

(28 Feb '15, 17:31) Yalim ♦♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×6
×4

Asked: 23 Feb '15, 06:08

Seen: 917 times

Last updated: 28 Feb '15, 17:31


© Copyright Gerger 2017