MSSQL Examples

[rhodelta66]

Hi, Ophir
First of all, I love SQLPAGE, thanks for your great initiative!

My question is this: Would it be possible to create the examples for MSSQL too?
For example MSSQL does not have "RETURNING" like postgres does. It would be great if existing examples would also be available for the MSSQL dialect.

[lovasoa]

Hello!
Maintaining 4 versions of all examples for the 4 sql dialects that we currently support would probably require more resources than we have.

But having at least one or two more complete mssql examples is a good idea and would be very welcome! Could you make a pull request ?

[lovasoa]

Concerning Returning: in mssql, there is OUTPUT:https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16

[rhodelta66]

Thanks, I will make a pull request.

[castlekaybee]

I second that. I was trying the form component that triggers this sql_

UPDATE BasisAggregatsTyp SET Kurzname = 'NewKurzname', Bezeichnung_DE = 'Neue Bezeichnung_DE', Bezeichnung_EN = 'New Bezeichnung_EN', Kommentar = 'New Kommentar', GeaendertAm = GETDATE(), GeaendertVon = 'UpdatedUser' WHERE AT_ID = $at_id_from_url RETURNING 'redirect' as component, '/UI/BasisTypen/PageBasisAggregatsTyp.sql' AS link and got this error (V0.24):

[lovasoa]

Hello ! If the data you are returning does not depend on the updated data, you can make two separate statements:

UPDATE BasisAggregatsTyp
SET
  Kurzname = 'NewKurzname',  
  Bezeichnung_DE = 'Neue Bezeichnung_DE',  
  Bezeichnung_EN = 'New Bezeichnung_EN',  
  Kommentar = 'New Kommentar',  
  GeaendertAm = GETDATE(),  
  GeaendertVon = 'UpdatedUser'
WHERE  AT_ID = $at_id_from_url;

SELECT 'redirect' as component, '/UI/BasisTypen/PageBasisAggregatsTyp.sql' AS link;

[lovasoa]

When you are in a case where RETURNING is actually used, you can use OUTPUT instead. For instance:

UPDATE BasisAggregatsTyp
SET
  Kurzname = 'NewKurzname',  
  Bezeichnung_DE = 'Neue Bezeichnung_DE',  
  Bezeichnung_EN = 'New Bezeichnung_EN',  
  Kommentar = 'New Kommentar',  
  GeaendertAm = GETDATE(),  
  GeaendertVon = 'UpdatedUser'
WHERE  AT_ID = $at_id_from_url
OUTPUT 
    'redirect' as component,
    CONCAT('/UI/BasisTypen/PageBasisAggregatsTyp.sql?old_name=', DELETED.Kurzname) AS link;

[castlekaybee]

Hello ! If the data you are returning does not depend on the updated data, you can make two separate statements:

UPDATE BasisAggregatsTyp
SET
  Kurzname = 'NewKurzname',  
  Bezeichnung_DE = 'Neue Bezeichnung_DE',  
  Bezeichnung_EN = 'New Bezeichnung_EN',  
  Kommentar = 'New Kommentar',  
  GeaendertAm = GETDATE(),  
  GeaendertVon = 'UpdatedUser'
WHERE  AT_ID = $at_id_from_url;

SELECT 'redirect' as component, '/UI/BasisTypen/PageBasisAggregatsTyp.sql' AS link;

This does the trick for me, thanks a lot!

[rhodelta66]

Merci! Op di 2 jul 2024 om 15:48 schreef Ophir LOJKINE ***@***.***>:

…

When you are in a case where RETURNING is actually used, you can use OUTPUT <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16> instead. For instance: UPDATE BasisAggregatsTypSET Kurzname = 'NewKurzname', Bezeichnung_DE = 'Neue Bezeichnung_DE', Bezeichnung_EN = 'New Bezeichnung_EN', Kommentar = 'New Kommentar', GeaendertAm = GETDATE(), GeaendertVon = 'UpdatedUser'WHERE AT_ID = $at_id_from_url; OUTPUT 'redirect' as component, CONCAT('/UI/BasisTypen/PageBasisAggregatsTyp.sql?old_name=', DELETED.Kurzname) AS link; — Reply to this email directly, view it on GitHub <#353 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHS2S7FJKQFJR5NRQNHMGKTZKKVULAVCNFSM6AAAAABIQXU4J2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TSMZXGE3TM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

-- Met vriendelijke groet, Rick Hoek Directeur Tracy Data Solutions B.V.*|* ABC Westland 666/670 *|* 2685 DH Poeldijk *|* Netherlands *Ph:* +31 10 744 18 13 *|* *Mob:* +31 6 53 19 74 06 *|* *E: ***@***.*** *|* *I:* www.tracy.nu Aan dit bericht kunnen geen rechten worden ontleend.

