CODEWITHSUNDEEP
javaoracle-databasejdbcspring-jdbc
Arturo
Arturo

Reputation: 4180

Concatenating variable in SQL syntax

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

Upvotes: 1

Views: 441

Answers (1)

Arturo
Arturo

Reputation: 4180

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)";

Upvotes: 2

Related Questions