Getting SQL Replication Info Programmatically from Java

winterWisteria Wisteria leaves in winter.



SQL Server has a process called replication, whereby you can automatically copy records from one database to another, and keep them in-sync.  One use for this is to have one database as the transactional database, and another to report from, with the reporting database having indexes and constraints tailored more toward faster reporting (reads) than fast input.

Sometimes replication gets a little behind, and if you are running reports on the database that is a little behind, you might like to know.

The stored procedure sp_replcounters will tell you the count of transactions awaiting transfer to the distribution database (which lies between the transaction database and the destination database in a replication process).  You can call this stored procedure, but you want it to return the result set to Java.

One way to achieve this is to wrap your own stored  procedure around it, put the records into a temp table and query the temp table to return the result set.  This only works if you specify SET NOCOUNT  ON, otherwise the Java Persistence API (JPA) gets confused by the multiple queries and you don’t get the result set in your Java code.

CREATE PROCEDURE GetReplicationLatency


--This is important.  Results from multiple queries within stored procedures throw JPA off unless SET NOCOUNT ON is used.

declare @db varchar(50)
set @db = db_name()

create table #tempTable ([database] varchar(50), [replicated transactions] int, [replication rate trans/sec] int,
 [replication latency (sec)] decimal(16,6), replbeginlsn varchar(40), replnextlsn varchar(40))
insert into #tempTable
exec sp_replcounters
select * from #tempTable where [database] = @db

drop table #tempTable


Then your Java code looks like this:

private static final String REPLICATION_LATENCY_QUERY = "exec GetReplicationLatency ";Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
List<Object[]> results = query.getResultList();




Hibernate Dynamic Update

ThorAndMe  That’s a pic of me with Thor.

I had a situation come up where every time a particular table was updated, an indexed view was also getting updated.  That view was intended to speed up a report, but it made the table update really horrible.  The view would only get updated if one particular column in the table was being updated.  That column never got updated through the application, because once the record was created that column’s input field got grayed out.  But Hibernate’s default behavior is to update all columns rather than to check which ones changed and only update the changed columns.

There is a performance trade-off.  Usually you probably want the default behavior.  But in this case, definitely not.

But Hibernate has an option to allow for updating only the columns that changed.  This option is called dynamic-update.  Different versions of Hibernate implement it differently.  In the example in this blog post, hibernate-annotations version 3.4.0.GA was the dependency brought in.

Because Hibernate’s org.hibernate.annotations.Entity class is not a replacement, but rather a supplementation of javax.persistence.Entity, you have to implement it like this:

import javax.persistence.Entity;
@org.hibernate.annotations.Entity(dynamicUpdate = true, selectBeforeUpdate = true)
@Table(name = "blah")
public class Blah...

The selectBeforeUpdate option is not required with the dynamicUpdate.  It basically states that before doing an update, do a select to make sure you have the most recent copy of the table, then the dynamicUpdate option says only update changed columns.

Creating a config file for MSI packaging software for an executable name that varies


The problem

Client wants our software packaged as an MSI (Microsoft Installer).  Found some software that will do this called exemsi.  Exemsi comes in a free mode with a GUI interface and a professional mode ($200 per machine) that can be called from a build process.

The professional mode relies on a configuration file.  The name of the executable file that you want to wrap in MSI is specified in the configuration file.

If the name of the executable file changes from release to release (for example, if it has a version number as part of the file name), the configuration file is supposed to have a way of finding the file based on wildcards and regular expressions.  I could not get this to work and had extreme difficulty reaching the author of the software.  He did reply to me once, but his suggestion did not solve the problem.

So, after much frustration, I decided to just make a little script that would read from a basic template configuration file, and write out nearly every line to a temporary configuration file for the current build.  Nearly every line because one line would have a substitution.  The line that specifies the executable name would be substituted for the installer file name of the current release.

I started writing this script as a DOS batch file.  I ran into trouble trying to make a loop with multiple command lines inside the loop.  I started thinking I’d scrap DOS and do it in Python, when my boss suggested Powershell.  Either one would serve.  The two machines on which this script would need to run already had Powershell installed, so that’s what won.

Besides, it afforded me a nice opportunity to learn how to use Powershell, having never used it before.

The solution

Here’s the Powershell script, which I called makeMsiWrapperConfig.ps1:

#To call from command line:
#  powershell -noexit "& ""path\makemsiwrapperconfig.ps1"" ""path\installer executable file name"" ""path\exemsi config template file"" ""path\resultant exemsi config file"""
#  where path is substituted with the actual path to the powershell file,
#  and installer executable file name is substituted with the actual lite installer file name,
#  and exemsi config template file is the name of the template configuration file for the exemsi software that wraps our installer executable in an MSI package,
#  and resultant exemsi config file is the output file with the correct executable name in it.
#  The outer set of quotation marks is so that powershell won't incorrectly parse the parameters if there are any spaces in the paths or filenames.
#  The next level of quotation marks is to escape the inner-most level of quotation marks so that once inside the script, the variable will be quoted.
#  powershell -noexit "& "".\makemsiwrapperconfig.ps1"" "".\target\app-123456.exe"" ""c:\resources\msiWrapper.xml"" "".\target\msiWrapper.xml"""  

