Difference between inline sql and sql.run?

[trumblejoe]

Hi,
I don't fully understand the difference between running inline and using sql.run.
Running code inline will return a result-set of object-literals as {id,user,description}
While running run.sql will return an array of object-literals [{id,user,description}] which makes object-literal usage significantly more difficult.

To illustrate: I have placed the three select examples in order but only one would be used,

SET user_id_arg = select id from users where username = sqlpage.user_info('preferred_username');
SET user_accessible_variable = sqlpage.run_sql('API/apiEndpoint.sql', json_object('user_id', $user_id_arg));
select 'table' as component,
    sqlpage.user_info('preferred_username') || '''s Access' as description,
    '#_edit_form_modal?action=edit&update_id= {id}' as edit_url,
     '#_edit_form_modal?action=delete&delete_id={id}' as delete_url;
     select * from (sqlpage.run_sql('API/apiEndpoint.sql', json_object('user_id', $user_id_arg))); -- doesn't work in sqlpages.
     
     select * from $user_accessible_variable -- runs but returns an array of key, value,full path, etc with all object literal results in value -- can not parse object literals from value as json_extract and json_each get jammed up on each other. Couldn't set array to variable and then extract against the variable either.

SELECT
 id,
 name,
  description,
etc
FROM
  other code
    WHERE user.user_id = $user_id_arg
  ); -- runs, provides clean object literals. 

Final question too, how would I use the id from the object literal in the edit and delete buttons of the table? For me {id} did not auto-replace. I am using sqlite3 so I don't have stored procedures so I wanted to just use my api endpoints and call those to keep my code clean. I also tried a dynamic component but ran into the same issues of too many difficulties extracting object literals from the array.

[lovasoa]

Hi !

I understand the confusion. See https://sql-page.com/extensions-to-sql

All variables and function results are simple strings in SQLPage. sqlpage.run_sql returns a string that contains a json array. You can iterate over it using your database json functions.

See also the docs for the dynamic component, which is handy to combine with run_sql: https://sql-page.com/component?component=dynamic

About your inline modals triggered from a table: this is currently not feasible in sqlpage (without writing a few lines of custom javascript). You'll need separate edit and delete pages, not modals.

Side note: you should not rely on preferred_username to identify users. See https://openid.net/specs/openid-connect-basic-1_0.html#ClaimStability