Yeah, that was the problem. Thanks a lot, I am done with all my custom sql
statements.
Thanks and Regards,
On Fri, Feb 27, 2009 at 9:35 AM, Voxland, Nathan <
[email protected]> wrote:
> 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:[email protected]]
> *Sent:* Friday, February 27, 2009 8:30 AM
> *To:* [email protected]
> *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 <
> [email protected]> 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:[email protected]]
> *Sent:* Thursday, February 26, 2009 5:07 PM
> *To:* [email protected]
> *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-14jvm\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\openclinica3.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-14jvm\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\openclinica3.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
> [email protected]
> 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
> [email protected]
> 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
[email protected]
https://lists.sourceforge.net/lists/listinfo/liquibase-user
