B4J Question Dynamic Query (NextReports - MySQL)

[Harris]

In a multi-tenant system, I need to create a temp (memory) table that outputs the final query results to a unique table name. I need to pass this temp table name as a param to NR and have it report on it.

However, NR doesn't support dynamic quiries and their support suggested I use a stored procedure to achieve desired results (they support SP). However, a simple test passing table name param into a SP gives error on the table name???

The whole idea is to prevent other users, creating the same report, from crushing other users (data) running the report at the same time.

Anyone have any ideas on how to approach this? Some other method perhaps?

Thanks

 

[OliverA]

I need to create a temp (memory) table that outputs the final query results to a unique table name

Why create a unique table name? Just have another column that (such as report id) that can be used to distinguish the "temp" output of each report. Then you would have no "trampling" of your data.

Log this under "other method".

 

[Harris]

Why create a unique table name? Just have another column that (such as report id) that can be used to distinguish the "temp" output of each report. Then you would have no "trampling" of your data.

Log this under "other method".

I Like It! Brilliant...

Also, I could add a datetime col for when the report was run and delete old data from the table for this user (report id) prior to adding new stuff (and all others older than 1 day since it is temp output).
This way, the table becomes self cleaning. Just pass the report id as a param to NR for the correct output.

THIS is a prime example of the community providing (simple) solutions to complex questions. Seems it was not so complex at all, just could not get my head around it until reaching out. This will come in handy since I have many situations like this. Sometimes I wait too long (waste time and mental resources) before reaching out. My bad...

 

[keirS]

Which RDMS are you using?

 

[Harris]

Which RDMS are you using?

Mysql