DZone Snippets: sql code

how to create a new user in MySQL

jm3 (john manoogian III) — Mon, 24 Mar 2008 04:06:23 GMT

// first, mysql -uroot mysql
// then:





  mysql% GRANT ALL PRIVILEGES ON *.* TO 'jm3_spoon'@'localhost' IDENTIFIED BY 'stirthatshit' WITH GRANT OPTION;

  mysql% create database jm3_agitator;

Drop an unnamed constraint from SqlServer 2000

frost137 (Douglas Wyatt) — Wed, 12 Mar 2008 22:00:43 GMT

// description of your code here



declare @name nvarchar(32), 

    @sql nvarchar(1000)



-- find constraint name

select @name = O.name 

from sysobjects AS O

left join sysobjects AS T

    on O.parent_obj = T.id

where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0

    and O.name not like '%dtproper%'

    and O.name not like 'dt[_]%'

    and T.name = 'MyTable'

    and O.name like 'DF__MyTable__MyColu%'



-- delete if found

if not @name is null

begin

    select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'

    execute sp_executesql @sql

end



-- do your ALTER TABLE here



-- replace the constraint

select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]'

execute sp_executesql @sql

Rails Array#add_condition Method

timmorgan (Tim Morgan) — Mon, 18 Feb 2008 02:54:44 GMT

Lets you add a condition to a set of ActiveRecord conditions easily like this:



  conditions = ['active = ? and type = ?', true, 2]

  conditions.add_condition ['person_id = ?', 345]



class Array

  def add_condition(condition, conjunction='and')

    if condition.is_a? Array

      if self.empty?

        (self << condition).flatten!

      else

        self[0] += " #{conjunction} " + condition.shift

        (self << condition).flatten!

      end

    elsif condition.is_a? String

      self[0] += " #{conjunction} " + condition

    else

      raise "don't know how to handle this condition type"

    end

    self

  end

end

Regular Expressions with MySQL

ki4ngel (Benoit Asselin) — Sat, 19 Jan 2008 22:05:22 GMT



SELECT * FROM texts WHERE content REGEXP '[^a-z]Hello[^a-z]' ;

If you really want to force a REGEXP comparison to be case sensitive, use the BINARY keyword to make one of the strings a binary string.



SELECT * FROM texts WHERE content REGEXP BINARY '[^a-zA-Z]Hello[^a-zA-Z]' ;

Warning: some characters do not work. Example :



SELECT * FROM texts WHERE content REGEXP '[^\w]Hello[^\w]' ;

ab-d.fr source code

GPS distance and initial bearing between points (MySQL)

mrclay (Steve Clay) — Wed, 16 Jan 2008 21:14:20 GMT

Assume you have a table of locations with Latitude and Longitude for each one. In my case the table is "station" and the primary key is "LocID".

First we create a view to help with the 3D geometry (6378 = Earth's radius in km):



CREATE VIEW gpsGlb AS

    SELECT 

        LocID

        ,6378 * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude)) AS x

        ,6378 * COS(RADIANS(Latitude)) * SIN(RADIANS(Longitude)) AS y

        ,6378 * SIN(RADIANS(Latitude)) AS z

    FROM station;

Here I query for distances to all my locations that are NOT LocID = 405 (rounded miles in my case):



SELECT 

    LocID

    ,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi

FROM

    (SELECT

        SQRT(dx * dx + dy * dy + dz * dz) AS d

        ,LocID

     FROM

        (SELECT

            p1.x - p2.x AS dx

            ,p1.y - p2.y AS dy

            ,p1.z - p2.z AS dz

            ,p2.LocID

        FROM gpsGlb p1

        JOIN gpsGlb p2 ON (p1.LocID = 405 AND p2.LocID != 405)

       ) t1

    ) t2

ORDER BY dist_mi

Here I get the initial bearing to the locations. The "boxed" calculation will come in handy later.



SELECT

    LocID

    ,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg

    ,ROUND(((360 + DEGREES(ATAN2(y, x))) % 360) / 22.5) * 22.5 

     AS initBearingBoxed_deg

FROM

    (SELECT

        SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude)) 

        AS y

        ,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))

            - SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))

               * COS(RADIANS(s2.Longitude - s1.Longitude)) 

        AS x

        ,s2.LocID

    FROM station s1

    JOIN station s2 ON (s1.LocID = 405 AND s2.LocID != 405)

    ) q1

Here's the combined query plus boxed degrees converted to 'NNE', etc. I've also added a limit for the distance in the qq1 subquery.



