default field values

[amwassil]

Steve,

Even though I have default values set in the database, when I add a new record in DF, the values of all my dropdown selectors default to "Please Select..." instead of picking up the default value. Is there a way to get the new record to automatically display the defaults?

I suspect I might be able to do so in fields.ini or valuelists.ini, but don't know what to put there - default:value = or something like that?

Michael Wassil

[shannah]

Hi Michael,

Default values are not currently supported in version 0.5.3. They have been added in 0.6 which will be available before May 15.

Best regards

Steve

[mbrown]

Now that 0.6 is out how do you set the default value for a field? I have tried guessing a few ways and could not find it in the documentation.

Thanks.

[shannah]

Default values can be set in 3 ways:

1. Set the default value in the SQL table:
e.g. FirstName VARCHAR(11) DEFAULT 'Steve'

2. In the fields.ini file:
[FirstName]
Default = Steve

3. In the delegate class:
e.g.

function FirstName__default(){
return 'Steve';
}

Best regards

Steve

[Aoirthoir]

Just one more thing to love about Dataface. This thing just gets better and better.

[njw]

Hi Steve, sorry to be a pain

I am trying to timestamp records on a table. I have tried various combinations on the MySQL field definition and all end up with me getting a timestamp of 0000-00-00. So, I changed it to a Date field and used the example above as a template

function timestamp__default(){
return date("Y-m-d");
}

Now I get a null.

Any thoughts on what I;ve got wrong?

Many thanks

Neil

[Aoirthoir]

Neil there are several ways to do this directly in mysql. It may depend on your version, I am at 5.0.

If you use phpmyadmin you can change a field to type TIMESTAMP. You can have only ONE timestamp field though. Then you can choose under attributes ON UPDATE CURRENT_TIMESTAMP. You can also choose default value CURRENT_TIMESTAMP. This can go in directly to the tables Create Table SQL statement if you like for that field. The on Update and the default of current timestamp will work even if it is a DATETIME field. But in either case you can have only ONE timestamped field. Mysql will not automatically update two fields even if one is default current_timestamp and the other is on update current_timestamp...it wont work.

Since I have two fields, one for insert date and the other for update date I use an SQL trigger to do this. My fields are called respectively inserted and updated. The trigger code looks like this:

Code: Select all


DELIMITER $$
CREATE TRIGGER my_unique_triggername BEFORE INSERT ON mytable
  FOR EACH ROW BEGIN
        IF NEW.inserted is null THEN
        SET NEW.inserted = now();
        END IF;
  END;
$$
CREATE TRIGGER my_unique_triggername BEFORE UPDATE ON mytable
  FOR EACH ROW BEGIN
        SET NEW.updated = now();
  END;
$$
DELIMITER ;


You have to have root privileges to assign triggers currently. In the latest release (5.1) you do not have to have root.

[njw]

Hi Joe - couldn't you sleep?

The first method doesn't seem to work for me, possibly because my host is on MySQL 4.1.21. I doubt if I can set triggers because I also don't have root priveleges!

However, I thought I was on a later version of MySQL (my memory is fading - it was PHP that was upgraded), so maybe I need to do something different with that old a version.

Still don't understand why the Dataface code doesn't work, but that's probably because I'm not that good at PHP code!

Thanks

Neil

[njw]

Interesting comment in the MySQL manual that I missed previously:

ALTER TABLE .. CHANGE COLUMN allows only literal values, using CURRENT_TIMESTAMP throws an error. This means the only two ways of setting CURRENT_TIMESTAMP is through CREATE TABLE and CREATE COLUMN.

Problem is, MySQL-Front (and no doubt other MySQL GUI's) handle this situation by *silently* updating your timestamp defaults to ZERO! (instead of throwing an error and rolling back) Fortunately, on our production database we were able to spot this inconsistency before it caused too many problems!

My testing shows that CREATE COLUMN doesn't set the Timestamp right either - only CREATE TABLE - and I don't want to reload the table ...

Hmmmmmm ...... is this a bug or a feature?

[Aoirthoir]

If you create a table with a mysql GUI, or if you add the timestamp in something like phpMyAdmin, then COPY the table, it should work fine. It is what I had to do. Table copies in mysql are pretty fast. Once the table is copied...you can rename the original table and then the new table..do a test and you should be set. If it is only one field you are looking to timestamp..then its a matter of do you only want to timestamp as a default value or do you want the timestamp also to update as well...if so make the proper selections in phpMyAdmin.

As to the code above..it will not work until at least mysql 5.0. Mysql 5.1 will allow a non-root to add triggers if they are given permission to do so.

[shannah]

Ok... here is a simple dataface way of making a timestamp column.

In the fields.ini file, add:

timestamp = insert

on a datetime column to make it store the insert timestamp.

(you could use timestamp = update) to have the timestamp applied on update.

I generally have my datetime fields set as default NULL so that Mysql doesn't force the 0000-00-00 value as the default - which dataface picks up.

Hope this helps a little.

-Steve

[njw]

Thanks Joe and Steve. I think I'll opt for the Dataface route as I will remember that in future, if only because I can look back at the ini files! Tomorrow's job as I've been struggling this evening to print labels from a spreadsheet in OpenOffice for two hours, to end up doing the job in MS Publisher in about 10 minutes. For once, MS was the simpler route!

Thanks again

Neil

[njw]

Working well. Thanks Steve & Joe once more.