How to solve error "Bind variable ? not set"?-CodePudding

I have an SQL query written in Java that should connect to Snowflake database.

I am using jdbc template to connect to Snowflake and this is the string that I am using:

url: jdbc:snowflake://xxxxx.eu-central-1.snowflakecomputing.com/?db=DATABASE_NAME&warehouse=TEST_WAREHOUSE&schema=PUBLIC&role=SYSADMIN

user: username
password: password

Here is query:

public void EPI_GAME_REP_QUERY(String id) {
     long userId = Long.parseLong(id);
      String REP_QUERY = "SELECT \n"  
                "                \"ProductName\",\n"  
                "                \"UserID\",\n"  
                "                \"UserName\",\n"  
                "            FROM \"DATABSE4\".\"RAW_DATA\".\"BETA\"\n"  
                "            WHERE \"UserID\" = ? \n"  
                "            UNION ALL\n"  
                "            SELECT \n"  
                "                \"ProductName\",\n"  
                "                \"UserID\",\n"  
                "                \"UserName\",\n"  
                "            FROM \"DATABASE3\".\"RAW_DATA\".\"BETAC\"\n"  
                "            WHERE \"UserID\" = ? \n"  
                "            UNION ALL\n"  
                "            SELECT  \n"  
                "                \"ProductName\",\n"  
                "                \"UserID\",\n"  
                "                \"UserName\",\n"  
                "            FROM \"DATABASE1\".\"RAW_DATA\".\"ACTIVITY\"\n"  
                "            WHERE \"UserID\" = ? \n"  
                "            UNION ALL\n"  
                "            SELECT  \n"  
                "                \"ProductName\",\n"  
                "                \"StartTime\",\n"  
                "                \"UserID\",\n"  
                "                \"UserName\",\n"  
                "            FROM \"DATABASE1\".\"RAW_DATA\".\"HISTORY\"\n"  
                "            WHERE \"UserID\" = ? \n"  
                "            ORDER BY \"StartTime\" ASC;";

        log.info("Executing query...");

        jdbcTemplate.query(REP_QUERY, rs -> {
        },  userId);
    }

I am passing id as parameter, and because it's String type in code and number type in database I am parsing it to int.

When I run the code, I am getting this error:

nested exception is net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 33 at position 29
Bind variable ? not set.

CodePudding user response：

You SQL request contains several variable to bind (one for each '?'), and you have to provide a value for each one:

jdbcTemplate.query(REP_QUERY, rs -> {},  userId, userId, userId, userId);