NULL does not equal NULL
I came across a bug today where records from a mysql table were not being returned when I expected them to be. It took me a while to strip away all the complexities of the query before I found the culprit:
AND !(oldvalue = null and newvalue = null)
The person who wrote the query was trying to exclude any records where both the oldValue and newValue data was null, but NULL does not equal NULL so when used in the criteria of a query, there won’t be any records returned.
I found a good conceptual explanation of this from a stackoverflow answer:
NULLmeans “no value” or “unknown value”, and thus any comparison with any actual valuemakes no sense.
The proper notation to test for nulls is “is null” or “is not null”. You can see this at work in this query:
select null = null tests
union
select null is null
Ordered Structs in ColdFusion
Last week I was working on a project and wanted to use a ColdFusion struct to push data into XML and JSON. The challenges were a) I wanted the XML and JSON translations to be “dumb”. i.e., I can push a structure to each one, and they translate them automatically and b) I wanted the output to be the same each time and the same across formats. However, if you call structKeyList() the list of structure keys are not ordered, so looping over the keys returns unpredictable results.
The solution was to access CF’s underlying Java. The ColdFusion struct is based on the Java HashMap. An extension of this is the Java LinkedHashMap, which behaves much like (and can be access by CF in the same way) as a HashMap/CF Struct but it keeps an ordered list of the keys.
<cfset structA = structNew() />
<cfset structA.first = “first” />
<cfset structA.second = “second” />
<cfset structA.third = “third” />structNew:<br>
<cfloop list=”#structKeyList(structA)#” index=”i”>
<cfoutput>#i#<br></cfoutput>
</cfloop>
<br>
<cfset structB = createObject(“java”, “java.util.LinkedHashMap”).init() />
<cfset structB.first = “first” />
<cfset structB.second = “second” />
<cfset structB.third = “third” />LinkedHashMap:<br>
<cfloop list=”#structKeyList(structB)#” index=”i”>
<cfoutput>#i#<br></cfoutput>
</cfloop>
Above we’re creating the similar objects, one of which is a regular CF struct (Java HashMap) and the other a LinkedHasMap. Running this code returns the following:
structNew:
THIRD
SECOND
FIRSTLinkedHashMap:
FIRST
SECOND
THIRD
Hat tip to @raelehman for finding the solution.
Call for Help: MS Access Select Statement Crashing CF Server
I have a client that still relies on an MS Access database, and I occasionally have a problem with a corrupt mdb file. If I open the file with Access 2000, the field looks empty in datasheet view; in Access 2010 it shows up as ################.
I’m trying to build in some error checking to catch the corrupt data before it gets sent out, and the column typically has a whole lot of data, so I wrote a query in ColdFusion to check if that field is less than 100 characters.
select count(1) as foo from main where len(pers_stmt) < 100
When I ran the page, however, the CF service immediately stopped. There are no entries in the server or application logs, so I can’t figure out what about the request is crashing the server. I even tried wrapping the query in a try/catch and the server still crashed.
Does anyone have any ideas (besides ditching Access) about where I can find logs relating to this?
CFHTTPS and untrusted SSL certificates
This morning I was trying to connect to a webservice over https, and I received an exception from ColdFusion with the message “javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated”. After a bit of Googling, I found this excellent blog post by Mark Kruger, but it’s from 2005, and a few things had changed, so I thought I write a follwup/update.
The reason this error is generated is because CF is trying to connect to a server over HTTPS, but the JRE powering your ColdFusion app doesn’t trust the SSL certificate that that server is using. The answer solution is to tell Java that source of that SSL certificate is acceptable.
Step 1. Download the SSL Certificate manually
To do this, bring up the URL in Firefox (making sure you’re using https). Go to Tools > Page Info, and select Security. Click View Certificate, select the Details tab, and then click the Export button, and save the certificate to your hard drive.
Step 2. Locate your JRE
Now that you have the certificate on your machine you essentially need to show it to the JRE, and tell it “I can vouch for him”. Before doing this, you’ll need to find your JRE. This can vary depending on your CF version and Edition, so to find this information log into the CF Administrator and go to Server Settings > Settings Summary. You’ll find the JRE location under “Java Home”. I changed the JRE I was using, so my Java Home value is C:\Program Files\Java\jre1.6.0_13.
Step 3. Check your currently accepted certificate sources
To store these trusted certificate sources, the JRE uses something called a keystore, and to interact with the keystore, you’ll need to use the keytool. Open a command line console, and navigate bin directory of the Java Home value you found in the last step. This is where my experience differed from Mark’s. I’m currently on ColdFusion8, and while Mark’s keytool.exe is in the %JAVAHOME%\lib directory, mine was in %JAVAHOME%\bin. You can navigate to the your JRE bin and lib folders in Windows Explorer to see which one contains the keytool.exe.
Once in this directory, you’ll run keytool -list to see a list of the SSL sources that are currently trusted. You don’t have to do this, but it will tell you how many entries your keystore currently has, and knowing this number will make it easy to verify that your certificate gets imported successfully.
C:\Program Files\Java\jre1.6.0_13\bin>keytool -list -storepass changeit -keystore ../lib/security/cacerts
To break this down, what this does is keytool (run keytool.exe) -list (list the certificates in my keystore) -storepass changeit (the password for the keystore is changeit) -keystore ../lib/security/cacerts (this is where you can find the keystore, which is shorthand for cd C:\Program Files\Java\jre1.6.0_13\lib\security\cacerts)
This is roughly what will be returned:
Keystore provider: SUN
Your keystore contains 44 entries
digicert, Aug 25, 2010, trustedCertEntry,
Certificate fingerprint (MD5): C9:A5:6B:6E:E7:F4:BA:56:62:03:21:E1:EB:DE:8F:85
entrustclientca, Jan 9, 2003, trustedCertEntry,
Certificate fingerprint (MD5): 0C:41:2F:13:5B:A0:54:F5:96:66:2D:7E:CD:0E:03:F4
verisignclass3g2ca, Mar 25, 2004, trustedCertEntry,
Certificate fingerprint (MD5): A2:33:9B:4C:74:78:73:D4:6C:E7:C1:F3:8D:CB:5C:E9
thawtepersonalbasicca, Feb 12, 1999, trustedCertEntry,
Certificate fingerprint (MD5): E6:0B:D2:C9:CA:2D:88:DB:1A:71:0E:4B:78:EB:02:41
globalsignca, Aug 1, 2007, trustedCertEntry,
Certificate fingerprint (MD5): AB:BF:EA:E3:6B:29:A6:CC:A6:78:35:99:EF:AD:2B:80
addtrustclass1ca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): 1E:42:95:02:33:92:6B:B9:5F:C0:7F:DA:D6:B2:4B:FC
verisignclass2g3ca, Mar 25, 2004, trustedCertEntry,
Certificate fingerprint (MD5): F8:BE:C4:63:22:C9:A8:46:74:8B:B8:1D:1E:4A:2B:F6
thawtepersonalpremiumca, Feb 12, 1999, trustedCertEntry,
Certificate fingerprint (MD5): 3A:B2:DE:22:9A:20:93:49:F9:ED:C8:D2:8A:E7:68:0D
addtrustexternalca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): 1D:35:54:04:85:78:B0:3F:42:42:4D:BF:20:73:0A:3F
valicertclass2ca, Jan 20, 2005, trustedCertEntry,
Certificate fingerprint (MD5): A9:23:75:9B:BA:49:36:6E:31:C2:DB:F2:E7:66:BA:87
entrustsslca, Jan 9, 2003, trustedCertEntry,
Certificate fingerprint (MD5): DF:F2:80:73:CC:F1:E6:61:73:FC:F5:42:E9:C5:7C:EE
equifaxsecureebusinessca2, Jul 18, 2003, trustedCertEntry,
Certificate fingerprint (MD5): AA:BF:BF:64:97:DA:98:1D:6F:C6:08:3A:95:70:33:CA
equifaxsecureebusinessca1, Jul 18, 2003, trustedCertEntry,
Certificate fingerprint (MD5): 64:9C:EF:2E:44:FC:C6:8F:52:07:D0:51:73:8F:CB:3D
thawtepremiumserverca, Feb 12, 1999, trustedCertEntry,
Certificate fingerprint (MD5): 06:9F:69:79:16:66:90:02:1B:8C:8C:A2:C3:07:6F:3A
verisignclass2g2ca, Mar 25, 2004, trustedCertEntry,
Certificate fingerprint (MD5): 2D:BB:E5:25:D3:D1:65:82:3A:B7:0E:FA:E6:EB:E2:E1
addtrustqualifiedca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): 27:EC:39:47:CD:DA:5A:AF:E2:9A:01:65:21:A9:4C:BB
entrustglobalclientca, Jan 9, 2003, trustedCertEntry,
Certificate fingerprint (MD5): 9A:77:19:18:ED:96:CF:DF:1B:B7:0E:F5:8D:B9:88:2E
utnuserfirsthardwareca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): 4C:56:41:E5:0D:BB:2B:E8:CA:A3:ED:18:08:AD:43:39
starfieldclass2ca, Jan 20, 2005, trustedCertEntry,
Certificate fingerprint (MD5): 32:4A:4B:BB:C8:63:69:9B:BE:74:9A:C6:DD:1D:46:24
verisignclass1g3ca, Mar 25, 2004, trustedCertEntry,
Certificate fingerprint (MD5): B1:47:BC:18:57:D1:18:A0:78:2D:EC:71:E8:2A:95:73
thawteserverca, Feb 12, 1999, trustedCertEntry,
Certificate fingerprint (MD5): C5:70:C4:A2:ED:53:78:0C:C8:10:53:81:64:CB:D0:1D
verisignclass3ca, Oct 27, 2003, trustedCertEntry,
Certificate fingerprint (MD5): 10:FC:63:5D:F6:26:3E:0D:F3:25:BE:5F:79:CD:67:67
entrustgsslca, Jan 9, 2003, trustedCertEntry,
Certificate fingerprint (MD5): 9D:66:6A:CC:FF:D5:F5:43:B4:BF:8C:16:D1:2B:A8:99
globalsignr2ca, Aug 1, 2007, trustedCertEntry,
Certificate fingerprint (MD5): 94:14:77:7E:3E:5E:FD:8F:30:BD:41:B0:CF:E7:D0:30
geotrustglobalca, Jul 18, 2003, trustedCertEntry,
Certificate fingerprint (MD5): F7:75:AB:29:FB:51:4E:B7:77:5E:FF:05:3C:99:8E:F5
verisignclass1g2ca, Mar 25, 2004, trustedCertEntry,
Certificate fingerprint (MD5): DB:23:3D:F9:69:FA:4B:B9:95:80:44:73:5E:7D:41:83
utnuserfirstclientauthemailca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): D7:34:3D:EF:1D:27:09:28:E1:31:02:5B:13:2B:DD:F7
comodoaaaca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): 49:79:04:B0:EB:87:19:AC:47:B0:BC:11:51:9B:74:D0
baltimorecybertrustca, May 10, 2002, trustedCertEntry,
Certificate fingerprint (MD5): AC:B6:94:A5:9C:17:E0:D7:91:52:9B:B1:97:06:A6:E4
equifaxsecureca, Jul 18, 2003, trustedCertEntry,
Certificate fingerprint (MD5): 67:CB:9D:C0:13:24:8A:82:9B:B2:17:1E:D1:1B:EC:D4
verisignclass2ca, Oct 27, 2003, trustedCertEntry,
Certificate fingerprint (MD5): B3:9C:25:B1:C3:2E:32:53:80:15:30:9D:4D:02:77:3E
verisignserverca, Jun 29, 1998, trustedCertEntry,
Certificate fingerprint (MD5): 74:7B:82:03:43:F0:00:9E:6B:B3:EC:47:BF:85:A5:93
entrust2048ca, Jan 9, 2003, trustedCertEntry,
Certificate fingerprint (MD5): BA:21:EA:20:D6:DD:DB:8F:C1:57:8B:40:AD:A1:FC:FC
utndatacorpsgcca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): B3:A5:3E:77:21:6D:AC:4A:C0:C9:FB:D5:41:3D:CA:06
soneraclass2ca, Mar 28, 2006, trustedCertEntry,
Certificate fingerprint (MD5): A3:EC:75:0F:2E:88:DF:FA:48:01:4E:0B:5C:48:6F:FB
utnuserfirstobjectca, May 2, 2006, trustedCertEntry,
Certificate fingerprint (MD5): A7:F2:E4:16:06:41:11:50:30:6B:9C:E3:B4:9C:B0:C9
verisignclass1ca, Mar 25, 2004, trustedCertEntry,
Certificate fingerprint (MD5): 97:60:E8:57:5F:D3:50:47:E5:43:0C:94:36:8A:B0:62
gtecybertrustglobalca, May 10, 2002, trustedCertEntry,
Certificate fingerprint (MD5): CA:3D:D3:68:F1:03:5C:D0:32:FA:B8:2B:59:E8:5A:DB
baltimorecodesigningca, May 10, 2002, trustedCertEntry,
Certificate fingerprint (MD5): 90:F5:28:49:56:D1:5D:2C:B0:53:D4:4B:EF:6F:90:22
soneraclass1ca, Mar 28, 2006, trustedCertEntry,
Certificate fingerprint (MD5): 33:B7:84:F5:5F:27:D7:68:27:DE:14:DE:12:2A:ED:6F
thawtepersonalfreemailca, Feb 12, 1999, trustedCertEntry,
Certificate fingerprint (MD5): 1E:74:C3:86:3C:0C:35:C5:3E:C2:7F:EF:3C:AA:3C:D9
gtecybertrust5ca, May 10, 2002, trustedCertEntry,
Certificate fingerprint (MD5): 7D:6C:86:E4:FC:4D:D1:0B:00:BA:22:BB:4E:7C:6A:8E
verisignclass3g3ca, Mar 25, 2004, trustedCertEntry,
Certificate fingerprint (MD5): CD:68:B6:A7:C7:C4:CE:75:E0:1D:4F:57:44:61:92:09
godaddyclass2ca, Jan 20, 2005, trustedCertEntry,
Certificate fingerprint (MD5): 91:DE:06:25:AB:DA:FD:32:17:0C:BB:25:17:2A:84:67
equifaxsecureglobalebusinessca1, Jul 18, 2003, trustedCertEntry,
Certificate fingerprint (MD5): 8F:5D:77:06:27:C4:98:3C:5B:93:78:E7:D7:7D:9B:CC
Step 4. Import the new certificate
In the last step, we saw that there were 44 entries in my keystore. Now we’ll add the new certificate.
-file g:\mynewcert.crt
This is similar to the options to get keystore list. The difference is instead of using -list, we’re using -import, and we’ve added -alias DigiCert (telling the keytoolwhat to name the entry) -noprompt (telling the keytoolnot to bother confirming that we want to add this entry) -trustcacerts (telling the keystore you want to add this as a trusted certificate) and -file g:\mynewcert.crt (telling the keytool where the new certificate is).
Step 5. Verify the import
After running the last command, you’ll get a response saying “Certificate was added to keystore” so this is probably unnecessary, but in the interest of thoroughness, I like to run the command from Step 3 to make sure that the number of entries in my keystore has incremented.
Keystore type: JKS
Keystore provider: SUN
Your keystore contains 45 entries
I’ve truncated the response, but as you can see my keystore now contains 45 entries.
The final step is to restart each instance of CF that needs to access the webservice so that CF has access to the updated keystore.
I came across an odd issue with Safari 4 this morning. A form in our application wasn’t submitting. I checked the error console, and it was returning an error of “An invalid form control with name=shipDepartment is not focusable”. I wasn’t getting any errors with Chrome, Firefox, and most surprisingly IE even worked fine.
I tried putting breakpoints wherever shipDepartment was referenced in the javascript, but none of them were firing. After using the Safari Web Inspector Console to change the value of the form field a few times, I found the problem. The field’s value was being set via javascript, and the value that was being set was longer than the maxlength attribute allowed. To fix the problem, I simply upped the maxlength attribute.
It’s not the most descriptive error message, and Googling the error mostly turned up Chromium error forums and comments, so maybe this will help someone having the same problem in Safari.
ColdFusion Job in Washington DC
We at Interfolio are looking for an intermediate CF developer to join our team. Interfolio helps our members (primarily applicants to graduate school, PhD programs, and teaching positions) collect and distribute their dossiers. Our newest product–the Interfolio Portfolio–also allows these academics to publicly showcase the work they’ve done throughout their careers in order to manage their online identity, differentiate themselves from other applicants, and build networks within their respective fields.
I have been with the company for almost eight years now and still get excited about the things that we’re doing. We’re an energetic and passionate group and because the company is still small this is a great opportunity for someone to take on responsibility and quickly impact our users. If this interests you, see the job description below.
Interfolio. Software Developer. Join Our Team.
Interfolio is the first all-in-one credentials, dossier & academic portfolio service.
We are a successful software-as-a-service business founded in 1999. Our services are built on powerful, elegant software using Adobe ColdFusion. We are a vibrant, innovative company that is working to become a major player in the current and next generation of consumer technology.
Interfolio is seeking an intermediate ColdFusion software developer to join our technical team in downtown Washington, DC and assist in the design, development, and implementation of new features in our products.
Responsibilities:
- Digest and understand user requirements/specifications
- Participate in maintenance, enhancements and new development
- Contribute to the testing and release of new software
- Work with other members of the technical team to plan software solutions
- Make recommendations to improve the products and software to increase revenue/profitability
- Keep track of multiple projects at once and adapt to shifting priorities
- Perform other related duties to support Interfolio’s technology
Qualifications:
Required:
- ColdFusion MX and above (3+ years)
- SQL, preferable MySQL
- HTML
- JavaScript
- CSS
- AJAX
- Mach-II or similar Object-Oriented Framework
- Good written and verbal communication skills with both technical and non-technical audiences
Preferred:
- CVS/SVN familiarity
- UI design experience
- Understanding of ColdFusion, MySQL, IIS Administration
Qualities:
- Desire to work in a constantly evolving environment
- Confidence in yourself and your abilities
- Importance on being a unique individual
- Self-starter
- Ability to flourish in a “start-up” like environment
- Team player
If you are looking for a groundbreaking opportunity where you can imprint your style and make an immediate impact in a professional organization, we may be the perfect place for you. To learn more about us, please visit our website at www.interfolio.com
Please send your cover letter and resume to recruiting@interfolio.com
Interfolio is an Equal Opportunity Employer
INTEGER datatype in MySQL
I’ve spent the week away from work, taking a MySQL DBA course here in DC and have learned a lot about things I’d never even seen and things that I had misconceptions about. One of those misconceptions that I bet a lot of other developers share is regarding the INTEGER/INT datatype in MySQL.
When creating a new column, I have always put the max number of digits in parentheses after INTEGER. For example, if I didn’t expect a particular field to ever be more than 9,999 (in value), I would create the column as INTEGER(4). This is dead wrong, and according to my teacher quite a lot of people do this. The number after INTEGER is actually the minimum number of characters that you want to show if you have ZEROFILL turned on.
For example:
CREATE TABLE myintegers (theints INTEGER(4) ZEROFILL);
INSERT INTO myintegers VALUES (3),(333),(33333),(3333333);
SELECT theints FROM myintegers;
will return
| theints |
|---|
| 0003 |
| 0333 |
| 33333 |
| 3333333 |
In actuality, if you don’t ever plan on a column going above 4 digits long, you should use a small int datatype. Not only is it more specific to what you want, but it results in only 2 bytes of storage space being allocated versus 8 for an INT. On a high performance database, this can make a big difference.
Finally, while I’m on the subject, I thought I’d mention the UNSIGNED option. Adding the UNSIGNED option tells the storage engine that there can be no negative numbers. Because negative numbers are disallowed, your upper limit for the value of your column is twice the value minus 1 what it would be if you allowed negatives. A lot of you my be saying ‘of course’, but that was one of those things I always wondered about and never bothered to look up.
The storage space and lower/upper limits for MySQL integer (lowercase) data can be found here.
