adding defaults in MySQL from Access data transfer
I used a database transfer utility (Bullzip) to move data from Access to MySQL
I couldn't transfer the data if i did not opt to leave the default values, therefore the default values didn't get transfered.
I checked the access database and some of the fields have default values, ie, current time to go in automatically.
Obviously, these being absent in MySQL are causing problems as they show NULL
I use MYSQL query browser, . from mysql.com, the GNU one,
Can i add the defaults using this? I also note it has a migration utility, but im not sure what i can do with it , since ive already transferred the data. I only want to add defaults.
Does anyone know a simple way for me to do it? I do have a copy of the old Access database on my desktop, so should i just manually do it?
If anyone has done anything like this, pls offer some tips. Many thanks in advance.
Default values in Access are simply a way to calculate a value to insert into the field that has no value when a new record is inserted. Once the record exists the value is there in the field, so when you do the transfer all of the values would go.
Now if you are trying to add the same default value process to your MySQL table you will have to set that up in MySQL, probably with the use of triggers or something similar.
Ron thanks, and i'll explain in a little more detail, so it's all clear and no misunderstanding. I'd appreciate it if you or someone else could tell me what to try where exactly for the field below, i'm not html literate.
I opened the old access database, right clicked and chose "design view".
This brought up some data, and i'll show you a few relevant lines from the old ACCESS database. Here are 4 columns in this table, i had to colour them differently, the bold is the top column, and their fields are in the same colour.
Field Name Data type Description Default Value
fldPosted Date/Time Article is posted since
fldUpdated Date/Time Last updated
fldCREATED Date/Time Now()
Now to the new database MYSQL
I use MySqL query browser Version 1.2.13
This particular software came with various database scripts to run against a database, with a new installation.
I have an MySQL script that can be run against the MySQL database. But i think this only works if you start new, and not with a trasfer of data as im doing. I'm transferring an Access database records to MySQL
nm_tbl_news is one table in the database, the one that contains the article details when i add them. This is what the mysql script that you're supposed to run on the database when you install it says about this table
CREATE TABLE `nm_tbl_news` (
`ID` int(10) NOT NULL auto_increment,
`fldTITLE` varchar(85) default NULL,
`fldSUMMARY` varchar(255) default NULL,
`fldACTIVE` int(10) default '1',
`fldAID` int(10) default NULL,
`fldPOSTED` datetime default NULL,
`fldUPDATED` datetime default NULL,
`fldEXPIRES` datetime default NULL,
`fldCREATED` timestamp NULL default CURRENT_TIMESTAMP,
`fldVIEWS` int(10) default '0',
`fldIMAGE` varchar(255) default NULL,
`fldVOTES` int(10) default '0',
`fldVALUE` int(10) default '0',
`fldALLOW_COMMENTS` int(10) default '0',
`fldALLOW_VOTING` int(10) default '0',
`fldUPDATE` datetime default NULL,
`fldHIGHLIGHT` int(10) default '0',
As an experment i just ran the complete script which includes the part above, against the MySQL database and it deleted all the transferred data. However, i did see default values in the tables, when i checked "edit table" in the query browser. But the records showed 0, which means they got deleted.
Since i now had the tables and default values, but no data, that was no use to me. I then re-transferred the data from the old Access database, using Bullzip, and as i explained earlier i can't transfer the default values, as some are not recognised, so i unchecked that button. The data transferred OK, overwriting what was already there, but no default values obviously. The 3 new records i'd added to the new mysql database since the transfer obviously got deleted, so i'm back with MYSQL, with the old data.
Before i experiment and add new records, i think i'd better add some default values? In particluar the fldCreated, as this one is causing a problem on a page that relies on it.
So I guess i need to add them manually? If you look at the script above it says "CURRENT-TIMESTAMP" as the default value.
Just so there is no confusion, this value is supposed to remain unchanged once added, it is not the current time that it should display, but the date that the article was actually added to the database.
The record that is causing problems in particular is the fldCREATED one. The field, shows the date that the article was actually created and added to the database. It is different to the published date, because the software gives you the ability to add an article now, and set the date to appear in public for let's say, next week, as an example. So this date fldCreated remains unchanged, even though the other dates can be altered automatically, ie. if you change the published date, or the update article date, expiry date etc.
Should i add this manually in the "Edit table" facility or what? and what should i put?
Would the solution be for me to open both databases on my desktop and manually add the defaults in the MySQL query browser? I'd like to sort out the fldCREATED first, but i think i'd better do the other ones too.
Thanks in advance
Last edited by karen987; 11-19-2008 at 06:32 AM.
My guess would be that the fldCREATED is the wrong data type.
You have it set to timestamp when it should be datetime.
Also you may need to change the default as well
If CURRENT-TIMESTAMP is only the current time then you will need one that
gives you both Date And Time.
The Now() function in Access returns both date and time. You need one in MySQL that
does the same. Is there a CURRENT-DATETIME ?
By luca90 in forum Database
Last Post: 01-14-2007, 07:03 AM
Last Post: 10-08-2006, 08:59 AM
Last Post: 08-13-2003, 01:56 PM
By RADIAZMTZ in forum Database
Last Post: 04-24-2002, 03:17 PM
By Michelle in forum VB Classic
Last Post: 04-19-2002, 05:01 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL