Problem with property DefaultValues of TMSQuery

Sergio Bertolotti · Post by **Sergio Bertolotti** » Sat 19 Jan 2008 15:13

Dear Support,

I use last version SDAC 4.X ver. professional with CodeGear Delphi 2007 prof. with last service packs and MS SQL 2005 Express edition with service pack 2 in italian.

I create a simple DB with this table :

CREATE TABLE [dbo].[MASTER] (
[CODICE] int IDENTITY(1, 1) NOT NULL,
[ESENTE] bit DEFAULT 0 NOT NULL,
[RAGSOC] varchar(30) COLLATE Latin1_General_CI_AS NULL,
[UTENTE] varchar(30) COLLATE Latin1_General_CI_AS DEFAULT user_name() NULL,
[ULTMOD] datetime DEFAULT getdate() NULL,
CONSTRAINT [MASTER_pk] PRIMARY KEY CLUSTERED ([CODICE]),
UNIQUE ([CODICE])
)

With Delphi I create a simple project with a TMSConnection, TMSQuery,TDatasource,TDBNavigator and a TDBGrid .

I set with MSQuery editor SQL property : select * from master and SQLINsert :

INSERT INTO master
(ESENTE, RAGSOC, UTENTE, ULTMOD)
VALUES
(:ESENTE, :RAGSOC, :UTENTE, :ULTMOD)
SET :CODICE = SCOPE_IDENTITY()

After this I set options - DefaultValues of TMSQuery - equal true and compile my project.

When I press insert button on DBNavigator I obtain an error message :
- '(getdate())' is not a valid date and time -

I see in edit fields editor of TMSQuery component that :

Default Expression of esente field is : true.
Default Expression of ultmod field is : getdate().
Default Expression of utente field is : username().

Is this correct ?
Why default expression of esente field is equal true ? I set in DB equal false to default value.
Why I obtain an error message on funtion getdate() ?

Best regards.
Sergio Bertolotti

Antaeus · Post by **Antaeus** » Mon 21 Jan 2008 09:37

SDAC interprets BIT columns as TBooleanField. True and False are acceptable values for BIT fields. True means 1, False means 0.

To avoid the problem with the getdate() function, you should exclude UTENTE and ULTMOD fields from your INSERT statement. To view in the grid values generated by the server, include the roAfterInsert value into RefreshOptions.
We will investigate the possibility to improve the DefaultValues functionality in the near future.

Sergio Bertolotti · Post by **Sergio Bertolotti** » Mon 21 Jan 2008 12:44

Dear Antaeus,

many thank's for your answer...

SDAC interprets BIT columns as TBooleanField. True and False are acceptable values for BIT fields. True means 1, False means 0.

OK. But why default expression for the esente field is true.
If you see my originally srcipt of table you can view :

...
ESENTE] bit DEFAULT 0 NOT NULL,
...

Default is False not true.

Why SDAC set default value of this field to true ?

Is it a bug ?

Best regards.

Sergio Bertolotti

Antaeus · Post by **Antaeus** » Tue 22 Jan 2008 12:30

Thank you. We have fixed this problem. This fix will be included in the next SDAC build.

Sergio Bertolotti · Post by **Sergio Bertolotti** » Tue 19 Feb 2008 10:39

Dear Antaeus,

I download your new version of components 4.35.1.15 for Delphi 2007.

I see that bug with BIT field was solved , but bugs for getdate() and user_name() function still persist.

I remove from my InsertSQL of TMSQuery - getdate() and user_name() functions but nothing to do, when I press insert button on TDBNavigator I obtain an error message...

If you want I send to you a small test program with script SQL to create the problem.

Best regards.

Sergio Bertolotti

Challenger · Post by **Challenger** » Fri 22 Feb 2008 12:48

We have fixed the problem for non-string fields. So the user_name default value does not work. We are working on it. As soon as we get any results we will let you know.

Sergio Bertolotti · Post by **Sergio Bertolotti** » Sat 23 Feb 2008 14:46

Dear Challenger,

many thank's for your answer.

I stay tune about your news.

Best regards.

Sergio Bertolotti

Antaeus · Post by **Antaeus** » Wed 05 Mar 2008 16:17

We have fixed the problem for string fields as well. This fix will be included in the next build of SDAC.

Sergio Bertolotti · Post by **Sergio Bertolotti** » Thu 06 Mar 2008 07:52

Dear Antaeus,

many thank's for your answer and support for SDAC product.

Best regards.

Sergio Bertolotti