Temporary table not available in card -> embed file

[vsbabu]

I had my application dashboard all in one SQL file. It started by creating two temporary tables (sqlite3) and then various queries to build components. Recently, I refactored it to have multiple files accessed by dynamic component with sqlpage_runsql(). One of the components is a card with two embedded charts - each pointing to its own SQL file using sqlpage.link(). Only for these, often I get error stating temporary table is not created. When I refresh, often times it comes up.

Entry point creates a dynamic component running this sql. That includes two embeds which are single sql queries each.

I am guessing sqlpage.link() actually creates a new session and that is why temporary tables are not there. On reload, often I get data for previous query.

My goal is two simply have two charts side by side. If I can do that with some component using sqlpage.runsql(), then that is great. Otherwise, I will've to write a custom component - wanted to see if any simpler fixes are available.

[lovasoa]

The temporary tables you create in r_add_temp_tables.sql (via run_sql from index.sql) live only for the duration of that single database connection. SQLPage may decide to reuse an old connection for a new request or not. The embed links in r_net_chart_by_category.sql are not server-side includes, they are URLs that the browser fetches as separate HTTP requests after the page loads. Each of those fetches potentially gets its own database connection from the pool, which has no access to the temp tables created on the original request's connection, so r_net_chart_by_category_treemap.sql and r_net_chart_by_category_time.sql fail with "no such table". On refresh it sometimes works because the pool may happen to hand back the same connection that still has the temp tables but this is racy and unreliable.

Temporary tables are the wrong thing to use if you want to persist data across requests. Use a normal table or url parameters to pass state for anything that has to be persisted.

[vsbabu]

Thanks! I looked at link() source code and guessed it is creating a new request (not a rust developer) and also found it was firing two GET requests. I had moved to temporary tables to reduce duplicating code in CTEs. For chart dashboards, some way to render those inside a div.row with col classes will be useful. Otherwise, all charts come one after the other vertically. I created a small custom template, but as expected, dynamic component with run_sql adds generated chart htmls AFTER this; if I don't use dynamic component and just run_sql, then I get chart as json printed inside my template :) Will research more to see if some workaround is there.

[vsbabu]

This turned out to be easy - should have read full documentation. I used HTML component instead of card component and used dynamic component with run_sql for charts. I need to fix the gaps between charts and overall padding for this row, that's all.

SELECT 'html' AS component, '<div class="row">' AS html;
SELECT 'dynamic' as component, sqlpage.run_sql('r_net_chart_by_category_treemap.sql', $ctx_json) AS properties;
SELECT 'dynamic' as component, sqlpage.run_sql('r_net_chart_by_category_time.sql', $ctx_json) AS properties;
SELECT '</div>' AS html;