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

[Tobias Boege]

On Sun, 19 May 2019, Bruce wrote:
> I don't think I have seen this before. (in fact I've never tried it before.)
> 
> Basically, I have a Variant[] that contains the values that I want to
> substitute into a string. That variant could be any length but (in this
> case) is specific to the substitution pattern.
> 
> The pattern is in fact an SQL query pattern that looks like
> 	"SELECT * FROM &1 WHERE blah=$$&2$$ AND blahblah=&3".
> The value array actually comes from another query that returns a single row
> as an array, e.g. ["sales","D KB","27-Mar-2019"]. In other words, I am
> trying to build a query based on the results of a prior query.
> 
> So, I would "like" to be able to:
> 	sQry=Subst$(sPattern, vaCriteria)
> Is this feature possible?
> 

You should think twice about using Subst$() to build a database query.
Connection.Subst() will handle quoting better and in a database-specific
way. And if you use a real method of a real class, you can use

  Object.Call(hConnection, "Subst", ...)

to call it with an array of arguments, that you can freely build at
runtime. It's not pretty, but the only solution I can think of.

Example:

  Dim tmp As New String[]
  Dim h As New Connection
  h.Type = "sqlite3"
  h.Open()

  tmp.Push("SELECT &1 FROM &2 WHERE &1=&2")
  tmp.Insert(["a", "b"])
  Print Object.Call(h, "Subst", tmp)

  > SELECT 'a' FROM 'b' WHERE 'a'='b'

> (As usual, the long explanation...)
> 

Unusual for me but I'm going to skip that.

Regards,
Tobi

-- 
"There's an old saying: Don't change anything... ever!" -- Mr. Monk