L8storm's IT Blog

You can log nearly every SQL statement without modification of your application. This includes timing of SQL statements, prepared statements and so on…

This can be reached by a JDBC driver wrapper. I was successful using the “log4jdbc” driver, see: http://code.google.com/p/log4jdbc/

You have to follow these steps:

1. Place the log4jdbc4-1.2beta2.jar into your classpath
2. If not already done, place the slf4j-api-*.jar and the slf4j-log4j12-*.jar (for Log4J logging) into your classpath
3. Change your JDBC driver class to net.sf.log4jdbc.DriverSpy
4. Change your JDBC url to jdbc:log4jdbc:…
5. Configure Log4J (or your log framework) to log the category “jdbc.sqltiming” in INFO mode

Links:

• JDBC proxy driver: http://code.google.com/p/log4jdbc/
• Simple Logging Facade for Java: http://slf4j.org
• Apache Log4J: http://logging.apache.org/log4j/
• Forum discussion about how to log prepared statements: http://stackoverflow.com/questions/218113/logging-preparedstatements-in-java

Want to use MS Windows as an NTP time server?

1. Copy this into a file named ActivateNTP.reg:
   

   Windows Registry Editor Version 5.00
   
   [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters]
   "LocalNTP"=dword:00000001

2. Exeute the file ActivateNTP.reg as Administrator to change the registry informations
3. Open a command line: Start -> Run… -> cmd.exe
4. Restart the time service:
   

   net stop w32time
   net start w32time

5. Be sure the firewall enables all clients to access the NTP service (UDP 123)

Links:

• How can I enable a Network Time Protocol (NTP) server: http://www.windowsitpro.com/article/time-synchronization/how-can-i-enable-a-network-time-protocol-ntp-server-.aspx

Problem: You want to store files on a central server, not on a client

Solution: This can be done by a combination of a Apache Tomcat servlet container and a simple VBA Script. This solution uses a HTTP POST request to send a file. The server side stores the file in a given destination directory.

1. Set up Apache Tomcat and deploy the file “fileupload.war”.
2. Edit the VBA script and send files to the server

