Skip to content
Latest

Java Pairs Well with Which Database?

In the same way there’s a tight bond between MySQL and PHP, SQL Server and ASP.NET, SQL Server and ColdFusion – what goes well with Java? Oracle? Being curious, I started searching in employment web sites. I searched for “Java” and one of these databases: Oracle, MySQL, SQL Server and PostgreSQL. (I put in “SQL Server” using quotes.) The sites used were: craigslist, Monster.com, Dice.com, and Yahoo! Hotjobs.

The numbers signify how many job entries were returned.

So it does seem Oracle goes with Java. Also I noticed how many people call “SQL Server” just “SQL.” Sort of confusing and hard to tell if they’re referring to the platform or language.

SQL Server 2008 Installation Errors

Recently I’ve had to install SQL Server 2008 on a few machines. Some of these errors made absolutely no sense, but I found solutions that worked for me. Maybe they’ll help you.

Windows 2000 Server (32-bit)


“The procedure entry point GetConsoleProcessList could not be located in the dynamic link library KERNEL32.dll.”

That sounds pretty self-explanatory, right? Actually, this was the easiest error to make sense of. This is the error you get because SQL Server 2008 is not compatible with Windows 2000 Server. It comes from the fact that SQL Server 2008 needs .NET 3.5+, and that version of .NET is not Windows 2000-compatible.

Windows XP and Windows 2003 Server (32-bit)


Surprisingly, I received no errors while trying to install it on Windows XP. Everything went fine with Windows 2003 Server.

Windows 7 (64-bit)


OK, this was hell on earth. First I ran the setup, and mid-way through the installation, I received this gem:

“SQL Server Setup has encountered the following error:

Invoke or BeginInvoke cannot be called on a control until the window handle has been created..”

I noticed that this arises if you have the focus on another Window while the installer is running. I was browsing the web via Firefox and I received this. To make sure you don’t get this, don’t have any other Window running and only have the focus on the installer.

That wasn’t the only error I received. When I initially ran the setup, I saw a console window flash and then nothing would happen. (I waited for a few minutes and nothing. Setup.exe wasn’t even in the task manager.) When you run setup.exe, it fires landingpage.exe, which is the actual wizard installer. Unfortunately, it wasn’t running it. I even made sure I had the requirements, and nothing. I rebooted several times, and finally decided to run landingpage.exe directly. When I ran it, it finally saw the wizard. I was joyful, until it got to this part:

What the heck?! Where are the features?

Why would the wizard make it this far and then then not have the features to let me install? This was useless. If you’re curious, it should’ve looked like this:

Then for the heck of it, I decided to Install SQL Server 2008 SP1, without even installing SQL Server 2008 first. Don’t know why, but it let me install it anyways. Then when I ran the SQL Server 2008 installer again, but this time, I would see these features:

Ugh, still no luck. Finally, the only thing that I could conclude was that I may have a bad installation. So I restored my machine to an earlier restore point (to undo this installation mess) and re-downloaded the installation files.

Voila! No more problems. So that’s what it was. I was finally able to see all the features to install. So maybe the files got corrupted upon download or it was an incomplete download.

Convert Minutes to Hours

I often use both Winamp and my iPhone to listen to music. These two, unfortunately, show the time differently in the songs. Winamp displays the time in minutes (mm) while the iPhone does it hour/minutes (hh:mm). Here’s a quick little script I whipped together because I’m too lazy to do this in my head, especially for audio books where an audio book can be over 500 minutes, and I need to convert to iPhone time because I want to continue listening where I had just left off on Winamp.

use POSIX qw(ceil floor); # used for the floor function

sub GetToken {
  # @_ = flatten args list from an array
  # @_[0] = first argument
  
  $data      = @_[0];
  $delimiter = @_[1];
  $token     = @_[2] - 1;
    
  @tokens_array = split($delimiter, $data);   
  
  return @tokens_array[$token]; 
}

sub chr_conver_min {  
  if (length(@_[0]) == 1) {
    return "0".@_[0];
  }
  else {
    return @_[0];
  }   
}


