Parameter Query

Help
2010-08-29
2013-04-26
  • Mathias Krause
    Mathias Krause
    2010-08-29

    Hi,

    first of all: Thanks for this great tool. I like it!

    I have a question regarding Paramter Query as this is, what i can't get to work:

    In Report Writer, i create a report and insert a fiel with "Parameter Query". I set the Column to the parameter name. Then i define the parameter in Document->Parameter List-> Add.
    I tried a lot of tricks, but i can't get the parameter query to work. There's nothing rendered.

    I found a workaround by creating a query the does a simple SELECT <?value("param_name") ?> as col and then use the Query source as the field source, but that is propably not the way it should be.

    Any suggestions?

    regards,
    gizzmo

     
  • Pierce Tyler
    Pierce Tyler
    2010-08-29

    Hi, gizzmo:

    Have you seen the OpenRPT documentation here: http://www.xtuple.org/docs/openrpt ? See if that helps.

    Regards,
    Pierce

     
  • Mathias Krause
    Mathias Krause
    2010-08-29

    Hi,
    sure, I've seen that. But there's no hint about using parameter queries.
    I had a look into the sources, and until now, i see no point, where the parameter query is filled.

    There's a -if  "Context Query"- in the source code. But for parameter query, it seems, that the rednerer tries to search for a SQL value with column = parameter name. So what i think is, that this is not implemented, yet. Right ?

     
  • Mathias Krause
    Mathias Krause
    2010-08-29

    OK … after some intensive lookings into the code, i came to the conclusion, that "Parameter Query" is not implemented, yet.
    Therefore i added some line to the code, to implement that feature:

    In ORPreRenderPrivate::renderSection i added at line 781:

    else if(f->data.query == "Parameter Query")
            {
                str = _lstParameters.value(f->data.column).toString();
                d_val = _lstParameters.value(f->data.column).toDouble(&isFloat);
            }
    

    That does the trick. And i did the same for the text fields…

     
  • Chris Ryan
    Chris Ryan
    2010-08-30

    The Parameter Query is working correctly. The Parameter Query is actually built SQL when the report is first created and accessed like a normal query. Because of this you need to be connected to a database for the Parameter Query to give you the correct results. If you connect to a database do you then start to get the expected results?

     
  • Mathias Krause
    Mathias Krause
    2010-08-30

    Hm … no. Even when i am connected to a database, the parameter query does not work.

    I am connected to MySQL, not PG SQL, maybe that's the issue?

    But as i implemented the Parameter Query direct into the fiels (like the context query), that works fine for me.

     
  • Chris Ryan
    Chris Ryan
    2010-08-31

    Indeed it is failing on MySQL. Your suggestion would solve any database compatibilities but there is the backward compat support required for ordered parameters. I will have to see if I can modify your suggested change to support that or determine if we can reasonably drop that support. Thank you for bringing this to our attention.

     
  • Mathias Krause
    Mathias Krause
    2010-09-02

    Hm … is the parameter query failing because of a missing MySQL-Feature, or just missing MySQL support in OpenRPT? In the second case, maybe you could give me a hint, where the parameters are built into SQL. Then i can try to implement that for MySQL…

    Btw: Are there parameter's available including arrays? How can i access them? (In fact, every row of a SQL query, needs another parameter index….)

     
  • Mathias Krause
    Mathias Krause
    2010-09-03

    OK … i think i found the right place for the parameter queries: So i changed orprenderer.cpp at line 1400 with

    for(int t = 0; t < _internal->_lstParameters.count(); t++)
      {
        Parameter p = _internal->_lstParameters[t];
        val = p.value().toString();
        val = val.replace(re, "''");
        if (_internal->_database.driverName() == "QMYSQL" )
            tQuery += QString().sprintf(", \"%s\" AS \"%d\"", val.toLatin1().data(), t + 1);
        else
            tQuery += QString().sprintf(", text('%s') AS \"%d\"", val.toLatin1().data(), t + 1);
        if(!p.name().isEmpty())
        {
            if (_internal->_database.driverName() == "QMYSQL" )
                tQuery += QString().sprintf(", \"%s\" AS \"%s\"", val.toLatin1().data(), p.name().toLatin1().data());
            else
                tQuery += QString().sprintf(", text('%s') AS \"%s\"", val.toLatin1().data(), p.name().toLatin1().data());
        }
      }
    

    So, this is where the queries get prepared with the parameter's from application. So the MySQL parameters now do work.
    Now, i am searching for a way, to create parameter queries for every record set of a detailed section (as array parameters). But that seems not as easy as i thought at first.

     
  • Chris Ryan
    Chris Ryan
    2010-09-09

    I've included your latest suggested change to fix mysql parameter queries. see http://www.xtuple.org/issuetracker/view.php?id=11963. This will be available in the next release of OpenRPT.

    As for the arrays there is a way to pass in QVariant<List> equivalents as parameters and then use MetaSQL to iterate over the array to build a query but OpenRPT doesn't understand or support dealing with arrays that come back from sql queries.