How to pass argument to SQL Database Task

[stroodl]

Hello,

I’m building a basic daily specials count down. Happy with the result so far - Admin has two buttons on order line - to set the current item quantity and to disable the counting for the specific product. If a waiter orders below zero, a message pops up and the order is canceled.

Showing the number of items left on the menu button header is easy. The thing is i only want to show it on the button header for the items that are currently being count down. I also dont want to bother the staff with changing the button header for daily specials every morning - I want to automate it.

I wrote an SQL script which works great - changes menu item header for a specific product to Product ({SETTING:ItemCount_Product}) which displays well in the menu.

UPDATE mi SET [Header]= mi.[Name] + ' ({SETTING:ItemCount_' + mi.[Name] + '})'
FROM [ScreenMenuItems] mi
WHERE Name = 'Product name goes here'

The last step is to run the script from Samba and to pass the Product name to the script.

I tried adding {0} instead of the product name to the sript and passing an argument from within samba as emre mentioned here Menu Item button display format - #19 by emre

I’m getting this error no matter what I try:

How to solve this please?

This could be a super simple to use and clean way to track how many daily specials are left.

Thank you,

Ondra

[Jesse]

Where are you running this script? What event?

[Memo]

You cannot pass commands to a DB task afaik. Plus, [CBL] will prompt for Confirmation, then perform a Backup, and then Logout. Not what one wants when just updating day-to-day stuff.

You are taking input from a user and passing it as-is into a SQL query. This is bad from a SQL injection stand point. There’s no way to parametrise the inputs in anything we use in SambaPOS.

First would be creating a stored procedure. This will take inputs as parameters and avoid a potential SQL injection attack.

Here’s the DDL for the stored procedure:
Execute in SSMS, or save it as [CB]Menu_Item_Header_SP.sql in the DB Tasks folder and execute from there.

/* Update Screen Menu Item Header */

IF OBJECT_ID('dbo.p_UpdateMenuItemHeader') IS NOT NULL
  DROP PROCEDURE dbo.p_UpdateMenuItemHeader;
GO

CREATE PROCEDURE dbo.p_UpdateMenuItemHeader
  @MenuItemName NVARCHAR(MAX),
  @HeaderText NVARCHAR(MAX)
AS
  BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.ScreenMenuItems
        SET Header = @HeaderText
        WHERE Name = @MenuItemName;
  END;
GO

Then create a script to run the query:

Name: Update Menu Item Header
Handler: menuItem
Script:

function updateHeader(menuItemName, headerText)
{
    var q = "dbo.p_UpdateMenuItemHeader '" + menuItemName + "', '" + headerText + "'";
    
    sql.ExecSql(q);
}

function refreshProductCache()
{
    var q = '{query:mutation resetProductCache{postResetProductCacheMessage{id}}}';
    gql.Exec(q);
}

Then to update use an execute script action:

Action Name: Execute Script
Action Type: Execute Script
Function: [:Function x.y()]
Run in background: False

use it like this:

menuItem.updateHeader('Menu Item Here', 'Header Text Here')

you can also call the script like this:
{CALL:menuItem.updateHeader('Menu Item Here', 'Header Text Here')}

After you update everything, you’ll want to refresh the cache on the terminals. This can be done via a broadcast message and a rule for message received to execute the Refresh Cache action or you can call refreshProductCache() in the above script.

VERY IMPORTANT - MUY IMPORTANTE - SEHR WICHTIG

Only refresh cache when you’re done updating everything you need. This causes all terminals and message server to hit the db and pull menu data and has the potential to affect performance in a bad way.

For mobile client, you will have to manually refresh the settings.

[stroodl]

That is awesome! Thank you for the explanation and for the code. If there is a competition for the best forum answer, you’ve got my vote