Using parameterized SQL with LIKE in a WHERE clause (Pervasive SQL)

Question

I have a Pervasive database with which I connect to C ++. All my queries are still parameterized, i.e. "SELECT USER.NAME FROM USER WHERE USER.ID = ?" and it works great. But in the search query, I use LIKE in the WHERE clause, and it seems like I cannot use parameters and wildcards (%).

My query looks something like this: "SELECT * FROM DOG WHERE DOG.NAME LIKE '%?%'" , And since there are two "around the? -Mark parameter, this fails, he searched for dogs with? -mark in their name. In SQL Server, this will probably be solved by concatenating a string like "SELECT * FROM DOG WHERE DOG.NAME LIKE '%' + ? + '%'" , But this is not valid syntax in Pervasive (see Bottom of this page: http: / /ww1.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-55.html ).

I also tried adding% -signs to the parameter itself, but this does not work either.

Does anyone know about this solution?

EDIT1: 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 _CommandPtr and m_pAdoRecordset is _RecordsetPtr . I changed the name of the SQL tables in this example to make them feel here.)

The above code will return a line with a dog that has the name '%bob%' , but I would like it to return all dogs that have a bob in their name.

Answer 1

Why don't you add a wildcard to the value that you assign to the parameter?

So that means your request looks like

 SELECT * FROM dog WHERE dog.name LIKE ? 

And then, for example, you assign the value% bob% to this value. I see, you say that you tried it too, but it does not work, which is very strange. Can you show the code of what you did then?

I also see that you put quotation marks around the parameter in one of your examples. This does not seem like a good idea, since after that the parameter will no longer be recognized as a parameter. This is just a line at this moment.