-
Notifications
You must be signed in to change notification settings - Fork 464
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
How to bind a multidimensional array #1637
Comments
Because you're using the tagged-template syntax, the library is trying to automatically parameterise your query, hence the error about As this fixture is trusted, you can skip parametirisation of the query by calling the function "traditionally": import sql from 'mssql';
const fixtures = [
{ id: 1, name: 'Children Bicycles' },
{ id: 2, name: 'Comfort Bicycles' },
{ id: 3, name: 'Cruisers Bicycles' },
{ id: 4, name: 'Cyclocross Bicycles' },
{ id: 5, name: 'Electric Bikes' },
{ id: 6, name: 'Mountain Bikes' },
{ id: 7, name: 'Road Bikes' },
];
const pool = await sql.connect({ /* options */})
const transaction = pool.transaction();
try {
await transaction.begin();
const request = transaction.request();
await request.batch`SET IDENTITY_INSERT production.categories ON`;
- await request.batch`MERGE INTO production.categories AS target
+ await request.batch(`MERGE INTO production.categories AS target
USING (
- VALUES ${fixtures.map((category) => [category.id, category.name])}
+ VALUES ${fixtures.map((category) => `(${category.id}, '${category.name}')`).join(',\n')}
) AS source (id, name)
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET name = source.name
WHEN NOT MATCHED THEN
- INSERT (id, name) VALUES (source.id, source.name);`;
+ INSERT (id, name) VALUES (source.id, source.name);`);
await request.batch`SET IDENTITY_INSERT production.categories OFF`;
} catch (error) {
console.error(error);
await transaction.rollback();
} finally {
await pool.close();
} The library doesn't have any auto-parameterisation of arrays into values like that because the library is not a query builder and doesn't understand the context of the array being passed in (so it doesn't know to convert it to tl;dr - your "built" query approach is correct. |
@dhensby Is there a way to nest to use SQL fragments (like slonik does)? so you can tell the library not to escape an already escaped string By example await request.batch`MERGE INTO production.categories AS target
USING (
VALUES ${fixtures.map((category) => sql`(${category.id}, '${category.name}')`).join(',\n')}
) AS source (id, name)
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET name = source.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (source.id, source.name);`; |
No, there isn't. |
I am trying to load some fixtures to be used in my integration and end-to-end tests
I've tried this to reset the value of the fixtures
I've also tried with this other code, by converting the array into a string
Expected behaviour:
I expect to run the following query
Actual behaviour:
Error when an array is passed
Error when the array is converted to string
The only way I've found so far it's to build the query first and the pass it to the library
Configuration:
docker-compose.yaml
.env
database-config.ts
Software versions
The text was updated successfully, but these errors were encountered: