Page 1 of 2
Importing Data with SQL INSERT
Posted: Mon Jan 15, 2018 8:13 pm
by mikep345678
I've created some nuBuilder Forte tables and need to populate them with a bunch sample data. Normally the simplest way to do so would be to paste in some SQL INSERT commands via phpMyAdmin.
However, I get "duplicate key" errors (literally, #1062 - Duplicate entry '' for key 'PRIMARY') when I try to do the import. This seems to be because as a char, naturally nuBuilder's primary key can't autoincrement.
Any suggestions for importing some data sets while maintaining nuBuilder primary key integrity?
Thanks,
Mike
Re: Importing Data with SQL INSERT
Posted: Mon Jan 15, 2018 9:28 pm
by cypherinfo
nuBuilder use a primary key of type varchar (25 characters). You need to add it in any table you'll be using along with the fields of your interest.
Re: Importing Data with SQL INSERT
Posted: Mon Jan 15, 2018 10:17 pm
by mikep345678
How does NB calculate the IDs? (For example, a couple of NB-form-added records have IDs "5a5cf8efee9840e" and "5a5cfdbc05a3b05") Without knowing the "formula", seems that ensuring uniqueness is impossible.
I suppose one way it could be done might be to create several records manually using the NB form (thus generating unique IDs), then export those records, then edit the SQL to add the external data, then reimport them. Seems kludgey.
Further suggestions? Am looking for "best practice" here, not necessarily just more workarounds! : )
Mike
Re: Importing Data with SQL INSERT
Posted: Mon Jan 15, 2018 10:32 pm
by cypherinfo
Have you tried to use REPLACE INTO instead?
https://mariadb.com/kb/en/library/replace/
Re: Importing Data with SQL INSERT
Posted: Mon Jan 15, 2018 11:38 pm
by mikep345678
Hmm, doesn't seem that REPLACE INTO would solve the problem of needing NB's unique keys in order to append records...
Re: Importing Data with SQL INSERT
Posted: Tue Jan 16, 2018 12:19 am
by mikep345678
Am wondering-- should things work if I manually create my tables without the nuDB "key field" column, import my sample data, then just build my forms. Are those key fields necessary for other things to work, like lookups or nuSQL statements?
I'd tried that, had built several forms, etc, then-- rather all of a sudden, the Form Tab field of every part of every form went blank and my forms stopped worked. (I'd love to be able to recover all of that work, if that's something that could be fixed...)
Mike
Re: Importing Data with SQL INSERT
Posted: Tue Jan 16, 2018 1:16 am
by admin
mike,
Run a Procedure with this code, and it will add and populate a primary Key. (Make sure it doesn't have one to start with.)
I'll add it to Forte soon.
Code: Select all
nuAddnuID_a('aaa');
function nuAddnuID_a($tab){
$id = $tab."_id";
nuRunQuery("ALTER TABLE `$tab` ADD `nuautoid` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`nuautoid`)");
nuRunQuery("ALTER TABLE `$tab` ADD `$id` VARCHAR(25) NOT NULL AFTER `nuautoid`;");
$t = nuRunQuery("SELECT nuautoid AS id FROM $tab");
while($r = db_fetch_object($t)){
$nid = nuID();
nuRunQuery("UPDATE $tab SET `$id` = '$nid' WHERE nuautoid = '$r->id'");
}
nuRunQuery("ALTER TABLE `$tab` DROP nuautoid");
nuRunQuery("ALTER TABLE `$tab` ADD PRIMARY KEY(`$t_id`)");
}
Hope it helps
Steven
Re: Importing Data with SQL INSERT
Posted: Tue Jan 16, 2018 1:55 am
by mikep345678
Steven,
To clarify: is nuAddnuID_a intended to be run on an table that doesn't already have a nuID column?
Thank you!
Mike
Re: Importing Data with SQL INSERT
Posted: Tue Jan 16, 2018 2:16 am
by mikep345678
OK, yep, this did add a nuID to a table imported without that column.
It completed successfully, but this was in nuDebug:
Code: Select all
[0] :
===USER==========
globeadmin
===PDO MESSAGE===
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column '' doesn't exist in table
===SQL===========
ALTER TABLE `Employees` ADD PRIMARY KEY(``)
===BACK TRACE====
/home/mmpullen/public_html/dbtest/bcr_nb4/nucommon.php(1238) : eval()'d code - line 22 (nuRunQuery)
/home/mmpullen/public_html/dbtest/bcr_nb4/nucommon.php(1238) : eval()'d code - line 1 (nuAddnuID_a)
/home/mmpullen/public_html/dbtest/bcr_nb4/nucommon.php - line 1238 (eval)
/home/mmpullen/public_html/dbtest/bcr_nb4/nurunphp.php - line 15 (nuEval)
Er, what is the importance of the nuID on each record?
Mike
Re: Importing Data with SQL INSERT
Posted: Tue Jan 16, 2018 2:26 am
by admin
Mike,
Its just a unique string. (which is way bettter than using auto IDs for lots of reasons). Here's a couple
http://nubuilder.blogspot.com.au/2010/09/.
Steven