Search

Subscribe

Enter your email address to subscribe to this blog.

Recent Comments

Intro to Spring Security Core for Grails
Eric Pierce said: Thanks for these screencasts, Dan! You made it crazy simple to get up and running w/security core h... [More]

Removing duplicates from an array of objects
Arvind said: Great tip, can't thank you enough for this. [More]

Intro to Spring Security Core for Grails
Santosh said: Thanks much for putting up these screencasts. As the others here I'm a beginner and I've been having... [More]

Grails Spring Security Plugin - Logout postOnly setting
eriihine said: I still had some issues with this one. It seems that the href link is always generating a GET method... [More]

Intro to Spring Security Core for Grails
Dan Vega said: Just a heads up but I decided to write up a quick post on your question just in case it trips up any... [More]

SQL Server: Execute SQL Statement Shortcut

This is going to be pretty obvious to anyone who uses SQL Server on a regular basis but I am not one of those people. In almost every other SQL editor that I use you can place a semi colon after your statement and hit cntrl+enter to execute the query. In SQL Server I always end up trying that only to add a new line to my statement. Now you could hit the execute button but I really dislike the mouse.

Anyways, this whole post was to remind myself that there is a keyboard shortcut for this and to be precise there are 3 shortcuts for this. You can use cntrl+e, alt+x or F5. This will execute the selected query or if nothing is selected it will run the entire query window.

 
 

Create SQL Insert statements from a spreadsheet

I know this is probably old news to most but I was helping a friend out yesterday who didn't know this little trick so I thought I would share it. While some of you may have access to production databases its pretty common that these servers are guarded by a DBA. If I get a huge spread sheet of data I can't really import the data I need to send the sql statements to the dba and the script is run against the production database.

In this example I just got a spread sheet of 5 users that need to be imported into our users table.



Now for 5 users this is not a big deal but what about 100 or even a 1000 users. There is actually an easy way to create your insert statements using excel. First we will mark our D column as SQL. Next place click on the cell D1. What we are going to do is write a sql statement that will grab data from the columns a,b and c. First we write our normal insert statement but for the values we can evaluate the data in a cell using the following formula. The & is just used for concatenating.

Then we can use that same formula for every row in our sheet. Simply copy and then past that formula all the way down for as many records as you have and you will end up with something like this.

While this is great you should see the issue. The user id field is fine but we have no single quotes around our strings, but we can fix that using the concatenate function. Now our insert statements look a lot better. Again, not the coolest thing in the world but it really helps out nicely in this spot.

 
 

ORDER BY NULL Dates First

I came across a little problem today that I would like to share with you. I have an application that keeps track of emails sent to customers. My list method will return a query of emails sent. At first I was ordering them by DateSent DESC so that I would be able to view the emails from the last 1 sent. I quickly realized that I have emails that are drafts that have not yet been sent. I would like these emails to show up before any of the sent emails, so how do you do it.

view plain print about
1<cfquery name="getNotifications" datasource="#variables.dsn#">
2SELECT subject, datesent
3FROM notifications
4ORDER BY ISNULL(dateSent,GETDATE()) DESC
5</cfquery>

The ISNULL replaces NULL with the specified replacement value. I can use todays date because no emails will be sent from the future.

 
 

Comments In SQL

It seems that alot of people dont realize that you can place comments in SQL queries just like you can in html and cfml. Here is a quick example of single and multi line comments. I would like to see more developers using comments in their sql code just as you use them in your day to day coding.
view plain print about
1/*
2 Query: getUsers()
3 Description: Just a quick list of students that are active or pending
4 Author: Daniel Vega
5*/

6SELECT fname,lname,email
7FROM users
8/*active clients */
9WHERE status = 'active'
10/* or pending */
11OR status = 'pending'
 
 

SQL What do I do here?

So I ran into a problem last night. I had a bunch of old data that I needed to add a quick search to. One of the fields was a due date. We wanted to be able to search by due date. The main problem here is that all of the dates in the database where datetime types with a timestamp, probably from a Now() function.So the following code would not work and rightfully so.

view plain print about
1select duedate
2from table
3where duedate = 07/25/2006

The code above was comparing 2006-07-25 17:08:41.147 to 07/25/2006. So even though the customer was supplying a date that existed it would never return the record. I finally came up with a quick solution but I was wondering If I could get some feedback from everyone as to what the best approach to this would be. I ended up using the sql convert function. I may write more about this later but here is my code. The convert function will allow us for comparison to temporarily convert a field to the type and expression of our choice.

view plain print about
1Syntax for CONVERT:
2CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
view plain print about
1select duedate
2from table
3where CONVERT(varchar,duedate,101) = '07/25/2006'
 
 

More Entries