Page 1 of 1
DefaultValues for GUIDs and DefaultValues for scalar function
Posted: Tue 29 Oct 2013 14:18
by 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
which works fine for SQL Server, but SDAC fails to execute the function, instead I get an invalid field value exception.
Re: DefaultValues for GUIDs and DefaultValues for scalar function
Posted: Tue 29 Oct 2013 14:39
by 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.
Re: DefaultValues for GUIDs and DefaultValues for scalar function
Posted: Wed 30 Oct 2013 11:50
by 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.
Re: DefaultValues for GUIDs and DefaultValues for scalar function
Posted: Wed 30 Oct 2013 12:06
by 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.
Re: DefaultValues for GUIDs and DefaultValues for scalar function
Posted: Wed 30 Oct 2013 14:04
by AndreyZ
We added support of default values of UNIQUEIDENTIFIER fields without curly brackets. This functionality will be included in the next SDAC build.
Re: DefaultValues for GUIDs and DefaultValues for scalar function
Posted: Wed 30 Oct 2013 14:08
by jmuehlenhoff
Thank you, much appreciated.
Re: DefaultValues for GUIDs and DefaultValues for scalar function
Posted: Wed 30 Oct 2013 14:09
by AndreyZ
You are welcome.