Linked Server via MS Jet 4.0 Provider

Posted by Dan | Posted in Databases, SQL Server | Posted on 06-07-2010

0

Here’s another way to created a linked server using another provider, Microsoft Jet 4.0 OLE DB Provider. This is for SQL Server 2008. Check out my previous Linked Server tutorial if needed for SQL Server 2005.

For this setup, let’s use the login’s current security context. Make sure that you whatever SSMS you use to connect to the server uses the same user credentials as user that created the linked server.

So if I create that on a server, then I’ll have query it using the same credentials just used. If you want to know how to query it, check out my Previous Tutorial.

Important Phone Features

Posted by Dan | Posted in Technology, iPhone | Posted on 06-07-2010

0

My AT&T contract expires in August and am considering what phone I should get. The progress of the iPhone is practically stagnating compared to how much Android is making progress. Not to mention the incredibly sucky service AT&T provides and how much cash you have to shell out for it. So I’m considering getting away from the iPhone. Anyways, for my next phone, I’m looking for:

  • Good reception
  • 4G
  • Act as a hotspot
  • Android or iPhone OS
  • The phone must have headphones that function as a remote to control songs (pause/play)
  • Physical Keyboard

That’s really it. Also, I’m looking for a platform that provides the following apps:

  • PDF reader
  • Chm reader
  • Weather
  • Google maps
  • Lirr schedule + map
  • Subway map
  • Rss reader
  • Twitter app
  • Fandango
  • Chat
  • YouTube
  • Radio podcasts
  • Wikipedia
  • Yelp
  • Imdb
  • Taxi
  • Soundhound
  • Facebook
  • Linkedin
  • Amazon

Which both the Android and iPhone OS provide. I would’ve gotten the EVO 4G except it didn’t have a physical keyboard. We’ll see what comes out this year.

Tag a Revision using Subclipse

Posted by Dan | Posted in Development, Eclipse, Languages | Posted on 05-20-2010

0

Tagging a revision is a simple task if you’re using the subclipse plugin for Eclipse. For this example, we’re going to tag the trunk in our repository. This also assumes that your repository uses the traditional “3 directory setup” of branches, tags, and trunk.

1. Make sure you have a SVN View tab in Eclipse.

To do this, go to:
Window > Show View > Other > SVN > SVN Repositories

2. Under “WebSites” (in the following screenshot), right click on the trunk, and select “Branch/Tag…”:

3. Make sure you use the appropriate tag version that adheres to your versioning needs. Also, make sure it’s under the “tags” folder: svn://WebSites/Project-2/tags/

Resetting Image in Word

Posted by Dan | Posted in Technology | Posted on 05-20-2010

0

Here’s a silly little tip I’ve just discovered. Often when you take a screenshot and paste it into Word, it’ll come up blurry. To reset it so that the image is the same size and also looks like the actual screenshot:

1. Right click on the image and choose Format Picture…

2. Click on “Picture” then “Reset Picture”…

That’s it!

Testing Database Import Scripts with Snapshots

Posted by Dan | Posted in Automation / Scripting, Databases, SQL Server, Systems | Posted on 05-05-2010

0

Can’t stress enough how handy database snapshots (compatible only in Enterprise and Developer editions of SQL Server 2005+) come in when testing bulk imports, data scrubbing, or any sort of data / schema modification script. In nearly no time, I can create a snapshot (via shortcut snippet), run my script – don’t like the results? My script crapped out the data? I can run an instant undo by recovering from the snapshot, which works pretty fast. If I want to create another snapshot, I usually tend to overwrite the snapshot I created, and then create it again.

It’s so handy that I have a snippet for it:

Anywhoot, here’s how you create a snapshot:

1
2
3
4
5
6
7
-- CREATING A SNAPSHOT
CREATE DATABASE YourDatabase_snap ON         -- Name of new snapshot
(
  NAME = yourdb_data,                        -- Logical filename of source db you want to snapshot
  FILENAME = 'c:\YourDatabase_data_1800.ss'  -- Sparse file to create
)
AS SNAPSHOT OF YourDatabase;                 -- Source db name
1
2
3
4
-- RECOVERING FROM A SNAPSHOT
USE master;
RESTORE DATABASE YourDatabase                    -- Source db name where the data resided
FROM DATABASE_SNAPSHOT = 'YourDatabase_snap';    -- Snapshot db name

Deleting a snapshot is just like dropping a database.

1
DROP DATABASE YourDatabase_Snap

Implementing the Strategy Pattern in ColdFusion

Posted by Dan | Posted in ColdFusion, Java | Posted on 05-02-2010

0

In spirit of the amazing book Head First Design Patterns, I wanted to put together a ColdFusion example that depicts the Strategy pattern. Essentially, the Strategy Pattern lets you group related algorithms together so that an object is able to select which algorithm to run at runtime.

So let’s say for example you had a parent class that has two properties and two methods. You now create a subclass that inherits the parent class, meaning that it will gobble up all the properties and methods (whether you like it or not) from the parent class. You can’t choose, for example, what methods you subclass needs, even if some methods don’t make sense for your subclass. It’s an all-or-nothing solution. Yes, you can override, but what if there were 10 methods to override? Also, what if you had to create other types of subclasses – you’ll have to override those as well. Things can get a little sloppy at the end. That’s where the strategy pattern comes in.

With this pattern, you first think about related methods and algorithms. (One method can have various algorithms; different implementations for doing the same thing.) For this blog post’s example, we’ll think of different ways a SuperHero can punch. To keep things simple, let’s give him two ways (two different algorithms for punching) he can punch. He can punch normally, or he can punch you, which freezes you as well. Let’s UML this to make things clearer.

First what the lines mean:


Here’s the UML diagram for our SuperHero scenerio:


From the diagram, you can see that the SuperHero abstract class is able to choose a punch set of algorithms (a strategy). We also see that Punch and PunchFreeze are implementation classes (classes that serve to implement an interface). Both of them have a punch() method that return void (nothing) – in this example, they’ll do stuff, and not return anything.

To make things a little more interesting, and to follow closer the example in the Strategy Pattern chapter of the book, we’re going to also create a subclass called Freezer that inherits the SuperHero class. Also, we’ll create another strategy for kicking. Here’s what the UML for that looks like:


Here’s the CF code:

SuperHero.cfc

<cfcomponent>
 
<!--- This is our abstract class. Responsibility to implement is delegated to --->
<!--- classes that implement interfaces. --->
 
<cffunction name="init" access="public" returntype="SuperHero">  
  <!--- Some other init code goes here.  --->  
  <cfargument name="name" type="string">  
  <cfargument name="gender" type="string">  
  <cfset this.name = arguments.name />  
  <cfset this.gender = arguments.gender />  
  <cfreturn this />  
</cffunction>
 
<cffunction name="setPunchAlgorithm" access="public">
  <!--- The next two lines are key. It's where you set the implementation --->
  <!--- of punch from an object that's being passed in. --->
  <cfargument name="PunchAlgorithm" type="IPunchAlgorithm" required="true" />  
  <cfset this.punchAction = PunchAlgorithm.punch />
</cffunction>
 
<cffunction name="setKickAlgorithm" access="public">
  <cfargument name="KickAlgorithm" type="IKickAlgorithm" required="true" />  
  <cfset this.kickAction = KickAlgorithm.kick />
</cffunction>
 
<!--- This function will be overridden.  --->
<cffunction name="energyProject" access="public" returntype="SuperHero" >  
  You have been pointed at by a weak flash light.  
  <cfreturn this />
</cffunction>
 
</cfcomponent>

IPunchAlgorithm.cfc

<cfinterface>
 
<cffunction name="punch" access="public" />
 
</cfinterface>

Punch.cfc

<cfcomponent implements="IPunchAlgorithm">
 
<cffunction name="punch" access="public">
  You have been punched normally. Ouch.
</cffunction>
 
</cfcomponent>

PunchFreeze.cfc

<cfcomponent implements="IPunchAlgorithm">
 
<cffunction name="punch" access="public">
  You have been punched and are now frozen, stuck. Good luck thawing!
</cffunction>
 
</cfcomponent>

IKickAlgorithm.cfc

<cfinterface>
 
<cffunction name="kick" access="public" />
 
</cfinterface>

Kick.cfc

<cfcomponent implements="IKickAlgorithm">
 
<cffunction name="kick" access="public">
  You have been kicked in the gut. Yummy.
</cffunction>
 
</cfcomponent>

Freezer.cfc

<cfcomponent extends="SuperHero">
 
<!--- We are overriding the energyProject from Freezer's parent class.  --->
<cffunction name="energyProject">
  You have been snowed on. 
</cffunction>  
 
</cfcomponent>

Now let’s actually use these the pattern:

run.cfm

<!--- Create context object.  --->
<cfset IceMan = CreateObject( "component", "SuperHero" ).init( Name = "Iceman", Gender = "Male" ) />
 
<!--- Create a strategy #1 for punching.  --->
<cfset PunchStrategy_1 = CreateObject( "component", "Punch" ) />
 
<!--- Create a different strategy for punching.  --->
<cfset PunchStrategy_2 = CreateObject( "component", "PunchFreeze" ) />
 
<!--- Tell the IceMan object that you'll be using the Punch Strategy #2, NOT #1.  --->
<cfset IceMan.setPunchAlgorithm( PunchStrategy_2 ) />
 
 
<!--- Now let's create a kicking strategy... --->
<cfset KickStrategy = CreateObject( "component", "Kick" ) />
 
<!--- ... and now let's tell the IceMan object that you'll be using the Kick Strategy.  --->
<cfset IceMan.setKickAlgorithm( KickStrategy ) />
 
 
<!--- Now let's see some action!  --->
 
<!--- Punch, using the strategy chosen! --->
<cfset IceMan.punchAction() />
 
<!--- Punch, using the strategy chosen! --->
<cfset IceMan.kickAction() />
 
 
<!--- Let's create another SuperHero object. --->
<cfset Frosty = CreateObject( "component", "SuperHero" ).init( Name = "Frosty the Snowman", Gender = "Unknown" ) />
 
<!--- Let's inspect the objects --->
<p>Notice that both objects have different number of methods - only the methods they need. </p>
<cfdump var="#IceMan#" /><hr />
<cfdump var="#Frosty#" /><hr />
 
 
<!--- Let's create another SuperHero object. --->
<cfset Frostman = CreateObject( "component", "Freezer" ).init( Name = "Calvin Hobbes", Gender = "Male" ) />
<cfset Frostman.energyProject() />

You can download all the CF code here with the original Visio diagram source.

TSQL Functions Inspired By ColdFusion’s Lists Functions

Posted by Dan | Posted in ColdFusion, Databases, SQL Server | Posted on 04-27-2010

0

In my last project, there was a bit of data scrubbing on the database side (SQL Server 2008) that I decided to create a few UDF’s that function similar to ColdFusion’s Lists function. The one that varies a little bit is ListLen(), since I needed to take into account empty tokens. The ChopIf() was inspired by Perl’s chop() function. These UDFs should be SQL Server 2005-compatible.

I should say though, that some of these functions depend on each other. ListLen(), GetToken(), and ChopIf() are independent.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
------------------------------------------------------------------
-- Functions similarly like ColdFusion ListSort() function,
-- except it currently only sorts strings. 
--
-- Example 1:
--    dbo.ListSort( 'dan is so mega awesome that he rules all the time', 'ASC', ' ' )
--
-- Returns: 
--    all awesome dan he is mega rules so that the time
--
-- Example 2:
--    dbo.ListSort( 'dan is so mega awesome that he rules all the time', 'DESC', ' ' )
--
-- Returns: 
--    time the that so rules mega is he dan awesome all
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListSort]
(
  @string    VARCHAR(2000),  
  @sort_type CHAR(3)       = 'ASC',
  @delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(500)
AS
 
BEGIN
 
  DECLARE @position AS INT
  DECLARE @token AS VARCHAR (2000)
  DECLARE @counter   AS INT
  DECLARE @sortedList AS VARCHAR(500)
 
  DECLARE @sortTempTable TABLE ( token VARCHAR(500) )
  DECLARE @sortedTable   TABLE ( token VARCHAR(500) )  
 
  SELECT @string   = @string + @delimiter,
         @counter  = 1,
         @position = 0,
         @token    = ''
 
  WHILE ( PATINDEX( '%' + @delimiter + '%' , @string ) <> 0 ) 
  BEGIN
    SELECT @position = PATINDEX('%' + @delimiter + '%' , @string ),
           @token    = LEFT( @string, @position - 1 ),
           @string   = STUFF( @string, 1, @position, NULL ),
           @counter  = @counter + 1
 
    INSERT @sortTempTable( token ) VALUES( @token )     
  END
 
  SET @sortedList = ''
 
  -- Let's sort the table and put it into @sortedTable
  -- Because of nature of Rank(), we can't set @sortedList in this statement.
  -- Have to separate it into another select clause.
  INSERT INTO @sortedTable
    SELECT LTRIM( token )
    FROM   @sortTempTable
    ORDER  BY CASE WHEN @sort_type = 'ASC'  THEN ( RANK() OVER ( ORDER BY LTRIM(token) ASC ) )
                   WHEN @sort_type = 'DESC' THEN ( RANK() OVER ( ORDER BY LTRIM(token) DESC ) )
              END  
 
  SELECT @sortedList = @sortedList + token + @delimiter
  FROM   @sortedTable
 
  RETURN dbo.ChopIf( @sortedList, @delimiter )
 
END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
------------------------------------------------------------------
-- Functions sort of like ColdFusion's ListLen() method, but it
-- takes into account empty tokens. 
--
-- Example 1:
--    dbo.ListLen( 'Dan is cool', ' ' )
--
-- Returns: 
--    3
-- 
-- Example 2:
--    dbo.ListLen( 'dan,,very,,,,awesome,', ',' )
--
-- Returns: 
--    8
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListLen]
(
  @string VARCHAR(2000),
  @delimiter VARCHAR(2000) = ','
)
RETURNS INT
AS
BEGIN
 
  DECLARE @loopCount INT, 
          @tokenCount INT
 
  SELECT @loopCount = 0, 
         @tokenCount = 0
 
  -- If it's an empty string, the list length is 0
  IF DATALENGTH( @string ) = 0
    BEGIN
      SET @tokenCount = 0
    END
  ELSE
    BEGIN
      -- Count tokens, including empty ones like dan,,very,,,,awesome,
      SET @tokenCount = @tokenCount + 1
      WHILE ( @loopCount < DATALENGTH( @string ) )
      BEGIN
        IF SUBSTRING( @string, @loopCount, DATALENGTH( @delimiter ) ) = @delimiter
          BEGIN
            SET @tokenCount = @tokenCount + 1
          END
        SET @loopCount = @loopCount + 1
      END
    END
 
  -- Handle extra count from space being delimiter
  IF @delimiter = ' '
    SET @tokenCount = @tokenCount - 1
 
  -- If there's no token to the right of the last delimiter, then count that
  -- as an empty token.
  IF ( RIGHT( @string, 1 ) = @delimiter ) 
  BEGIN
    SET @tokenCount = @tokenCount + 1
  END
 
  RETURN @tokenCount
 
END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
------------------------------------------------------------------
-- Functions like ColdFusion's ListLast()
-- Gets token value that's been separated by a delimiter.
--
-- Example:
--    dbo.ListLast( 'Dan is cool', ' ' )
--
-- Returns: 
--    cool
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListLast]
(
  @string VARCHAR(2000),
  @delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  RETURN dbo.ListGetAt( @string, dbo.ListLen( @string, @delimiter ) , @delimiter  )
 
END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
------------------------------------------------------------------
-- Wrapper for GetToken() Function
-- Gets token value that's been separated by a delimiter.
--
-- Example:
--    dbo.ListGetAt( 'Dan is cool', 2, ' ' )
--
-- Returns: 
--    is
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListGetAt]
(
  @string VARCHAR(2000),
  @token INT,
  @delimiter VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN
  RETURN dbo.GetToken( @string, @token, @delimiter )
END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
------------------------------------------------------------------
-- Returns the first item in a tokenized list.
--
-- Example:
--    dbo.ListFirst( 'Dan is cool', ' ' )
--
-- Returns: 
--    Dan
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListFirst]
(
  @string VARCHAR(2000),
  @delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  RETURN dbo.ListGetAt( @string, 1, @delimiter )
 
END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
------------------------------------------------------------------
-- Functions similarly like ColdFusion GetToken() Function.
-- Gets token value that's been separated by a delimiter.
--
-- Example:
--    dbo.GetToken( 'Dan is cool', 2, ' ' )
--
-- Returns: 
--    is
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[GetToken]
(
  @string VARCHAR(2000),
  @tokenPosition INT,
  @delimiter VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  DECLARE @position AS INT
  DECLARE @token AS VARCHAR (2000)
  DECLARE @counter AS INT
 
  SELECT @string = @string + @delimiter,
         @counter = 1,
         @position = 0,
         @token = ''
 
  WHILE ( PATINDEX('%' + @delimiter + '%' , @string ) <> 0) AND ( @tokenPosition + 1 <> @counter )
  BEGIN
    SELECT @position = PATINDEX('%' + @delimiter + '%' , @string),
           @token    = LEFT(@string, @position-1),
           @string   = STUFF(@string, 1, @position, null),
           @counter  = @counter + 1
  END
 
  RETURN @token
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
------------------------------------------------------------------
-- Chops the last character if it's @chopped
--
-- Example:
--    dbo.ChopIf( 'Dan is cool!', '!' )
--
-- Returns: 
--    Dan is cool
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ChopIf]
(
  @string VARCHAR(2000),
  @chopped VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  IF ( RIGHT( @string, DATALENGTH(@chopped) ) = @chopped )
  BEGIN
    SET @string = LEFT( @string, DATALENGTH( @string ) - DATALENGTH( @chopped ) ) 
  END 
 
  RETURN @string
 
END
GO

Generate Junk Files

Posted by Dan | Posted in Python | Posted on 04-24-2010

0

The other day I was testing benchmarks for a delete script. I needed to create files with various file sizes. More specific, 1,000,000 files with 5K per file. A while ago I found this great snippet on StackOverflow to generate a junk random string:

junk =  (("%%0%dX" % (junk_len * 2)) % random.getrandbits(junk_len * 8)).decode("hex")

I’ve wrapped that around to make a utility function and snippet:

import os, random, sys
 
# This tool takes 3 parameters
#
#   testing <directory to put files in> <how many files> <size of each file in bytes>
#
# Example:
#
#   testing dan 100 500
 
def createLocalDirectory( directoryName ):
  if not os.path.exists( directoryName ):
    os.makedirs( directoryName )
 
folderName      = sys.argv[1]
how_many_files  = int(sys.argv[2])
junk_len        = int(sys.argv[3])
 
 
createLocalDirectory( folderName )
 
for i in range( 0, how_many_files ):  
  junk =  (("%%0%dX" % (junk_len * 2)) % random.getrandbits(junk_len * 8)).decode("hex")
  path = folderName + "/" + str(i) + ".txt"
  f = open( path, 'w' )
  f.write( junk )
  print f
  f.close()

Search In One File from Keywords in Another File

Posted by Dan | Posted in Languages, Python | Posted on 04-24-2010

0

I needed to see if this list of email addresses were found in a logs file. So I had one file with a list of email addresses. Another file is a list emails sent. I needed to make sure that the emails were sent. Here’s a quick Python script I put together that does this:

import re
import sys
 
def searchInLogFile( FILE, query ):  
  FILE.seek( 0, 0 )  
  for line in LogFile:
    logLine = line.replace("\n","").replace("\r","").rstrip().lstrip()
    if re.search( query, logLine, re.IGNORECASE | re.MULTILINE ):
      return True
 
# This file has a list (\r\n delimited) email addresses.
EmailListFile = open( "email-list-internal.txt", "r")
 
# This is the log file which we'll use to see if email addresses are in here.
LogFile = open( "POST20100201.log", "r" )
 
EmailFound = []
EmailNotFound = []
breakTime = 0
# 0 = does the whole list
EmailsToSearchFor = 0
 
for emailLine in EmailListFile:
  email = emailLine.replace("\n","").replace("\r","").rstrip().lstrip()
 
  if ( searchInLogFile( LogFile, email ) ):
    print email, "was found"
    EmailFound.append( email )
 
  else:
    print email, "not found"
    EmailNotFound.append( email )  
 
 
  if ( EmailsToSearchFor != 0 ):
    breakTime += 1
    if ( breakTime == EmailsToSearchFor ):
      break;
 
LogFile.close()
EmailListFile.close()
 
# Log results to a file.
OutputFile = open( "output.log", "w" )
 
divider = "\n\n======== Found ========================================"
print divider
OutputFile.write( divider )
 
for i in EmailFound:
  print i
  OutputFile.write( "\n" + i )
 
divider = "\n\n======== Not Found ===================================="  
print divider
OutputFile.write( divider )
 
for i in EmailNotFound:
  print i
  OutputFile.write( "\n" + i )
 
OutputFile.close()

Pretty straightforward. The script also writes a file called “output.log” which has a list of emails that were found (marked under “found”) and not found (marked under “not found”).

RedGate SQL Search

Posted by Dan | Posted in Databases, SQL Server | Posted on 04-24-2010

0

I love this tool. It’s a free add-on if you own SQL Prompt, RedGate’s version of intellisense, which is better, in my eyes, than Microsoft’s. It uses SQL Prompt’s FTS collection to search for any piece of string in any DB object. It’s crazy fast. What I like the most though, is the ability to right click on a result, and move the focus to appropriate DB object in the explorer panel. Hit CTRL+ALT+D to trigger the search screen and start typing away.