Unrecognized bind variable passed to the bind call

General
1

I have a flow using a template to send an Oracle SQL query
Here is the relevant section

image
image863Ă—294 35.5 KB

The resulting query from the template is created just fine and if I copy/paste it in oracle SQL Developer it executes fine. So no issues there.
However, the query never get executed because I get this error:
"Oracle query error: ORA-01036: unrecognized bind variable Student PIDM passed to the bind call
Help: ORA-01036 - Database Error Messages"
BTW I'm using node-red-contrib-oracledb-2.

This is the mustache template.

INSERT INTO SHRDGIH ( 
    SHRDGIH_PIDM,
    SHRDGIH_DGMR_SEQ_NO,
    SHRDGIH_HONR_CODE,
    SHRDGIH_ACTIVITY_DATE,
    SHRDGIH_TRANSC_PRT_IND,
    SHRDGIH_COMMENCE_PRT_IND,
    SHRDGIH_USER_ID,
    SHRDGIH_DATA_ORIGIN 
) VALUES (
    {{payload.Student PIDM}},
    {{payload.Degree Sequence}},
    '{{payload.Institutional Honors}}',
    SYSDATE,
    '{{payload.Transcript Indicator}}',
    '{{payload.Commencement Report}}',
    USER,
    'GradAutomation'
)

Any ideas to help solve the issue would be appreciated.

2

I am not going to be able to help with Oracle, but first check what is coming out of the template to make sure it looks correct.

3

Hi Colin,
Thanks for your reply. As I mentioned in my post the template result is OK. If I copy/paste the result into the database if works.
The issue is not the created result. It seems like something else is expecting me to declare things differently, perhaps.
I've been banging my head for an entire day but cannot find why I get that error.

4

I suspect the issues are here: spaces in the property names.

5

It seems highly unlikely the output of that template would produce what you expect due to the space in the mustache replacements {{ }}

Please add a debug to the output of the template node BEFORE and AFTER and show us what you get for both debug nodes.

6

Just to note, the template node does not care about spaces in property names
e.g.

chrome_screenshot_22 Aug 2024 18_54_49 BST
chrome_screenshot_22 Aug 2024 18_54_49 BST1920Ă—315 48.4 KB

template

This is the payload["id var"]: {{payload.id var}}
7

@bakman2, Steve-Mcl,

Thanks for your reply.
Here is the result of the template:
image

As you can see the data is correctly formatted. I can paste it into the db and it works.

The before/after template debug returns:

image

They are identical as far as I can see.

8

@E1cid,

I concur. I do not think spaces matter because I have another query (update) in the flow and it works fine.
Is only in this insert query that is not happy.

9

This seems strange as msg.query does not have a variable name like that after it is created with the template node. This would suggest that msg.payload is being parsed as a bind var in the oracle node, try deleting or moving msg.payload prior to the oracle node

1 Like
10

Who knew! (I rarely use template node and even rarer do I have spaces in property names!)

As this looks like an Oracle message AND it states your variable by name **"Student PIDM" I suspect it is due to the payload containing these fields BUT you dont use them in the query

Try setting payload to an empty object or null.

PS, although I dont see it documented, i believe there is a way to use bound variables and avoid SQL Injection hacks. At a guess, you would match the values to properties in the payload.

something like:

msg.payload = {
   name: "bill o'connell",
   age: 34
}

msg.query = "INSERT INTO Users (name, age) VALUES (:name, :age)"

return msg

Note how I didnt have to escape quotes in the names or provide quotes in the values field!

1 Like
11

E1cid,

Given the structure of my code (here it is for your reference),

image
image1066Ă—447 46.1 KB

I'm afraid I do not understand your suggestion " try deleting or moving msg.payload prior to the oracle node" because as it stands it is before the oracle node.
Interestingly, though, if I change the message property from msg.query to msg.payload the error it gives me is different:

image

Notice the reference to parameter 2?

12

Steve-Mcl,

Let me explore your idea for a bit. I'm kinda new to NodeRed. :slight_smile:

