How to find Specific Column Names or Types in a mySQL Database

I'm working on a rather extensive CMS admin application, and need to make some adjustments to all of the "sort" fields in the database. Many of the tables have either a 'sort' or 'sort_order' (and in one case, I think I saw 'SortOrder'), and I need hunt them down, creating a list of column names and table names so i can quickly find the columns I want to edit.

I could open each table one by one, and look for those column names... but of course, there's a much better way:


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE lower(column_name) LIKE '%sort%'
AND TABLE_SCHEMA='//my database name here//'

This returns a perfectly formatted query of table names and column names, along with the 'data type' for any column containing 'sort' (or 'Sort'). Now it will be a snap to make my changes to all of the numeric sort fields in the entire database.

The query can be modified to return all sorts of useful info, such as all columns in the entire database:


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='//my database name here//'

or all columns with a specific data type


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='//my database name here//'
AND DATA_TYPE='varchar'

And then, if you wanted to get really lazy, er, creative... you can loop the results of that query, and 'do stuff' to all of the columns automatically with just a few more lines:


alter table #table_Name# modify #column_name# VARCHAR(55) ;

(this would set the size of your varchar field to 55 characters, for example)

Installing SQL Server 2008 Management Studio Express

I don't do much work on MS SQL, but a recent project requires I connect to a remote SQL 2008 database, so I went looking for the new version of the SQL Server Management Studio Express program which has worked so well, and so simply, for SQL 2005 databases.

Simple enough, right? Not quite. It didn't take me long on Google to realize i was far from the only one having trouble installing just the Management Studio, without the server components. Microsoft has managed to make this process incredibly unintuitive, including links and download pages which don't actually point you to what you think you are getting.
( for examples, see the google link above... i'm being really nice in this post by comparison!)

The best compromise i could find was the official MS download page, which offers a 160+mb file containing the full database server system. Blog posts mention compatibility issues (Win7 did throw up some warnings of its own when i started the process, so i backed out and kept looking), incomplete installations and the need to run the installer twice so you can 'add' the Management Studio as a component to an existing installation.

Huh? Exactly.

So, anyway...  for those that might be in the same plight... I have the solution!

Go here: http://www.microsoft.com/web/Downloads/platform.aspx and install the MS 'Web Platform Installer'.  Then, when the installer loads up, find the little 'customize' link under the "Database" section, where you can tick the boxes for the components you want to install. ( see image )

Once i got it going, this was actually a slick little installer.
(So finally I have SQL 2008 Management Studio ready to go.
Now, what was i going to use it for?)

 

How to find Duplicate Entries in a mySQL Database

Here's a quick easy query that will return all records where a given column's value appears more than once.

The trick is the 'group by' phrase, combined with the selection of the 'count(*)' value, which simply counts the records found for each match of 'Name'.

This query will return any records where the same value appears more than one time in the given column (in this case, the 'Name' column), along with the 'Counter' value showing how many times the duplicate appears.


SELECT Name,count(*) as Counter
FROM Table_Name
GROUP BY Name
HAVING Counter >
1

Javascript How To: Add a Number of Days to a Date

I have a form where a user can input a date in the format mm/dd/yyyy, and we need to add an 'expiration date' that is 30 days from the date entered, regardless of calendar month, leap years, or other complex calculations.

Thankfully, javascript already has the 'setDate()' function built in. We just have to parse the date object (a full javascript formatted date string) out of the given form input ( e.g. '11/10/2009').

Easy enough - first we parse out the numeric date/time object, and in this example, write it to the screen.

Then, we add 30 days (the number of days can be changed in the setting for the 'interval' variable - use a negative number to remove days)

Again, this example simply writes the date to the screen. In the current usage, I will use that date value to populate another text input in the same form as the initial date being entered.


<script type="text/javascript">
function setExpDate(formDate){
    // set number of days to add
    var interval = 30;
    var startDate = new Date(Date.parse(formDate));
    document.write('start: ' + startDate);
    var expDate = startDate;
    expDate.setDate(startDate.getDate() + interval);
    document.write('<br>expire: ' + expDate);
};
</script>

Example usage on a form input:



<input type="text" size="10" maxlength="10" id="startDate" name="startDate" onblur="setExpDate(this.value)">   

jQuery Cycle slideshow demo with next/back/paging links

This demo uses some of the advanced features of the jQuery Cycle plugin to create a mixed content slideshow (html/text/images) with a visual navigation bar, and next/back arrows

In this setup, the slideshow advances automatically, until somebody clicks one of the links, which stops the automatic action and allows the user to control the advancement of the slides. The Cycle plugin allows for pausing, resuming or stopping the slideshow with a simple click action.

See the demo (includes css/jquery code for easy reuse) http://www.gowestwebdesign.com/demos/jquery-cycle-gallery-next-back-paging/

Thanks to Mike Alsup for the versatile Cycle plugin: http://www.malsup.com/jquery/cycle/

Feel free to use the code for any purpose, and please drop me a comment or link to check out your work. As always, comments/suggestions/fixes are welcome.

Show any Twitter Feed on your site with ColdFusion and CFFEED

This is so simple, I'll let the comments do the 'splainin:



<!---
start CF Twitter Feed
--->


<!--- SET THE URL: add your username instead of mine --->

<cfset feedurl="http://search.twitter.com/search.atom?q=from%3Agowestweb" />

<!--- CFFEED does all the work --->

<cffeed
source="#feedurl#"
properties="feedmeta"
query="feeditems"
overwrite="true" />


<!--- CFOUTPUT shows the feed like a query --->

<ul class="tweet">
<!--- change maxrows to suit your layout --->
<cfoutput query="feeditems" maxrows="3">
<li>#content#</li>
</cfoutput>
</ul>

<!---
end CF Twitter Feed
--->


<!---
NOTE: in addition to #content#, you can show date and other columns related to each feed item.
Use <cfdump var="#feeditems#"> to see all the available columns in the query
--->



ColdFusion Form Spam Prevention: CF Meetup Notes and Follow-Up

Thanks to all who attended my first CF Meetup presentation.

Info: http://www.meetup.com/coldfusionmeetup/calendar/11596049/?a=nr1p_grp&rv=nr1p

Recording: http://www.meetup.com/coldfusionmeetup/pages/Recordings_of_the_ColdFusion_Meetup-2009/

Demo Page (as seen in the presentation): http://www.gowestwebdesign.com/demos/contact-form/index.cfm

As far as I can tell we had a good turnout, and though talking to a silent connection takes some getting used to, the live-chat comments and interaction during the talk really made it fun (and helped me fill the full time slot with this simple subject!).

I really enjoy meeting other developers and designers, and I hope anybody who attended, had questions or just wants to get more involved in the community will drop me an email, check out our web developer newsgroups (see the link top right of this blog), and of course... be my Facebook and/or Twitter friend.

Thanks also to Charlie Arehart for all of his efforts keeping the CF Meetups going, and growing strong. He was the perfect balance of aggressive and encouraging in getting me to present this topic, and I am very glad he persisted. He's definitely the right guy for the positing of CF Host and quickly removed any concerns or worries I might have had just before we went live.

Now that it is done, I would like to do another! On that note, if there is anything you think I know about, that you'd like to see me share, please do drop me a line.

Several people asked to have a copy of the code used in the presentation. The sample below is directly from the file shown in the demo.



<!--- START PROCESSING --->
<cfif isDefined('form.senderFrom')>

<!--- VALIDATE FIELDS --->

<!--- check email --->
<cfif NOT len(trim(form.senderFrom)) gt 6 or NOT isValid('email',form.senderFrom)>
<cfset request.formError = 'A valid email address must be provided'>

<!--- message --->
<cfelseif NOT len(trim(form.senderMessage))>
<cfset request.formError = 'Be sure to include a message'>

<!--- honeypot --->
<cfelseif len(trim(form.email_address))>
<cfset request.formError = 'Spam!! <br />(Run away! Run away!)'>
</cfif>

<!--- /end VALIDATE FIELDS --->

<!--- CHECK FOR UNWANTED CONTENT--->
<!--- loop all form variables --->
<cfloop index="f" list="#form.fieldnames#">
<!--- set variable for field value --->
<cfset value="#evaluate('form.#f#')#">

<!--- BANNED WORDS --->
<cfset bannedWordsList = "herring,albatross,dragon,grail,lumberjack">

<!--- loop the banned words list and see if we have a match --->
<!--- Check for banned words --->
<cfloop list="#bannedWordsList#" delimiters="," index="w">
<cfif FindNoCase(w,value)>

<cfset request.formerror="<br />Beg your pardon? <br />Your WHAT hurts?">

<cfbreak>
</cfif>
</cfloop>
<!--- / end BANNED WORDS --->

<!--- HTML BLOCK --->
<cfset leftChar = '<' >
<cfset rightChar = '>' >

<!--- look for both characters contained in our content --->
<cfif findNoCase(leftChar, value) AND findNoCase(rightChar, value)>
<cfset request.formerror = "Text only please - no HTML">
<cfbreak>
</cfif>
<!--- / end HTML BLOCK --->

</cfloop>
<!--- / end CHECK FOR UNWANTED CONTENT --->

<fieldset>
    <legend>
        <cfif isDefined('request.formerror') and len(trim(request.formerror))>
        Error!
            <cfelse>
        Thank You
        </cfif>
    </legend>

<!--- SHOW RESPONSE --->
<cfif isDefined('request.formerror') and len(trim(request.formerror))>
<p><strong>ERROR: <cfoutput>#request.formerror#</cfoutput></strong></p>
<p>Go <a href="javascript:history.back()">back</a> and try again</p>

<cfelse>
<p>Thank you. <br /><br />Your message has been sent and we will reply soon!</p>
</cfif>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
</fieldset>

<!--- IF FORM NOT SUBMITTED (show the form) --->
<cfelse>

<fieldset><legend>Sample Contact Form</legend>

<cfform name="contactForm" action="#cgi.SCRIPT_NAME#" method="post">
<div>
<label for="senderName">Name:  </label><cfinput type="text" name="senderName" size="48" value="" required="true" message="Your Name is required">
</div>
<div>
<label for="senderFrom">Email:  </label><cfinput type="text" name="senderFrom" size="48" value="" required="true" validate="email" message="Email Address is required">
</div>
<div>
<label for="senderPhone">Phone:  </label><input type="text" name="senderPhone" size="20" value="">
</div>
<div>
<label for="senderPhone">Your Message:</label><textarea name="senderMessage" cols="30" rows="12" style="width:310px;"></textarea>
</div>
<div style="text-align:center">
<input type="submit" value="Submit">
</div>

<div id="email_wrapper">
<input type="text" name="email_address" value="" size="20">
</div>

</cfform>

</fieldset>

</cfif>
<!--- / end IF FORM SUBMITTED --->

Thanks again to Charlie and everyone else - this has been a very positive and rewarding experience, and I hope to return with more good CF-Stuff to share.

Come to my (first) CF Meetup: Captcha-Free Spam Prevention for ColdFusion Forms

Tired of Spam? (Who isn't?)

No matter how good our filters are, or how careful we may be about making our email addresses publicly accessible, some of the nasty stuff still gets through.

But there is a way to fight back, defending your websites' contact forms and other form-to-email inputs, without making your website visitors jump through extra hoops, solve puzzles or read fuzzy scrambled letters (captcha) just to drop you a note.

To learn more, tune into my ColdFusion Meetup presentation this Thursday, October 15, 2009. (If you haven't yet attended the CF Meetup, this is a good time to start!)

Details here: http://www.meetup.com/coldfusionmeetup/boards/view/viewthread?thread=7841126

The live meetup will be here: http://experts.acrobat.com/cfmeetup/

Recording will be posted here: http://recordings.coldfusionmeetup.com/

See you at Noon Eastern (10am here in Utah) on Thursday, October 15 !

A whole new Outlook

I depend on Outlook 2007 in a number of ways but it has been kludgy, memory-hoggish and recently, a dead weight on anything else that was running at the same time. Granted, I have a massive storage of email and use the heck out of tasks, but still, it should work once things get loaded. Today I got fed up with the freezes and did some investigation.

I am happy to report I found what seems to be a magic tonic, restoring the Outlook I used to like!

The cause was apparently an "Add-In", and the slowness is a common symptom. I won't go into all I read about Outlook Add-ins here, but it is a little subculture all its own.

The cure, for me, was as easy as disabling all Outlook add-ins... once I found where and how (it is not obvious!)

1) To do this in Vista, you have to be running Outlook as an administrator . (right click on Outlook.exe, select 'run as administrator')

2) Once Outlook starts up, go to Tools, then Trust Center

3) In the Trust Center window, select 'Add-Ins' - you'll see a list of Add-Ins, and below that a select box labeled "Manage:"  Select "COM Add-ins" and click "Go", to bring up the window where you can actually Manage your Add-ins. ( They must over-engineer this on purpose. 1 out of 10 for intuitiveness, MS!)

4) In the Add-Ins window, you can uncheck the box for any that you want to disable. I unchecked them all, not seeing a darn thing there I thought I might ever need. ( And surprised at the number of things that allow themselves to 'talk' to Outlook behind the scenes)

5) Save, OK and otherwise Exit your way all the way out, close Outlook and if you like , reboot your computer.

6) Launch Outlook again like always, and marvel at the speed. Seriously, this made a *huge* difference!

 

 

 

 

How to use the CFEclipse Scribble Pad as a fast and easy ColdFusion sandbox

Like most developers I know, I'm always checking functions, testing little changes and trying to find just the right syntax for certain bits and blocks of code. Every project usually ends up with a 'test.cfm' file, which gets written and overwritten with all sorts of little snippets, queries, and other scribbly things, then deleted. To use the test file, I open it like any file in my editor, make changes, then load that page's local url in the browser, refresh, repeat... not bad, it works... but I just discovered something much better!

Thanks to Mark Esher's MX Unit blog post about the CFEclipse Scribble Pad , I've now got a one-click (or one key, F8) instant sandbox to scribble whatever I like, across all projects in my Eclipse workspace. Launching the CFEclipse scribble now (F8) causes 2 things to happen automatically: my 'scribble.cfm' file (stored in a _temp project in my workspace) opens for editing, and the Eclipse browser view (which I seldom use otherwise but is perfect for viewing little bits, cfdumps and snippets) pops up, showing me the rendered output. Very nice.

It isn't just saving the clicks, but saving the thought process. While coding, the distraction of stopping, making a temp file, going to that file in the browser.. every step takes my mind further off of the super-intensive outrageously-important totally-impressive thing I was doing when I decided I needed to scribble in the first place.

Setting it up was easy - just follow the directions : http://blog.mxunit.org/2009/04/timesavers-cfeclipse-scribble-pad.html . This is another perfect example of the powerful, practical tools and timesavers that hide behind a previously-unnoticed menu option or toolbar icon - and though one of the simplest, I have no doubt this will be among my most-used commands when working in Eclipse!

 

Show open workspace name in Eclipse title bar

In Eclipse, I can switch worskpaces by going to File > Switch Workspace ... but it doesn't give any indication of which workspace I have open at the time.... until now!

The -showLocation parameter causes Eclipse to show the current workspace name in the title bar of the application window (as well as the perspective name for the open perspective).

To add this to your Eclipse configuration (in Windows) is very easy:
Just right-click the shortcut you use to open Eclipse, and select 'properties'.
Then, add "-showLocation" (without the quotes) to the end of the "Target" line.

My shortcut target was: C:\ECLIPSE35\eclipse\eclipse.exe 3.5
Now it looks like this: C:\ECLIPSE35\eclipse\eclipse.exe 3.5 -showLocation

More Entries

blogcfc 5.9.1.002 by raymond camden
contact michael evangelista