Using parameterized SQL with LIKE in WHERE clause (Pervasive SQL)
parameters, pervasive, sql, sql-like
Solution
Why don't you add a wildcard to the value you're assigning to the parameter ?
So, this means, your query looks like
SELECT * FROM dog WHERE dog.name LIKE ?
And then, you assign the value '%bob%' to this parameter, for instance. I see you say that you've tried this as well, but it doesn't work, which is very strange. Can you show the code of what you've done then ?
I also see that you put quotes around the parameter in one of your examples. This doesn't sound like a good idea, since then the parameter won't be recognized as a parameter anymore. At that point, it is just a string.
Problem
I have a Pervasive database that I connect to using C++. All my queries so far is parameterized, i.e `"SELECT USER.NAME FROM USER WHERE USER.ID = ?"`, and that works fine. But in a search query I use LIKE in the WHERE clause, and then it seems I can't use parameters and wild chars (%). My query looks something like this `"SELECT * FROM DOG WHERE DOG.NAME LIKE '%?%'"` and since there are two ' around the parameter ?-mark, this fails, it searched for dogs with a ?-mark in their name. In SQL Server this would probably be solved by concatenating the string like `"SELECT * FROM DOG WHERE DOG.NAME LIKE '%' + ? + '%'"`, but this is invalid syntax in Pervasive (see bottom of this page: http://ww1.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-55.html). I've also tried to add the %-signs to the parameter itself, but that doesn't seem to work either. Does anyone know a solution to this? EDIT1: Some C++ Code example: ``` CString sqlCommand = "SELECT * FROM DOG WHERE DOG.NAME LIKE ?;"; m_pAdoCommand->CommandText = _bstr_t(sqlCommand); m_pAdoCommand->Parameters->Append( m_pAdoCommand->CreateParameter("p0", adVarChar, adParamInput, 25, _bstr_t("'%bob%'")) ); m_pAdoRecordset = m_pAdoCommand->Execute(NULL,NULL,adCmdText); ``` (`m_pAdoCommand` is a `_CommandPtr`, and `m_pAdoRecordset` is a `_RecordsetPtr`. I've changed the name of the SQL Tables in this example so that they make sens here.) The above code will return a row with a dog that has the name `'%bob%'`, but I would like it to return all dogs that have bob in their name.