Closed nanawel closed 9 months ago
Indeed, the on_connect file contents are passed as is to the database, without SQLPage parsing and splitting it.
This allows you to use the raw database syntax, but limits you to what your database accepts, and MySQL accepts a single statement by query by default.
You can work around that by using a user-defined function.
Unfortunately that's a no-go for what I intended to do where I cannot write a single persistent piece of data on the remote server.
I was trying to build a dev tool to inspect rapidly a running production database where I can only create temporary tables and execute SELECT statements.
Here I was using on_connect.sql
to "persist" common data into a temporary table that I could then reuse on every page.
I managed to partially work around this by turning my CREATE TEMP TABLE
+ INSERT INTO
to a single CREATE TABLE ... SELECT ...
but it stills means I can only use it once, and for a single record.
Oh, I get it, the limitation is indeed annoying. I'll look into whether there is a way to bypass MySql single statement limitation.
Why would CREATE ... SELECT
be limited to a single record?
Out of curiosity, what is it you are building with SQLPage ?
Why would
CREATE ... SELECT
be limited to a single record?
Well, that's not entirely true, but having to chain UNION
does not really help readability when those data are hardcoded and not the results of a SELECT
against the database.
Out of curiosity, what is it you are building with SQLPage ?
As written above, a dev tool to inspect rapidly a running production database. I hope it could help me gather interesting data quickly, and be able to look at them whenever I need through a cute webGUI. Currently I use dBeaver with a pile of SQL queries I often reuse but it lacks the user-friendliness of a dedicated tool.
MySQL's documentation gives the following example:
CREATE TABLE tvx
SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html
Right! I didn't know this syntax. Thanks for the tip.
Anyway, eventually I'll probably need several tables to meet my needs, so this does not completely solve the problem.
By querying the database differently, we should be able to support multiple statements in on_connect even in MySQL :tada:
I pushed a fix, it should be automatically published as sqlpage:main on docker hub soon. Can you give it a try, @nanawel ?
Introduction
Using
on_connect.sql
as described in the documentation does not seem to work if more that one query is used.To Reproduce
:heavy_check_mark: Working
:x: Not working
Actual behavior
App logs:
Expected behavior
One should be able to use any number of properly terminated queries in the
on_connect.sql
bootstrap file.Version information