January 12, 2013 at 11:13 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
This topic was discussed some time ago at:
http://www.sqlservercentral.com/Forums/Topic690526-169-1.aspx
But I just found it and I put it back. So I do apologize if I'm doing a wrong thing
QUESTIONS
I wonder if the code 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?
Also if the random string created by the code is "unique"?
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=xxxxx ;
--
-- 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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply