MariaDB connector for NodeJs | How to insert value if not exists

Question

I'm trying to run a batch query from a pool. the query itself is simple but I'm having trouble with the NodeJS Connector

the query with the '?' placeholders is

INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
(SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1

what I'm trying to achieve is to check if a value is present before inserting it. the TS code I'm using is something like this:

const INSERT_REGION =
  'INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
(SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1';

const insertRegionParam = [['ITC1', 'ITA', 'EU', 'ITC1'] , ['ITP1', 'ITA', 'EU', 'ITP1'] ]

  private insertRegions$(insertRegionParam: string[][]) {
    return from(this.pool.batch(INSERT_REGION, insertRegionParam)).pipe(
      catchError((err: mdb.SqlError) => throwError(() => err))
    );
  }

I'm getting this error:

R_DUP_FIELDNAME: Duplicate column name '?'
sql: INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
    (SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1
    at /code/dist/resources/regions-sectors/regions-sectors.controller.js:24:22
    at /code/node_modules/rxjs/dist/cjs/internal/operators/catchError.js:13:51
    at OperatorSubscriber._this._error (/code/node_modules/rxjs/dist/cjs/internal/operators/OperatorSubscriber.js:43:21)
    at Subscriber.error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:60:18)
    at Subscriber._error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:84:30)
    at Subscriber.error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:60:18)
    at Observable.init [as _subscribe] (/code/node_modules/rxjs/dist/cjs/internal/observable/throwError.js:8:58)
    at Observable._trySubscribe (/code/node_modules/rxjs/dist/cjs/internal/Observable.js:41:25)
    at /code/node_modules/rxjs/dist/cjs/internal/Observable.js:35:31
    at Object.errorContext (/code/node_modules/rxjs/dist/cjs/internal/util/errorContext.js:22:9)

like if the string has not been parsed

score 1 · Accepted Answer · answered Apr 25 '23 at 02:44

1

You have:

FROM (SELECT ?, ?, ?) AS tmp

When you join or select from a subquery, the "table" thus formed (tmp in this case) has to have unique names for each column, even in contexts where you don't use the columns by name. You can simply give arbitrary names:

FROM (SELECT ? x, ? y, ? z) AS tmp

but it's probably better to use names that reflect the values:

FROM (SELECT ? region_code, ? country_code, ? continent_code) AS tmp

answered Apr 25 '23 at 02:44

ysth

96,171
6
121
214

I've acceppted your answer since it solved the specific issue, but now I'm having another error: `{ text: 'This command is not supported in the prepared statement protocol yet', sql: 'INSERT INTO region (region_code, country_code, continent_code) \n' + 'SELECT * FROM (SELECT ? region_code, ? country_code, ? continent_code) AS tmp \n' + "WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1 - parameters:[['ITC1','ITA','EU',...]", fatal: false, errno: 1295, sqlState: 'HY000', code: 'ER_UNSUPPORTED_PS' }` – ALGDB Apr 25 '23 at 07:34
**ANY IDEA ON HOW I CAN ACHIVE THE SAME GOAL?** – ALGDB Apr 25 '23 at 07:38
What does `select version();` show? – ysth Apr 25 '23 at 08:54
If you have your code just do a simple `insert into region (region_code, country_code, continent_code) values (?,?,?)` does it get the same error? – ysth Apr 25 '23 at 08:58
**DB Version: 10.11.2** no it doesn't but in the question I omitted the fact that the `this.pool.batch()` is part of a transaction ` this.pool.beginTransaction()` – ALGDB Apr 25 '23 at 11:13
no idea why you would get that error, especially just for a more complicated insert. try opening a new question, showing enough of your code that someone who knows node.js can actually try it out. – ysth Apr 25 '23 at 17:58
you can try something like `insert into region (region_code, country_code, continent_code) with params as (select ? region_code, ? country_code, ? continent_code ) select params.* from params left join region using (region_code) where region.region_code is null;` (note only 3 ?, no repetition of region code) but I don't see why that would work any differently – ysth Apr 25 '23 at 18:41
or if region_code is the primary key of the table or has a unique constraint, just do `insert ignore into region (region_code, country_code, continent_code) values (?,?,?)` – ysth Apr 25 '23 at 18:42
Yes I'm actually using the latter, thanks! I'll give a shot to the former solution, thanks again – ALGDB Apr 26 '23 at 16:10
anyway as far as I undertood, the promlem seems to be the fact that I'm doing a transcation, and the ORM doesn't support nested query in this case. Probably your solution will work, I'll let you know – ALGDB Apr 26 '23 at 16:12

MariaDB connector for NodeJs | How to insert value if not exists

1 Answers1