Private Function postFile (fileName, url, remoteFileName, remoteDir)
  ' Prepare
  Randomize Timer

  Dim boundary 'As String
  boundary = "---------------------------32203174618290" 

  Dim simpleFileName 'As String
  If InStrRev(fileName, "\") = 0 Then
    simpleFileName = fileName
  Else
    simpleFileName = Mid(fileName, InStrRev(fileName, "\") + 1)
  End If

  Dim payload 'As Variant

  'file object
  payload = "--" & boundary & vbCrLf
  payload = payload & "Content-Disposition: form-data; name=""File""; filename=""" _
             & simpleFileName & """" & vbCrLf
  payload = payload & "Content-Type: application/octet-stream" & vbCrLf
  payload = payload & vbCrLf
  payload = payload & BinaryToString(ReadBinaryFile(fileName)) & vbCrLf

  'form data
  payload = payload & "--" & boundary & vbCrLf
  payload = payload & "Content-Disposition: form-data; name=""remote_filename"" & vbCrLf
  payload = payload & vbCrLf
  payload = payload & remoteFileName & vbCrLf

  payload = payload & "--" & boundary & vbCrLf
  payload = payload & "Content-Disposition: form-data; name=""remote_dir"" & vbCrLf
  payload = payload & vbCrLf
  payload = payload & remoteDir & vbCrLf

  payload = payload & "--" & boundary & "--" & vbCrLf

  ' Execute POST request
  ' see: http://msdn.microsoft.com/en-us/library/ms760305%28v=VS.85%29.aspx
  Dim objHTTP 'As Variant
  Set objHTTP = WScript.CreateObject("MSXML2.ServerXMLHTTP")

  objHTTP.Open "POST", url, False
  objHTTP.setRequestHeader "User-Agent", _
                   "Mozilla/5.0 (compatible; MSIE 6.0; Windows NT 5.0)"
  objHTTP.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
  objHTTP.send (payload)

  postFile = objHTTP.status

  objHTTP.abort
End Function

' http://www.motobit.com/tips/detpg_read-write-binary-files/
Function ReadBinaryFile(FileName)
  Const adTypeBinary = 1

  Dim BinaryStream 'As Variant
  Set BinaryStream = CreateObject("ADODB.Stream")

  BinaryStream.Type = adTypeBinary
  BinaryStream.Open
  BinaryStream.LoadFromFile FileName

  ReadBinaryFile = BinaryStream.Read
End Function

Function BinaryToString(Binary)
    'Antonin Foller, http://www.motobit.com
    'Optimized version of a simple BinaryToString algorithm.

    Dim cl1, cl2, cl3, pl1, pl2, pl3
    Dim L
    cl1 = 1
    cl2 = 1
    cl3 = 1
    L = LenB(Binary)

    Do While cl1<=L
        pl3 = pl3 & Chr(AscB(MidB(Binary,cl1,1)))
        cl1 = cl1 + 1
        cl3 = cl3 + 1
        If cl3>300 Then
            pl2 = pl2 & pl3
            pl3 = ""
            cl3 = 1
            cl2 = cl2 + 1
            If cl2>200 Then
                pl1 = pl1 & pl2
                pl2 = ""
                cl2 = 1
            End If
        End If
    Loop
    BinaryToString = pl1 & pl2 & pl3
End Function

' Test it here:
Dim retCode
retCode = postFile ("D:\example.zip", "http://localhost:8080/fileupload/fileupload", "myexample.zip", "myfolder/mysubfolder/")

If retCode = 200 Then
  MsgBox "Upload was successful"

Else
  MsgBox "Upload was NOT successful, error code: " & retCode

End If

Files:

• Server: fileupload.zip

Further links:

• Send binary data to URL: http://www.binarynow.com/pdf-conversion/simple-vbscript-vb-vbs-vba-converting-documents-doc-xls-ppt-eml-to-pdf-from-soap-web-service/
• Another possibility to send POST data: http://www.ms-office-forum.de/forum/showthread.php?t=208852
• How to work with binary files: http://www.motobit.com/tips/detpg_read-write-binary-files/
• MSDN XMLHTTPRequest reference: http://msdn.microsoft.com/en-us/library/ms759148%28v=VS.85%29.aspx
• Using the XMLHTTP object in VBA: http://scriptorium.serve-it.nl/view.php?sid=40
• Simple example how to do a HTTP post: http://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba

On Linux, there is an easy way to check if an xml file is well formed:

xmllint <file>

The process will give you an error result if the XML is not well formed, otherwise prints the XML. You can also use xmllint as a code formatter for XML. See help for this.

To insert a line break in your XSL template, just use this line in your stylesheet:

<xsl:text>&#10;</xsl:text>

Links:

• Thanks to rabidrobot in this post: http://forum.umbraco.org/yaf_postst2991_How-to-output-line-breaks-in-XSLT.aspx

Java has integrated XML functionality (Parser, DOM and XPath handling, …) starting with version 1.5. This is nice, because one does not need additional libraries to provide XML handling in the own code. The integrated XML functionality (compatible to JAXP 1.3) in Java 1.5 and 1.6 is provided by the Apache Xerces and Xalan libraries. Problematic is that SUN/Oracle uses quite old versions of these libraries:

• Xerces: Xerces-J 2.6.2, released: 20.02.2004 (!)
• Xalan: Xalan Java 2.6.0, released: 29.02.2004 (!)

This can lead to unexpected behavior of your code. For example, I am using an XSLT transformation in a software project. The code is correct and worked as expected – when running single threaded. But when starting in a multi threaded environment, even while taking care about the API comments to use one XML Transformer object for a thread, it sometimes worked and sometimes it does not. NullPointerExceptions were thrown by an internal (Xalan) class. Using more heap space delayed the problem, but did not solve it. The solution was an update of the much too old Xalan libraries included in Java 1.6. The problem disappeared after the upgrade.

The newest versions of Xerces an Xalan are currently:

• Xerces: Xerces-J 2.10.0, released: 18.06.2010
• Xalan: Xalan Java 2.7.1, released: November 2007

If you encounter problems using the SUN/Oracle Java built in implementation, you can replace the implementation by a new one this way:

1. Download the Xalan and/or Xerces libraries (Xalan is shipped with the matching Xerces libs!)
2. Unpack the *.jar files from the archive
3. Place them somewhere in your applications lib/ folder
4. Be sure that you call your application with the *.jar files on its classpath (java -cp xalan.jar:xercesimpl.jar:serializer.jar:xml-apis.jar MyApplication)

One may place the files in the $JAVA_HOME/jre/lib/ext/ folder instead. Why Java 1.6 is shipped with internal implementations of Xerces that are more that 6 years old is a question that only SUN/Oracle can answer. Even the newest Xalan version is three years old now, so one could expect that the libs are integrated in the JRE in the meantime…

How to find out the version of Xalan you are using:

1. Download this JAVA program: Xalanv.java
2. Compile it: javac Xalanv.java
3. Run it: java Xalanv