$exeFile = $args[0]
$configTemplate = $args[1]
$resultantConfig = $args[2]
write-host "Creating configuration file for exemsi, the software which wraps the lite installer in an MSI package.  Parameters:"
write-host "  exe file to wrap - " $exeFile
write-host "  config template - " $configTemplate
write-host "  resultantconfig - " $resultantConfig
" " | set-content -path $resultantConfig
foreach($line in Get-Content $configTemplate) {
    $exportLine = $line
    if ($line -like '*<Executable*') {
        $exportLine = '<Executable FileName="' + $exeFile + '"/>'
    #write-host $exportLine
    $exportLine | add-content -path $resultantConfig


It just reads every line from the template exemsi configuration file, and when it finds the line that has “<Executable” in it, makes a substitution using the file name from the parameter passed in.  Then it spits out the lines to a tailored-to-the-current-release configuration file.

I may tweak this to customize other lines in the configuration file.  For example the version number is one of the things in the configuration file that would vary from release to release and that has its own tag.  So more work will likely need to be done but at least this proves the concept.

The next thing I needed to do was call this from the Apache Ant build.xml file.  Here’s that code (the names have been changed to remove references to proprietary software):

<!-- create MSI (Microsoft Installer) artifacts -->
<exec executable="powershell" >
    <arg line="-noexit"/>
    <arg line="&amp; c:\resources\makemsiwrapperconfig.ps1 .\target\app-123456.exe c:\resources\msiwrapper.xml .\target\msiwrapper.xml"/>
<exec executable="c:\resources\MsiWrapperBatch.exe">
    <arg value='config=.\target\msiWrapper.xml'/>

It works.  I would hope there are easier solutions than this.  But once I had asked my boss to purchase the exemsi software and spent some time learning it, I was kinda like a dog with a bone and determined to make this solution work.  And hey, I learned something about Powershell and how to call a Powershell script from an Apache Ant build.xml file in the process.

UTC date/time string to Java date in local time zone



Uses joda-time (because yes, I still work in Java 1.6).  There is probably a simpler way.  But this works. This assumes all parts of the string are supplied.

public static Date convertUTCStringToLocalDateTime(String utcDate)
    DateTime utcDateTime = new DateTime(utcDate);
    Date localDateTime = utcDateTime.toDate();
    return localDateTime;

Specifying headerColors for Actionscript Alert in CSS



Simple idea – you want Alert pop-ups in Actionscript to have a header color that is different from the default (which is same color as the background).  The only difficulty comes in knowing the correct syntax, because two colors are necessary.  They can be the same color if you just want a solid color, or two colors if you want one color to fade into the other.  These two colors get converted to an array at run-time.

After trying various combinations with and without quotes, square brackets, squiggly brackets, I finally got the correct syntax.  So just writing it down here to keep track.

dropShadowEnabled: true;
headerColors: blue, blue;
headerHeight: 20;

It’s just a comma-delimited list of two colors – no quotes or brackets or anything fancy.  Also the numeric red green blue values may be used instead of color keywords.  The first color in the list is the top of the gradient; the second the bottom.

By the way a low-tech way to get the RGB value of a color is to copy something with that color into Paint, and then use the dropper tool to select the color, then click on “Edit colors”.


SQL Server datetime to int


declare @currentDateTime datetime 
declare @timeStampValue int 

set @currentDateTime = SYSDATETIME(); 
print @currentDateTime 
set @timeStampValue = DATEDIFF(second, '1970-01-01', @currentDateTime) 
print @timeStampValue

To check the result:


UTC milleseconds example:

declare @timeStampValue Bigint  
declare @utcDateTime datetime 

set @utcDateTime = GETUTCDATE(); 
set @timeStampValue = DATEDIFF(SECOND, '1970-01-01', @utcDateTime) 
set @timeStampValue = @timeStampValue * 1000


Python clean up old files


Was learning Python and wrote this script to clean up files.  It’s not awesome.  Has no error handling for when the target file is already open.  Just kinda barfs when that happens.  So it needs work.  This was written in Python 3.

#This script uses a library called  Get it from here:
#Download the Python Wheel file.  At this writing, it is called  
#Install using the command: pip install <name of wheel file>.
#You must have Python installed first.  Been a while since I installed it, but here is the official place to 
#get it from:
#Once you have everything installed and the script works, use Windows Task Scheduler to run it, so you don't forget.

import timefrom 
path import path

def main():  
  # Because in python, the \ is an escape character, we need to use two of them in directory specifications  
  paths = ["c:\\jboss\\jboss-eap-5.1\\jboss-as\\server\\default\\tmp","c:\\jboss\\jboss-eap-5.1\\jboss-as\\server\\default\\log"]  
  DAYS = 7  
  time_in_seconds = time.time() - (DAYS * 24 * 60 * 60)  
  removed = 0  
  for p in paths:    
    print (p)    
    d = path(p)      

    for f in d.walk():      
      if f.mtime <= time_in_seconds:        
        print ("Deleting " + f.basename())        
        removed += 1     
  print ("Removed " + str(removed) + " files.")
if __name__ == "__main__":