[rhodelta66]

Hi Ophir, I use MSSQL; This Code produces this error after parsing, so I think that you should need to output the semi-colon too! error returned from database: A MERGE statement must be terminated by a semi-colon (;). ## Here is the code ``` MERGE INTO user_info AS target USING (SELECT :username AS username, sqlpage.hash_password(:password) AS password_hash) AS source ON (target.username = source.username) WHEN NOT MATCHED THEN INSERT (username, password_hash) VALUES (source.username, source.password_hash) ; --note that I provide the ";" here, but after prsing it is gone... ``` ## This is after parsing: Failed to execute SQL statement: MERGE INTO user_info AS target USING (SELECT ***@***.*** AS VARCHAR(MAX)) AS username, ***@***.*** AS VARCHAR(MAX)) AS password_hash) AS source ON (target.username = source.username) WHEN NOT MATCHED THEN INSERT (username, password_hash) VALUES (source.username, source.password_hash)

[lovasoa]

which version of SQLPage are you using? The latest version should properly include the semicolons from the source sql file

[rhodelta66]

Indeed I was using the old V0.20; The ; Issue is perfectly solved in later version.

[rhodelta66]

User authentication demo

This example demonstrates how to handle user authentication with SQLpage.

MSSQL version

It is based on the original PostgreSQL database example, but now modified to be used with MSSQL.

All the user and password management is done in SQLPage,
which uses best practices for password storage.

Take note that the database is a little bit different:

