How to make TSQLQuery to have default field values when inserting record?

[lazjump]

Greetings Lazarus users,

I use Lazarus 1.2.4 to connect to MySQL 5.1. I am very new to Lazarus but I have a little bit of database programming before.

Suppose I have this table:

Code: [Select]

CREATE TABLE `mytable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NULL DEFAULT NULL,
`avalue` DECIMAL(10,2) NOT NULL DEFAULT '10000.00',
PRIMARY KEY (`id`)
)
ENGINE=MyISAM;
As you can see above, the field "avalue" have a default value of 10000.00.

Now, how to make TSQLQuery to fill that default value upon inserting record?

Code: [Select]

  SQLQuery1.Close;
  SQLQuery1.SQL.Text:='select * from mytable';
  SQLQuery1.Open;

  SQLQuery1.Append;
  if SQLQuery1.FieldByName('avalue').AsFloat=10000 then
    ShowMessage('Hooray I have a default value.')
  else
    ShowMessage('I do not have a default value. I think it is cool to have a default value here.');
  SQLQuery1.Cancel;
Thank you.

[minesadorada]

I thought the purpose of a DEFAULT constraint was to insert the default value if no other is specified.  Not an SQLQuery action, but a database one.

[lazjump]

Hi minesadorada,

Yes, like you said, the DEFAULT constraint is a server side action that provides default values if the field name is omitted in the insert statement.

So with the table declared above we can do like:

Code: [Select]

insert into mytable (name) values ('john');
and the values of "id" and "avalue" would be filled by the server.

However it would be very nice if SQLDB could "propagate" those default values to the client side. It would help programmers to use default values from server without the need to redeclare/redefine default values on the client. If someone change default constraints on the server, then the client programmer do not have to do anything. This would also make it easier to create user interfaces; just call SQLQuery1.Append and all the data aware controls linked to SQLQuery1 would have default values.

[minesadorada]

I think querying table structures varies between databases, so might be difficult to code into the SQLQuery object.

http://stackoverflow.com/questions/3362225/describe-table-structure

[lazjump]

Sure it must be of some complexity to code such information_schema queries. To deal with differences between database servers and conditions (for example: default values for fields in multitable queries). But data access layer is all about giving programmers uniform ways to talk to different databases; data access layers "flattens out" differences. So while it might be complex, it still fits the purpose of data access layer.

I have installed Zeos and try it for a while. But it seemed that Zeos can't supply default values too.