The trouble is that you have < and > tags inside your text block with
confuses the XML parser. You need to wrap it in a CDATA section:
"<!"[CDATA[ at the beginning, "]]>" at the end. You'll also need to add
the splitStatements="false" attribute to the <sql> tag.
Nathan
From: Pradnya Gawade [mailto:pradnya.gawa...@gmail.com]
Sent: Friday, February 27, 2009 8:30 AM
To: liquibase-user@lists.sourceforge.net
Subject: Re: [Liquibase-user] Liquibase: Custon sql with <sql> tag
I am still not successful in implementing my create function custom sql
:-(
Following is my custom sql tag:
<changeSet author="pgawade (generated)" id="1235684743487-225">
<sql>
CREATE FUNCTION event_crf_trigger() RETURNS "trigger"
AS 'DECLARE
pk INTEGER;
entity_name_value TEXT;
BEGIN
IF (TG_OP = ''UPDATE'') THEN
IF(OLD.status_id <> NEW.status_id) THEN
/*---------------*/
/*--Event CRF status changed*/
SELECT INTO pk
NEXTVAL(''audit_log_event_audit_id_seq'');
SELECT INTO entity_name_value ''Status'';
IF(OLD.status_id = ''1'' AND NEW.status_id = ''2'') THEN
IF (NEW.electronic_signature_status) THEN
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''14'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value, OLD.status_id,
NEW.status_id, NEW.event_crf_id);
ELSE
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''8'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value, OLD.status_id,
NEW.status_id, NEW.event_crf_id);
END IF;
ELSIF (OLD.status_id = ''1'' AND NEW.status_id = ''4'')
THEN
IF (NEW.electronic_signature_status) THEN
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''15'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value, OLD.status_id,
NEW.status_id, NEW.event_crf_id);
ELSE
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''10'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value, OLD.status_id,
NEW.status_id, NEW.event_crf_id);
END IF;
ELSIF (OLD.status_id = ''4'' AND NEW.status_id = ''2'')
THEN
IF (NEW.electronic_signature_status) THEN
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''16'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value, OLD.status_id,
NEW.status_id, NEW.event_crf_id);
ELSE
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''11'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value, OLD.status_id,
NEW.status_id, NEW.event_crf_id);
END IF;
END IF;
/*---------------*/
END IF;
IF(OLD.date_interviewed <> NEW.date_interviewed) THEN
/*---------------*/
/*--Event CRF date interviewed*/
SELECT INTO pk
NEXTVAL(''audit_log_event_audit_id_seq'');
SELECT INTO entity_name_value ''Date interviewed'';
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''9'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value,
OLD.date_interviewed, NEW.date_interviewed, NEW.event_crf_id);
/*---------------*/
END IF;
IF((OLD.interviewer_name <> NEW.interviewer_name) AND
(OLD.interviewer_name <> '''')) THEN ---------------
--Event CRF interviewer name
SELECT INTO pk
NEXTVAL(''audit_log_event_audit_id_seq'');
SELECT INTO entity_name_value ''Interviewer Name'';
INSERT INTO audit_log_event(audit_id,
audit_log_event_type_id, audit_date, user_id, audit_table, entity_id,
entity_name, old_value, new_value, event_crf_id)
VALUES (pk, ''9'', now(), NEW.update_id,
''event_crf'', NEW.event_crf_id, entity_name_value,
OLD.interviewer_name, NEW.interviewer_name, NEW.event_crf_id);
/*---------------*/
END IF;
RETURN NULL; /*--return values ignored for ''after''
triggers*/
END IF;
END;
'
LANGUAGE plpgsql;
ALTER FUNCTION public.event_crf_trigger() OWNER TO clinica;
</sql>
</changeSet>
I get following error on console:
C:\Program Files\PostgreSQL\8.2\bin>java -jar
C:\Pradnya\Softwares\liquibase\liq
uibase-1.9.1-14jvm\liquibase-1.9.1-14jvm\liquibase-SNAPSHOT-14jvm.jar
--classpat
h=C:\Projects\chin\chinapp\war\WEB-INF\lib\postgresql-8.2-508.jdbc3.jar
--change
LogFile=C:\Pradnya\liquiBase\openclinica_2.5_snapshot\openclinica3.xml
--url="jd
bc:postgresql://localhost:5432/test_openclinica_2.5_snapshot"
--username=clinica
--password=clinica --logLevel=finest migrate
Feb 27, 2009 9:25:05 AM liquibase.database.template.JdbcTemplate comment
INFO: Create Database Lock Table
Feb 27, 2009 9:25:05 AM liquibase.database.template.JdbcTemplate comment
INFO: Lock Database
Feb 27, 2009 9:25:05 AM liquibase.lock.LockHandler acquireLock
INFO: Successfully acquired change log lock
Feb 27, 2009 9:25:05 AM liquibase.database.template.JdbcTemplate comment
INFO: Create Database Change Log Table
Feb 27, 2009 9:25:05 AM liquibase.database.AbstractDatabase
checkDatabaseChangeL
ogTable
INFO: Creating database history table with name: databasechangelog
Feb 27, 2009 9:25:06 AM liquibase.parser.xml.XMLChangeLogParser$1
fatalError
SEVERE: The content of elements must consist of well-formed character
data or ma
rkup.
Feb 27, 2009 9:25:06 AM liquibase.database.template.JdbcTemplate comment
INFO: Release Database Lock
Feb 27, 2009 9:25:06 AM liquibase.lock.LockHandler releaseLock
INFO: Successfully released change log lock
Migration Failed: Error parsing line 2315 column 23 of
C:/Pradnya/liquiBase/open
clinica_2.5_snapshot/openclinica3.xml: The content of elements must
consist of w
ell-formed character data or markup.. For more information, use the
--logLevel
flag)
Feb 27, 2009 9:25:06 AM liquibase.commandline.Main main
SEVERE: Error parsing line 2315 column 23 of
C:/Pradnya/liquiBase/openclinica_2.
5_snapshot/openclinica3.xml: The content of elements must consist of
well-formed
character data or markup.
liquibase.exception.ChangeLogParseException: Error parsing line 2315
column 23 o
f C:/Pradnya/liquiBase/openclinica_2.5_snapshot/openclinica3.xml: The
content of
elements must consist of well-formed character data or markup.
at
liquibase.parser.xml.XMLChangeLogParser.parse(XMLChangeLogParser.java
:78)
at
liquibase.parser.ChangeLogParser.parse(ChangeLogParser.java:28)
at liquibase.Liquibase.update(Liquibase.java:105)
at liquibase.commandline.Main.doMigration(Main.java:646)
at liquibase.commandline.Main.main(Main.java:95)
I don't know what is wrong in xml syntax. Please help. Please provide
links to some sample <sql> tags if you have.
Thanks and Regards,
Pradnya
On Thu, Feb 26, 2009 at 6:15 PM, Voxland, Nathan
<nvoxl...@intelligentinsites.com> wrote:
I think you are forgetting to wrap your <sql> tag in a <changeSet> tag.
You can support multi-line sql by using the splitStatements="false"
attribute
Nathan
From: Pradnya Gawade [mailto:pradnya.gawa...@gmail.com]
Sent: Thursday, February 26, 2009 5:07 PM
To: liquibase-user@lists.sourceforge.net
Subject: [Liquibase-user] Liquibase: Custon sql with <sql> tag
Hi,
I have a query about implementing Custon sql with <sql> tag in the
liquibase changeLog xml file.
I have to add some CREATE FUNCTION sql code in the <sql> tag. It has
multilined sql statements and I am not successful in excecuting the
same. I am not very clear with the line and statement separators inside
<sql> tag
I get following exception:
C:\Program Files\PostgreSQL\8.2\bin>java -jar
C:\Pradnya\Softwares\liquibase\liquibase-1.9.1-14jvm\liquibase-1.9.1-14j
vm\liquibase-SNAPSHOT-14jvm.jar
--classpath=C:\Projects\chin\chinapp\war\WEB-INF\lib\postgresql-8.2-508.
jdbc3.jar
--changeLogFile=C:\Pradnya\liquiBase\openclinica_2.5_snapshot\openclinic
a3.xml
--url="jdbc:postgresql://localhost:5432/test_openclinica_2.5_snapshot"
--username=clinica --password=clinica migrate
Migration Failed: Error parsing line 2307 column 10 of
C:/Pradnya/liquiBase/openclinica_2.5_snapshot/openclinica3.xml:
cvc-complex-type.2.4.a: Invalid content was found starting with element
'sql'. One of
{"http://www.liquibase.org/xml/ns/dbchangelog/1.9":changeSet,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":include,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":includeAll}' is
expected.
It was a multilined create function in it this time. When I tried with a
simple insert statement as follows,
<sql>insert into status (status_id, name, description) values (00001,
'test', 'test')</sql>
It gave me similar error as follows (logLevel is set to 'finest' this
time):
C:\Program Files\PostgreSQL\8.2\bin>java -jar
C:\Pradnya\Softwares\liquibase\liquibase-1.9.1-14jvm\liquibase-1.9.1-14j
vm\liquibase-SNAPSHOT-14jvm.jar
--classpath=C:\Projects\chin\chinapp\war\WEB-INF\lib\postgresql-8.2-508.
jdbc3.jar
--changeLogFile=C:\Pradnya\liquiBase\openclinica_2.5_snapshot\openclinic
a3.xml
--url="jdbc:postgresql://localhost:5432/test_openclinica_2.5_snapshot"
--username=clinica --password=clinica --logLevel=finest migrate
Feb 26, 2009 5:42:55 PM liquibase.database.template.JdbcTemplate comment
INFO: Create Database Lock Table
Feb 26, 2009 5:42:55 PM liquibase.database.template.JdbcTemplate comment
INFO: Lock Database
Feb 26, 2009 5:42:55 PM liquibase.lock.LockHandler acquireLock
INFO: Successfully acquired change log lock
Feb 26, 2009 5:42:55 PM liquibase.database.template.JdbcTemplate comment
INFO: Create Database Change Log Table
Feb 26, 2009 5:42:55 PM liquibase.database.AbstractDatabase
checkDatabaseChangeL
ogTable
INFO: Creating database history table with name: databasechangelog
Feb 26, 2009 5:42:57 PM liquibase.parser.xml.XMLChangeLogParser$1 error
SEVERE: cvc-complex-type.2.4.a: Invalid content was found starting with
element'sql'. One of
'{"http://www.liquibase.org/xml/ns/dbchangelog/1.9":changeSet,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":include,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":includeAll}' is
expected.
Feb 26, 2009 5:42:57 PM liquibase.database.template.JdbcTemplate comment
INFO: Release Database Lock
Feb 26, 2009 5:42:57 PM liquibase.lock.LockHandler releaseLock
INFO: Successfully released change log lock
Migration Failed: Error parsing line 2307 column 10 of
C:/Pradnya/liquiBase/openclinica_2.5_snapshot/openclinica3.xml:
cvc-complex-type.2.4.a: Invalid content w
as found starting with element 'sql'. One of
'{"http://www.liquibase.org/xml/ns/dbchangelog/1.9":changeSet,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":in
clude, "http://www.liquibase.org/xml/ns/dbchangelog/1.9":includeAll}' is
expected.. For more information, use the --logLevel flag)
Feb 26, 2009 5:42:57 PM liquibase.commandline.Main main
SEVERE: Error parsing line 2307 column 10 of
C:/Pradnya/liquiBase/openclinica_2.5_snapshot/openclinica3.xml:
cvc-complex-type.2.4.a: Invalid content was found s
tarting with element 'sql'. One of
'{"http://www.liquibase.org/xml/ns/dbchangelog/1.9":changeSet,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":include, "ht
tp://www.liquibase.org/xml/ns/dbchangelog/1.9":includeAll}' is
expected.liquibase.exception.ChangeLogParseException: Error parsing line
2307 column 10 o
f C:/Pradnya/liquiBase/openclinica_2.5_snapshot/openclinica3.xml:
cvc-complex-type.2.4.a: Invalid content was found starting with element
'sql'. One of '{"http:
//www.liquibase.org/xml/ns/dbchangelog/1.9":changeSet,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":include,
"http://www.liquibase.org/xml/ns/dbchangelog/1.9":includeAll}' is
expected.
at
liquibase.parser.xml.XMLChangeLogParser.parse(XMLChangeLogParser.java
:78)
at
liquibase.parser.ChangeLogParser.parse(ChangeLogParser.java:28)
at liquibase.Liquibase.update(Liquibase.java:105)
at liquibase.commandline.Main.doMigration(Main.java:646)
at liquibase.commandline.Main.main(Main.java:95)
Could some one pls explain what could be wrong with this. Also providing
a sample of multilined sql statements and multiple statements in the
same xml file would help me understand the rules for muliti lined
stataments and multiple statements in the same file.
Thanks in advance.
- Pradnya
------------------------------------------------------------------------
------
Open Source Business Conference (OSBC), March 24-25, 2009, San
Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the
Enterprise
-Strategies to boost innovation and cut costs with open source
participation
-Receive a $600 discount off the registration fee with the source code:
SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Liquibase-user mailing list
Liquibase-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user
------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Liquibase-user mailing list
Liquibase-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user
