Nubuilder does not save with AI id

[Tobias]

Hi,
I have a little Problem with Nubuilder.
I try to save (or clone) a record to a table, where the ID is a numeric auto increment one. Unfortunately nuBuilder doesn't save anything and reloads the empty form.
Strangely it seems like the AI counter is increasing nevertheless. If I insert a new record manually through for example phpmyadmin it has a higher count after several nuBuilder fails.
I have entered the primary ID correctly into NuBuilder, get no error message from NuBuilder about that and have nowhere the ID on the form.

I hope somebody can help me with that issue.

Regards
Tobias

[massiws]

Tobias, from wiki pages:

It is also suggested that Unique Indexes not be used from now on, allowing nuBuilder's validating rules to check for duplicates.
This is because of the way nuBuilder implements the checking for Primary Key types.

Can be this the reason of your problem?

[zazzium]

Tobias,
if u need auto increment field, don't use it as id.
Let nuBuilder handel the id and make some other column auto increment

[Tobias]

Hi, thanks for your answers.

@massiws: I already checked that. The Primary key column has just PK, NN and AI flags enabled. Unique indexes is not enabled.

@zazzium: my "problem" is that my database is a combination from two old databases. To migrate the data I use a SQL script. In SQL directly I'm (as far as I know) not able to create IDs like nuBuilder uses. For getting those I would have to make a php script right? So far I didn't like to do that because in my opinion it would make things more complex as necessary... But I guess I simply have to make this effort.

[zazzium]

not able to create IDs like nuBuilder uses. For getting those I would have to make a php script right? So far I didn't like to do that because in my opinion it would make things more complex as necessary

nop, it's as easy as a php function

Code: Select all

uniqueid(1);

(it's exactly what's nuBuilder uses to generate id's)

[massiws]

Tobias, zazzium is right, use unique fields as ID is a better choice.
Anyway, nuBuilder support also auto-increment ID:
- I created a simple test table in phpmyadmin:

Test table in phpmyadmin

pma.png (15.6 KiB) Viewed 9263 times

- created a form with Form Wizard:

Form's General tab

form.png (12.65 KiB) Viewed 9263 times

Form's Browse tab

formBrowseTab.png (9.36 KiB) Viewed 9263 times

- this is what I get:

Browse Screen

browseScreen.png (4.16 KiB) Viewed 9263 times

Edit Screen

editScreen.png (3.24 KiB) Viewed 9263 times

Maybe, there is a misconfiguration in your form's settings.

Max

[Tobias]

@zazzium: sure I know that NuBuilder handles it like this. But uniqueid(); is a php function. My current migration Script is pure SQL. Thats what I wanted to say with my last post.

@massiws: I double checked everything a few times: so far its a pretty simple form with just 2 Lookups, 2 Dropdowns, 1 Text and several Display objects. They all work as they should and I can't find any mistake there or in the Form Settings itself.
The strange thing is that I don't even get a SQL error or something like this... nothing, it simply doesn't save the new/cloned records. Updating an already existing record works fine.
The only thing coming to my mind that could cause the problem is that my primary key is named id_product (and not product_id like it is recommended in the wiki). But this shouldn't be a problem right?

Tobias

[massiws]

Tobias, I have made some tests and all works fine, also with AI fields.
Some suggestions:

• have you one/more JOIN in your SQL? If yes, make sure PK and FK are the same type and size in all linked tables;
• If something goes wrong on save event, maybe could be an error in SQL; try this:
• edit formupdate.php (around line 159) adding a nuDebug() function:

  Code: Select all

  if(count($formFields) > 0){ //--dont update if there has been nothing changed
  	$s                      = "UPDATE $form->sfo_table SET $updateString WHERE $form->sfo_primary_key = '$recordID'";
  	nuDebug("SQL: $s");    // add this line to capture SQL sent to MySQL
  	nuRunQuery($s);
  }

• restart your application and try to save a new (and duplicate an existing) record: in Setup > Debug (or in phpmyadmin > zzsys_trap table) you should get the real SQL sent to MySQL: copy/paste in phpmyadmin, maybe, could give you more info
• remember to remove debug line after all tests.

Max

[Tobias]

Thanks Max,
now I found at least the problem. Unfortunately it seems to be a bug in NuBuilder which really doesn't like AI.

Nubuilder always wants to execute this querry:

Code: Select all

UPDATE product SET ethnologue_code = 'deu', product_name = 'test55', product_type_id = '1', product_copyright_holder_id = '7', book_name_id = '' WHERE id_product = '0'

So NuBuilder probaly also does the INSERT with id_product='0'. (which means to tell mysql that it should choose the next AI number as described here. And after that it wants to update that entry (which has an other ID now).

The strange thing is that the entry that probably was created through INSERT before is gone also after that.


EDIT: I think I got it where the problem is: My DB is build with the INNODB engine and has several foreign Key constraints that are set to NotNull. Through this the mechanism in dbfunctions.php to create a new record an get his ID doesn'tt work with the Database.

Code: Select all

function dbGetUniqueID($pID, $pTable, $pPrimaryKey){
		
	$pTable       = trim($pTable);
	$pPrimaryKey  = trim($pPrimaryKey);
	

	if($pID == '-1' or $pID == ''){             //-create new record

		if(dbIsAutoID($pTable, $pPrimaryKey)){  //-- create auto id
			$db   = nuRunQuery(''); // returns $db[0]=$DBHost; $db[1]=$DBName; $db[2]=$DBUserID; $db[3]=$DBPassWord;
			$link = mysql_connect($db[0], $db[2], $db[3]);
			if (!$link) { die('Could not connect: ' . mysql_error());}
			mysql_select_db($db[1]);
			mysql_query("INSERT INTO `$pTable` (`$pPrimaryKey`) VALUES (NULL)");     //<------------- here's the Problem!!!
			return mysql_insert_id();
		}else{                                //-- create string id
			$id   = uniqid('1');
			nuRunQuery("INSERT INTO `$pTable` (`$pPrimaryKey`) VALUES ('$id')");
			return $id;
		}
	}else{                                    //-- return current record id
		return $pID;
	}

}

At the marked line NuBuilder tries to insert a new record into the table with no values at all. Through this it should get a new AI ID but it can't work out because of the NOTNULL/Foreign Key fields.
Strange is only that nuBuilder doesn't throw out an error message for that... in e.g. phpmyadmin I get one while trying to execute the query that nuBuilder executes.

Probably this behavior wouldn't change much if I switch to uniqid keys. I hope NuBuilderPro handles this stuff in a better, working way.

EDIT2:

I could fix this behavior with disabling the NotNULL Flag in the DB-Tables and let NuBuilder handle this with "Stop blanks".
But now I have the next problem through the foreign key constraints: There are also such constraint fields that should be "nullable". But if I don't fill those fields in the Form NuBuilder wants to update them to '' instead of NULL which obviously doesn't work through the constraints.

Tobias

[massiws]

Tobias, you are right: nuBuilder can't manage NULL values in PK/FK.

My workaround to this problem was:

• use uniqid for all tables;
• where I needed auto increment fields, I made it with few lines of custom code (there is also a nuBuilder build-in function);
• to manage FK NULL values, I create one default "empty" record for each related table, setting the Default Value SQL object's field, so when you insert (or clone) a record in your primary table, nuBuilder fill-out also all FK with default values. Here an example with dropdown, but the same is for lookup object:

  

  Example of dropdown object with default value

  dropdown.png (7.17 KiB) Viewed 9235 times

• if a user want to set a costraint to NULL, in Custom Code > After Save I set the FK to default (uniqid) value.

Hope this helps,
Max