batchErrors with data truncation and data validation
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 2.4k
- Forks
- 1.1k
- PR merge metrics
- No merged PRs in 30d
Description
1. Describe your new request in detail
I can't really find documentation on what I want to achieve with this scenario so it could be an improvement or otherwise there is an answer please guide me:
Normal scenario:
I need to insert thousands of rows in a table, there is no doubt that executeMany() works great together with batchErrors, since if I want to insert 50k rows it is possible that there are 5, 10 or rows are errors (foreign key for example), I was reading about this in:
Desired scenario:
But if I define my bindDefs, my autoCommit and my batchErrors as true and between the data of 50k rows there is one row with columns that could throw a data truncation error (because the length of the data exceeds the length value defined in Oracle) the batchErrors feature is not useful and the program throws an exception (this exception details the error and the row with the problem) but the remaining 49,999 rows are not inserted, the same happens in typing, if I send a STRING instead of a NUMBER type, this forces me to use logic in my application to validate the length of STRING types, remove those that are wrong from the data before using executeMany().
// dataBatches.length is 49.000
try {
dataBatches.unshift({
C_ONE: 'AAAAAAAAAAAAAAA', // length is > that 5
C_TWO: 'TEST',
C_THREE: 'TEST'
});
// now dataBatches.length is 50.000
console.time('savedb');
result = await context.executeMany(
`
INSERT INTO TEST_TBL
(
C_ONE,
C_TWO,
C_THREE
)
VALUES
(
:C_ONE,
:C_TWO,
:C_THREE
)
`,
batch as [],
{
autoCommit: true,
batchErrors: true,
bindDefs: {
C_ONE: { type: oracledb.STRING, maxSize: 5 }, // C_ONE length is 5
C_TWO: { type: oracledb.STRING, maxSize: 500 },
C_THREE: { type: oracledb.STRING, maxSize: 10 }
}
}
);
console.timeEnd('savedb');
// I want 'result.rowsAffected' = 49.000 (inserted successfully) and 'result.errors' with the errors info about the bad row
console.log(result);
} catch (error) {
// this code throw an exception with 0 rows affected
}
finally {
// close db
}
Why?
This occurs if I define bindDefs with the type and length (for performance), if bindDefs is not defined, then the behavior is what I want but the time to insert the information is affected a bit (in my case I went from 7 seconds to 13 seconds).
I think oracle-db should achieve the scenario I want even if bindDefs is defined, I don't know what the community thinks about it.
2. Give supporting information about tools and operating systems. Give relevant product version numbers
Oracledb v5.1.0
Node.js v14.16.1
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the executeMany() entry point and the documented batchErrors and bindDefs behavior, then reproduce the supplied INSERT scenario with one oversized value and one type mismatch. Done means determining whether bind validation can produce result.errors while allowing valid rows to insert, or documenting the supported behavior and constraints if it cannot.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, node.js
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100