InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:48:31 GMT20http://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxSimplest 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.Sat, 12 Jan 2013 11:57:09 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxHIFirst I must say that Im just a rookie on SQL.At this moment I'm creating a website using Joomla 2.5So 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[quote]Create View OneRandomString20AS witha1 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 ',' + RandomStringfrom cteRandomStringfor xml path ('')),',','') AS Results;GOCREATE FUNCTION fn_RandomString()returns varchar(20)ASBEGINDeclare @results varchar(20)SELECT top 1 @results = Results from OneRandomString20return @resultsENDGOselect 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 */;[/quote]But I got the following error as result of the import[quote]Errorconsulta SQL: DocumentaciónCreate 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[/quote]Can someone Help meSat, 12 Jan 2013 10:48:00 GMTnaaxmilcorphttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspx[quote][b]Lowell (4/6/2009)[/b][hr]why is it every woman i meet on the internet is really a guy?just kidding, and my apologies.[/quote]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.Mon, 06 Apr 2009 06:53:43 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxwhy is it every woman i meet on the internet is really a guy?just kidding, and my apologies.Mon, 06 Apr 2009 06:33:26 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspx[quote][b]Lowell (4/6/2009)[/b][hr]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.[/quote]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.Mon, 06 Apr 2009 06:23:06 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxI 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.Mon, 06 Apr 2009 06:03:47 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspx[quote][b]Lynn Pettis (4/4/2009)[/b][hr]First, I was slightly confused, as the code Lowell posted uses a CTE and this is in a SQL Server 7, 2000 forum.[/quote]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.Mon, 06 Apr 2009 05:46:14 GMTAFIFMhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxFirst, 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.[code]create view dbo.MyNewID asselect newid() as NewIDValue;gocreate 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);endGO[/code]Sat, 04 Apr 2009 22:00:36 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxI 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.Sat, 04 Apr 2009 19:34:44 GMTAFIFMhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxsorry 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?)Sat, 04 Apr 2009 19:25:00 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxI think I got it. I entered 'dbo.fn_RandomString()' and it worked.Sat, 04 Apr 2009 18:55:18 GMTAFIFMhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxVery 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?Sat, 04 Apr 2009 18:47:28 GMTAFIFMhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxyou'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 thoughcan 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:[code]tmpid password morestuff1 BSLPYRZPOAH8LDCW19MJ one2 T2OD2LLJC9IPWLNEOIJE two3 C3U4KN5NHSXEIGOZ6BGH three[/code]and the code:[code]Create View OneRandomString20AS witha1 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 ',' + RandomStringfrom cteRandomStringfor xml path ('')),',','') AS Results;GOCREATE FUNCTION fn_RandomString()returns varchar(20)ASBEGINDeclare @results varchar(20)SELECT top 1 @results = Results from OneRandomString20return @resultsENDGOselect 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[/code]Sat, 04 Apr 2009 17:32:58 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxI 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.Sat, 04 Apr 2009 17:15:47 GMTAFIFM