DefaultValues for GUIDs and DefaultValues for scalar function

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jmuehlenhoff
Posts: 36
Joined: Fri 30 Apr 2010 11:25

DefaultValues for GUIDs and DefaultValues for scalar function

Post by jmuehlenhoff » Tue 29 Oct 2013 14:18

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
Posts: 36
Joined: Fri 30 Apr 2010 11:25

Re: DefaultValues for GUIDs and DefaultValues for scalar function

Post by jmuehlenhoff » Tue 29 Oct 2013 14:39

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

Re: DefaultValues for GUIDs and DefaultValues for scalar function

Post by AndreyZ » Wed 30 Oct 2013 11:50

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
Posts: 36
Joined: Fri 30 Apr 2010 11:25

Re: DefaultValues for GUIDs and DefaultValues for scalar function

Post by jmuehlenhoff » Wed 30 Oct 2013 12:06

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

Re: DefaultValues for GUIDs and DefaultValues for scalar function

Post by AndreyZ » Wed 30 Oct 2013 14:04

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

jmuehlenhoff
Posts: 36
Joined: Fri 30 Apr 2010 11:25

Re: DefaultValues for GUIDs and DefaultValues for scalar function

Post by jmuehlenhoff » Wed 30 Oct 2013 14:08

Thank you, much appreciated.

AndreyZ

Re: DefaultValues for GUIDs and DefaultValues for scalar function

Post by AndreyZ » Wed 30 Oct 2013 14:09

You are welcome.

Post Reply