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

[Bruce]

Hi Ced,
I think you may have misunderstood my issue.
We have all the stuff you mention, the problem was (originally) I wanted 
to use a Variant array in a subst command instead of an unknown number 
of parameters as in Subst(pattern, a1,a2,a3, ...an) as the stored 
queries take a widely different number of criteria.
Anyway Tobi's Object.Call approach solved that nicely.

The subsequent probelm all comes down to quoting  for a shell command 
where the target program (psql) expects the CLI options to be quoted in 
specific ways.  Tobi looks to have solved that too.

Anyway, its bedtime here.
later
Bruce

On 19/5/19 10:01 pm, Cedron Dawg wrote:
> 
> 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
> 
> 
> ----[ Gambas mailing-list is hosted by https://www.hostsharing.net ]----
>