Update PKey Database to VARCHAR(25) / MySQL Storage engine

Update PKey Database to VARCHAR(25) / MySQL Storage engine

Postby semsik » Wed Mar 30, 2016 10:28 pm

Dear Sirs,

I just recently started playing around with nuBuilderPro.
I'm not a programmer, but an enthousiast for creating small computer applications that make my life easier.
I'm more than decent with MS Access, wich is a great help at my day to day job as a desk jockey!

Working via the web is the next natuaral step in my evolution, I already learned SQL, and have a good base in php.

In my experimentation with nuBuilderPro I stumbled upon the next questions.
1.
Is there a way to use the built in nuBuilderPro php function nuID() to update the current Primary Key Value, wich used to be an autoincrement,
in an existing database?
I would like to do this because i would like to follow the recommended table structure as proposed in the documentation.
The table I need to update the Primary Key from is a postal code table from Belgium, many records!!

2.
The documentation mentions a recommended table structure but what is the recommended storage engine?
Is it good nuBuilderPro practice to create relationships in the mysql db,
if so then innoDB could be the preferred storage engine,
if not maybe MyISAM should be the preferred.

Keep up the good work, I like what I've seen from nuBuilderPro so far!

regards
Sam
semsik
 
Posts: 2
Joined: Wed Mar 30, 2016 9:47 pm

Re: Update PKey Database to VARCHAR(25) / MySQL Storage engi

Postby admin » Thu Mar 31, 2016 7:54 am

Sam,

1)
Changing existing tables could be done like this..

Code: Select all
ALTER TABLE tablename CHANGE id id VARCHAR(25) NOT NULL;
update tablename SET id = LPAD('id',15,'x');


2). We use myISAM, we feel its more reliable.

Steven
admin
Site Admin
 
Posts: 3234
Joined: Mon Jun 15, 2009 9:53 am

Re: Update PKey Database to VARCHAR(25) / MySQL Storage engi

Postby semsik » Fri Apr 01, 2016 8:45 pm

Dear Steven,

Thanx for the quick reply!

Clever using the LPAD function to 'fill up' the id field with x's followed by the original autoincrement value.
For future reference for other people: disable auto increment first!

The suggested solution worked like a charm!!

A small remark about the suggested LPAD function.
update tablename SET id = LPAD('id',15,'x');
should be
update tablename SET id = LPAD(id,15,'x');

The first argument of LPAD function must be passed as the id field and not the string 'id', otherwise you end
up with 'xxxxxxxxxxxxxid' as Primary Key for every record.

I will use myISAM storage engine as proposed.

Sam
semsik
 
Posts: 2
Joined: Wed Mar 30, 2016 9:47 pm

Re: Update PKey Database to VARCHAR(25) / MySQL Storage engi

Postby admin » Mon Apr 11, 2016 3:36 pm

.
admin
Site Admin
 
Posts: 3234
Joined: Mon Jun 15, 2009 9:53 am


Return to General