Plain Black, makers of WebGUI - Re: SQL Report how to use prequery statements

arjan

Date: 2/17/2008 5:13 pm · Subject: SQL Report how to use prequery statements · Rating: 9

Hi all,

I have an SQL report that I use to search through several Collaboration Systems. I want both a percentage relevance and a total number of found pages. For this I need the highest relevance and the number of found rows. Therefore I do the search twice. The first time I limit the results to one and order on relevance, so I get the highest relevance that I can use to calculate the percentage. At the same time I use the SQL_CALC_FOUND_ROWS function, so I can display the found rows in the next query.

For this last thing I put in the prequery statement of the second query:

set @rows := FOUND_ROWS();

However this does not work. With debug on, the SQL Report says:

Debug: Prequery statement is not allowed: set @rows := FOUND_ROWS();

But even if I use the example from the hoover-help it says:

Debug: Prequery statement is not allowed: set @myVariable := 3

How am I supposed to use prequery statements?

(Btw if somebody knows how to use a value from the first returning row of the result in all other rows of the same result, that would be helpfull as well, but I think that's not possible.)

Kind regards,

Arjan Widlak

United Knowledge
Internet for the public sector

www.unitedknowledge.nl

--- (Edited on 18-February-2008 00:13 [GMT+0100] by arjan) ---

Back to Top

Rate [ | ]

arjan

Date: 2/19/2008 10:20 am · Subject: Re: SQL Report how to use prequery statements · Rating: -7

I think the answer to my question is: the prequery statement - also the example in the hoover-help - does not work because I used the WebGUI database. The databaselink of the WebGUI database does not allow the keyword 'set'. The allowed keywords are hardcoded and seem to be: select describe desc show and call.

In sub new in DatabaseLink.pm I found:

# databaseLinkId 0 is reserved for the WebGUI database.

if ($databaseLinkId eq "0") {
                        %databaseLink = (
                                databaseLinkId=>"0",
                                DSN=>$session->config->get("dsn"),
                                username=>$session->config->get("dbuser"),
                                identifier=>$session->config->get("dbpass"),
                                title=>"WebGUI Database",
                                allowedKeywords=>"select\ndescribe\ndesc\nshow\ncall",
                allowMacroAccess=>0,
                additionalParameters=>'',
                                );

Kind regards,

Arjan Widlak

United Knowledge
Internet for the public sector

www.unitedknowledge.nl

--- (Edited on 19-February-2008 17:20 [GMT+0100] by arjan) ---

Back to Top

Rate [ | ]

martink

Date: 5/12/2008 11:02 am · Subject: Re: SQL Report how to use prequery statements · Rating: 0

Hi Arjan,

You cannot change the properties of the default db link for security reasons. If you need to change the allowed keywords setting for queries on the webgui db, you'll have to create an additional dblink that links to the webgui db.

Martin

--- (Edited on 2008-05-12 18:02 [GMT+0200] by martink) ---

Back to Top

Rate [ | ]

elnino

Date: 2/26/2008 10:35 pm · Subject: Re: SQL Report how to use prequery statements · Rating: -1

VERY Timeely. Thank you. I was trying to use set on the webgui database too. I think this is a bug. Did you report it already?

LN

--- (Edited on 2/26/2008 10:35 pm [GMT-0600] by elnino) ---

Back to Top

Rate [ | ]

zzois

Date: 5/6/2008 6:33 am · Subject: Re: SQL Report how to use prequery statements · Rating: 0

Here you can find related discussion or at least report of the same problem: http://www.webgui.org/web_design_templates_and_themes/prequery-in-sql-report-doesnt-work

As it seems nobody hasn't done so, I will report this bug/missing feature.

--- (Edited on 6-May-2008 13:33 [GMT+0200] by zzois) ---

Back to Top

Rate [ | ]

zzois

Date: 5/6/2008 9:37 am · Subject: Re: SQL Report how to use prequery statements · Rating: 0

And here it is: http://www.webgui.org/bugs/tracker/sql-report-doesnt-support-use-prequery-statements

--- (Edited on 6-May-2008 16:37 [GMT+0200] by zzois) ---

Back to Top

Rate [ | ]

SQL Report how to use prequery statements

Recent Discussions Color Key