NetTalk Central
NetTalk Web Server => Web Server - Ask For Help => Topic started by: Ubaidullah on October 16, 2020, 06:01:19 AM
-
Hi Bruce,
I am inserting a bunch of data into a SQL table using insert statements. What would be the best way to generate GUID values in SQL ?
A hack I am thinking of is to have a temp table with GUID values populated by a Clarion procedure and then update the inserted rows. But a SQL function to generate GUID along the lines of StringTheory would be good.
edit: I thought of using this for GUID fields inserted using SQL:
substring(cast(newid() as char(37)),1,16)
Thoughts?
Regards,
Ubaidullah Nubar.
-
Here's an example of how I do it in SQL tables, Ubaidullah. Table has a default value for the GUID that automatically uses a function to generate one when a new row is inserted. That function relies on a view to make SQL random be non-deterministic. (Google is my friend!)
-- jf 12/10/2018 - used by jfGuid function
create view iFunction as
select MyNewID = newid(),
MyDate = getdate(),
MyString = cast(newid() as varchar(36)),
MyChar = char(floor(rand() *(122-97+1)+97)),
MyNum = cast((floor(rand() *(9) + 1)) as char(1)),
MySelector = floor(rand()*(2-1+1) + 1);
-- jf 12/10/2018 - make non-standard guid
-- relies on iFunction view for non-deterministic random stuff
-- EX version has parameters for length and case. This one uses defaults
create function jfGuid ()
returns varchar(100)
as
begin;
declare @a int = 1,@len int = 16;
declare @rv varchar(16)='',@selector int,@thisChar char(1);
while @a <= @len
begin;
set @a +=1;
select @selector = MySelector from ifunction
--print @selector;
if @selector = 1
begin;
select @thisChar = MyChar from iFunction
end;
else
begin;
select @thisChar = MyNum from iFunction
end;
set @rv += @thisChar;
--print 'rv = '+ @rv;
end;
select @rv = upper(@rv);
return @rv;
end;
CREATE TABLE [dbo].[appLoginAudit](
[pk] [int] IDENTITY(1,1) NOT NULL,
[GUID] [char](16) NOT NULL,
[windowsName] [varchar](100) NULL,
[workstationName] [varchar](100) NULL,
[appName] [varchar](20) NULL,
[startupTime] [datetime] NULL,
[activeTimeBias] [int] NULL,
[ts] [float] NULL,
[sts] [float] NULL,
[dts] [float] NULL,
CONSTRAINT [appLoginAuditGuid] PRIMARY KEY CLUSTERED
(
[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[appLoginAudit] ADD DEFAULT ([dbo].[jfGuid]()) FOR [GUID]
GO
ALTER TABLE [dbo].[appLoginAudit] ADD DEFAULT ([dbo].[jfUtcOffset]()) FOR [activeTimeBias]
GO
ALTER TABLE [dbo].[appLoginAudit] ADD DEFAULT ((0)) FOR [ts]
GO
ALTER TABLE [dbo].[appLoginAudit] ADD DEFAULT ((0)) FOR [sts]
GO
ALTER TABLE [dbo].[appLoginAudit] ADD DEFAULT ((0)) FOR [dts]
GO
[edited to add remaining table defaults]
-
MySQL has a function called UUID() that will do this for you. Other SQLs probably have similar functions.
You can even add it to an insert trigger and it will prime itself for you. But from memory, Clarion PROP:ServerAutoInc can't handle this (or at least I haven't bothered to find out how to get it to work).
-
>> MySQL has a function called UUID() that will do this for you. Other SQLs probably have similar functions.
true, but they make binary values, not alphanumeric values. This would be a bad thing.
What you want to do is populate the field with a random string - using (ideally) only upper case alphabetic chars, and numbers.
(ie, basically a 16 digit, base36 number.)
Also, while it can be set in a trigger, this is again not ideal (I'd go so far as to say "bad"). If it was in a trigger, it should ONLY populate the value if the field is BLANK. One of the goals of using GUIDs is to reduce the whole "server side assigning a value" problem, which makes parent-child forms so ugly.
Cheers
Bruce
-
>> I am inserting a bunch of data into a SQL table using insert statements. What would be the best way to generate GUID values in SQL ?
the best way is not to generate them in SQL at all. Like all the other fields in the row, they should be generated by the client program.
Cheers
Bruce