13

add a change node prior to the oracle and after the template node.
and
set msg.payload to {}
or
delete msg.payload
or
move mag.payload to msg.whatever

[edit] What node are you actually using, can you provide a link.

14

Hi Steve-Mcl,

OK. Assuming I understood the "spirit" of your post, this is what I did.:

image
image387Ă—593 8.7 KB

It returns the very same error as before. :frowning:

15

This WILL be because of spaces in the field names.

Change your payload object to use names like StudentPIDM then ONLY put that as the value e.g. :StudentPIDM

example.

msg.payload = {
   StudentPIDM: msg.payload['Student PIDM'],
   etc:  msg.payload['etc field'],
   etc:  msg.payload['etc field'],
   etc:  msg.payload['etc field'],
   etc:  msg.payload['etc field'],
   etc:  msg.payload['etc field'],
   etc:  msg.payload['etc field'],
   etc:  msg.payload['etc field']
}

msg.query = `INSERT INTO XXXXX (
    SHRDGIH_PIDM,
    SHRDGIH_DGMR_SEQ_NO,
    SHRDGIH_HONR_CODE,
    SHRDGIH_ACTIVITY_DATE,
    SHRDGIH_TRANSC_PRT_IND,
    SHRDGIH_COMMENCE_PRT_IND,
    SHRDGIH_USER_ID,
    SHRDGIH_DATA_ORIGIN )
VALUES (
    :StudentPIDM,
    :etc,
    :etc,
    :etc,
    :etc,
    :etc,
    :etc
)`

return msg

NOTE

Please post CODE and TEXT as text (not screenshots) - that way, I can offer you complete code suggestions (I aint gonna re-write what you put in a screenshot!)

16

Hey Steve,
Changing the name legend from 'Student PIDM' to 'StudentPIDM' may be a bit of a problem because I get the data from the client who in turn uses a piece of software to create the data.

Let me explore if I can do some manipulation of the data on my end to alter the variable names.

Note: Sure, I'll be happy to use formatted code instead of images.

17

You're missing the point.

The sample code I wrote above does the remaining of properties to remove spaces.

It only changes the payload going into the oracle node so that the field names match the bindings. Nothing else changes. The database fields stay the same, the supplied data stays the same. the only thing these data values will be used for is passing values to the database.

18

@Steve-Mcl,

Oh! Of course ... I did not read the code carefully.
I'll give it a try for sure. Brilliant!

19

@Steve-Mcl
Good Day.
I implemented your code idea.

msg.payload = {
    StudentPIDM : msg.payload['Student PIDM'],
    DegreeSequence : msg.payload['Degree Sequence'],
    InstitutionalHonors : msg.payload['Institutional Honours'],
    TranscriptIndicator : msg.payload['Transcript Indicator'],
    CommencementReport : msg.payload['Commencement Report']
}

INSERT INTO SHRDGIH ( 
    SHRDGIH_PIDM,
    SHRDGIH_DGMR_SEQ_NO,
    SHRDGIH_HONR_CODE,
    SHRDGIH_ACTIVITY_DATE,
    SHRDGIH_TRANSC_PRT_IND,
    SHRDGIH_COMMENCE_PRT_IND,
    SHRDGIH_USER_ID,
    SHRDGIH_DATA_ORIGIN)
VALUES(
    :StudentPIDM,
    :DegreeSequence,
    :InstitutionalHonors,
    SYSDATE,
    :TranscriptIndicator,
    :CommencementReport,
    USER,
    'GradAutomation'
)

return msg;

Oddly enough I get the very same error:

Oracle query error: ORA-01036: unrecognized bind variable Student PIDM passed to the bind call
I'm beginning to wonder if there is some sort of corruption in the files. I mean, I no longer have a bind variable "Student PIDM" but StudentPIDM.

20

Somewhere, somehow you are STILL sending that to the DB.

At a guess it is one of the multiple lines you have going into the SQL node

image

I realise the above will be out of date now but if you dont share your flows I have no way of knowing.