CODEWITHSUNDEEP
delphi
Da black ninja
Da black ninja

Reputation: 359

SQL Query Parameters

This works just fine: 

Query  = 'SELECT * from table_1 where code = :value; ';

I'm however trying to use the LIKE statement and It says that It couldn't find the parameter VALUE In this case:

Query = 'SELECT * from table_1 where code LIKE ''%:value;%''';

Param := ADOQuery1.Parameters.ParamByName('value');
Param.DataType := ftString;
Param.Value := 'bob';

I wanted to use a backslash to ignore the quotes, because It works in most languages but It looks like It doesn't work in Delphi.

Upvotes: 1

Views: 3702

Answers (2)

user6695319
user6695319

Reputation:

"No need to tell it it's a string (the Param.DataType) . It will detect that automagically."

In general it is true. Actually it depends of some TConnection properties, such as .ParamCreate,.ResourceOptions.AssignedValue.rvParamCreate, .ResourceOptions.AssignedValue.rvDefaultParamType=false,and defaultParamDataType . If those properties were not changed then It will detect that automagically

So in some cases that would be explicit.

Upvotes: 1

Jerry Dodge
Jerry Dodge

Reputation: 27296

Parameters automatically put the quotes around strings for you. Therefore, you cannot include such quotes in the SQL query. Because of that, you also cannot pass the % syntax with the query either.

Instead, keep your statement as the original (replacing = with LIKE), and pass the % around the actual value instead.

Query = 'SELECT * from table_1 where code LIKE :value';
Param := ADOQuery1.Parameters.ParamByName('value');
Param.DataType := ftString;
Param.Value := '%bob%';

On a side note, this code's a bit more elegant...

ADOQuery1.Parameters.ParamValues['value']:= '%bob%';

No need to tell it it's a string. It will detect that automagically.

Upvotes: 5

Related Questions