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
