0

I'm trying to create the constraint with regexp using knex on the oracle database. Knex creates the constraint on oracle column but it replaces "?" with placeholder like ":1", ":2". I tried to replace the question mark (according to documentation) with "\?". It doesn't work for me on oracle db. Here is an example of oracle column constratint

 knex.raw('ALTER TABLE atest ADD CONSTRAINT C_TEST CHECK (REGEXP_LIKE(COL_A, \'^((([A]{0,4})WW)|(W{0,2}))\\?([B]{5}WW)+((([C]{5})W\\?))$\'))');
Rafał Felczuk
  • 1
  • 1
  • give a sample pattern for this regexp_like – psaraj12 Apr 22 '20 at 13:15
  • Example of pattern ^((([A]{0,4})WW)|(W{0,2}))\\?([B]{5}WW)+((([C]{5})W\\?))$' But any other pattern contains ? will not work in the constraint when the constraint was added by knex migration script – Rafał Felczuk Apr 23 '20 at 10:04

1 Answers1

0

Congratulations! Looks like you found a bug in knex (https://runkit.com/embed/qlo6re5ixn4s). Strange that tests has not caught this one. Please open a bugreport in knex's github.

Though if database accepts value bindings in that position of query you could try this:

knex.raw(
  'ALTER TABLE atest ADD CONSTRAINT C_TEST CHECK (REGEXP_LIKE(COL_A, ?))', 
  ['^((([A]{0,4})WW)|(W{0,2}))?([B]{5}WW)+((([C]{5})W?))$']
);
Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • 1
    I already report the issue https://github.com/knex/knex/issues/3823 – Rafał Felczuk Apr 24 '20 at 11:00
  • 1
    @Mikeal your line will throw an error, there is no ability to use bindings in DDL :D I already checked this :) – Rafał Felczuk Apr 24 '20 at 11:01
  • Then only way (which is not officially supported by knex) that comes in my mind is to manually acquire database driver's connection and use it directly to run the query... like this: https://stackoverflow.com/questions/59949825/knex-js-force-session-reuse-for-4-following-queries/59966900#59966900 except that passing connection to knex use it directly to execute queries. – Mikael Lepistö Apr 25 '20 at 10:20