Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL cannot be parsed when using binary type "char (16) for bit data": X'0123' #1089

Closed
pascalgn opened this issue Sep 7, 2015 · 1 comment
Closed

Comments

@pascalgn
Copy link
Contributor

@pascalgn pascalgn commented Sep 7, 2015

When using UUIDs in DB2 (or Derby, they are very similar), the column is defined as

CREATE TABLE my_table (my_uuid CHAR (16) FOR BIT DATA NOT NULL,
CONSTRAINT pk_my_table PRIMARY KEY (my_uuid));

Binary literals for CHAR FOR BIT DATA are written like string literals, but with a leading X:

SELECT * FROM my_table WHERE my_uuid = X'123';

So, when I want to add a new column in a Flyway SQL migration, this syntax is used:

ALTER TABLE my_table ADD my_other_uuid CHAR (16) FOR BIT DATA
NOT NULL DEFAULT X'0000';

Unfortunately, Flyway does not recognize the X' as an opening string quote token, so the migration script cannot be parsed (The exact error in my case is: Syntax error: Encountered ";" at line 2, column 25.).

I found no clear documentation for specifying binary literals, but I found a short summary of the most common syntaxes:
http://stackoverflow.com/questions/9320200/inline-blob-binary-data-types-in-sql-jdbc

Due to this bug, new CHAR FOR BIT DATA columns cannot be added with NOT NULL, because that would require a DEFAULT clause.

@pascalgn pascalgn changed the title SQL cannot be parsed when using binary type "char (16) for bit data": X'123' SQL cannot be parsed when using binary type "char (16) for bit data": X'0123' Sep 7, 2015
axelfontaine added a commit that referenced this issue Oct 3, 2015
@axelfontaine axelfontaine added this to the Flyway 4.0 milestone Oct 3, 2015
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Oct 3, 2015
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Oct 3, 2015

Thank you for reporting this and providing the fix! Merged!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants