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

Question

Hi all,

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.

Answer 1

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.