SELECT

    qq2.LocID

    ,dist_mi

    ,CASE initBearingBoxed_deg

        WHEN 22.5 THEN 'NNE'   WHEN 45 THEN 'NE'

        WHEN 67.5 THEN 'ENE'   WHEN 90 THEN 'E'

        WHEN 112.5 THEN 'ESE'  WHEN 135 THEN 'SE'

        WHEN 157.5 THEN 'SSE'  WHEN 180 THEN 'S'

        WHEN 202.5 THEN 'SSW'  WHEN 225 THEN 'SW'

        WHEN 247.5 THEN 'WSW'  WHEN 270 THEN 'W'

        WHEN 292.5 THEN 'WNW'  WHEN 315 THEN 'NW'

        WHEN 337.5 THEN 'NNW'  ELSE 'N'

     END AS bearing

FROM (

    SELECT 

        LocID

        ,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi

    FROM

        (SELECT

            SQRT(dx * dx + dy * dy + dz * dz) AS d

            ,LocID

         FROM

            (SELECT

                p1.x - p2.x AS dx

                ,p1.y - p2.y AS dy

                ,p1.z - p2.z AS dz

                ,p2.LocID

            FROM gpsGlb p1

            JOIN gpsGlb p2 ON (p1.LocID = 405 AND p2.LocID != 405)

           ) t1

        ) t2

    ) qq1

JOIN (

    SELECT

        LocID

        ,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg

        ,(360 + ROUND((DEGREES(ATAN2(y, x))) / 22.5) * 22.5) % 360 

         AS initBearingBoxed_deg

    FROM

        (SELECT

            SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude)) 

             AS y

            ,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))

                - SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))

                   * COS(RADIANS(s2.Longitude - s1.Longitude)) 

             AS x

            ,s2.LocID

        FROM station s1

        JOIN station s2 ON (s1.LocID = 405 AND s2.LocID != 405)

        ) q1

    ) qq2 ON (qq1.LocID = qq2.LocID

              AND qq1.dist_mi <= 60)

ORDER BY dist_mi

Find Exact Directory name of Variable Directory Name

JhaRakesh (Rakesh Jha) — Wed, 09 Jan 2008 08:29:43 GMT

This sql server procedure help you, if you have remmember some string of your directory and path name then you will get the exact directory name of that path.



create proc usp_srchDir

(

	@pathName nvarchar(100),

	@strDirName nvarchar(50),

	@ExactName nvarchar(20) OUTPUT

)

AS

BEGIN

	SET NOCOUNT ON

	DECLARE @strCMD nvarchar(500)

	SET @strCMD='dir  "'+ @pathName +'" /ad/o | find /I "'+@strDirName+'"'

	create table tblFindDIR(SearchDIRName nvarchar(200))

	insert into tblFindDIR(SearchDIRName)	

	exec master.dbo.xp_cmdshell @strCMD

	delete from tblFindDIR where isnull(SearchDIRName,'')=''

	select @ExactName=ltrim(rtrim(right(SearchDIRName,20))) from tblFindDIR where SearchDIRName like '%'+@strDirName+'%' 

	drop table tblFindDIR

	SET NOCOUNT OFF

END



GO

Some problems with charset in UTF-8 ?

ki4ngel (Benoit Asselin) — Fri, 23 Nov 2007 22:07:58 GMT

So you can use this request MySQL before all others, for fix your problems :



...

mysql_query( "SET NAMES 'utf8' " );

...

Source: ab-d.fr
Languages: PHP and MySQL

Use get_magic_quotes_gpc();

ki4ngel (Benoit Asselin) — Sun, 18 Nov 2007 20:57:07 GMT



function f_magic_quotes($text) {

	if ( !get_magic_quotes_gpc() ) {

		return addslashes($text);

	} else {

		return $text;

	}

}



function f_clean_quotes($text) {

	if ( !get_magic_quotes_gpc() ) {

		return $text;

	} else {

		return stripslashes($text);

	}

}

Source: ab-d

How to produce daily database table dumps in CSV format

ejboy (Fyodor Kupolov) — Fri, 16 Nov 2007 21:08:12 GMT

The following code demonstrates how to produce CSV files with dynamic
file name pattern based on a current day. The produced files have the following naming format:
TABLE_NAME_MM_DD_YYYY.csv







     

        table_name=test

    

          classpath="ojdbc14.jar" user="scott" password="tiger"/>

    

     

        SELECT * FROM ${table_name}

Use Scriptella ETL to run the example.

See How to execute an ETL file from command line, Ant or directly from Java .

disable SQL / MySQL in rails logging in development mode

jm3 (john manoogian III) — Fri, 02 Nov 2007 00:09:50 GMT

// description of your code here



ActiveRecord::Base.logger = Logger.new("#{RAILS_ROOT}/log/#{RAILS_ENV}_database.log")

goes at end of config/env