some issues with formatted sql statements

General Discussion
1

Hi,

im trying out the formatted sql statements as a way to manage my database since i already have written sql scripts.

and im running into some trouble getting them to run.


so heres my liquibase.properties file

  2. username: test
  3. password: test
  4. driver: oracle.jdbc.OracleDriver
  5. classpath: ojdbc5.jar
  6. url: jdbc:oracle:thin:@db02:1521:test1
  7. promptForNonLocalDatabase: false

my changelog file 

  2. <databaseChangeLog
  3.     xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  4.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  5.     xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
  6.     xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
  7.     http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
and heres the formatted sql files 
  2. [root@zhangvm liquibase]# cd database/test/
  3. [root@zhangvm test]# ls
  4. ta.sql  test4.sql  test.sql
  5. [root@zhangvm test]# pwd
  6. /root/liquibase/database/test
  7. [root@zhangvm test]# cat *
  8. --liquibase formatted sql

  10. --changeset ta2.sql:1

  12. CREATE TABLE TEST4(
  13.         ID INT,
  14.         NAME VARCHAR2(20)
  15. );
  16. --liquibase formatted sql


  19. --changeset test4.sql:1

  21. CREATE TABLE TEST4(
  22.         ID INT,
  23.         NAME VARCHAR2(20)
  24. );
  25. --liquibase formatted sql

  27. --changeset test.sql:1

  29. CREATE TABLE TEST1(
  30.         ID INT,
  31.         NAME VARCHAR2(20)
  32. );
  33. [root@zhangvm test]# 

the problem here is that when i run the script with 

java -jar liquibase --changeLogFile=change.xml update

the output tells me its sucessful but no table is created


also no rows are added into DATABASECHANGELOG table


is it something i did wrong?


2

You shouldn’t need to keep repeating the --liquibase formatted sql line, just have it at the beginning of each file.


Also, the format of the --changeset command is : not :. That shouldn’t cause it to not work, though.



Does it work to not use but just use for each .sql file?


3

also,

with formatted sql


while running scripts that i have to create sequences 

heres a example 

  2. --liquibase formatted sql


  5. --changeset _sequences_test:1
  6. define seq_name=test_sequence
  7. define seq_minvalue=1
  8. define seq_maxvalue=4294967295
  9. define seq_increment=1
  10. define seq_start=1

  12. WHENEVER SQLERROR CONTINUE;
  13. column next_value new_value seq_start noprint;
  14. SELECT &&seq_name..nextval next_value FROM dual;
  15. DROP SEQUENCE &&seq_name;

  17. WHENEVER SQLERROR EXIT FAILURE;
  18. CREATE SEQUENCE &&seq_name
  19.   MINVALUE &&seq_minvalue
  20.   MAXVALUE &&seq_maxvalue
  21.   START WITH &&seq_start
  22.   INCREMENT BY &&seq_increment
  23.   CYCLE
  24.   ;
i get a bunch of invalid SQL errors

with the define on top, the WHENEVER SQLERROR CONTINUE/EXIT FAILURE

and substitution.

am i just doing it wrong?

4

i found the error


it turns out if the author contain any special characters(?) it will refuse to read the changelog

if i use include it still doesnt work


the reason i had  filename as author is because i have hundreds of files and if i have my script set the author to me i might run into collisions with names 


i ended up changing the ‘.’ to _ and it worked fine



Hoever i seem to found more things…


for instance i have some scripts that look like

  1. create table tableone ( i number(20), str varchar2(20))
  2. /
it gives me a error i think because of the /

same thing seems to happen with ’ ; ’ on a new line, but semi colon works well if its put on the same line as a command



5

didnt know these where SQL*plus specific commands 


never mind