Quoting of Queries in perls mojolicious-CodePudding

I have a mojolicious app where I am using lots of queries, which are hard coded, so I am trying to differentiate between code and query by having new .sql files for each query.

I have some problems with the following query:

my $sql = $self->db->query(qq(
          select 
               a,
               b,
               c
          from
               table
          where 
               date_format(d, "%m") >= $month
          and
               date_format(d, "%m") <= $month
          and
                date_format(d, "%Y") >= $year
          and
                date_format(d, "%Y") <= $year
          and
                if(? = "", a, a=?)
          ), $a, $a);

The query above work perfect, but when I try to read it from a file my result is null.

my $path = Mojo::File->new('/path/to/file')->slurp;
my $sql = $self->db->query($path, $month,$month, $year, $year, $a, $a);

It doesn't matter if I use double quotes, single quotes or each perl function of quoting, the result is always null. When I get my params with $sth->{ParamValues} I get the right params for each call.

After coupule of hours of debugging and testing, I found out that the problem lies on the sql function "date_format" which is not called properly, but I couldn't find out why not. An alternative could be to format the time inside perl, but is not a beautiful way to handle that. I also tried to use sql variables for setting the params.

My sql file looks like:

          select 
               a,
               b,
               c
          from
               table
          where 
               date_format(d, "%m") >= ?
          and
               date_format(d, "%m") <= ?
          and
                date_format(d, "%Y") >= ?
          and
                date_format(d, "%Y") <= ?
          and
                if(? = "", a, a=?)

CodePudding user response：

The solution was to use the interval function of mysql which is smart enough to calculate the date.

For the parameters I used the sprintf function and used one variable instead of 2.

my $date = sprintf('M-d-01');

select sum(a), sum(b) from t1 where c between $date and $date   interval 1 month;