DefaultValues for GUIDs and DefaultValues for scalar function

[jmuehlenhoff]

Hi,

I have encountered some problems when using `Query.Options.DefaultValues := True`.

1. GUIDs

When I specify a static GUID like

Code: Select all

'DC5045E2-EAB9-4B94-851C-61406F3D793D'

as the default value for a UNIQUEIDENTIFIER field it doesn't work. I have to write it in Delphi syntax

Code: Select all

'{DC5045E2-EAB9-4B94-851C-61406F3D793D}'

(with square brackets) whereas the SQL Server accepts both syntax variants.

2. Scalar function

I got a table that has a DATETIME field. This field should call a scalar function to obtain it's DEFAULT value. So I set the default for the field to

Code: Select all

[dbo].[DefaultDeadline] ()

which works fine for SQL Server, but SDAC fails to execute the function, instead I get an invalid field value exception.

[jmuehlenhoff]

You can forget the second problem I was able to fix it.

I accidently put the DEFAULT value in single quotes which made it invalid. If I leave out the single quotes the custom scalar function works fine as a DEFAULT value.

[AndreyZ]

If you want to represent GUID values as strings, they must be enclosed with the {} symbols. It is a correct way to work with GUID values in Delphi. You can check this using the following code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  gd = 'DC5045E2-EAB9-4B94-851C-61406F3D793D';
begin
  StringToGUID(gd); // here the ''DC5045E2-EAB9-4B94-851C-61406F3D793D' is not a valid GUID value' error will be generated
end;

The following code does not cause any problems:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  gd = '{DC5045E2-EAB9-4B94-851C-61406F3D793D}';
begin
  StringToGUID(gd);
end;

StringToGUID is a standard Delphi function that converts strings to GUIDs.

[jmuehlenhoff]

I understand that, I was just pointing out that you can have:

Code: Select all

CREATE TABLE
  test
(
    test_id    UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
  , foreign_id UNIQUEIDENTIFIER NOT NULL DEFAULT 'DC5045E2-EAB9-4B94-851C-61406F3D793D'
  , name       NVARCHAR(100) NOT NULL
)

INSERT INTO
  test
(
  name
)
VALUES
(
  'abc'
)

SELECT * FROM test

SQL Server will insert a new row just fine.

If you try to insert a row from Delphi with TMSQuery/TMSTable and "DefaultValues := True" you will get an error message, because it's not a valid Delphi GUID.

This was causing a lot of trouble for me, I had to change all static GUID default values in my database to use the bracket style GUID strings.

I can live with it. It would however be nice if SDAC did support both GUID string formats.

[AndreyZ]

We added support of default values of UNIQUEIDENTIFIER fields without curly brackets. This functionality will be included in the next SDAC build.

[jmuehlenhoff]

Thank you, much appreciated.

[AndreyZ]

You are welcome.