Hi,

I need to know the insert and update statements Formspider is doing. Where can i find them?

Development with Formspider is very fast UNTIL you run into mysterious error messages....

In the first place, all database errors are accompanied by a yellow exclamation mark in the right upper corner of the app. When i click it a popup with a red cross appears, but there is no message. I guess that's a bug.

I "solved" that problem by an exception in the commit-procedure and display sqlerrm in a textfield.

The first error i encountered was this: "ORA-22816: unsupported feature with RETURNING clause". It seems that that has something to do about rowid... So for this case i added a unique key to the table in such a way that i did not have to use the Oracle ROWID feature. To my surprise that solved the problem with the update statement.

But now i run into an error message that a column value is NULL during INSERT although there is a value displayed in the textfield on the screen. So it is certainly not null. I can do the insert in sqlplus without problems; that is - the statement as i think it would be. My conclusion is that Formspider is doing "something" with the statement in such a way that it fails. Perhaps columns are mixed up... I have spend hours on trial and error to figure it out, but it is useless. I can not find any cause and the problem is really not logical.

I can only solve this when i know what Formspider is doing. HOW?

Kind regards, Jan Willem Vermeer

asked 23 Sep '15, 11:25

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

Hi Jan,

It would help us immensely if you could send us the SQL of the view with the problem. It would be even better if you could send the export of your application, too.

Kind Regards,
Yalim

