Error inserting multiple children in subform

[lxnunes]

Hello from Portugal!

I am facing a problem submitting multiple items via a subform. I did search the forum but nothing came up regarding this particular issue (could also be lack of caffeine)...

If I add one item (child) and save the main form, the item is properly saved. If I add 2 (or more) items like shown below and save, only the 1st one goes through and I get a MySQL error for the rest (error is shown below for better readability). The picture below shows the form and subform I am using. Please disregard the fact I am always using the same value for column 1, this is allowed.

AddingChildrenPic.JPG

I was able to trace the problem to the automatic generation of the INSERTS by nuBuilder, because for the 1st item the query is

Code: Select all

INSERT INTO provas_historico  (`prvh_prova_id`, `prvh_arbitro_id`)  VALUES ('1', '3')

However, for the second child item, the INSERT becomes

Code: Select all

INSERT INTO provas_historico  (`prvh_prova_id`, `prvh_arbitro_id`, `prvh_funcao_id`, `prvh_funcaosecundaria_id`, `prvh_disponibilidade`, `prvh_confirmado`, `prvh_nomeado`)  VALUES ('1', '3', '', '', '0', '0', '0')

As you can see in this case, a full statement was generated, when in fact only columns 1 and 2 were needed. With this INSERT i bump into the issue that the 3rd and 4th columns expect integers (or NULLs) but not empty strings.

Anyway, would anyone know if this behaviour is expected from nuBuilder (different INSERT statements for the 1st and subsequent children) or have I completely misconfigured my subform?

The logged error was

Code: Select all

[0] : 
===USER==========
globeadmin

===PDO MESSAGE=== 
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'prvh_funcao_id' at row 1

===SQL=========== 
INSERT INTO provas_historico  (`prvh_prova_id`, `prvh_arbitro_id`, `prvh_funcao_id`, `prvh_funcaosecundaria_id`, `prvh_disponibilidade`, `prvh_confirmado`, `prvh_nomeado`)  VALUES ('1', '3', '', '', '0', '0', '0');

===BACK TRACE====
/var/www/html/cacftp-nu/core/nudata.php - line 422 (nuRunQuery)

/var/www/html/cacftp-nu/core/nuapi.php - line 85 (nuUpdateDatabase)

Thank you in advance for your help!

[kev1n]

Hi,

Are you using an auto increment primary key? This can lead to issues while saving and is not recommended.

Recommended Table Structure:
https://wiki.nubuilder.net/nubuilderv3/ ... _Structure

Naming conventions:
https://wiki.nubuilder.cloud/ ... onventions

More about this topic:
viewtopic.php?f=19&t=9572

[lxnunes]

True, the pk is auto increment. I will have a look at changing to the nuBuilder's approach.
Still I do not understand why different INSERTs would be generated...
I will do a quick test and let you know.
cheers

[lxnunes]

Hi again!
I tested removing all auto increments, updating all private keys to varchar(25) and it did the trick.
Many thanks for the guidance!

Completely off-topic now: I did not see a Portuguese translation, if the vacancy is still open, I'd like to contribute. Let me know how I could proceed.

cheers

[kev1n]

Hi,

I'm glad you could sort it out.

Yes, there isn't a Portuguese translation get. I'll drop you a private message with credentials to our translation platform.