sub iphone_time_convert {

  # converts winamp time to iphone - winamp stores time only in minutes.  
  # @_[0]   =  winamp_time, e.g. 124:34
  # $hour   = floor($winamp_time/60);
  # $minute = $winamp_time % 60;

  $winamp_hour_min = GetToken(@_[0], ":", 1);  
  $winamp_seconds  = GetToken(@_[0], ":", 2);  
  
  return floor($winamp_hour_min/60).":".chr_conver_min( ($winamp_hour_min % 60) ).":".$winamp_seconds;
  
}


sub winamp_time_convert {  

  # converts iphone time to winamp  
  # @_[0] = iphone_time, e.g. 3:43:34    
  $iphone_hour     = GetToken(@_[0], ":", 1);  
  $iphone_min      = GetToken(@_[0], ":", 2);    
  $iphone_seconds  = GetToken(@_[0], ":", 3);
    
  return (($iphone_hour * 60) + $iphone_min).":".$iphone_seconds;
  
}

sub show_help {
  print "\nDisplays the conversion of time between winamp and iPhone.\n\n";
  print "   winamptime [-w2i|-i2p] [mm:ss][hh:mm:ss]\n\n";
  print "Example to convert winamp time to iPhone: \n\n";
  print "   winamptime -w2i 212:41\n\n";
  print "Example to convert iPhone time to winamp, seconds being optional: \n\n";
  print "   winamptime -i2w 2:31:41\n";
  print "   winamptime -i2w 2:31\n\n";
}


# START

# Optimize this:
if( $ARGV[0] eq "-w2i" ) 
{
  # winamp to iphone time
  if ( length($ARGV[1]) > 0 ) {
    print "iPhone Time: ".iphone_time_convert( $ARGV[1] )."\n";
  }
}
elsif( $ARGV[0] eq "-i2w" ) 
{
  # iphone to winamp time
  if ( length($ARGV[1]) > 0 ) {
    print "Winamp Time: ".winamp_time_convert( $ARGV[1] )."\n";
  }
}
else 
{
  show_help();
}

Output:

Compress and Move Log Files

Sometimes log files bog a system down. For one of our servers, I made this little Python script that compresses (via WinRAR) the log files in a directory, and then moves them to a backup location. The only little catch is that I wanted to leave the latest log files for in that directory. Log files are created daily, so the the latest log files have a datestamp of today. Here’s how I did it.

First Create the Python Script:

import os
import datetime

dateStamp  = datetime.datetime.now().strftime("%Y-%m-%d") 
imsLogPath = 'd:\\LogFiles\\'                     
# Don't use a mapped drive but use UNC for network drives. Task Schedule seems to choke when it calls Python.
newRARPath = '"\\\\192.168.1.2\\Root\\backups\\' + dateStamp + '.rar"'
rarPath    = '"C:\\Program Files\\WinRAR\\rar.exe" a -m5 ' + newRARPath 

# Get Latest Files
smtpLatest   = os.popen(r"dir /od /a-d /b " + imsLogPath + "SMTP*.log").read().splitlines()[-1]
postLatest   = os.popen(r"dir /od /a-d /b " + imsLogPath + "POST*.log").read().splitlines()[-1]
ischedLatest = os.popen(r"dir /od /a-d /b " + imsLogPath + "iSched*.log").read().splitlines()[-1]
relayLatest  = os.popen(r"dir /od /a-d /b " + imsLogPath + "Relay*.log").read().splitlines()[-1]
qengLatest   = os.popen(r"dir /od /a-d /b " + imsLogPath + "Qeng*.log").read().splitlines()[-1]

# Get List of All Files
allFiles     = os.popen(r"dir /od /a-d /b " + imsLogPath + "*.log").read().splitlines()

# Remove Latest Files from All Files List
allFiles.remove( smtpLatest )
allFiles.remove( postLatest )
allFiles.remove( ischedLatest )
allFiles.remove( relayLatest )
allFiles.remove( qengLatest )

# allFiles Array Has the list of files

# Flatten Array allFiles to be used as a parameter in system command
flatLogPathList = ""
for filenameWithPath in allFiles:
  flatLogPathList = flatLogPathList + imsLogPath + filenameWithPath + " "


# Execute WinRar
path = rarPath + " " + flatLogPathList.rstrip()
os.system( '"' + path + '"' )

# Delete all log files
os.system( '"del ' + flatLogPathList.rstrip() + '"' )

Then I set up the Scheduled Task:

With these Settings:

Get Latest File

In my last post, I made a quick script that checks for the date. It was very limiting, since it used the dir command. This one uses several date/time Python modules and is more capable.

import os, os.path, stat, time
from datetime import date, timedelta, datetime

# Reference
# http://docs.python.org/library/datetime.html
# http://docs.python.org/library/time.html

def getFileDate( filenamePath ):    
  
  used = os.stat( filenamePath ).st_mtime      
  year, day, month, hour, minute, second = time.localtime(used)[:6]
  objDateTime = datetime(year, day, month, hour, minute, second)
  
  return objDateTime
  
  # Ways to reference this DateTime Object
  # objDateTime.strftime("%Y-%m-%d %I:%M %p")
  # objDateTime.year
  # objDateTime.month
  

def isDaysOldFromNow( filenamepath, days ):
  
  # Checks how old a file is. Is it older than "days" [variable] days?
  inTimeRange = False  
  timeDeltaDiff = ( datetime.now()-getFileDate( filenamepath ) ).days
  
  # Check if the file's date is days old or less:
  if ( timeDeltaDiff >= days ):
    inTimeRange = True  
    
  return inTimeRange

fname = "C:/temp/decision2.pdf"  

# Set this variable to check if the file is this days old
howOld = 3


if ( isDaysOldFromNow( fname, howOld ) ):
  print fname, "is more than", howOld, "days old"
else:
  print fname, "is NOT more than", howOld, "days old"

Output:

Base Tag Not Supported In Hotmail

It’s interesting how the <base> works in various clients. I thought all major web clients would support it, but apparently it is not the case.

Let’s look at how some email clients process this tag:

Yahoo.com! Web Mail

Base tags are modified by their mail server and relative URLs are changed to absolute. So it works.

Gmail Web Mail

Base tags are modified by their mail server and relative URLs are changed to absolute. So it works.

Gmail Account via Thunderbird

Base tags are shown and its functionality is intact. URLs remain relative. So it works.

iMS Account via Outlook 2007

Base tags are shown and its functionality is intact. URLs remain relative. So it works. More info on iMS.

ShinyLight Account via iPhone

Can’t see the HTML, but all URLs worked.

Hotmail

Unfortunately, this is the ugly duckling. Hotmail servers remove the base tag and leave your relative links as relative. Big FAIL.

Console2 Alternative to CMD

You know what, I tried PowerShell and I guess I never got used to it. I’ve been using DOS for the longest time that I prefer its simplicity when navigating through files. As far as scripting with it – forget it – it’s very cryptic and limiting, not to mention awkward. That’s why for scripting now I’m using Python. Nonetheless though, I sometimes need a playing field to run the scripts for, or running console utilities. That’s where Console2 comes in.

Console2 is a great tool that provides tabbing (you can tab through multiple consoles) and several ways to customize the UI. Here’s an example:



I prefer this configuration:


How I Have It Setup



Download Console2:

I have the Console2 open up when I hit CTR+\ via AutoHotkey. Here’s the snippet for this to happen in AutoHotkey. I have this run when Window starts (it must stay in memory).

^\::Run C:\dan-local\dos-win\Console.exe

Change the CMD prompt by creating a PROMPT system variable:




Download the Console2 Config File

Other Tips


Autocomplete
Good tip about command console = CTRL+I
can select files in the current directory, kinda like autofill-in
you can also put in parts of the file and it autoputs it for you

Rename tab
CTRL+R

New Tab with default transparency
CTRL+F1

New Tab with lighter transparency
CTRL+F2

Switch to NEXT adjacent tab
CTRL+TAB

Switch to Previous adjacent tab
CTRL+SHIFT+TAB

CTRL+[NUMBER]
Goes to the tab instance. To make it easier, it’s best if you rename your tabs,

[INSTANCE NAME].[NAME OF TAB]

So for example,

1.Batch File
2.wget list
3.ftp the files

Search As You Type

Just upgraded recently from WindowsXP to Windows7. One feature I used a lot forever (since Win95 I think), was the search as you type (this is what I call it anyways). It’s when you have an Explorer window open (let’s say in this case c:\ ) and you type “W” – it moves the cursor to directory that starts with the letter “W.” You can type “Win” quickly and it will go to the first directory that starts with those keywords in case you have directories that start with “Win.”

So after I configured the UI settings on Win7 to the way I saw fit, I realized that this feature was gone. It would automatically start searching for text in all files recursively – very annoying. I asked how to turn this feature off in many forums and do what I wanted to do and no one knew.

I finally figured it out. It was a setting that I turned on accidentally. (D’oh!)

Explorer Window -> Organize -> Folder Options -> View Tab -> When typing
into list view -> Select the typed item in the view

That wasn’t checked. I suppose I did it instinctively without thinking. In WindowsXP, the area is used to trigger Advanced File Sharing, and I guess I didn’t realize this in Windows7. Also, I don’t think in XP this is an option you can control because it just works.

Windows7 Options

WindowsXP Options

CTRL+TAB in Eclipse

By default, CTRL+Tab (forward switching of windows) and CTRL+SHIFT+TAB (previous switching of windows) aren’t set in Eclipse to switch between editors windows. There are other Eclipse, non-standard defaults like CTRL+F6 for forward switching. To set these shortcuts, do the following:

  1. Run Eclipse
  2. Window -> Preferences
  3. General -> Keys
  4. Search for: Next Editor
  5. Put the cursor in the “Binding” textbox and delete that hotkey. Then, press and hold CTRL+TAB
  6. Hit Apply.
  7. Search for: Previous Editor
  8. Put the cursor in the “Binding” textbox and delete that hotkey. Then, press and hold CTRL+SHIFT+TAB
  9. Hit Apply and OK.

Apply similar steps for Ctrl+Shift+Tab.

Reasons I’m Not a Fan of Google Chrome OS

I’m assuming you’ve seen what Chrome OS can do. Just Google “Chrome OS,” because it’s everywhere.

I would give Chrome OS an 8.0 / 10.0 for computer beginners, like the grandmas of the world. It’s gets a 2.0 / 10.0 for techies. Why? Three reasons.

  • Needs too many resources for what it delivers.
  • Cannot run executables, not even virtually/sandboxed.
  • Needs the cloud for storage.

Kind of annoying that I can’t just run it on any old laptop / netbook. System requirements are too demanding for what it promises and it seems it’s needed just to have that 2-4 second boot-up time, which honestly it doesn’t mean much to me. There’s never a time where I’m in such a hurry that I need my computer that fast, because I’m either doing something else, or I just use my phone.

I some people really care about boot-up time? I’d rather wait 10 more seconds and have the ability to run .EXE files and have it run on old PC’s. For a minimalist device, I have my table PC or iPhone already that can do all that stuff, and I don’t have to worry about malware on the iPhone. I don’t think techies have to worry to much about malware since they’re careful about getting them in the first place and have good Anti-viruses.

Lastly, an Internet connection is not ubiquitous yet. I’m on the subway a lot where there’s no WIFI and sometimes I travel to places without an Internet connection. I want to be able to at least read my latest documents without connecting to the cloud.

The OS is still in the works, so I suppose maybe they’ll address some of these features in the future.

Temporary Stored Procedure

They can be handy sometimes if you want to modularize your code for the life-time of a routine. To create a temp stored procedure, all you do is use the # sign before the name of the sproc. Same concept as a temp table. Only the owner can execute it and its life is that of the session. Here’s an example that uses the Northwind db.

CREATE PROCEDURE #GetCustomers 
(
  @companyName VARCHAR(50)
)
AS
BEGIN
  SELECT * FROM Customers
  WHERE  CompanyName LIKE '%' + @companyName + '%'
END

To use it:

EXEC #GetCustomers 'The'

You can find the temp sproc in the tempdb:

If you want more information on the temp sproc, you can query the system view under the tempdb:

SELECT * FROM tempdb.sys.objects
WHERE [object_id] = OBJECT_ID('tempdb.dbo.#GetCustomers')

Your next question maybe if you can create temp user defined functions? The answer is a big juicy delicious, “NO!” (at least as of SQL Server 2008 Enterprise).

Convert Relative URLs to Absolute

I put this ColdFusion UDF together the other day to turn relative URLs to Absolute. Code is pretty straightforward.

