## Friday, September 23, 2011

### Get HTTPS to work for localhost on Tomcat

I needed to test how my app works with HTTPS locally, so I began going through the Tomcat 7 HOW-TO for setting up SSL. I got the basics out of it, but it didn't work locally and being a security n00b I really didn't understand what I was doing anyway. So I made a StackOverflow post, SSL in Tomcat 7 where EJP helped me get it to work. And thus I document it here so that I can repeat these steps in a year's time when I have completely forgotten what I did to get it working. I will note below what bits the Tomcat 7 HOW-TO didn't make clear with this marker: HOW-TO Note.

I am using Tomcat 7, Windows 7, JDK 6 - and I have %JAVA_HOME%\bin correctly set in my path.

## 1. Make a Key Pair

Use the Java keytool utility to generate a key pair (a public key and associated private key). If you haven't already created a keystore, this command will create one for you (by default in user home, so for Windows 7 the keystore created is the file C:\Users\USERNAME\.keystore).

The following shows what input I used for the command suggested by the HOW-TO: keytool -genkey -alias tomcat -keyalg RSA.

keytool -genkey -alias tomcat -keyalg RSA
What is your first and last name?
[Unknown]:  localhost
What is the name of your organizational unit?
[Unknown]:  Dev
What is the name of your organization?
What is the name of your City or Locality?
[Unknown]:  Melbourne
What is the name of your State or Province?
[Unknown]:  Victoria
What is the two-letter country code for this unit?
[Unknown]:  AU
Is CN=localhost, OU=Dev, O=MyBusiness, L=Melbourne, ST=Victoria, C=AU correct?
[no]:  yes



HOW-TO Note: in response to the question What is your first and last name? enter localhost, not your name!

If you have never run this command before, it will output a single file: C:\Users\USERNAME\.keystore. If you like, confirm that the aliased key pair has been made with a command such as keytool -list -alias tomcat, shown below.

keytool -list -alias tomcat
tomcat, 23/09/2011, PrivateKeyEntry,
Certificate fingerprint (SHA1): B2:B9:DB:B8:C3:9F:7D:2B:F8:82:A2:A2:C7:A2:9A:94:90:65:69:6E


## 2. Create Connector in server.xml

Open up the server.xml file for your Tomcat. I am running a Tomcat instance through Eclipse so for me the file is not the one stored in the Tomcat install directory (TOMCAT-INSTALL-DIR\conf\server.xml) but ECLIPSE-WORKSPACE\Servers\Tomcat v7.0 Server at localhost-config\server.xml. Look for the comment that includes the text Define a SSL HTTP/1.1 Connector on port 8443, and add the below XML after that comment.

<Connector port="8443" maxThreads="200"
scheme="https" secure="true" SSLEnabled="true"
keystoreFile="\${user.home}/.keystore" keystorePass="changeit"
clientAuth="false" sslProtocol="TLS"/>


Now restart Tomcat.

## 3. Load the App, Trust the Certificate

HOW-TO Note: the HOW-TO doesn't mention anything about this. Probably they didn't have localhost in mind, or maybe it's because I am not a trusted source of certificates.

1. Load up your app in IE: https://localhost:8443/path/to/your/app (note the use of port 8443 - as per the Connector XML above).
2. You will see an error page (There is a problem with this website's security certificate....). Click Continue to this website (not recommended).
3. The address bar will be red and have the text "Certificate Error". Click on it. See the message: The security certificate presented by this website was not issued by a trusted certificate authority.
4. Click View Certificates. See the below dialogue box.
5. Click Install Certificate > Next
6. Select Place all certificates in the following store > Browse > select Trusted Root Certificate Authorities > click OK.
7. Click Next > click Finish. See this:
.
8. Click Yes > see the message The import was successful > click OK > click OK.
9. Re-load https://localhost:8443/path/to/your/app in IE and it should work without showing any certificate error!

## Unresolved Issues

This isn't perfect though. There are two problems I haven't worked out yet.

It doesn't work if I install the certificate first. You can export the certificate like this.

keytool -export -alias tomcat -file tomcatcertfile.cer
Certificate stored in file 

This will output a file in the same directory as the keystore file, by default C:\Users\USERNAME\tomcatcertfile.cer.

1. Go Start Menu, type "Internet Options" > select Content tab > click Certificates.
2. Select Trusted Root Certification Authorities tab.
3. Click Import > click Next > enter File Name: C:\Users\USERNAME\tomcatcertfile.cer > click Next
4. Click Next > click Finish > click OK > click close > click OK.

But after I did this, I still see a certificate error in IE.

It doesn't work in Chrome or Firefox - although in Firefox you can add an exception. In Chrome, I see the following error, which I have found no workaround for.

## Monday, September 19, 2011

### Unsupported major.minor version 51.0 while patching component in LiveCycle ES2

Got this when patching a component in my local LiveCycle ES 2.

When trying to patch the component I got the following unhelpful error from (Eclipse based) Adobe LiveCycle Workbench ES2:

ALC-DSC-027-000: com.adobe.idp.dsc.registry.component.IncompatiblePatchException: Cannot patch component: au.com.blah.NotMyRealComponentName with component having id of: au.com.blah.NotMyRealComponentName
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
... snip *yawn*


I tried un-installing and then re-installing the component, and got an equally unhelpful error from Workbench:

ALC-DSC-000-000: com.adobe.idp.dsc.DSCRuntimeException: Internal error.
at sun.reflect.GeneratedMethodAccessor644.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
... snip *yawn*


Finally I remembered to look in server.log and found the smoking gun: Unsupported major.minor version 51.0.

2011-09-19 15:03:27,064 ERROR [org.jboss.ejb.plugins.LogInterceptor] RuntimeException in method: public abstract java.lang.Object com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterLocal.doBMT(com.adobe.idp.dsc.transaction.TransactionCallback) throws com.adobe.idp.dsc.DSCException:
at sun.reflect.GeneratedMethodAccessor644.invoke(Unknown Source)
... snip
Caused by: java.lang.UnsupportedClassVersionError: au/com/blah/datatype/PukErrorListCombo : Unsupported major.minor version 51.0
... snip


From this old java.net post called Unsupported major.minor version 51.0: It means that you compiled your classes under a specific JDK, but then try to run them under older version of JDK. So, you can't run classes compiled with JDK 6.0 under JDK 5.0. The same with classes compiled under JDK 7.0 when you try to run them under JDK 6.0.

I have had this before, when I had to switch major versions e.g. revert from using JDK5 for development to JDK4 because the end server used that version. In my case, it was a lot narrower. I was building the component jar using JDK 1.6.0_26 whereas the LiveCycle ES2 uses JDK 1.6.0_14 (which I found by looking at what Java it installed: C:\Adobe\Adobe LiveCycle ES2\Java\jdk1.6.0_14\bin\javaw.exe). So I reverted my build script to use the earlier JDK.

The next problem I found is that merely patching the component still fails. I had to un-install and then re-install the component to get it to work.

### Eclipse and SVN on 64-Bit Windows

I am running the following components.

• JDK 1.7.0 (build 1.7.0-b147), Java HotSpot(TM) 64-Bit Server VM (build 21.0-b17, mixed mode)
• Eclipse eclipse-jee-indigo-win32-x86_64
• Subclipse 1.6.18
• Windows 7 64-bit
• CollabNet Subversion Command-Line Client v1.6.17 (for Windows)

And I get the below error from Eclipse every time I try to use SVN for the first time (since opening Eclipse). The operation still works, but this error is annoying.

Subclipse talks to Subversion via a Java API that requires access to native libraries. Those libraries were either not found or an incompatible version was loaded. The errors are displayed below. Informaction on getting a proper version installed and visible to Eclipse is available here: http://subclipse.tigris.org/wiki/JavaHL

These are the errors that were encountered:
C:\Program Files (x86)\CollabNet\Subversion Client\libapr-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libapriconv-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libeay32.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\ssleay32.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libaprutil-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsasl.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_subr-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_delta-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_diff-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_wc-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_fs-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_repos-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_ra-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvn_client-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
C:\Program Files (x86)\CollabNet\Subversion Client\libsvnjavahl-1.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform
no svnjavahl-1 in java.library.path
no svnjavahl in java.library.path
java.library.path = C:\Windows\system32;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;.;C:\cygwin\home\rbram\bin;C:\cygwin\usr\local\bin;C:\cygwin\bin;C:\Program Files (x86)\CollabNet\Subversion Client;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0;C:\Program Files\Intel\WiFi\bin;C:\Program Files\Common Files\Intel\WirelessCommon;C:\Program Files\Intel\DMIX;C:\Program Files\TortoiseSVN\bin;C:\Program Files (x86)\WinMerge;C:\Program Files (x86)\QuickTime\QTSystem;C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn;C:\Program Files (x86)\IDM Computer Solutions\UltraEdit;C:\Program Files\Intel\WiFi\bin;C:\Program Files\Common Files\Intel\WirelessCommon;C:\Users\rbram\Documents\Java\apache-ant-1.8.2\bin;C:\Program Files\7-Zip;C:\cygwin\bin;C:\cygwin\lib\lapack;.


When I look at the page the error mentions, it has a section on 64-Bit Windows that essentially says I need to install SlikSVN. So I installed Slik-Subversion-1.6.17-x64.msi and removed C:\Program Files (x86)\CollabNet\Subversion Client from my path. After a re-boot, I haven't seen the problem come back.

## Sunday, September 18, 2011

### Autohotkey script for HTML/XML - create closing tags

Note. Get the AHK script. Discuss it on the Autohotkey forum, or see discussion on Autohotkey for the best way to select a word.

UltraEdit is my favourite text editor: it has a very strong macro language and lets you write scripts in Javascript as well. Previously I wrote about a set of UltraEdit macros for creating XML/HTML tags: UltraEdit macro for HTML/XML - auto-create closing tags. I use these two macros so often in UltraEdit that I began to feel the pain whenever I was editing some Google Doc, forum post, or XML file in Eclipse and wanted the same functionality. This is where Autohotkey comes in - I have already written about my favourite Autohotkey scripts. Autohotkey is an amazing Windows scripting language that is flexible and relatively easy to grasp. So I set about translating these two UltraEdit macros into Autohotkey. Here is the result.

The first macro outputs an empty container tag, with the cursor left in the middle. For example, if I were to type out xmlTag| and press Alt+Control+Shift+r, I will be left with the following: <xmlTag>|</xmlTag>. The pipe character (|) indicates cursor position, i.e. when you invoke the macro, your cursor should be at the end of the word (after the "g" in this example) and will be left in between the two tags when the macro finishes. If you have a tag name that contains non alpha-numeric characters such as hyphens, underscores or (Maker forbid) spaces, select the entire tag name first.

; Make XML tag out of current token, leaving cursor in the middle.
; Usage GOOD (selects token with a control+shift LEFT):
;  token|
;  >token< entire "token" selected
;  >token-token< entire "token-token" selected
;  >token_token< entire "token_token" selected
;  token-token|
;  token_token|
;  toke|n
;  |token
!^+r::
ClipSaved := ClipboardAll  ; Save the entire clipboard to a variable of your choice.
Clipboard := ; Clear the clipboard
Send ^x
if (clipboard == "") {
Send, {CTRLDOWN}{SHIFTDOWN}{LEFT}{CTRLUP}{SHIFTUP}{CTRLDOWN}x{CTRLUP}
}
token := RegExReplace(Clipboard, "[[:space:]]")
whiteSpace := RegExReplace(Clipboard, "\S")
Send <%token%></%token%>%whiteSpace%
x := StrLen(token) + 3 + StrLen(whiteSpace)
Loop, %x% {
Send, {Left}
}
Clipboard = %ClipSaved%   ; Restore the original clipboard. Note the use of Clipboard (not ClipboardAll).
ClipSaved =   ; Free the memory in case the clipboard was very large.
return


The second macro does the same thing except that it will paste whatever is in your clipboard in between the two tags and leave the cursor at the end of the closing tag. For example, if I copied the text lorum ipsum baby! and then typed out xmlTag| and pressed Alt+Control+Shift+t, I will be left with the following: <xmlTag>lorum ipsum baby!</xmlTag>|. Again, the pipe character (|) indicates cursor position, i.e. when you invoke the macro, you cursor should be at the end of the word (after the "g" in this example) and will be left at the end of the closing tag when the macro finishes. If you have a tag name that contains non alpha-numeric characters, select the entire tag name first.

; Make XML tag out of current token, pasting clipboard contents in between and leaving cursor after the end of the closing tag.
; Usage GOOD (selects token with a control+shift LEFT):
;  token|
;  >token< entire "token" selected
;  >token-token< entire "token-token" selected
;  >token_token< entire "token_token" selected
;  token-token|
;  token_token|
;  toke|n
;  |token
!^+t::
tagContents = %clipboard%    ; Convert any copied files, HTML, or other formatted text to plain text.
Clipboard := ; Clear the clipboard
Send ^x
if (clipboard == "") {
Send, {CTRLDOWN}{SHIFTDOWN}{LEFT}{CTRLUP}{SHIFTUP}{CTRLDOWN}x{CTRLUP}
}
token := RegExReplace(Clipboard, "[[:space:]]")
whiteSpace := RegExReplace(Clipboard, "\S")
Clipboard = %tagContents%   ; Restore the original clipboard as text.
Send <%token%>^v</%token%>%whiteSpace%
x := StrLen(whiteSpace)
Loop, %x% {
Send, {Left}
}
tagContents =   ; Free the memory in case the clipboard was very large.
clipLength :=
return


## Not as good as UltraEdit

Unfortunately, this Autohotkey script is not as good as the UltraEdit macro - because the UltraEdit macro command SelectWord correctly handles "selecting current word under the cursor" irrespective of whether your cursor is at the start, end or in the middle of the word, or whether the word is at the start, end or somewhere in the middle of a line. In the Autohotkey script, I am using {CTRLDOWN}{SHIFTDOWN}{LEFT}{CTRLUP}{SHIFTUP} (control+shift+left) to select the word just typed (unless you have the entire word already selected). The control+shift+left action yields incorrect results if the cursor is not at the end of the word - and using control+right, control+shift+left yields incorrect results if the word is at the end of the line and the cursor is at the end of the word (because it will select the newline character as well). I don't know how to mimic the SelectWord behaviour properly in Autohotkey without resorting to imitating a double click (which is crazy - your mouse and cursor will be at different positions when you are typing) or using caret logic that won't work in all types of inputs that accept text.

As a result, I use the UltraEdit macros when in UltraEdit and the Autohotkey macros when in any other program - but I still have the same keyboard mappings for both! How do I do this? Because Autohotkey is so cool that you can even define what applications a script should be active for. I surround the Autohotkey script logic in a #IfWinNotActive, UltraEdit directive that will prevent it from working in UltraEdit. See below for an example of this.

#IfWinNotActive, UltraEdit
; ... Autohotkey script that will work in any app other than UltraEdit.
#IfWinNotActive


So I get functionality that is more or less the same everywhere, just more flexible in UltraEdit.

## Saturday, September 17, 2011

### Separate DDL and DML in Transact-SQL with Batches

I wrote about this problem in a Stackoverflow.com post called Problem with alter then update in try catch with tran using Transact-SQL where Mikael Eriksson gave me just the hint I needed. My problem came about when I tried to run the below Transact-SQL via sqlcmd (SQL Server 2005) (using the command: sqlcmd -S "localhost\SQLEXPRESS" -U sa -P password -i "C:\path\to\transform.sql").

USE PUK;
BEGIN TRANSACTION;
BEGIN TRY

-- Modify the table RETRIEVAL_STAT
SOURCE nvarchar(10) NULL,
ACCOUNTNUMBER nvarchar(50) NULL,
PUK nvarchar(20) NULL;

-- Modify data in new columns of table RETRIEVAL_STAT
update dbo.RETRIEVAL_STAT set
SOURCE = 'XX',
ACCOUNTNUMBER = 'XX',
PUK = 'XX';

END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO


Note that it contains code that alters a table (DDL) followed by code that modifies data in a table (DML). Also note that the DML is meant to work on the new columns introduced by the DDL. This code generates the below error.

(0 rows affected)
Changed database context to 'PUK'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 13
Invalid column name 'SOURCE'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 13
Invalid column name 'ACCOUNTNUMBER'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 13
Invalid column name 'PUK'.

The problem is that Transact-SQL is compiled first, then executed. It means that when the compiler goes through my SQL, it finds that the second block - the SQL that modifies the new columns - cannot possibly work because those columns don't exist yet. (Because it hasn't executed the first block yet, it is still compiling the whole lot!) The solution is simple enough. Place the blocks in separate batches, which are compiled (and thus executed) separately, but still in order because I will still place the batches in the same file, one after the other, and run them with the same command. A batch is a series of Transact-SQL statements (that may include a try-catch) separated by the GO command.

This code works.

USE PUK;
BEGIN TRANSACTION;
BEGIN TRY

-- Modify the table RETRIEVAL_STAT
SOURCE nvarchar(10) NULL,
ACCOUNTNUMBER nvarchar(50) NULL,
PUK nvarchar(20) NULL;

END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

USE PUK;
BEGIN TRANSACTION;
BEGIN TRY

-- Modify data in new columns of table RETRIEVAL_STAT
update dbo.RETRIEVAL_STAT set
SOURCE = 'XX',
ACCOUNTNUMBER = 'XX',
PUK = 'XX';

END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO


Pages that helped me with this post.

• What are the difference between DDL, DML and DCL commands? Data Definition Language (DDL) statements are used to define the database structure or schema (create, alter, drop etc). Data Manipulation Language (DML) statements are used for managing data within schema objects (select, insert, update etc).
• My Stackoverflow.com post called Problem with alter then update in try catch with tran using Transact-SQL where Mikael Eriksson gave me just the hint I needed.
• MSDN page on GO (Transact-SQL) - remember that a GO statement marks the end of a batch. Something that confused me mightily up 'till now is this: GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. I was getting very frustrated because whenever I tried testing SQL that contained GO in some tool other than an MS SQL Server tool (such as Execute Query, DbVisualizer or Eclipse, the code failed). Now I know why: GO isn't SQL.
• MSDN page on TRY...CATCH (Transact-SQL). A try-catch block cannot span multiple batches. It means you cannot put a GO within a try-catch, followed by more code.

## Thursday, September 15, 2011

### Windows: Delete a Service Definition when the files are gone

This happened to me wast week on my Windows 7 machine. I installed Adobe LiveCycle ES 2, something went wrong during the install and the un-installer didn't complete - so I simply deleted everything under C:\Adobe. I could not re-install Adobe LiveCycle ES 2 because the installer was telling me that the MySQL (and then the JBoss) services were already installed. These are services that the turnkey Adobe LiveCycle ES 2 installs. So in this case, none of the files were present (I deleted them) but the service definitions were still present in the Windows Control Panel > Services page. To delete them I ran the sc delete command as such:

sc delete "MySQL for Adobe LiveCycle ES2"
sc delete JBOSS_FOR_ADOBE_LIVECYCLE_ES2

## Wednesday, September 07, 2011

### JNDI binding in JBoss

How to get a JNDI name in a DSdotXML file (-ds.xml) with a matching application reference in JBoss. Thanks to Ketan, who is awesome!

I had big problems getting this to work. I wrote about in the Adobe LiveCycle Developers Google Group: Problem with JNDI binding in JBoss (LiveCycle turnkey) and on StackOverflow: Problem with JNDI binding in JBoss (LiveCycle turnkey), but it was my friend Ketan who pointed out the solution.

I needed a jboss-web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<jboss-web>
<resource-ref>
<res-ref-name>RMB_DS</res-ref-name>
<jndi-name>java:/RMB_DS</jndi-name>
</resource-ref>
</jboss-web>


And changed the original web.xml to have:

<resource-ref>
<description>DB Connection</description>
<res-ref-name>RMB_DS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>


Which matches rmb-ds.xml

<datasources>
<local-tx-datasource>
<jndi-name>RMB_DS</jndi-name>
<connection-url>jdbc:sqlserver://localhost\SQLEXPRESS;DatabaseName=RMB</connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<user-name>sa</user-name>
<check-valid-connection-sql>SELECT 1 FROM sysobjects</check-valid-connection-sql>
<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<type-mapping>MS SQLSERVER2000</type-mapping>
</local-tx-datasource>
</datasources>


## Tuesday, September 06, 2011

### Get JBoss Web Console and JMX Console back in Turnkey ES2

In my new work-place, we are using the turnkey version of ES2 (Adobe - LiveCycle Enterprise Suite - note that as of ES3, it is being renamed to ADEP, Adobe Digital Enterprise Platform). The turnkey project installs LiveCycle on JBoss with MySql.

I am having trouble finding out what JNDI datasources are present, so I looked for JBoss JMX Console or JBoss Web Console. But they are missing in the turnkey project - for security purposes. Thankfully, it is very easy to bring them back.

First, find out what version of JBoss you are using. I found this in the JBoss readme: C:\Adobe\Adobe LiveCycle ES2\jboss\readme.html (by default on Windows, ES2 installs itself to C:\Adobe\Adobe LiveCycle ES2). Once you know your version (4.2.1.GA for ES2), download the original zip file for that version of JBoss and unzip it somewhere. Copy the management directory (web console) and jmx-console.war war file (JMX console) into your JBoss' deploy directory. For me that meant copying C:\Temp\jboss-4.2.1.GA\server\all\deploy\management and C:\Temp\jboss-4.2.1.GA\server\all\deploy\jmx-console.war to C:\Adobe\Adobe LiveCycle ES2\jboss\server\lc_turnkey\deploy.

Now I have web-console: http://localhost:8080/web-console/ and JMX Console: http://localhost:8080/jmx-console/.

### Making Sugar fields sortable and linkable

I am using Sugar Community Edition, Version 6.2.0RC3 (Build 6350).

I found that in the list views for custom modules in Sugar - and in the sub-panels Sugar creates when you make relationships between custom modules in Sugar - only name fields appear as links to the items being listed. Furthermore, it only allows name fields to be sortable by default. This is annoying default behaviour - particularly if you don't want to use a name field. Thankfully, it is relatively easy to fix.

Linking requires a different fix for list views and sub-panels. I came across a post in the Sugar forums that gave me the clues I needed for this: No list view link.

Linking in list views. You need to find the listviewdefs.php file for the particular module you are working on. This path will differ slightly depending on whether you have the module in a package or not (I do). In my example, assume I have a field called "FIELD_P" in a module called "Module_X" in a package called "Package_A". Look for the file <SUGAR_INSTANCE>\custom\modulebuilder\packages\Package_A\modules\Module_X\metadata\listviewdefs.php. (The path to SUGAR_INSTANCE will be a the unzipped Sugar install deployed to an Apache instance.) Within that file, find the array representing the field you want to make "linkable". It should look like this:

'FIELD_P' =>
array (
'type' => 'enum',
'studio' => 'visible',
'label' => 'LBL_FIELD_P',
'sortable' => false,
'width' => '10%',
'default' => true,
),


