Skip to content

Commit 788a2ae

Browse files
alumnialumni
committed
fix(oracle): pass duplicated parameters correctly to the client when executing a query
1 parent ead4f98 commit 788a2ae

File tree

7 files changed

+140
-90
lines changed

7 files changed

+140
-90
lines changed

‎ormconfig.sample.json‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,12 @@
4444
"database": "temp/better-sqlite3db.db",
4545
"logging": false
4646
},
47+
{
48+
"skip": true,
49+
"name": "sqljs",
50+
"type": "sqljs",
51+
"logging": false
52+
},
4753
{
4854
"skip": false,
4955
"name": "postgres",

‎src/driver/oracle/OracleDriver.ts‎

Lines changed: 1 addition & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -386,18 +386,13 @@ export class OracleDriver implements Driver {
386386
if (!parameters || !Object.keys(parameters).length)
387387
return [sql, escapedParameters]
388388

389-
const parameterIndexMap = new Map<string, number>()
390389
sql = sql.replace(
391390
/:(\.\.\.)?([A-Za-z0-9_.]+)/g,
392391
(full, isArray: string, key: string): string => {
393392
if (!parameters.hasOwnProperty(key)) {
394393
return full
395394
}
396395

397-
if (parameterIndexMap.has(key)) {
398-
return this.parametersPrefix + parameterIndexMap.get(key)
399-
}
400-
401396
const value: any = parameters[key]
402397

403398
if (isArray) {
@@ -421,7 +416,7 @@ export class OracleDriver implements Driver {
421416
}
422417

423418
escapedParameters.push(value)
424-
parameterIndexMap.set(key, escapedParameters.length)
419+
425420
return this.createParameter(key, escapedParameters.length - 1)
426421
},
427422
) // todo: make replace only in value statements, otherwise problems

‎test/functional/query-builder/parameters/entity/Person.ts‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
import { Column, Entity, PrimaryGeneratedColumn } from "../../../../../src"
2+
3+
@Entity()
4+
export class Person {
5+
@PrimaryGeneratedColumn()
6+
id: number
7+
8+
@Column()
9+
firstName: string
10+
11+
@Column()
12+
lastName: string
13+
}

‎test/functional/query-builder/parameters/reused-parameters.ts‎

Lines changed: 116 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,116 @@
1+
import { expect } from "chai"
2+
import "reflect-metadata"
3+
import { DataSource } from "../../../../src/data-source/DataSource"
4+
import {
5+
closeTestingConnections,
6+
createTestingConnections,
7+
reloadTestingDatabases,
8+
} from "../../../utils/test-utils"
9+
import { Person } from "./entity/Person"
10+
import { DriverUtils } from "../../../../src/driver/DriverUtils"
11+
12+
describe("query builder > parameters > reused parameters", () => {
13+
let dataSources: DataSource[]
14+
before(
15+
async () =>
16+
(dataSources = await createTestingConnections({
17+
entities: [Person],
18+
schemaCreate: true,
19+
dropSchema: true,
20+
})),
21+
)
22+
23+
beforeEach(() => reloadTestingDatabases(dataSources))
24+
after(() => closeTestingConnections(dataSources))
25+
26+
it("should generate a valid query", () =>
27+
Promise.all(
28+
dataSources.map(async (dataSource) => {
29+
const personRepository = dataSource.getRepository(Person)
30+
await personRepository.save([
31+
{ firstName: "Jane", lastName: "Smith" },
32+
{ firstName: "Johanna", lastName: "Schmidt" },
33+
{ firstName: "Ioana", lastName: "Fieraru" },
34+
{ firstName: "Giovanna", lastName: "Ferrari" },
35+
])
36+
37+
const sqlSubstring =
38+
dataSource.driver.options.type === "oracle"
39+
? "SUBSTR"
40+
: "SUBSTRING"
41+
const firstNameTrimmed = `${sqlSubstring}(${dataSource.driver.escape(
42+
"firstName",
43+
)}, 1, :charCount)`
44+
const lastNameTrimmed = `${sqlSubstring}(${dataSource.driver.escape(
45+
"lastName",
46+
)}, 1, :charCount)`
47+
48+
const queryBuilder = personRepository
49+
.createQueryBuilder()
50+
.select(firstNameTrimmed, "firstName")
51+
.addSelect(lastNameTrimmed, "lastName")
52+
.setParameters({ charCount: 5 })
53+
54+
const [query, parameters] = queryBuilder.getQueryAndParameters()
55+
56+
if (DriverUtils.isPostgresFamily(dataSource.driver)) {
57+
expect(query).to.equal(
58+
'SELECT SUBSTRING("firstName", 1, $1) AS "firstName", SUBSTRING("lastName", 1, $1) AS "lastName" FROM "person" "Person"',
59+
)
60+
expect(parameters).to.have.length(1)
61+
} else if (DriverUtils.isMySQLFamily(dataSource.driver)) {
62+
expect(query).to.equal(
63+
"SELECT SUBSTRING(`firstName`, 1, ?) AS `firstName`, SUBSTRING(`lastName`, 1, ?) AS `lastName` FROM `person` `Person`",
64+
)
65+
expect(parameters).to.have.length(2)
66+
} else if (DriverUtils.isSQLiteFamily(dataSource.driver)) {
67+
expect(query).to.equal(
68+
'SELECT SUBSTRING("firstName", 1, 5) AS "firstName", SUBSTRING("lastName", 1, 5) AS "lastName" FROM "person" "Person"',
69+
)
70+
expect(parameters).to.have.length(0)
71+
} else if (dataSource.driver.options.type === "spanner") {
72+
expect(query).to.equal(
73+
'SELECT SUBSTRING("firstName", 1, @param0) AS "firstName", SUBSTRING("lastName", 1, @param0) AS "lastName" FROM "person" "Person"',
74+
)
75+
expect(parameters).to.have.length(1)
76+
} else if (dataSource.driver.options.type === "oracle") {
77+
expect(query).to.equal(
78+
'SELECT SUBSTR("firstName", 1, :1) AS "firstName", SUBSTR("lastName", 1, :2) AS "lastName" FROM "person" "Person"',
79+
)
80+
expect(parameters).to.have.length(2)
81+
} else if (dataSource.driver.options.type === "mssql") {
82+
expect(query).to.equal(
83+
'SELECT SUBSTRING("firstName", 1, @0) AS "firstName", SUBSTRING("lastName", 1, @0) AS "lastName" FROM "person" "Person"',
84+
)
85+
expect(parameters).to.have.length(1)
86+
} else {
87+
// e.g.: SAP
88+
expect(query).to.equal(
89+
'SELECT SUBSTRING("firstName", 1, ?) AS "firstName", SUBSTRING("lastName", 1, ?) AS "lastName" FROM "person" "Person"',
90+
)
91+
expect(parameters).to.have.length(2)
92+
}
93+
94+
const statistics = await queryBuilder.getRawMany<unknown>()
95+
96+
expect(statistics).to.deep.equal([
97+
{
98+
firstName: "Jane",
99+
lastName: "Smith",
100+
},
101+
{
102+
firstName: "Johan",
103+
lastName: "Schmi",
104+
},
105+
{
106+
firstName: "Ioana",
107+
lastName: "Fiera",
108+
},
109+
{
110+
firstName: "Giova",
111+
lastName: "Ferra",
112+
},
113+
])
114+
}),
115+
))
116+
})

test/functional/query-builder/parameters/query-builder-parameters.ts renamed to test/functional/query-builder/parameters/sqlite-parameters.ts

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,14 @@
1+
import { expect } from "chai"
12
import "reflect-metadata"
2-
import { Example } from "./entity/Example"
3+
import { DataSource } from "../../../../src"
34
import {
45
closeTestingConnections,
56
createTestingConnections,
67
reloadTestingDatabases,
78
} from "../../../utils/test-utils"
8-
import { expect } from "chai"
9-
import { DataSource } from "../../../../src"
9+
import { Example } from "./entity/Example"
1010

11-
describe("query builder > parameters", () => {
11+
describe("query builder > parameters > sqlite", () => {
1212
let connections: DataSource[]
1313
before(
1414
async () =>

‎test/github-issues/7308/entity/weather.ts‎

Lines changed: 0 additions & 10 deletions
This file was deleted.

‎test/github-issues/7308/issue-7308.ts‎

Lines changed: 0 additions & 70 deletions
This file was deleted.

0 commit comments

Comments
 (0)