error: bind message supplies 1 parameters, but prepared statement "" requires 0

user_7121735 0
Viewed 10301

I have a table 'article' with column 'content' .I want to query Postgresql in order to search for a string contained in variable 'temp'.This query works fine-

pool.query("select * from article where upper(content) like upper('%some_value%')");

But when I use placeholder $1 and [temp] in place of some_value , I get the above error - 

pool.query("select * from article where upper(content) LIKE upper('%$1%')",[temp] );

Note - Here $1 is a placeholder and should be replaced by the value in [temp] , but it treats '%$1%' as a string , I guess. Without the quotes ' ' , the LIKE operator doesn't work. I have also tried the query - 

pool.query("select * from article where upper(content) LIKE upper(concat('%',$1,'%'))",[temp] );

to ensure $1 is not treated as a string literal but it gives the error - error: could not determine data type of parameter $1

node.js sql
3 Answers

The easiest way I found to do this is like the following:

// You can remove [0] from character[0] if you want the complete value of character.
  database.query(`
    SELECT * FROM users 
    WHERE LOWER(users.name) LIKE LOWER($1)
    ORDER BY users.id ASC`, 
    ["%" + character[0] + "%"]
  );

// [%${character}%] string literal alternative to the last line in the function call.

There are several things going on here, so let me break each line it down.

  1. SELECT * FROM users

    • This is selecting all the columns associated with table users

  2. WHERE LOWER(users.name) LIKE $1

    • This is filtering out all the results from the first line so that where the name(lowercased) column of the users table is like the parameter $1.

  3. ORDER BY users.id ASC

    • This is optional, but I like to include it because I want the data returned to me to be in ascending order (that is from 0 to infinity, or starting low and going high) based on the users.id or the id column of the users table. A popular alternative for client-side data presentation is users.created_at DESC which shows the latest user (or more than likely an article/post/comment) by its creation date in reverse order so you get the newest content at the top of the array to loop through and display on the client-side.

  4. ["%" + character + "%"]

    • This part is the second argument in the .query method call from the database object (or client if you kept with that name, you can name it what you want, and database to me makes for more a sensical read than "client", but that is just my personal opinion, and it's highly possible that "client" may be the more technically correct term to use). The second argument needs to be an array of values. It takes the place of the parameters inserted in the query string, for example, $1 or ? are examples of parameter placeholders which are filled in with a value in the 2nd argument's array of values. In this case, I used JavaScript's built-in string concatenation to provide a "includes" like pattern, or in plain-broken English, "find me columns that contain a 'this' value" where name(lowercased) is the column and character is the parameter variable value. I am pulling in the parameter value for the character variable from req.params (the URL, so http://localhost:3000/users/startsWith/t), so combining that with % on both ends of the parameter, it returns me all the values that contain the letter t since is the first (and only) character here in the URL.

I know this is a VERY late response, but I wanted to respond with a more thorough answer in case anyone else needed it broken down further.

user_89282830

In my case : My variable was $1, instead of ?1 ... I was customizing my query with @Query

user_61508810
Related