April 4, 2009 at 5:15 pm
I want to create a random string each time a new record is ceated in the database. I have the SQL code which will create the string but I am not sure how to make it execute and insert the value each time a new record is created.
April 4, 2009 at 5:32 pm
you'll need to make your code a function.
once it is a function, say udfRandomString()
you can create a table and set it's default value to the function.
you might run into problems if your script uses newid in it, since that's not allowed in a directly function;
you can get around that by using a view though
can we see your function?
here is a complete example, i'm using a view and really nice function(it uses a Tally table) that assumes 20 characters, you can change the values to what you need.
here's typical results:
tmpidpassword morestuff
1 BSLPYRZPOAH8LDCW19MJ one
2 T2OD2LLJC9IPWLNEOIJE two
3 C3U4KN5NHSXEIGOZ6BGH three
and the code:
Create View OneRandomString20
AS
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select
row_number() over (order by N) as N
from
a4)
, cteRandomString (
RandomString
) as (
select top (20)
substring(x,(abs(checksum(newid()))%36)+1,1)
from
Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a
)
select
replace((select
',' + RandomString
from
cteRandomString
for xml path ('')),',','') AS Results;
GO
CREATE FUNCTION fn_RandomString()
returns varchar(20)
AS
BEGIN
Declare @results varchar(20)
SELECT top 1 @results = Results from OneRandomString20
return @results
END
GO
select dbo.fn_RandomString()
CREATE TABLE tmp(tmpid int identity(1,1) not null primary key,password varchar(20) default(dbo.fn_RandomString()),morestuff varchar(30) )
insert into tmp(morestuff)
select 'one' union all select 'two' union all select 'three'
select * from tmp
Lowell
April 4, 2009 at 6:47 pm
Very nice sample. So now I have a function called 'fn_RandomString' and in the table field I set the 'Default Value for Binding' to 'fn_RandomString()'. But when records are inserted in the table the field is populated with the string 'fn_RandomString()' and not the random string. Where you think I went wrong?
April 4, 2009 at 6:55 pm
I think I got it. I entered 'dbo.fn_RandomString()' and it worked.
April 4, 2009 at 7:25 pm
sorry about the naming thing...i wrote my comments, then added the code, change the name along the way in there....
glad it's working for you.
did you change it to be a different length than 20 characters? what are you using it for? ( i assumed a random password?)
Lowell
April 4, 2009 at 7:34 pm
I changed the 20 to 64 and I am using this random string as a handshake token between two applications. My next task is to replicate this random string on the other application LDAP directory DB.
April 4, 2009 at 10:00 pm
First, I was slightly confused, as the code Lowell posted uses a CTE and this is in a SQL Server 7, 2000 forum.
But, since it worked, I thought I'd throw something else out for you all. This code looks familiar, but I made a slight change.
First, I created a view that returns a NEWID as NewIdValue. I then use this view in my function where Lowell had the NEWID() function in his view. The difference, a single function that takes as a paramter the length of the random string to be returned.
create view dbo.MyNewID as
select newid() as NewIDValue;
go
create function dbo.ufn_RandomString(
@pStringLength int = 20
) returns varchar(max)
as begin
declare @RandomString varchar(max);
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select
row_number() over (order by N) as N
from
a4)
, cteRandomString (
RandomString
) as (
select top (@pStringLength)
substring(x,(abs(checksum((select NewIDValue from MyNewID)))%36)+1,1)
from
Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a
)
select @RandomString =
replace((select
',' + RandomString
from
cteRandomString
for xml path ('')),',','');
return (@RandomString);
end
GO
April 6, 2009 at 5:46 am
Lynn Pettis (4/4/2009)
First, I was slightly confused, as the code Lowell posted uses a CTE and this is in a SQL Server 7, 2000 forum.
My bad. I am using SQL 2005 and I posted the question in the wrong forum. The code provided by Lowell worked fine. Lynn, I will try your suggestion later today. Thank you both.
April 6, 2009 at 6:03 am
I like Lynn's a lot more; having a view with just a newid in it is more useful, since i could use the same view for other purposes.
my code was a verbatim copy of one of her examples anyway, i just made it a function.
Lowell
April 6, 2009 at 6:23 am
Lowell (4/6/2009)
I like Lynn's a lot more; having a view with just a newid in it is more useful, since i could use the same view for other purposes.my code was a verbatim copy of one of her examples anyway, i just made it a function.
Two things, Lowell. One, I thought the code looked familiar, and two, I'm a he. Don't worry, others here have mad the same mistake, and after many years I have learned to shrug it off.
April 6, 2009 at 6:33 am
why is it every woman i meet on the internet is really a guy?
just kidding, and my apologies.
Lowell
April 6, 2009 at 6:53 am
Lowell (4/6/2009)
why is it every woman i meet on the internet is really a guy?just kidding, and my apologies.
Not a problem. I have a friend whose name is Stacey and a co-worker whose name is Kim. Both have a simiar problem as myself, they are both guys.
We've learned to live with it and not take offense.
January 12, 2013 at 10:48 am
HI
First I must say that Im just a rookie on SQL.
At this moment I'm creating a website using Joomla 2.5
So I use PHPmyadmin
I wonder if the code you submited could be imported into the phpmyadmin (as a sql file) to create a table that includes a field with random string each time a new record is created in the database.
I used a notepad and save the following as a sql file
Create View OneRandomString20
AS
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select
row_number() over (order by N) as N
from
a4)
, cteRandomString (
RandomString
) as (
select top (20)
substring(x,(abs(checksum(newid()))%36)+1,1)
from
Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a
)
select
replace((select
',' + RandomString
from
cteRandomString
for xml path ('')),',','') AS Results;
GO
CREATE FUNCTION fn_RandomString()
returns varchar(20)
AS
BEGIN
Declare @results varchar(20)
SELECT top 1 @results = Results from OneRandomString20
return @results
END
GO
select dbo.fn_RandomString()
--
-- Estructura de tabla para la tabla `jrk57_users`
--
CREATE TABLE IF NOT EXISTS `jrk57_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`username` varchar(150) NOT NULL DEFAULT '',
`email` varchar(100) NOT NULL DEFAULT '',
`password` varchar(100) NOT NULL DEFAULT '',
`usertype` varchar(25) NOT NULL DEFAULT '',
`block` tinyint(4) NOT NULL DEFAULT '0',
`sendEmail` tinyint(4) DEFAULT '0',
`registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`activation` varchar(100) NOT NULL DEFAULT '',
`params` text NOT NULL,
`lastResetTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date of last password reset',
`resetCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Count of password resets since lastResetTime',
usmasterkey varchar(20) default(dbo.fn_RandomString()),
PRIMARY KEY (`id`),
KEY `usertype` (`usertype`),
KEY `idx_name` (`name`),
KEY `idx_block` (`block`),
KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=xxxx ;
--
-- Volcado de datos para la tabla `jrk57_users`
--
INSERT INTO `jrk57_users` (`id`, `name`, `username`, `email`, `password`, `usertype`, `block`, `sendEmail`, `registerDate`, `lastvisitDate`, `activation`, `params`, `lastResetTime`, `resetCount`) VALUES
(914, 'XXXXXXX', 'XXXXXX', 'XXXX@XXXXXXX.net', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxl', 'deprecated', 0, 1, '2013-01-08 15:24:12', '2013-01-08 21:21:28', '0', '', '0000-00-00 00:00:00', 0);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
But I got the following error as result of the import
Error
consulta SQL: Documentación
Create View OneRandomString20 AS with a1 as (select 1 as N union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1), a2 as (select 1 as N from a1 as a cross join a1 as b), a3 as (select 1 as N from a2 as a cross join a2 as b), a4 as (select 1 as N from a3 as a cross join a2 as b), Tally as (select row_number() over (order by N) as N from a4) , cteRandomString ( RandomString ) as ( select top (20) substring(x,(abs(checksum(newid()))%36)+1,1) from Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a ) select replace((select ',' + RandomString from cteRandomString for x[...]
MySQL ha dicho: Documentación
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a1 as (select 1 as N union all
select 1 union all
select 1 union' at lin
Can someone Help me
January 12, 2013 at 11:57 am
Simplest answer I can give you is that you are trying to use MS SQL Server based code in MySQL and MySQL doesn't like it.
This is a Microsoft SQL Server site, what you find here works on MS SQL Server (with the correct version depending on the code).
You may want to look for a MySQL forum where the may be able to provide you with better assistance.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply