Forgot your password?
typodupeerror
User Journal

Journal: SQL Server Search in stored procedures

Journal by joke_dst

A small script to search for a string in all stored procedures/triggers/functions etc. It outputs every occurance with some context (currently 50 chars before & after)
This searches for InFileID. Replace with something else.
This is not a stored procedure, because I don't want to create stored procedures in every database I need this functionality...

declare @pattern nvarchar(1000) set @pattern='%InFileID%'
declare @oid int set @oid=0
declare @oldOid int set @oldOid=-1
declare @text nvarchar(4000)
declare @index int
declare @relativeIndex int
declare @begindex int

while 1=1 begin
        select top 1 @oid=c.id, @text=c.text from sys.syscomments c where c.id>@oid and c.text like @pattern order by c.id
        if @oid=@oldOid break
        select o.Name, o.Type from sys.sysobjects o where o.id=@oid --Output name & type of proc/function/trigger

        set @index=0
        while 1=1 begin
                select @relativeIndex=patindex(@pattern, substring(@text, @index+1, 9999))
                if @relativeIndex=0 break
                set @index=@index+@relativeIndex
                set @begindex = (@index-50 + ABS(@index-50))/2 -- equals Max(0, @index-50)
                select @index inx, substring(@text, @begindex, 100) -- Output location and context of found occurance
        end

        set @oldOid=@oid
end

User Journal

Journal: bash script for ignoring all .*.cmd files

Journal by joke_dst

This command finds all directories with .*.cmd files (like all the .o.cmd files generating when building the kernel) and set the svn ignore command on them

svn status | grep "\.cmd" | cut -b8- | cut -d. -f1 | sort | uniq | xargs svn propset svn:ignore '.*.cmd'

Perl

Journal: Perl script for finding patterns

Journal by joke_dst

Ok, I reallt needed a bash command that could list the index of every occurance of a pattern in a file. I found this one-liner before:

perl -0777 -ne 'print index $_, "\x5d\x00\x00\x80\x00\x00"' afile.bin

This locates the first LZMA header in a file, but I need to find ALL headers, not just the first... Ok, I'm no perl specialist, but I came up with this:

#!/usr/bin/perl
use strict;
use warnings;

my $count = @ARGV;
if ( $count < 2 ) {
print "bff - Binary Find in Files. Finds all occurances of a string in a file\nusage: bff <string> <filename>\n";
exit();
}

open FILE, $ARGV[1] or die $!;
my $char = eval '"'.$ARGV[0].'"';
my $offset = 0;
my $result = index(<FILE>, $char);

while ($result != -1) {
$offset += $result;
print "Found at $offset\n";

$offset += 1;
seek FILE, $offset, 0;
$result = index(<FILE>, $char);
}

This allows me to write

bff "\x5d\x00\x00\x80\x00\x00" afile.bin

and lists all occurances of the header in the file.

As always, this is a personal reference page, but use it if you like it...

User Journal

Journal: Bash variable handling 1

Journal by joke_dst
Since I still haven't found any comprehensive source for how the evil variables in bash can be truncated, clipped and so on so here's a short list for me mostly:

VAR=hello in all examples

String length: ${#VAR}
ex:
echo ${#VAR} #yields '5'

Substring: ${#VAR:[chars to remove from start]:[chars to return]}
Last parameter can be ignored if you want to the end ("${VAR:2}")
ex:
echo ${VAR:1:3} #yields 'ell'

Remove last character: ${VAR%?}
ex:
echo ${VAR%?} #yields 'hell'

Return last character: ${VAR:${#VAR}-1}
Bit messy, best I could find though
ex:
echo ${VAR:${#VAR}-1} #yields 'o'

To be continued...

User Journal

Journal: SQL Trigger that turns insert into update

Journal by joke_dst
-- When something is INSERTed into this table and the post already exists, does an UPDATE instead

Create TRIGGER PriceManager.Trigger_PriceList_Insert
   ON  TheDB.PriceList
   INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    --Update if exists
    UPDATE [PriceManager].[PriceList]
       SET [SalesPrice] = isnull(inserted.[SalesPrice],PriceList.[SalesPrice])
          ,[OnRequest] = isnull(inserted.[OnRequest],PriceList.[OnRequest])
          ,[ChangedTime] = isnull(inserted.[ChangedTime],PriceList.[ChangedTime])
          ,[ChangedBy] = isnull(inserted.[ChangedBy],PriceList.[ChangedBy])
    from inserted
     WHERE inserted.[Company] = PriceList.[Company]
          and inserted.[ItemNumber] = PriceList.[ItemNumber]
          and inserted.[Date] = PriceList.[Date]
          and inserted.[Type] = PriceList.[Type]
          and inserted.[PriceListId] = PriceList.[PriceListId]

    --insert if don't
    insert into [PriceManager].[PriceList]
    select * from inserted
    where not exists (
        select 1 from [PriceManager].[PriceList] p2
        WHERE inserted.[Company] = p2.[Company]
          and inserted.[ItemNumber] = p2.[ItemNumber]
          and inserted.[Date] = p2.[Date]
          and inserted.[Type] = p2.[Type]
          and inserted.[PriceListId] = p2.[PriceListId]
        )
END
User Journal

Journal: SQL function to calculate number of work days

Journal by joke_dst

This is a follow up on the previous post about easter day, the function getEasterDate is required...

This calculates the number of work days between two dates, using Swedish holidays.

This consists of two functions, one that calculates if a given day is a holiday (isHoliday()) and one that simply loops through the range of days given and checks if any is a holyday (numberOfWorkDays())

If anyone wants to convert this to another country or optimize this one, please tell me about it! I'd love to see it...

Create Function isHoliday(@date as datetime)
returns bit
as begin
    if DATEPART(dw, @date) in (1,7)
        or (DAY(@date)=1 and MONTH(@date) in (1,5)) --Jan 1, May 1
        or (DAY(@date)=6 and MONTH(@date)=6) --6 july, national day
        or (MONTH(@date)=12 and DAY(@date) in (25,26)) --xmas
        return 1
    declare @easter as datetime set @easter = dbo.getEasterDate(YEAR(@date))
    if DATEDIFF(day, @easter, @date) in (-2, 1, 39) --easter + ascention
        return 1
    return 0
end

go

Create function numberOfWorkDays( @fromDate as datetime, @toDate as datetime)
returns int
as begin
    declare @day as datetime
    declare @endday as datetime
    declare @dayCount int

    set @day = @fromdate
    set @endday = @toDate
    set @dayCount = 1

    if @endday @day begin
        declare @tempday datetime set @tempday=@day
        set @day=@endday
        set @endday = @tempday
    end

    while @day @endday begin
        if dbo.isHoliday(@day)=0
                set @dayCount = @dayCount + 1

        set @day = dateadd(day, 1, @day)
    end

    return @dayCount
end

User Journal

Journal: SQL function for calculating easter date

Journal by joke_dst

A SQL function to calculate the easter date on any given year:

create function getEasterDate( @yr int )
returns datetime
As Begin

        declare @g int, @c int, @x int, @z int, @d int, @e int, @n int

        set @g = @yr % 19 + 1
        set @c = @yr / 100 + 1
        set @x = (3 * @c) / 4 - 12
        set @z = (8 * @c + 5) / 25 - 5
        set @d = (5 * @yr) / 4 - @x - 10
        set @e = (11 * @g + 20 + @z - @x) % 30

        if (@e = 24) or ((@e = 25) and (@g > 11)) set @e = @e + 1

        set @n = 44 - @e
        if (@n 31 begin
                set @g = 4
                set @n = @n-31
        end

        return cast( cast(@yr as varchar) + '-' + cast(@g as varchar) + '-' + cast(@n as varchar) as datetime)
End

"An open mind has but one disadvantage: it collects dirt." -- a saying at RPI

Working...