Problem with MariaDB Query

Stefanoxjx

Hi guys,
I've a problem with a query used with MariaDB.
I state that all other queries in the program works fine.
This is the code tha generate and execute the query:

   QSqlQuery Query;

   Query.prepare("SELECT d.*, dv.*, me.Descrizione FROM dipendenti d "
                  "LEFT JOIN (SELECT * FROM ("
                  "   SELECT *, row_number() over(partition by Dipendenti_Id order BY DataVisita desc) as rn "
                  "   FROM visitemediche) dv "
                  "   WHERE dv.rn = 1) dv ON d.id = dv.dipendenti_id "
                  "LEFT JOIN tipiscadenze t ON dv.TipiScadenze_id = t.Id "
                  "LEFT JOIN (SELECT * FROM ("
                  "  SELECT *, row_number() over(partition by Dipendenti_Id order by Dataformazione desc) as rn "
                  "  FROM mansioni) m "
                  "  WHERE m.rn = 1) m ON d.id = m.dipendenti_id "
                  "LEFT JOIN mansioni_elenco me ON m.Mansioni_Elenco_Id = me.id "
                  "LEFT JOIN (SELECT * FROM ("
                  "   SELECT *, row_number() over(partition by Dipendenti_Id order by DataDimissione DESC) as rn "
                  "   FROM assunzioni) a "
                  "   WHERE a.rn = 1) a ON a.dipendenti_id = d.Id "
                  "WHERE Cooperative_Id = :Cooperative_Id "
                  "AND dv.dataVisita IS NOT NULL "
                  "AND dv.tipovisita = :TipoVisita "
                  "AND a.dataassunzione IS NOT NULL AND datadimissione IS NULL "
                  "AND DATE_ADD(dv.DataVisita, INTERVAL t.Scadenza DAY) < :DataScadenza "
                  "GROUP BY d.Id");


    Query.bindValue(":Cooperative_Id", QString::number(ForYou::GetAziendaId()));
    Query.bindValue(":TipoVisita", "'"+cmbTipoVisita->currentData().toString()+"'");

    QDate DataScad = QDate::fromString(de->text(), "dd/MM/yyyy");
    Query.bindValue(":DataScadenza", "'"+DataScad.toString("yyyyMMdd")+"'");

    Query.exec();

The generated query is this:

"SELECT d.*, dv.*, me.Descrizione FROM dipendenti d LEFT JOIN (SELECT * FROM (   SELECT *, row_number() over(partition by Dipendenti_Id order BY DataVisita desc) as rn    FROM visitemediche) dv    WHERE dv.rn = 1) dv ON d.id = dv.dipendenti_id LEFT JOIN tipiscadenze t ON dv.TipiScadenze_id = t.Id LEFT JOIN (SELECT * FROM (  SELECT *, row_number() over(partition by Dipendenti_Id order by Dataformazione desc) as rn   FROM mansioni) m   WHERE m.rn = 1) m ON d.id = m.dipendenti_id LEFT JOIN mansioni_elenco me ON m.Mansioni_Elenco_Id = me.id LEFT JOIN (SELECT * FROM (   SELECT *, row_number() over(partition by Dipendenti_Id order by DataDimissione DESC) as rn    FROM assunzioni) a    WHERE a.rn = 1) a ON a.dipendenti_id = d.Id WHERE Cooperative_Id = 1 AND dv.dataVisita IS NOT NULL AND dv.tipovisita = 'O' AND a.dataassunzione IS NOT NULL AND datadimissione IS NULL AND DATE_ADD(dv.DataVisita, INTERVAL t.Scadenza DAY) < '20210703' GROUP BY d.Id"

and if I try it in HeidiSQL it works fine, but executed from Qt the result of Query.size() is always == 0.
I don't understand the problem.
Maybe can be a too long query?

Thanks.

SGaist

Hi,

Did you check whether you have an error ?
Did you print it ?

Stefanoxjx

Hi SGaist,
yes, I do it.

qDebug() << "Errors: " << Query.lastError() << Query.lastError().nativeErrorCode();

and the result is:

Errors:  QSqlError("", "", "") ""

SGaist

I just thought, not all database system returns the query size.

Did you try to go through the result of the query ?

mmiacca

@Stefanoxjx said in Problem with MariaDB Query:

"'"
Hi

You must remove "'" from de parameters of bindValue, the "'" are added by bindValue depending on data type, e.g. added in string, not added in integers.

Sorry for my english

Stefanoxjx

Hi mmiacca and thanks for your help.
You're right, the problem was generated from this line:

Query.bindValue(":DataScadenza", "'"+DataScad.toString("yyyyMMdd")+"'");

I changed it in:

Query.bindValue(":DataScadenza", DataScad.toString("yyyyMMdd"));

and the query works fine.

Don't worry for your english, me too is not very good in english :)

Regards.