<cffunction name="URLRelativeToAbsolute" returntype="string"
  hint="Converts relative URLs in an element and converts to absolute. It includes the http:// protocol prefix.">  
  
  <cfargument name="content" type="string" required="true" hint="HTML content that will be scanned and replaced." />

  <cfargument name="domain" type="string" required="true" hint="Add domain name to relative links." />
  
  <cfset var local = StructNew() /> 
  
  <!--- The following regexp handles the following elements: link, a, img, script, form, frame. --->
  <cfset local.contentFixed = REReplaceNoCase( Arguments.content, "(href|src|action)=""/?((\./)|(\.\./)+|)(?=[^http])", "\1=""http://" & domain & "/", "all" ) />  
  
  <!--- The following regexp handles the url() attribute of the background CSS property. --->
  <cfset local.contentFixed = REReplaceNoCase( local.contentFixed, "url\((\s)?(')?/?((\./)|(\.\./)+|)(?=[^http])", "url(\2http://" & domain & "/", "all" ) />  

  <cfreturn local.contentFixed />    

</cffunction>

Usage:

<cfsavecontent variable="htmlContent">
<textarea name="data" rows="20" cols="60">
  <style>
    body { 
      background-image:url('stars.png');
      background-image:url('../stars.png');
      background-image:url('/stars.png');
      background-image:url('/../../../stars.png');
    }
  </style>
  <a href="../../../images/shiny.jpg">Shiny</a>
  <a href="http://www.google.com">This should not be touched</a>
  <img border="0" src="/images/cool.png" /> 
  <link rel="index" href="../../index.asp">
  <form method="POST" action="cgi/processing.cgi"></form>  
</textarea>
</cfsavecontent>

<cfoutput>

  #htmlContent#
  #URLRelativeToAbsolute( htmlContent, "www.shinylight.com" )#
  
</cfoutput>

Result:

Backup MySQL DB From Remote To Your Local Machine

Why do this?

In case you don’t have shell access to your server from your hosting provider. Also if the provider doesn’t let you writing files locally from a script.

If you want to backup from a remote machine to your local machine:

(Don’t use the symbols when you type it, except the last > that redirects to a file.)

mysqldump --opt -Q -h  -u  --password=  > 

If you want to backup from a remote machine to that same remote machine.

mysqldump --opt -Q -h  -u  -p  > 

You can also pipe that to gzip to compress, and schedule it as needed.

Search for Code in Stored Procedures

One handy trick that I used to do was use the function OBJECT_DEFINITION() to view the code of a sproc. It’s a handy little function that doesn’t get much love as OBJECT_ID or OBJECT_NAME(). Let’s say we want to get the code of the sproc dbo.uspGetBillOfMaterials from DB AdventureWorks. We do like so:

SELECT OBJECT_DEFINITION( OBJECT_ID('dbo.uspGetBillOfMaterials') )

You get a result that may not be too pretty. In this case, use PRINT rather than SELECT, since SSMS strips out line breaks to squeeze it in the cell.

Now let’s search all available user sprocs in the database to search for the string “%GetBillOf% :

SELECT  OBJECT_DEFINITION( p.[object_id] ) AS code 
FROM    sys.procedures p
WHERE   OBJECT_DEFINITION( p.[object_id] ) LIKE '%GetBillOf%'

Now, I do longer search this way since every day I have a job that writes out all DB objects (tables, sprocs, etc.) to a individual files and they get indexed via Copernic Desktop Search. I script them out using SQL Compare.

Unfortunately, I don’t know why, OBJECT_DEFINITION() doesn’t work on tables. It’s been a wanted feature by the community. If you want to learn more about this function, check out:

Using Winrar from the Command Line

Winrar is a great tool used for compressing files. It also has a command line tool called “rar.exe” to compress files, in case you want to batch it up. Here’s an example of the most common switches I use.

"C:\Program Files\WinRAR\rar.exe" a -m5 -r -ep1 "C:\temp\Work\Upgrades\2010-02-06\111.rar" "C:\temp\Work\Upgrades\2010-02-06\Post-Upgrade\" 

First path is the location of the rar file to create. The second path is either the location of the file(s) or directory to compress.

options used

  -ep1  Exclude base directory from names
  a     Add files to archive
  -r    Recurse subdirectories (will compress entire directories)
  -m5   Set compression level to maximum

You can even assign a password to it using the -p switch