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.
CF Sighting
A sticker on a gas pump somewhere in West Virginia
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.
ColdFusion Builder: Source Control
If you decide to go the standalone route when installing ColdFusion Builder, be aware that Adobe pares down the included Eclipse build in order to make the file that you download as small as possible. One of the things that didn’t make the cut was the Eclipse CVS plugin. My colleague Sam Farmer blogged about this last week, but my experience was slightly different, so I thought I’d add to the discussion.
In order to add CVS into ColdFusion builder, go to Help > Software Updates. Sam had to add the Eclipse update site to his Software update sites, but the version that I installed (which I downloaded from the public beta site on 7/17/09) had the site already included. In Sam’s experience, the CVS Client was under the Eclipse SDK category, while I had to go into the “Uncategorized” section. Below is a screenshot of the trees that I opened. I neglected to capture the actual CVS builds that I got–there were several to choose from–but I just chose the highest version number listed: 1.1.2.x
After selecting the updates, click Install, and agree to the terms of use. CFB will ask to restart, and once it comes back up, if you previously used the same workspace for CVS in CFEclipse, CVS in CFBuilder will automatically attach itself to your projects.
Moving from CFEclipse to CF Builder: First Experience
This morning I held my breath and made the switch from CFEclipse to ColdFusion Builder. To keep things clean, I decided to install the standalone version. The install itself was painless, and so far CFB seems really snappy.
The first thing I did when it opened was point it at my old CFEclipse workspace so that I wouldn’t have to import my projects. In order to do this CFEclipse must be closed. Go to File > Switch Workplace > Other and browse to your old workplace folder. CFB will have to restart itself, but it does so very quickly. After starting back up, the first thing I noticed was that the snippets were missing. Snippets and shortcuts are important to me, and I really didn’t want to have to create them all over again, so I dug around in my workspace metadata and found them.
By default, cfeclipse seems to store them all in [workspaceroot]\.metadata\.plugins\org.cfeclipse.cfml\snippets. If you changed this folder in CFEclipse, all you have to do is (in CF Builder) go to Window > Preferences > ColdFusion > Snippets and update your snippet path. Since I’m moving away from CFE, I figured it would make more sense to not rely on a CFE folder to store my snippets, so I copied all the xml files from the default path above into [workspaceroot]\.metadata\snippets, closed the Snippets view, and opened it up again, and they all popped up.
iPhone and Windows Vista
I finally broke down and got an iPhone over the weekend. Since I spend the most amount of time on my work computer, I decided I’d use that as the main machine to sync it with. Monday morning I eagerly plugged the phone in, only to have Vista (Vista Ultimate, 64-bit) tell me it couldn’t find the driver for my new “digital camera” and iTunes ignore the iPhone. As someone who’s built a computer from scratch, I’ve found that drivers are always the first thing to check when your OS is behaving unexpectedly, and a quick Google search brought up this blog. My experiences were slightly different than his, so I thought I’d share them here.
1. Go to Computer, right click and choose “Properties”
2. Select the Device Manager
3. Scroll down to Portable Devices, and find the device with the missing/bad drivers. On the blog I found, the device was listed as an iPhone. On my machine it was called a Digital Still Camera. Right click on the device, and choose Update Driver Software.
4. On the next screen choose “Browse my computer for driver software”
5. Browse to C:\Program Files\Common Files\Apple and select this folder. After a minute or so of updating, all should be well.
Getting the current row number from MySQL
This morning I found a pretty slick way to pull the row number of a query (like Coldfusion’s #currentrow#) directly from MySQL.
select lookupID, lookupValue, lookupType, @rownum:=@rownum+1 as rownumber
from lookup, (SELECT @rownum:=0) r
This will return the lookupID, lookupValue, lookupType, and a number for each row, beginning with 1.
All the credit goes to this blog post, but I feel it’s always good to propagate tips like this.
