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

[Bruce]

Aaaarrgghh! SO CLOSE :-(
Some of the string criteria need to be $quoted in order for it to work 
the way I need it. (Surnames with apostrophes and strings with embedded 
spaces ARE quoted correctly according to the help, but as I am trying to 
use the query in a bash script
	psql -d auct2019 -F '|' -A -o '/tmp/gambas.500/12311/ext.tmp' -c 
'select * from sales where house = E'D KB' and errcode = 124;'
needs to be
	psql -d auct2019 -F '|' -A -o '/tmp/gambas.500/12311/ext.tmp' -c 
'select * from sales where house = E$$D KB$$ and errcode = 124;'
I can't find a way to get postgres to tell gambas to $quote string values.
I'll start looking at the gb.db Connection.Subst code to see if I can 
find something.
b

On 19/5/19 3:44 pm, Bruce wrote:
> Looks good to me! I'll give it a try.
> tx
> Bruce
> 
> On 19/5/19 3:40 pm, Tobias Boege wrote:
>> 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
>>