Concatenating variable in SQL syntax

Question

I have the following:

public void getProceduredName(long fileId, String proceduredName ){

    final String sql = "call " + proceduredName + " (:fileId)";

    SqlParameterSource namedParameters = new MapSqlParameterSource("fileId", fileId);

    try {
        namedParameterJdbcTemplate.update(sql, namedParameters);
    } catch (Exception e){
        ...     
      }
}

So once it reads that line and try to use it on namedParameterJdbcTemplate, it says: bad SQL grammar [call procedureName (?)]; nested exception is java.sql.SQLException: ORA-06576: not a valid function or procedure name

Now... If I put it this way: final String sql = "call hardCodedName (12345)"; then it works...

How can I fix it so I can pass in the variable and the parameters?

I looked here: Spring JDBC Template for calling Stored Procedures and it does not solve the problem.

What works: final String sql = "call proceduredName (:fileId)";

What I'm trying to do: final String sql = "call " + proceduredName + " (:fileId)";

I feel like when I pass in the variable in the middle of the tring, it stops taking the string as the sql syntax, so it's only taking as the syntax: "call " and " (:fileId)"

How can I make it work with the variable concatenation?

Thanks

EDIT: I'm calling getProceduredName like this: Dao.getProceduredName(fileId, proceduredName); where dao is the interface and daoImp will have the actual implementation of the getProceduredName(). All I'm passing there is the id type Long, and the name of the procedure type String

The concatenation you show here should be no different than hardcoding the function name. Can you show a bit more detail about how you call `abc()`? — Code-Apprentice, Aug 07 '19 at 16:18
I added it at the bottom of the post, where it says EDIT and renamed `abc()` to `getProceduredName()` — Arturo, Aug 07 '19 at 16:25
what are the values of `fieldId` and `proceduredName` that you pass in? Also, check the value of `sql` after the concatenation. Is it the **exact** same as when you hard code the procedure name? — Code-Apprentice, Aug 07 '19 at 16:27
where does the value of `proceduredName` come from? Is this user input of some sort? If so, are you scrubbing the input to avoid injection attacks? More importantly, what happens when you pass in a string literal instead? — Code-Apprentice, Aug 07 '19 at 16:29
The values of the file is a type long: `123` and the proceduredName: `myStoredProcedure1` and yes, I checked and it's exactly the same, I println it and shows the same — Arturo, Aug 07 '19 at 16:30
The only difference between the concatenated and hard-coded versions is what the `proceduredName` variable contains and therefore what `sql` is, so the `proceduredName` variable likely doesn't *exactly* contain `hardCodedName`. To debug, I'd recommend printing out `sql` and `proceduredName` directly, and see if it matches the hard-coded string. — 17slim, Aug 07 '19 at 16:30
What do you get for System.out.println(sql)? I'm guessing it's not what you think it is. — John, Aug 07 '19 at 16:33
Also, a string doesn't have "sql syntax" as you state. A string is a string. If `proceduredName` contains the string `hardCodedName` then `"call " + proceduredName + " (:fileId)";` is exactly equal to `"call hardCodedName (:fileId)";`. — 17slim, Aug 07 '19 at 16:35
The proceduredName is coming from another class that has some splitting method, `protected String[] postProcessActionToTake;` where there could be something like: `call myProcedure123`, then I'm passing the name: `myProcedure123` as the variable `secondWord` to the variable that stores it in the abstract class `this.proceduredName = secondWord` — Arturo, Aug 07 '19 at 16:35
@John actually I put two print statements with both cases, I was missing the database name in front of the procedureName. Thank you all for helping me debug it — Arturo, Aug 07 '19 at 16:52

score 2 · Accepted Answer · answered Aug 07 '19 at 16:53

2

The Syntax on my question is actually correct, I was missing in front of the procedureName the name of the DB, so it would be like this:

final String sql = "call sqlName." + proceduredName + " (:fileId)";

answered Aug 07 '19 at 16:53

Arturo

3,254
2
22
61

1

I can't tell you how often people trip over this gotcha on Oracle. If you have access to the connection(s) before they get handed out to worker processes, you can issue `ALTER SESSION SET CURRENT SCHEMA = YOURSCHEMANAME` in a programmatic way. – Gus Aug 07 '19 at 17:17

Concatenating variable in SQL syntax

1 Answers1