Trond Ulseth's Blog - SQL

A couple of "advanced" ANT (in Eclipse) questions

Thu, 02 Aug 2007 08:35:00 -0600

I started with ANT today. I see a huge potential in it. However I have now used the whole day trying to get two tings to work - and simply can't find the answer. I hope some ANT experts will take the time to answer the following questions: 1. Can I create a new Eclipse project with a ANT build? If so, how? 2. I get an error trying to create a database for my project with the following code:






		
CREATE DATABASE idlmedia02_${var.port}

The error: CREATE DATABASE statement not allowed within multi-statement transaction. Is there a way to use CREATE DATABASE statements from within ANT? As always when I ask for help - I would be really thankful to anybody who can shed light on these subjects.

My take on CF/SQL pagination

Wed, 26 Apr 2006 09:14:00 -0600

Raymond Camden has an entry on ColdFusion and Pagination explaining how to do result set pagination with CF code.

In a coment and following up with a post on his blog Rob Gonda sugests that for large recordsets the pagination should be done in the SQL rather than with CF code, utilizing stored procedures.

So Ray's method is easy to use/understand and works with any DB, but is not suitable for large recordsets, and also the code is in the "view" part of your application. Rob's method is good when performance becomes a problem because of large recordsets, but is tied to the db, and requires knowledge of stored procedures.

In all fairnes, I should get moving and learn the more advanced workabouts with SQL, but I've not yet.

So a while ago I was faced with the problem that I needed to deliver paged recordsets from a MSSQL db from a cfc to a Flash UI. So the method I've used earlier (very similar to Ray's) was unsuitable. In MySQL it can easily be done with use of LIMIT and OFFSET, but it was a MSSQL db, and I did not have access (or knowledge) enough to do it with stored procedures (or other zillion lines long SQL examples I found online for solving this).

I ended up with a fairly simple, yet very usable way of doing it. I'm not sure how it would hold with very large recordsets, but in the given situation that was not an issue.

Here's the (pseudo) code:


  

 SELECT TOP #arguments.maxrows# *
FROM news
WHERE objectid NOT IN (
     SELECT TOP #arguments.startrow# objectid
     FROM news
     ORDER BY datetimecreated DESC
)
ORDER BY datetimecreated DESC

Good SQL advice from Mark Kruger

Thu, 27 Oct 2005 22:41:00 -0600

Mark got an interesting article on using wildcards and square brackets in your queries where you use the LIKE expression.

Another SQL gotcha

Thu, 13 Oct 2005 09:22:00 -0600

Yesterday I was working on the sample app for a 'soon to be announced' new framework. I needed to query to get all records with a null value in a numerical field.

Here's what I tried at first:

SELECT *
FROM forum_messages
WHERE msgParentID = NULL

Problem was that it did not return any rows, but also did not throw any error. For a second I beleived that it must be something in the my application code that was wrong, but I threw the code into the SQL Query Analyzer and still got zero rows returned.

Of course the answer was not further away than a quick Google search, and what I found was that I needed to change my SQL to this:

SELECT *
FROM forum_messages
WHERE msgParentID IS NULL

If you are interested in the why of this you can read about it here