All you need to do is add another array element, 'link' => true, like so:

'FIELD_P' =>
array (
'type' => 'enum',
'studio' => 'visible',
'label' => 'LBL_FIELD_P',
'sortable' => false,
'width' => '10%',
'default' => true,
),


Linking in sub-panels. Find the PHP file for the subpanel. For my example, the default sub-panel will be in this file: <SUGAR_INSTANCE>\custom\modulebuilder\packages\Package_A\modules\Module_X\metadata\subpanels\default.php. Again, find the array representing the field you want to make "linkable". It should look like this:

'FIELD_P' =>
array (
'type' => 'enum',
'studio' => 'visible',
'label' => 'LBL_FIELD_P',
'sortable' => false,
'width' => '10%',
'default' => true,
),


All you need to do is add another array element, 'widget_class' => 'SubPanelDetailViewLink', like so:

'FIELD_P' =>
array (
'type' => 'enum',
'studio' => 'visible',
'label' => 'LBL_FIELD_P',
'sortable' => false,
'width' => '10%',
'default' => true,
),


Sorting is even easier, and you have already seen where to fix it for both sub-panels and list views - change 'sortable' => false, in the above two examples to 'sortable' => true,.

Final note: you are making changes to files that Sugar has generated. I noticed that whenever I requested further deployments and cache refreshes, the changes remained - but who can tell what sort of action will blow those changes away. For this reason, it is very important to back up your Sugar files regularly.

## Friday, September 02, 2011

### java.lang.ClassNotFoundException: No ClassLoaders found for: com.microsoft.sqlserver.jdbc.SQLServerDriver

Today I was making a JDBC call to an application running on JBoss and using MS SQL Server 2005 Express Edition (SP 4), when I received the below error.

Exception: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.microsoft.sqlserver.jdbc.SQLServerDriver; - nested throwable: (java.lang.ClassNotFoundException: No ClassLoaders found for: com.microsoft.sqlserver.jdbc.SQLServerDriver)); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.microsoft.sqlserver.jdbc.SQLServerDriver; - nested throwable: (java.lang.ClassNotFoundException: No ClassLoaders found for: com.microsoft.sqlserver.jdbc.SQLServerDriver)))

It meant that JBoss didn't have the JDBC driver jar installed.

Microsoft has a page for JDBC Drivers, which includes a link to download Microsoft SQL Server JDBC Driver 3.0. Download and run sqljdbc_3.0.1301.101_enu.exe (which is just a ZIP archive). Copy the driver jar: Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar to C:\Adobe\Adobe LiveCycle ES2\jboss\server\lc_turnkey\lib, then restart JBoss to make sure the changes take effect.

### TCP/IP connection to the host 127.0.0.1, port 1433 has failed

I encountered this annoying error today when attempting to make a JDBC connection to MS SQL Server 2005 Express Edition (SP 4).

Exception: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".))

The problem was that TCP/IP connections were not enabled. To fix this, open SQL Server Configuration Manager, select SQL Server 2005 Network Configuration (32bit), select Protocols for SQLEXPRESS, mark TCP/IP as Enabled. Also, restart MS SQL Server to ensure the changes take effect.

For future reference, MS SQL JDBC connection strings look like this: jdbc:sqlserver://host\instanceName;DatabaseName=TheDbName - optionally include username and password with jdbc:sqlserver://host\instanceName;DatabaseName=TheDbName;user=xxx;password=xxx. You can find out host and instance name through Microsoft SQL Server Management Studio Express: right click on the DB and select Properties.

Also, MS SQL Server runs on port 1433 by default, but the connection string doesn't specify this. Why?