--user:
CREATE TABLE [dbo].[user_info](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[username] [nvarchar](max) NOT NULL,
	[password_hash] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--login sessions:
CREATE TABLE [dbo].[login_session](
	[tsql.app_id] [int] IDENTITY(1,1) NOT NULL,
	[id] [varchar](max) NULL,
	[created_at] [datetime2](7) NULL,
	[username] [nvarchar](max) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[tsql.app_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[login_session] ADD  CONSTRAINT [DF_login_session_created_at]  DEFAULT (getdate()) FOR [created_at]
GO

This demonstrates how to implement:

• a signup form
• a login form
• a logout button
• secured pages that can only be accessed by logged-in users

User authentication is a complex topic, and you can follow the work on implementing differenet authentication methods in this issue.

MSSQL files

index.sql

SELECT 'shell' AS component,
    'User Management App ' + (select username from login_session where id=COALESCE(sqlpage.cookie('session'), '')) AS title,
    'user' AS icon,
    '/' AS link,
    CASE 
        WHEN COALESCE(sqlpage.cookie('session'), '') = '' 
        THEN JSON_QUERY('["signin", "signup"]')
        ELSE JSON_QUERY('["logout"]')
    END AS menu_item;

SELECT 'hero' AS component,
    'SQLPage Authentication Demo' AS title,
    'This application requires signing up to view the protected page.' AS description_md,
    'https://upload.wikimedia.org/wikipedia/commons/thumb/e/e1/Community_wp20.png/974px-Community_wp20.png' AS image,
    'protected_page.sql' AS link,
    'Access protected page' AS link_text;

login.sql

-- The authentication component will stop the execution of the page and redirect the user to the login page if
-- the password is incorrect or if the user does not exist.
SELECT 'authentication' AS component,
    'signin.sql?error' AS link,
    (SELECT top 1 password_hash FROM user_info WHERE username = :username) AS password_hash,
    :password AS password;

INSERT INTO login_session (id, username)
VALUES (sqlpage.random_string(32), :username);

SELECT 
    'cookie' AS component,
    'session' AS name,
    (select top 1 id from login_session where username=:username ) AS value,
    0 AS secure; -- You can remove this if the site is served over HTTPS.

-- Redirect the user to the protected page.
SELECT 'redirect' AS component, 'protected_page.sql' AS link;

create_user.sql

-- Attempt to insert the new user
-- We use a subquery to check for existence and only insert if the username doesn't exist
INSERT INTO user_info (username, password_hash)
SELECT :username, sqlpage.hash_password(:password)
WHERE NOT EXISTS (SELECT 1 FROM user_info WHERE username = :username)
    AND trim(:username) >N'';

-- Check if a row was inserted
-- If @@ROWCOUNT is 1, the insert succeeded; if 0, it failed due to existing username
SELECT 'redirect' AS component,
       CASE 
           WHEN @@ROWCOUNT = 1 THEN 'create_user_welcome_message.sql?username=' + :username
           ELSE 'create_user_welcome_message.sql?error&username=' + :username
       END AS link;

create_user_welcome_message.sql

SELECT 'hero' AS component,
    'Welcome' AS title,
    'Welcome, ' || $username || '! Your user account was successfully created. You can now log in.' AS description,
    'https://upload.wikimedia.org/wikipedia/commons/thumb/e/e1/Community_wp20.png/974px-Community_wp20.png' AS image,
    'signin.sql' AS link,
    'Log in' AS link_text
WHERE $error IS NULL;

SELECT 'hero' AS component,
    'Sorry' AS title,
    case when nullif(trim($username),N'') IS NULL
        THEN  'Sorry, but a blank user name "' || $username || '" is NOT allowed!' 
        ELSE  'Sorry, but the user name "' || $username || '" is already taken!' 
        END AS description,
    'https://upload.wikimedia.org/wikipedia/commons/thumb/f/f0/Sad_face_of_a_Wayuu_Woman.jpg/640px-Sad_face_of_a_Wayuu_Woman.jpg' AS image,
    'signup.sql' AS link,
    'Try again' AS link_text
WHERE $error IS NOT NULL;

generate_password_hash.sql

SELECT 'form' AS component;
SELECT 'password' AS name, 'Password to create a hash for' AS label, :password AS value;

SELECT 'code' AS component;
SELECT sqlpage.hash_password(:password) AS contents;

logout.sql

DELETE FROM login_session WHERE id = sqlpage.cookie('session');
SELECT 'cookie' AS component, 'session' AS name, 1 AS remove;

SELECT 'redirect' AS component, '/04' AS link;

protected_page.sql

--From Rick: note that || is translated by SQLPage, but that it would be more concise to use "+" or "concat() for MSSQL"

SET username = (SELECT username FROM login_session WHERE id = sqlpage.cookie('session'));

SELECT 'redirect' AS component,
        'signin.sql?error' AS link
WHERE $username IS NULL;

SELECT 'shell' AS component, 'Protected page' AS title, 'lock' AS icon, '/' AS link, 'logout' AS menu_item;

SELECT 'text' AS component,
        'Welcome, ' || $username || ' !' AS title,
        'This content is [top secret](https://wbo.ophir.dev/boards/bikkel#0,0,0.7).
        You cannot view it if you are not connected.' AS contents_md;

signin.sql

SELECT 'form' AS component,
    'Sign in' AS title,
    'Sign in' AS validate,
    'login.sql' AS action;

SELECT 'username' AS name;
SELECT 'password' AS name, 'password' AS type;

SELECT 'alert' as component,
    'Sorry' as title,
    'We could not authenticate you. Please log in or [create an account](signup.sql).' as description_md,
    'alert-circle' as icon,
    'red' as color
WHERE $error IS NOT NULL;

signup.sql

SELECT 'form' AS component,
    'Create a new user account' AS title,
    'Sign up' AS validate,
    'create_user.sql' AS action;

SELECT 'username' AS name;
SELECT 'password' AS name, 'password' AS type, '^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$' AS pattern, 'Password must be at least 8 characters long and contain at least one letter and one number.' AS description;
SELECT 'terms' AS name, 'I accept the terms and conditions' AS label, 1 AS required, 0 AS value, 'checkbox' AS type;

[lovasoa]

@rhodelta66 : that is a great example ! I think it could be useful to many.
Would you mind opening a pull request to add this example to this repo ?

https://docs.github.com/en/pull-requests/collaborating-with-pull-requests/proposing-changes-to-your-work-with-pull-requests/creating-a-pull-request

You could add a new folder here: https://github.com/lovasoa/SQLpage/tree/main/examples

[lovasoa]

I added a new mssql-specific example to the list of examples: Microsoft SQL Server Forms

[rhodelta66]

A great, thanks Ophir! Slowly I am doing more and more with SQLPage combined with MSSQL and our own TSQL.APP Framework Have a look: https://ux1.nl/tsql_index/index.sql And our movie on youtube is fun: https://www.youtube.com/watch?v=cdD3GYheq_w [image: image.png] [image: image.png] Op vr 8 nov 2024 om 16:42 schreef Ophir LOJKINE ***@***.***>:

…

I added a new mssql-specific example to the list of examples: Microsoft SQL Server Forms <https://github.com/sqlpage/SQLPage/tree/main/examples/microsoft%20sql%20server%20advanced%20forms> — Reply to this email directly, view it on GitHub <#353 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHS2S7DA7DBGBHHJT35ZRHTZ7TLV5AVCNFSM6AAAAABIQXU4J2VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCMJZGA2TQMQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Met vriendelijke groet, Rick Hoek Directeur Tracy Data Solutions B.V.*|* ABC Westland 666/670 *|* 2685 DH Poeldijk *|* Netherlands *Ph:* +31 10 744 18 13 *|* *Mob:* +31 6 53 19 74 06 *|* *E: ***@***.*** *|* *I:* www.tracy.nu Aan dit bericht kunnen geen rechten worden ontleend.

[rhodelta66]

https://github.com/sqlpage/SQLPage/releases Love your work and the fantastic tools and documentation! With kind regards, Rick