Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Random String on Record Creation Expand / Collapse
Author
Message
Posted Monday, April 6, 2009 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
why is it every woman i meet on the internet is really a guy?

just kidding, and my apologies.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #690944
Posted Monday, April 6, 2009 6:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:05 PM
Points: 20,738, Visits: 32,520
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #690964
Posted Saturday, January 12, 2013 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 12, 2013 10:55 AM
Points: 2, Visits: 1
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
Post #1406390
Posted Saturday, January 12, 2013 11:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:05 PM
Points: 20,738, Visits: 32,520
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1406401
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse