Bug 38245

This was cause in the logging of the file name; the file resource is null.
Somehow  SQL is broken in Ant1.7, and we dont (yet) have the regression tests to
check it.

<sql> is fundamentally broken. It doesnt need a file or depend on nested
transactions; text or src inline is sufficient.

  <target name="hsql-command" depends="ready-to-compile,init-hsqldb">
    <sql driver="${jdbc.driver}"
         classpathref="compile.classpath"
         userid="sa"
         password=""
         url="${jdbc.url}">
      ;
    </sql>
  </target>

Steve, didn't you fix the NPE?  Should we change the heading of this bug?

Correct. Marked as the current problem, we have no SQL tests.

Out of not-so-idle curiousity, what type of tests are you looking for?  I see
several issues and or levels of testing that might be done.

1) The SQL task is tightly coupled to the JDBC task, using Mocks to test for
very basic capability may be an option.

2) "Real" JDBC instances require some sort of DB on the other end - what is
available in the Ant/GUMP testbeds?  Derby?  MySql?

3)  Would this be a "good time" to allow for Platform-specific "decorators" for
the SQL task?  (Factory would be in JDBC, based on rdbms/rdbmsversion.  This
also allows for a "test" decorator to be inserted for verification/pre/post
conditions)

I guess, in general, I'm looking for what type/level of testing and against what
environments is feasible.

-HSQLDB builds in gump and is the easiest to test against. 

-Right now we just need to test that we can make SQL commands and transactions
from inline text and files.

-Test the property expansion stuff I just added

-see what happens when an invalid command is issued (ie does it fail)

I don't have any opinion on SQL decorators; I have only just started using the
task. For testing, I'd point you at dbunit of course.

We may want a specific JDBC datatype that can be reused; containing all the jdbc
options, multiple sql tasks can then share it.

-steve

(In reply to comment #6)
> -HSQLDB builds in gump and is the easiest to test against. 
OK, unfortunately another learning curve for me...
> -Right now we just need to test that we can make SQL commands and transactions
> from inline text and files.
Understand this one.
> -Test the property expansion stuff I just added
OK, I need to look at this. 
> -see what happens when an invalid command is issued (ie does it fail)
Well, this depends on the "continue on error" setting (I forget the attribute at
the moment) doesn't it?
> I don't have any opinion on SQL decorators; I have only just started using the
> task. For testing, I'd point you at dbunit of course.
I can discuss this with you offline;  For Oracle, there are several posted
modifications of the SQL task that allow for turning on "dbms_output" prior to
executing the command and retrieving its "output" after the command has executed
- seems to be almost tailor made for a SQL task "decorator" that is instantiated
when Oracle is specified.
> We may want a specific JDBC datatype that can be reused; containing all the jdbc
> options, multiple sql tasks can then share it.
I'll need more explanation here - is this a test fixture you're referring to? 

Ken

My idea for a JDBC datype would be


<sql:jdbc  id="jdbc.connection"
   driver="hsqldb"   //some well known options or a classname
   url="jdbc:hsqldb:file:${tmp.dir}/database"
   >
  <property name="jdbc.timeout" value="5" />
  <propertyset prefix="jdbc.mysql" />
</sql:jdbc />

<sql:sql jdbcid="jdbc.connection">
 CREATE TABLE customers ...
</sql:sql>

//and something I just made up:

<sql:query jdbcid="jdbc.connection" property="select.response">
 <query>
  SELECT UNIQUE c FROM customers WHERE c.id=?1
 <query>
  <param number="1" value="${custid}" />
</sql:query>

Excellent, Steve!  This addresses one of my other itches at the moment, being
able to connect to Oracle as "sysoper" or "sysdba".  You have to use connection
parameters to do so and I've been unable to puzzle out how to do so with the
current JDBC code.

Great!  When will you have it ready? ;^)

It'll be ready as soon as you've written it :)

Pasting in Ken's todo list for the testss 
from the mailing list

Tests for "onError", "delimiter", "delimterType", "keepFormat" on either files
or embedded text are where I'd focus, I think, because they *are* platform
specific.  IIRC the history of this task correctly, all of those attributes were
added to support loading Oracle PL/SQL procedures and SQL/PLUS scripts.

"onError" is really subjective, depending on what you're executing - a table
drop before recreating it is standard procedure, but the "table or view does not
exist" *is* an error

Testing aside for the moment, this is one of the reasons that I think the
"decorator" idea has merit.  Let the JDBC class create the DB specific
decorator, or a generic pass through if no db is specified.  BWC is maintained
if the addition of the "decorator" is driven from YAA (yet another attribute).

Here's my suggestion:

0.  Convince my boss and my wife that this is really beneficial for the world at
large and is worth the investment of some work and leisure time. ;^)

1.  Develop base tests for current behavior using what ever is readily available
on Gump (sounds like HSQL and Derby), using DBUnit for basic CRUD testing
verification.  I think we should assume a schema, not including schema script
generation in the base tests of the SQL task.

2.  Refactor JDBC/SQL to allow for db specific decorators:
    * Extract the java interface to the SQL task.
    * Rename the existing SQL task to CoreSql or BaseSql, implementing the
interface.
    * Edit JDBC to invoke via I/F
    * Retest.

3.  Create a "pass-through" decorator, 100% delegation of all methods to the
CoreSql code.
    * Refactor SQL to use it.
    * Retest.

4.  Create HSQL and or Derby decorators.  Unfortunately, I don't know what I'd
put in these - my particular itch is around the Oracle specifics.

5.  Open the door for some "community" development/testing of the commercial
platforms.

*** Bug 19921 has been marked as a duplicate of this bug. ***