David Mineer's Blog

Add a column if it doesn't exist to all tables?

Thu, 18 Apr 2013 22:32:00 -0600

Using MSSQL I needed to add a column to every table in a legacy database because I wanted to see whenever a new record was added. Needed a 'createdate' field that defaulted to getdate(), Instead of having to do each table one at a time I was able to find a query that did it all for me. Saved me a couple of hours. Thanks to Add a column if it doesn't exist to all tables? Here is the code:


EXEC sp_MSforeachtable '
if not exists (select * from sys.columns 
               where object_id = object_id(''?'')
               and name = ''CreatedOn'') 
begin
    ALTER TABLE ? ADD CreatedOn datetime NOT NULL DEFAULT getdate();
end';

Earning my money now cause I just shaved off a couple hours of billable time for my client. Microsoft SQL Server

Bad Samsung 27" Monitor

Tue, 05 Mar 2013 09:07:00 -0600

We recently attended the NAHB International Home Builders show were we had a booth. For this booth we bought two Samsung 27" monitors. The monitors are beautiful and big. Several times during the show, when we would first start up the computer the monitor would go blank. We were busy, so we didn't think much about. Unplugged, powered off, and it just seemed to work again. After returning home I replaced my second monitor with one of those 2 monitors. Whenever the monitor would come on it would go blank after a variable amount of time. Sometimes it would go right off, others it would stay on for 15-20 or even 30 seconds. The only solution I found was to power it off and back on which was quick and easy with the power button on the front bottom right of the screen. But this drove me crazy because some days I would have to power cycle it more than 10 times. When this happens, the monitor appears to go off, but you can actually see very faint shadows of what was on your screen. I updated the video drivers on my computer, did the monitor self test, and tried every solution I could find on google. I finally contacted samsung support and they had me try everything I had already tried and then asked if I had another computer to try it on. Since I had two monitors and couldn't remember for sure if I had ever had this one plugged into another computer I said I would try that and call them back. First thing I did though, was hook up our other exact same monitor and it does not have the same problem. It works great which to me means the other monitor is defective. We bought it from cosco and will be exchanging it for the same thing (that hopefully works). I do like the monitor, it is very crisp and bright and huge for a desktop monitor. If it works it is great.

Making it work with your busy spouse

Fri, 25 Jan 2013 13:00:00 -0600

Just read a great article written by the wife of a busy husband. My wife has always been very supportive of me and we are also knocking it out of the park with most of these suggestions. A great list: A Post Startup Execs Should Forward to Your Spouse or Partner. 12 Tips for Making it Work

Smart Commit messages for Jira Issues in bitbucket

Thu, 29 Nov 2012 22:46:00 -0600

Just realized that you can process JIRA issues with commit messages. They call these 'smart commits'. When we used unfuddle it was nice that we could make changes to the ticket with 'powerful commit messages'. We used SVN on unfuddle and could commit with a message like 'spent 2.5 hours on #345 resolved' or something to that affect and it would record the time spent and resolve the ticket. This was a feature that we missed when we moved to a GIT repo on bitbucket linked to our Jira instance. Well, it's finally here. The details can be found at Processing JIRA issues with commit messages I had to enable 'smart commits' in the DVCS management section of Jira. More info in that same area at Enabling DVCS smart commits We have not had a 'great' transition to GIT and my Developers have asked more than once to go back to SVN. GIT has a few quirks, but I don't mind it. And in the last few days I have taken a couple of tutorials and read through most of the Pro GIT Book and I am a convert. The more I learn the less I can imagine life without GIT. JIRA Even just announced the End of Life for SVN support inside their hosted JIRA. It's gonna be GIT for me from now on.

Removing bluedragon from one iis7 website

Tue, 13 Nov 2012 05:42:00 -0600

I am working on a site that was using NewAtlanta bluedragon. I don't know that much about bluedragon but I simply wanted to create another site in IIS and NOT use bluedragon. I set up the site, installed railo using this great document by Matt Woodward that explains railo and tomcat installation and it worked when testing with just a simple hello world example in index.cfm. So I uploaded my little fw/1 app and it broke. I got the following error:


Server Error in '/' Application.Object reference not set to an instance of an object.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
   com.naryx.tagfusion.cfm.engine.cfComponentData.buildPolymorphList() +354
   com.naryx.tagfusion.cfm.engine.cfSession.onRequestStart(cfFile requestFile) +311
   NewAtlanta.BlueDragon.CfmModule.OnBeginRequest(Object sender, EventArgs e) +599
   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +79
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +269

From that error message I could tell that bluedragon was still intercepting calls. I looked all over IIS and searched for everything. Posted to the railo group and the fw1 group and also posted to the New Atlanta support forums. Many people were very helpful. After several weeks of trying to find a solution here and there I finally stumbled on to it. I had tried removing the newatlanta entries from 'Handler Mappings' in the IIS7 site that I was using. That didn't work. Finally, I removed the bluedragon reference in the 'Modules' section of the website I was trying to configure and that immediately fixed it. The attached image highlights the line that needed to be removed.

Resolving Conflicts in Egit

Thu, 25 Oct 2012 10:37:00 -0600

I like GIT. It really is cool, especially the related resources on sites such as github and bitbucket. But it also drives me absolutely crazy at times. One of those times is when I have a conflict to resolve. I never can figure it out and I have to have one of my Developers come in and explain/show it to me. This time I did it myself from this resource: http://wiki.eclipse.org/EGit/User_Guide#Resolving_a_merge_conflict. I still don't totally understand it but I wanted this in writing so next time I am that much closer to figuring it out all the way. I use coldfusion builder in eclipse with eGit for my git repositories.

Parsing Phone number in Sql Server Removing non numeric characters

Thu, 11 Oct 2012 22:43:00 -0600

I inherited a system that has a few quirks. Great system but some of the stuff is hard to deal with. I was asked to make the phone number search work better. They are having a hard time with it cause you have to enter the phone number exactly like it is in the field. i.e. (444) 843-2234 and if you don't include everything it doesn't return any results. Not very convenient. What we wanted was to be able to type any portion of that phone number, including just a few digits, i.e. 444843 and have the matching records come up. So I need to do two things. Remove any non numeric characters from the inputted value and search the database field which had non numeric characters. The solution, and it seems to work, even though it feels totally hackish the following: To clean up the inputted value I use: varphone = rereplace(form.phoneNumber,"[^[:digit:]]","","ALL") To search the database with that value I used:


WHERE SUBSTRING(phonenumber,2,3) + SUBSTRING(phonenumber,7,3) +  SUBSTRING(phonenumber,11,4) like '%#varphone#%'

Now this assumes a consistent phone format like this '(444) 843-2234' which is the case in our database. That saved a lot of reworking code and it works like a charm.

Resetting Wordpress password

Sat, 06 Oct 2012 08:58:00 -0600

I recently inherited an old WordPress site that nobody had used for over a year. So nobody could remember their username and password. I needed the admin password to be able to go in and make the adjustments that they had asked me to make. I have access to the database on the back end and all the files on the server via ftp so I started searching for solutions. I found this page that seems to be the goto place for resetting a wordpress password. I needed to reset the admin one. All the info seems to be here: Resetting Your Password I tried several of those inlcuding FTP and the Emergency Password Reset Script. This site is using Microsoft SQL Server so some of the others didn't apply. I had tried simply adding my own email to the admin user account in the database but that didn't work. I got a mail error. Then, searching google, I found this page: Is it possible to calculate MD5 hash directly in T-SQL language? The solution was found on that page where there is the following SQL statement:


UPDATE T_WHATEVER_YOUR_TABLE_NAME_IS 
    SET PREFIX_Hash = LOWER(SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', LOWER('yournewpassword'))), 3, 32) )

This worked and created the password and I was able to login.

Missing Commit error using egit in coldfusion builder

Fri, 28 Sep 2012 10:28:00 -0600

I am using egit inside coldfusion builder which Is built on eclipse. We switched to Git a few months back and honestly it's not as easy as SVN was. We never had problems with SVN and my devs are almost begging to go back to SVN. Sometimes I want give in and go back (although I have several reasons I want to stay with git). Just now was one of those times. I was getting the following error:


Exception caught during execution of merge command. org.eclipse.jgit.errors.MissingObjectException: Missing commit a1846c1a954c270a0f03fe7e9f5d1da5693e3469
Exception caught during execution of merge command. org.eclipse.jgit.errors.MissingObjectException: Missing commit a1846c1a954c270a0f03fe7e9f5d1da5693e3469

Looking around on the internet I see this is a common occurrence, but one article I came across suggested just dropping into native git, which to me is git bash. I did that and ran 'git pull' and it worked. Frustration averted for now.

CFSCRIPT loop over a query and a big mistake

Fri, 20 Apr 2012 09:45:00 -0600

I have to look this one up every time. You can use cfscript to loop over a query by doing the following:


for (var i=1; i <= qry.recordcount;i++) {
				
}

OR

for (var i=1; i <= qry.recordcount;i = i+1) {

}

You notice a slight difference between the part that increments. The top one has an 'i++' the bottom has 'i+1'. I am pretty sure these do the same thing but later version sof coldfusion allowed you to use the double plus sign ++ to increment by 1. I got stuck half way between and had i=i++. That put my loop into eternal running mode and I had to keep stopping the application to make the thing quit running. I am embarrassed how long this stumped me. So don't do that.

More about varchar(max)

Thu, 08 Mar 2012 10:31:00 -0600

I am trying to understand the varchar(max) data type. I cam across a really old article but it had the exact answers I was looking for. Specifically when do I go from using varchar with some number i.e. varchar(700) to varchar(max) From This blog post:

The size range for VARCHAR is now 1 to 8,000 or MAX. Note there is nothing between 8,000 and MAX.

VARCHAR(MAX) is more than a replacement from TEXT. It works just as well with short strings as well as long ones. Therefore, if there is any possibility that you'll exceed 8,000 bytes then use VARCHAR(MAX).

The maximum size for a VARCHAR(MAX) is 2^31 - 1 bytes / characters. I hope, even though this article was from 2006 that this stuff is all still relevant.

Note templates in infusionsoft

Wed, 07 Mar 2012 09:27:00 -0600

This isn't something new to me but I am ashamed I don't use it more. Infusionsoft has what they call "Note Templates". That's kind of a funny name and doesn't really do justice to what you can do with note templates. It's actually like a macro, or just a way to kick of a whole bunch of repetitive tasks. There are lots of different uses for such great functionality but the other day I just wanted to do one thing. I was monitoring our support forums because we had made a change in the delivery of our product which required our customers to login to retreive their newsletter. We had many requests for their account information. I set up a note template that emailed them their account information and added a note to their account. This isn't very difficult, but it does take several minutes to select the email template and create a note. So I created a note template that did both of those things. Now I just click on the note template box and start typing the first few letters in the note template name, which shortly pops up. Tabbing out of the field selects the top one and another tab puts focus on the add note button. Press enter and poof email sent and note created. However long it took before it only takes seconds now. You can attach any number of actions to a note template. I shouldn't procrastinate creating note templates and I should review the work flow of my staff and save them oodles of time by creating key note templates for them. Not only saves them time but reduces entry errors.

Microsoft SQL Server to remove text datatype in future release

Fri, 02 Mar 2012 14:36:00 -0600

I was browsing google searching for information on data types as I was creating a new table in my db. Just wanted to make sure I was doing things right and I came across this snippet notifying of the following:

Important ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. Fixed and variable-length data types for storing large non-Unicode and Unicode string and binary data. Unicode data uses the UNICODE UCS-2 character set.

So in this particular table I did use varchar(MAX) instead of text. Didn't even know there was varchar(max) or I forgot.

cfmail using Gmail SMTP

Fri, 20 Jan 2012 10:25:00 -0600

I am trying to switch over to a fax service through vocalocity from our own fax server. Either way I send an email to the fax server to actually send a fax. But with this new one I only have on box and so emails have to come from the assigned email account. Since I have some faxes that are sent out automatically (as part of our newsletter subscription) I need to be able to let the server handle the whole process including logging in to the email account to send the faxes. Enter cfmail. I knew I could do it but hadn't played with it for a while. First google search I found exactly what I needed from Jamie Crug: cfmail using Gmail SMTP. I couldn't get TLS to work but I only tried once and since SSL worked I stuck with that.

ABBYY FineReader vs Acrobat Pro

Fri, 20 Jan 2012 07:36:00 -0600

I have a project I am working on. For this project I need searchable pdf documents. Since not all of the pdf's that I gather are searchable, many are image only, I was looking for an OCR solution. I use evernote frequently and it's ability to OCR image only pdf documents is awesome. I just upload them and then I search and all the text inside those docs is searchable and I feel like it does a great job. I whish I could upload these image only pdf's to evernote and then download a searchable pdf. But I can't. I do have an option when I right click to "Download searchable pdf" but it is only the text, doesn't have the image in front. After some research I think I found that evernote uses ABBYY technology for their ocr engine. So I went to their site at http://www.abbyy.com/ and downloaded their trial copy and eventually bought their pro version. I also have acrobat pro 9.4 and it has ocr built in. ABBYY was $169. I honestly can't tell you how much acrobat pro is cause I have been upgrading since version 5 or 6. Wow, just went to look and it is $449. I have been getting the upgrades for ~$200. Researching this I just noticed that acrobat.com has a free pdf converter and some monthly plans to do simple stuff like convert docs to pdf. Anyway, I ran some tests between ABBYY and Acrobat. Both have good OCR and can optimize the pdf's which saves a lot of space when your done. However, ABBYY did recognize some words that acrobat didn't. I just tested on one document but easily found 2 words that ABBY caught and Acrobat missed. Acrobat default optimizing was smaller than ABBYY but the quality was much less. I adjusted Acrobat Optimizing one click of the slider (didn't seem to want to let me slide it wherever, only stopped at certain intervals) and the resulting file was bigger, 116k but the quality was only a hair better than the default ABBYY (78k). ABBYY optimizes automatically. Acrobat you run the OCR then have to run the optimizing seperate (but it is easy). Not sure if there are settings you can customize for that or not. If you are looking for a good OCR tool and don't need Acrobat for much else (it is very powerful and does alot of stuff) then ABBYY is the better OCR and cheaper choice.