(28 Sep '15, 05:43) Yalim ♦♦

Hi,

For one problem i found a solution: remove the old datasource and create it again...

It appears that when you change a view in the database, the datasource is corrupted. On page Query you can click "refresh from database" but apparently that does not correct all internal formspider settings for that datasource.

The ORA-22816 error is also back again: try to insert a record on an updateable view. I solved that with the surrogate Primary Key.

If i had known exactly what Formspider was doing, i would not have lost this whole day. Probably the columns of the view were mixed up, but i do not know exactly offcourse.

KR Jan Willem

link

answered 23 Sep '15, 12:36

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

Hello Willem,

many thanks for your post as we are having the same problem like you are mentioning in second part of your question for more than 1 year. For some reasons, a formspider throws us an exception that it can not insert a null into table even if it's not null. When we did an application restart and were trying to insert exactly the same data then it was OK. So, it "randomly" throws us that error message. I assume, that something wrong is behind command:

api_datasource.doCommit(p_datasourceName);

Well as so far there is not an official workout and it's not easy to simulate it or prepare demo where this error occurs, we did following ugly "solution":

We created a new package, called API_SYS and created following procedure into it:

PROCEDURE api_datasource_doCommit(p_datasourceName VARCHAR2) IS
      l_status_tx VARCHAR2(255);
      cislo_obj  varchar2(50);
      pocet_new number:=0;
   BEGIN
      pad_num := pad_num + 1;
      BEGIN
         l_status_tx := api_datasource.getRowStatus(p_datasourceName);
      EXCEPTION
         WHEN OTHERS THEN
            api_debug.log('API_SYS.api_datasource_doCommit.ERROR STATUS' || p_datasourceName || ':' || SQLERRM);
      END;

      IF l_status_tx = api_datasource.ROW_STATUS_NEW THEN
         api_debug.log('}}} API_SYS.api_datasource_doCommit.Start: ''' || p_datasourceName || ''' ROW_STATUS_NEW');
      ELSIF l_status_tx = api_datasource.ROW_STATUS_UPDATED THEN
         api_debug.log('}}} API_SYS.api_datasource_doCommit.Start: ''' || p_datasourceName || ''' ROW_STATUS_UPDATED');
      ELSIF l_status_tx = api_datasource.ROW_STATUS_DELETED THEN
         api_debug.log('}}} API_SYS.api_datasource_doCommit.Start: ''' || p_datasourceName || ''' ROW_STATUS_DELETED');
      ELSIF l_status_tx = api_datasource.ROW_STATUS_TEMP THEN
         api_debug.log('}}} API_SYS.api_datasource_doCommit.Start: ''' || p_datasourceName || ''' ROW_STATUS_TEMP');
      END IF;

CASE p_datasourceName
         WHEN 'PJ_ADRESAR_MIEST_NVDETAILS1' THEN
            IF l_status_tx = api_datasource.ROW_STATUS_NEW THEN
               -- insert  
               INSERT INTO PJ_ADRESAR_MIEST_NV
                  (OBJECT_ID,
                   TIMESTAMP,
                   DATUM_ZMENY,
                   ZMENIL,
                   KOD,
                   ZAKAZNIK,
                   FIRMA_NAZEV,
                   ULICA,
                   PSC,
                   MESTO,
                   STAT,
                   FLAG_NAKLADKY,
                   FLAG_VYKLADKY,
                   DRUH_ZBOZI,
                   MISTO_ZACLENI,
                   MESTO_NAZOV,
                   STAT_NAZOV)
               VALUES
                  (api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.OBJECT_ID'),
                   api_datasource.getColumnValueDT('PJ_ADRESAR_MIEST_NVDETAILS1.TIMESTAMP'),
                   api_datasource.getColumnValueDT('PJ_ADRESAR_MIEST_NVDETAILS1.DATUM_ZMENY'),
                   api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.ZMENIL'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.KOD'),
                   api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.ZAKAZNIK'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.FIRMA_NAZEV'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.ULICA'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.PSC'),
                   api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.MESTO'),
                   api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.STAT'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.FLAG_NAKLADKY'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.FLAG_VYKLADKY'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.DRUH_ZBOZI'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.MISTO_ZACLENI'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.MESTO_NAZOV'),
                   api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.STAT_NAZOV'));
            ELSIF l_status_tx = api_datasource.ROW_STATUS_UPDATED THEN
               UPDATE PJ_ADRESAR_MIEST_NV
                  SET DATUM_ZMENY   = api_datasource.getColumnValueDT('PJ_ADRESAR_MIEST_NVDETAILS1.DATUM_ZMENY'),
                      ZMENIL        = api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.ZMENIL'),
                      KOD           = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.KOD'),
                      ZAKAZNIK      = api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.ZAKAZNIK'),
                      FIRMA_NAZEV   = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.FIRMA_NAZEV'),
                      ULICA         = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.ULICA'),
                      PSC           = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.PSC'),
                      MESTO         = api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.MESTO'),
                      STAT          = api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.STAT'),
                      FLAG_NAKLADKY = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.FLAG_NAKLADKY'),
                      FLAG_VYKLADKY = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.FLAG_VYKLADKY'),
                      DRUH_ZBOZI    = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.DRUH_ZBOZI'),
                      MISTO_ZACLENI = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.MISTO_ZACLENI'),
                      MESTO_NAZOV   = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.MESTO_NAZOV'),
                      STAT_NAZOV    = api_datasource.getColumnValueTX('PJ_ADRESAR_MIEST_NVDETAILS1.STAT_NAZOV')
                WHERE OBJECT_ID = api_datasource.getColumnValueNR('PJ_ADRESAR_MIEST_NVDETAILS1.OBJECT_ID');
            ELSE
               api_datasource.doCommit(p_datasourceName);
            END IF;
            COMMIT;
         WHEN 'PJ_VOZIDLADETAILS4' THEN
             .... for each problematic datasource  
    WHEN 'TEMPLETE' THEN
            IF l_status_tx = api_datasource.ROW_STATUS_NEW THEN
               NULL;
            ELSIF l_status_tx = api_datasource.ROW_STATUS_UPDATED THEN
               NULL;
            ELSE
               api_datasource.doCommit(p_datasourceName);
            END IF;
            COMMIT;
         ELSE
            api_debug.log('API_SYS.api_datasource_doCommit. ELSE OPTION: ' || p_datasourceName);
            api_datasource.doCommit(p_datasourceName);
      END CASE;
      api_debug.log('{{{ API_SYS.api_datasource_doCommit.End ' || p_datasourceName);
      pad_num := pad_num - 1;
   END;

Then everywhere where api_datasource.doCommit(p_datasourceName); was called we replace it with API_SYS.api_datasource_doCommit(p_datasourceName); We update this procedure each time we identify a problematic datasource. Then it works fine. I know it's not a perfect solution, but have no other options yet.

Please let me know if find a fix to this issue. Tomas

link

answered 26 Sep '15, 09:17

Tomeo's gravatar image

Tomeo
162353
accept rate: 3%

Hi Tomas,

many thanks for this work-around!

Thusfar i have only noticed the problem after adding columns to a view. Then Formspider starts mixing up columns. With trial-and-error i tried to figure out any kind of logic, but found none. It looks as if the mixing up occurs randomly. In my case a few numeric columns got the values of other numeric columns. What is the chance that users will notice that? Your work-around will not fix that.

I need to trust the Formspider DML-statements for 100% and at this moment i can not. So i asked the Formspider team to 1) solve this crucial bug and 2) add the executed DML-statements to the SYSTEM-level debugging.

Views change very often, certainly during development. The only safe work-around i have is to re-create the datasource again after such change. The procedure is:

1) Rename the old datasource - note that references in panels are changed too
2) Create the new datasource - it will get the original name so references in plsql-packages will work
3) Modify the references in the panels so the old datasource is not used anymore
4) Delete the old datasource

This is a lot of work and it can never be the intention of Formspider.

Kind regards, Jan Willem

link

answered 27 Sep '15, 04:37

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

Hi Jan,

We'll try to replicate this issue on our end and find a solution. This is a very odd error indeed. None of us here ran into this problem and we build applications with Formspider all the time and use Datasource Definitions based on views very often. And as you say, they change a lot during development. I'll update this post when we find out more.

Kind Regards,
Yalim

(28 Sep '15, 04:48) Yalim ♦♦

Hi Jan,

There is a On Error event in Formspider at the application level. (It is in the application edit dialog under events tab.) If you assign an action to this event, Formspider will execute it whenever there is an error in your application. Maybe that can help you to capture any errors you encounter in your application?

Kind Regards,
Yalim

(28 Sep '15, 08:39) Yalim Gerger ♦♦

Thanks Yalim, i have done something similar by adding an exception "when others" to the commit-procedure and that displays sqlerrm in my message line. KR JW

(28 Sep '15, 10:35) Jan Willem V...

Hi Yalim,

I think i have found the cause of the problem with the mixing up of columns. Formspider does not mix them up, it simply leaves them out.

For every datasource i can define properties for the columns. One of these properties is "Updateable". Because of this name, i expected that this column may YES OR NO be UPDATED by the application.

Today i discovered that this property also determines if this column may be INSERTED YES OR NO by the application.

When a user creates a new row in the datasource, he enters values for the columns. IMPORTANT: some of these columns are used for the primary key of the table. So, some columns must be entered but may NEVER be updated.

This is where the problem starts... In the grid i have to set the fields to EDITABLE, because otherwise the user can not enter values for new rows. But because of it is editable, the user can also change values. Which should not be possible offcourse when columns are part of the primary key, but as far as i know i can not make difference between enterable and updateable.

To make 100% sure that users do never change keys, i unchecked the value for updateable. But then i can not insert rows anymore because Formspider leaves them out of the INSERT-statement.

I guess there are three solutions: 1) set columns to enterable but not updateable somehow or 2) formspider needs to ignore "updateable" when doing inserts or 3) i have to change my 400 tables to have meaningless stupid numeric keys (and that i will NEVER do).

I hope solution 1) is already there somehow...

Kind regards, Jan Willem

link

answered 01 Oct '15, 09:32

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

Hi Jan,

Yes, the name of the Updatebale attribute is misleading. The attribute really should be called 'Include in DML Statements'. I think we'll rename this attribute in upcoming versions.

It is possible to do the option 1) i.e. you can detect if a row is a new or a row queried from the database. api_datasource package has an API for this called getRowStatus . (Make sure you use the constants defined in the api_datasource package for status information.)

However, there is some work you need to do to make the whole thing work.

  • The PK Columns in the rows queried from the database should not be updatebale.

You should set the editable attribute for the relevant textFields in the Grid to N.

  • The PK columns in the new rows to be updateable.

When you create a row, you should set the editable attribute for the relevant textFields to Y. You can do this using the api_component.setEditable API. The last parameter in this API is named in_row_id. (This is the Formspider Row ID , nor the Oracle Row ID.) You can use the FS Row ID of the new row to set its relevant textFields editbable value to Y.

  • The PK columns in new rows should switch to being not updatable after the data is committed.

You want the editable textFields mentioned in step 2 to become not-editable after a successfull commmit. To achieve this, you should loop over the datasource rows before you actually commit the datasource and store the FS Row ID's of the newly rows in a PL/SQL table or something. The api_datasource.getQuery API returns the SQL which you can use to loop the datasource rows. You can even amend this SQL with an additional where clause to avoid retrieving irrelevant rows.

After the commit, you should loop this PL/SQL table and set the relevant textFields' editable attribute value back to N.

I think this is the algorithm, roughly.

Hope this helps.

Kind Regards,
Yalim

link

answered 01 Oct '15, 10:29

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

Hi Yalim,

the steps until setEditable work fine! So i can create rows and prevent updating of primary keys columns after query.

The last step however is too complex... Besides the logic to store all rows in a plsql-table, there can be thousands of rows in the panel/datasource. That takes time... And in 99% of the cases only one row is added. So for setting one or two fields to not-editable, i have to loop through thousands.

There must be an easier way...

Kind regards, JW

link

answered 01 Oct '15, 11:35

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

By the way, if you think about changing the "include in DML statements", why not make a difference between INSERT and UPDATE.

(01 Oct '15, 11:38) Jan Willem V...

Hi Jan,

I think there is a misunderstanding. You should only store the row id's of the new rows. You can supplement the SQL you receive from the getquery API and have it return only rows with status new or status temp. So no, you don't need to loop over many records or store their row id's. You can do these operations only for the relevant ones.

Kind Regards, Yalim

link

answered 01 Oct '15, 11:59

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

edited 01 Oct '15, 12:00

Hi Yalim,

i have made a simple solution...

The PLSQL to create a row looks like this:

IF api_datasource.getRowStatus('datasource') = api_datasource.row_status_temp 
THEN
  -- This is already a new record
   l_row_id := api_datasource.getCurrentRowid('datasource');
ELSE
  -- Create record before current record
   l_row_id := api_datasource.createRow( 'datasource' 
               , in_nextsiblingrow_id => api_datasource.getcurrentrowid ('datasource') );
END IF;
-- Make key columns editable
api_component.setEditable('panelname.key1fieldname','Y',NULL,l_row_id);
api_component.setEditable('panelname.key2fieldname','Y',NULL,l_row_id);
-- Save rowid for later undoing these settings
s_sesPaymentBttGridRowid := api_session.getvaluetx('sessionRowID') || '#' || TO_CHAR(l_row_id) ;
api_session.add('sessionRowID' , s_sesPaymentBttGridRowid );

After commit, i call a procedure that splits the sessionvariable sessionRowId into separate rowid's and it resets the fields:

api_component.setEditable('panelname.key1fieldname','N',NULL,l_row_id);
api_component.setEditable('panelname.key2fieldname','N',NULL,l_row_id);

Offcourse this reset is also done when this datasource is queried, because after a query the keycolumns are by default not editable.

SO: This works perfectly... However, it would be millions times easier when it would be possible to set field attributes to 1) insertable for new records and 2) updateable for existing records. Please accept this as an enhancement request for version 1.9.1.

Kind regards, Jan Willem

link

answered 01 Oct '15, 16:10

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1431436
accept rate: 0%

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:

×9
×3

Asked: 23 Sep '15, 11:25

Seen: 1,652 times

Last updated: 01 Oct '15, 16:10


© Copyright Gerger 2017