[Gambas-user] Subst$ with a Variant array of values

[Cedron Dawg]

My Vault and VaultBox classes do exactly what you want, conveniently too, as the SQL statements are stored in an exteranl editable text file.

Check out the "ReporterDemo.zip" example from this thread:

https://forum.gambas.one/viewtopic.php?f=4&t=708

The replaceable parameters are in the HTML vault, but they work the same.

If you are trying to update one table from another, that can be done directly in SQL.

Here is the faulty query, and the corrected one, I mention in the thread:


--=|Set the inherits key in Classes. (Faulty)|=--

    UPDATE wk_classes
    SET    inherits_id = 
          (
            SELECT A.id 
            FROM   wk_classes AS A
            WHERE  A.name = wk_classes.inherits_name
          )
;
--=|Set the inherits key in Classes.|=--

    UPDATE wk_classes
    SET    inherits_id = 
          (
            SELECT     A.id

            FROM       wk_classes    AS A
			INNER JOIN wk_components AS C
			ON         A.wk_components_fk = C.id

            WHERE      A.name = wk_classes.name 
            AND        wk_classes.wk_components_fk = C.id
          )
;

This gets called from the code like this:

        ArgCon.Exec(ArgVault.Box("Set the inherits key in Classes."))

Replaceable parameters are designated insided :|   |: markings.

You could do yours like this:

--=|Sales Check|=--

        SELECT          * 
        FROM            sales AS s 
        LEFT OUTER JOIN catalogue  
        ON              [your are missing your join clause] 
        WHERE           S.auction  = :|Auction Value|:
        AND             s.saledate = :|SalesDate|:  
        AND             c.lotnum IS NULL;

In the code you would write:

        
        ArgVault.MyVaultParameters["Auction Value"] = YourValueHere
        ArgVault.MyVaultParameters["SalesDate"] = YourValueHere

        rs = ArgCon.Exec(ArgVault.Box("Set the inherits key in Classes."))

You can either embed any needed quotes in the text defintion, the value you pass, or introduce a new parameter :|Database Date Quote|: that you insert in SQL defintion and set at run time.

I developed the Vault class specically for this purpose.`

Ced