Loops?

[NathanielCJ99]

Want to implement a loop function so I can make a variable number of data entry points(typed by user) in a form. Does not seem as any loops are usable/available.

Using this to connect to a testing database, based on the number of values needed per test. Any suggestions?

[lovasoa]

Hello and welcome to SQLPage!

sql is a declarative language. it doesn't have loops.

However, what you want to do is definitively possible to express in sql using a recursive common table expression

select 'form' as component;

WITH RECURSIVE InputFields AS (
  SELECT 1 AS i
  UNION ALL
  SELECT i + 1
  FROM InputFields
  WHERE i < $number_of_fields::int
)
SELECT 'Input field ' || i AS name
FROM InputFields;

[NathanielCJ99]

Thanks for quick reply. Would this work with a Microsoft SQL server? I tried the above Query and get this error. "error returned from database: Incorrect syntax near 'InputFields'."

[lovasoa]

Microsoft SQL Server does indeed support recursive CTEs, but not the keyword "recursive".

[NathanielCJ99]

Wonderful. That should allow me to make some more progress. Thanks!

[lovasoa]

you'll find the full documentation on Microsoft's website: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

[NathanielCJ99]

Adding to this, I am wondering how I could insert the data from My from into the database after submission. Using the same WITH statement?

[NathanielCJ99]

I think I have almost got it... I am confused with what SQL page calls the array of my form results.(To replace the hardcoded section) That seems to be the piece of the puzzle I am missing. To input as the first argument for JSON_VALUE()

For the example given for sqlpage.variables

[lovasoa]

A call to sqlpage.variables() will return the json object you need, with form field names as keys and the user input as values.

[NathanielCJ99]

I got it to work. The spaces in my name were giving me issues... I changed them to under scores and now it works!

[rhodelta66]

@lovasoa; @NathanielCJ99 So funny to see this; We are building web apps with 100% sql too, but we can only build solutions for Microsoft SQL Server. Maybe we can join forces somehow so we can both be billionaires haha ​​(I saw your video on youtube)

[rhodelta66]

This is an example of action code we use:

--declare the switch variable and sow it on the modal page
DECLARE @moon_earth_switch nvarchar(max)='false'; EXEC sp_api_modal_get_value @name='@moon_earth_switch',@value = @moon_earth_switch OUT 		
EXEC sp_api_modal_switch @name = '@moon_earth_switch', @value = @moon_earth_switch OUT,@label = N'Earth - Moon switch',@direct_post = 1

--show text on the page						
DECLARE @t nvarchar(max)=CASE WHEN @moon_earth_switch = 'true' THEN N'Hello Moon' ELSE N'Hello Earth' END
EXEC sp_api_modal_text @T ,@class='h2'						

--send text to toast (bottom right) and audible output (speakers)
EXEC sp_api_toast @text=@t, @speech=@t, @speech_lang=N'en-en' --exit button (action ends)

--add a quit button
DECLARE @exit nvarchar(max); EXEC sp_api_modal_get_value @name='@exit'	,@value = @exit OUT 
EXEC sp_api_modal_button @name='@exit',@value = 'Exit', @valueout = @exit OUT,@class='btn-danger',@key='Esc'
IF @exit is not NULL EXEC sp_api_modal_clear