CODEWITHSUNDEEP
mysqlsqlpetapoco
peflorencio
peflorencio

Reputation: 2482

Prevent PetaPoco from recognizing variables as input parameters

I am using PetaPoco.Core 4.0.3 in a C# app to access a MySql database.

I'm trying to create a query that uses variables, but I don't want them to be treated by PetaPoco as input parameters. Here is a useless query just to show what I mean:

SET @num := 0;

SELECT
  @num := @num + 1 AS row_number
FROM
  buzz
LIMIT 100;

When I do that, PetaPoco recognizes @num as an input parameter and I receive the following error:

Parameter '@num' specified but none of the passed arguments have a property with this >name (in 'SET @num := 0; SELECT @num;')

I also tried to escape the @ character by doubling it

SET @@num := 0;

SELECT
  @@num := @@num + 1 AS row_number
FROM
  buzz
LIMIT 100;

but then I receive

Parameter '@num' must be defined.

Is there a way of declaring and using variables in a query without PetaPoco recognizing them as input parameters?

Thanks in advance

Paulo

Upvotes: 6

Views: 2630

Answers (1)

peflorencio
peflorencio

Reputation: 2482

I found the solution. The problem was in the .NET connector settings (see http://blog.tjitjing.com/index.php/2009/05/mysqldatamysqlclientmysqlexception-parameter-id-must-be-defined.html)

I had to change my connection string by adding the property "Allow User Variables" and setting it to true.

Database=db;Data Source=localhost;User Id=root;Password=pass;Allow User Variables=True

After that, the second version of my query (@@variable) started to work.

Upvotes: 6

Related Questions