Importing Data with SQL INSERT

[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

[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.

[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

[cypherinfo]

Have you tried to use REPLACE INTO instead?
https://mariadb.com/kb/en/library/replace/

[mikep345678]

Hmm, doesn't seem that REPLACE INTO would solve the problem of needing NB's unique keys in order to append records...

[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

[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

[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

[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

[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