Script number > 999 fails due to 'comma' in number for ChangLog insert statement

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create a deploy script that has a number greater then > 999  (say
01000_create_whatever_table.sql)
2. Run dbdeploy ant task making sure to output to a file (apply as a
separate step)  Note, the error does not occur if you apply with dbdeploy
with the same ant task.
3. Apply using <sql> ant task the outputted script

What is the expected output?  Expected should apply.

What do you see instead?   The change number has a "comma" in it when
inserting into the ChangeLog table causing this statement to fail

      [sql] Failed to execute:    INSERT INTO changelog (change_number,
complete_dt, applied_by, description) VALUES (1,000, CURRENT_TIMESTAMP,
USER(), '1000.sql')

What version of the product are you using? On what operating system?  3.0M2
on Windows XP 32-bit/Java 6

Please provide any additional information below.

Reading change scripts from directory
D:\Camel\di-0.0.1-SNAPSHOT\di-domain\src\test\resources\testdb\deltas...
Changes currently applied to database:
  (none)
Scripts available:
  1..3, 1000
To be applied:
  1..3, 1000
Generating undo scripts...
      [sql] Executing file:
D:\Camel\di-0.0.1-SNAPSHOT\di-domain\target\testdb-apply.sql
      [sql] Failed to execute:    INSERT INTO changelog (change_number,
complete_dt, applied_by, description) VALUES (1,000, CURRENT_TIMESTAMP,
USER(), '1000.sql')
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] Error executing ant tasks

Embedded error: The following error occurred while executing this line:
D:\Camel\di-0.0.1-SNAPSHOT\di-domain\src\test\resources\testdb\build.xml:67: 
java.sql.SQLException:
Column count does no
t match in statement [   INSERT INTO changelog (change_number, complete_dt,
applied_by, description) VALUES (1,000, CURR
ENT_TIMESTAMP, USER(),]

    <target name="update-database-and-apply-as-separate-step"
description="generate a sql upgrade script">

        <!-- use dbdeploy to generate the change script -->
        <dbdeploy driver="${db.driver}" url="${db.url}"
                  userid="${db.user}"
                  password="${db.password}"
                  dir="src/test/resources/testdb/deltas"
                  outputfile="target/testdb-apply.sql"
                  undoOutputfile="target/testdb-undo.sql"
                  dbms="${db.type}"
                />

        <!-- now apply the changescript to the database -->
        <sql driver="${db.driver}" url="${db.url}"
             userid="sa" password="" classpathref="libs.classpath">
            <fileset file="target/testdb-apply.sql"/>
        </sql>

    </target>

-- START CHANGE SCRIPT #1000: 1000.sql

-- Numbering convention:  The first three represent the schema_version that
-- corresponds to the tagged production schema_version.  The next 3
-- digits are there to order the scripts for the dbdeploy testdb creation.
create table ipdsis.schema_version (
    version_id          numeric(5)       not null,
    subversion_id       varchar(10),
    apply_date          timestamp        not null,
    rollback_date       timestamp,
    status              varchar(20)     not null,
    create_date         timestamp        not null,
    modified_date       timestamp        not null
);

INSERT INTO changelog (change_number, complete_dt, applied_by, description)
 VALUES (1,000, CURRENT_TIMESTAMP, USER(), '1000.sql');

COMMIT;

-- END CHANGE SCRIPT #1000: 1000.sql

Original issue reported on code.google.com by michael....@gmail.com on 5 Nov 2009 at 11:19

[GoogleCodeExporter]

Fixed in r135.

The error is caused by freemarker automatically inserting comma separators, as 
described at http://freemarker.org/docs/app_faq.html#faq_number_grouping.

Fixed in the way suggested by adding the ?c format string.

If you want to workaround without building a version of dbdeploy off trunk, see 
the 
bottom of GeneratingAndCustomisingScripts - download the fixed versions from 
trunk 
(e.g. http://code.google.com/p/dbdeploy/source/browse/trunk/dbdeploy-
core/src/main/resources/ora_apply.ftl), put them in a local directory and 
specify 
that directory on the command line. 

Original comment by gtack...@googlemail.com on 8 Nov 2009 at 3:08

• Changed state: Done
• Added labels: Component-Core, Milestone-Release3.0

[GoogleCodeExporter]

Issue 48 has been merged into this issue.

Original comment by gtack...@googlemail.com on 23 Feb 2011 at 5:38

[GoogleCodeExporter]

Issue 49 has been merged into this issue.

Original comment by gtack...@googlemail.com on 23 Feb 2011 at 5:40

[GoogleCodeExporter]

Original comment by gtack...@googlemail.com on 16 Mar 2011 at 11:43

• Added labels: Milestone-Release3.0M3
• Removed labels: Milestone-Release3.0