sqlpage.uploaded_file_path and sqlpage.uploaded_file_mime_type returns null if called within a sql file included with sqlpage.run_sql

[djyotta]

Introduction

Calling sqlpage.read_file_as_data_url from a nested sql page included with sqlpage.run_sql doesn't work.

To Reproduce

List of steps to reproduce the behavior. Include the sql file you are using and the eventual relevant parts of your database schema

Here is a working example: test.sql:

SET uploaded_file_path = sqlpage.uploaded_file_path('content');
SET data_uri = sqlpage.read_file_as_data_url($uploaded_file_path);
SET mime_type = sqlpage.uploaded_file_mime_type('content');

-- call run_sql after sqlpage.uploaded_file_path et al
SELECT 'dynamic' AS component, sqlpage.run_sql('inner.sql') AS properties;

inner.sql:

SELECT 'debug' AS component;
SELECT $mime_type AS mime_type;
SELECT $data_uri AS data_uri;
SELECT $uploaded_file_path AS uploaded_file_path;

SELECT 'form' AS component, 'Upload' AS validate;
SELECT 'Upload' AS value, '' AS label, 'hidden' AS type, 'action' AS name;
SELECT 'content' AS name, 'file' AS type, '' AS label;

Navigate to test.sql and upload a file.

You will see files you upload are logged with the debug statement.

Here is a not working example: test.sql:

-- call run_sql before sqlpage.uploaded_file_path et al
SELECT 'dynamic' AS component, sqlpage.run_sql('inner.sql') AS properties;

And inner.sql is like this:

SET uploaded_file_path = sqlpage.uploaded_file_path('content');
SET data_uri = sqlpage.read_file_as_data_url($uploaded_file_path);
SET mime_type = sqlpage.uploaded_file_mime_type('content');

SELECT 'debug' AS component;
SELECT $mime_type AS mime_type;
SELECT $data_uri AS data_uri;
SELECT $uploaded_file_path AS uploaded_file_path;

SELECT 'form' AS component, 'Upload' AS validate;
SELECT 'Upload' AS value, '' AS label, 'hidden' AS type, 'action' AS name;
SELECT 'content' AS name, 'file' AS type, '' AS label;

Navigate to test.sql and upload a file.

You will see the debug statements all log NULL.

Workaround

Call the sqlpage.uploaded_file_path before calling sqlpage.run_sql to get a non null response on file upload.

Also be sure to call sqlpage.read_file_as_data_url on the uploaded file path before calling sqlpage.run_sql if you want to get the MIME info. This is documented implicitly here: https://sql.ophir.dev/functions.sql?function=read_file_as_data_url#function In fact the documentation makes a stronger claim that the uploaded_file_path function itself must be passed in to get the mime info. But actually a variable works so long as its value is the same as the uploaded_file_path function AND called in the same context (before calling run_sql).

Needless to say, also call sqlpage.uploaded_file_mime_type before calling sqlpage.run_sql if you want to get the MIME info from the form.

I'm content with the workaround, but it just was confusing that it made a difference where these methods were called. Risk is that refactoring and moving stuff around into shared snippets included with run_sql could inadvertently break code using these methods.

Version information

• OS: MXLinux
• Database: N/A
• SQLPage Version: 0.20.5

[djyotta]

I was inspired by this https://sql.ophir.dev/functions.sql?function=persist_uploaded_file#function to try the workaround.

Name of the form field containing the uploaded file. The current page must be referenced in the action property of a form component that contains a file input field.

It took me ages to find it in the docs, but I had read it during my initial research before I even got started implementing uploads... Thought I was going nuts when I couldn't find the statement anywhere.

Anyway, I'm glad it was mentioned somewhere because otherwise I would have given up right away (I use run_sql heavily).

[lovasoa]

That behavior was documented, but the restriction was actually not hard to lift (albeit with a small performance penalty, but it should be negligible).

So, it's fixed in https://github.com/lovasoa/SQLpage/commit/af5d4a05b3330815d519adc63634261ac1387329

It will be in the next version.

[lovasoa]

In fact the documentation makes a stronger claim that the uploaded_file_path function itself must be passed in to get the mime info. But actually a variable works so long as its value is the same as the uploaded_file_path function

The stronger condition used to be required, but it works with a variable as of 0.20.5. I updated the docs in 1db5059d15593fa5adfb226cd8c73095a0f669e3

[lovasoa]

Anyway, thank you @djyotta for raising this. In the end, we made SQLPage better for everyone, today.

[djyotta]

Just to clarify, are you saying we can call the sqlpage.uploaded_file_path before calling sqlpage.run_sql in v0.20.5 ? Because in my test case I was using 0.20.5 and it wasn't working...

EDIT: sorry, just seeing this now (skimmed over it).

So, it's fixed in https://github.com/lovasoa/SQLpage/commit/af5d4a05b3330815d519adc63634261ac1387329. It will be in the next version.

[djyotta]

That behavior was documented, but the restriction was actually not hard to lift (albeit with a small performance penalty, but it should be negligible).

So, it's fixed in af5d4a0

Ah, I had read these docs long time back. I forgot about the inability to access uploaded files as it wasn't a requirement at the time.