CODEWITHSUNDEEP
sqlnode.jsoracle-databasenode-oracledb
undefined
undefined

Reputation: 2129

Oracle and Nodejs - can't bind parameters in SQL statement

Hi I have the following statement that I execute using node-oracle

await connection.execute(`SELECT * FROM TABLE WHERE NAME LIKE '%And%'`)

But now I want to bind a parameter instead of using a hard coded value

const queryText = 'And';
await connection.execute(`SELECT * FROM TABLE WHERE NAME LIKE '%:queryText%'`, {queryText});

it throws Error: ORA-01036: illegal variable name/number

What is the correct way of binding a parameter here, since the documentation doesn't cover this situation?

Upvotes: 0

Views: 5161

Answers (3)

adR
adR

Reputation: 528

Here is a working example.

let queryText = "John"
let sql = "SELECT * FROM TABLE WHERE NAME LIKE :queryText"
let binds = {queryTarget: {dir: oracledb.BIND_IN, val: queryText, type: oracledb.STRING}}

let result = await connection.execute(sql, binds, options)

Do not add '%' like the other people suggested.

Upvotes: 0

davidm
davidm

Reputation: 1760

Try with the following:

const queryText = 'And';
await connection.execute(

"SELECT * FROM TABLE WHERE NAME LIKE :queryText", 

{

queryText: { dir: oracledb.BIND_IN, val: '%'+ queryText +'%', type: oracledb.STRING }

});

Upvotes: 2

GMB
GMB

Reputation: 222512

Use string concatenation:

SELECT * FROM TABLE WHERE NAME LIKE '%' || :queryText || '%'

Upvotes: 0

Related Questions