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

Posted At : August 2, 2007 8:35 AM | Posted By : Trond Ulseth
Related Categories: SQL, Eclipse

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:

<target name="sql.runAll" depends="sql.getLogin">
<sql driver="${sql.jdbcClasspath}" url="${sql.jdbcURL}" userid="uid" password="pw">
<classpath>
<pathelement path="${sql.jdbcPath}" />
</classpath>
CREATE DATABASE idlmedia02_${var.port}
</sql>
</target>

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.

Comments (5) | Print | Send | del.icio.us | Linking Blogs

My take on CF/SQL pagination

Posted At : April 26, 2006 9:14 AM | Posted By : Trond Ulseth
Related Categories: SQL, ColdFusion

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:

<cffunction access="remote" name="getNews"> <cfargument default="5" type="numeric" name="maxrows"> <cfargument default="0" type="numeric" name="startrow">

<cfquery datasource="#application.dsn#" name="getNews"> 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
</cfquery>

<cfreturn getNews>
</cffunction>

Good SQL advice from Mark Kruger

Posted At : October 27, 2005 10:41 PM | Posted By : Trond Ulseth
Related Categories: SQL

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

Another SQL gotcha

Posted At : October 13, 2005 9:22 AM | Posted By : Trond Ulseth
Related Categories: SQL

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