[Gambas-user] How to write sql query in gambas way

[bb]

On Tue, 2022-05-10 at 12:57 +0200, Benoît Minisini wrote:
> Le 09/05/2022 à 17:55, Safiur Rahman a écrit :
> > Hi
> > 
> > I get result when I execute the following query:
> > 
> > Dim res As Result
> >    res = conn.Exec("select fldtitle from tbltest where
> > flddepartment 
> > in('Austria','Azerbaijan')")
> > 
> > But I don't get result when I execute the following query:
> > res = conn.Exec("select fldtitle from tbltest where flddepartment
> > in 
> > &1", ["Austria", "Azerbaijan"])
> > 
> > How can I write this query properly in gambas passing an array
> > argument?
> > 
> > (Attached a project to demonstrate)
> > 
> > -- 
> > Regards
> > Safiur Rahman
> > 
> 
> Substitution ("&1", "&2"...) can handle simple datatypes only. It's 
> there for quoting, to avoid SQL injections in your code.
> 
> You have to build the request argument by using the
> Connection.Subst() 
> method.
> 
> aArg = ["Austria", "Azerbaijan"]
> 
> For I = 0 to aArg.Max
>    aArg[I] = conn.Subst(aArg[I])
> Next
> 
> res = conn.Exec("select fldtitle from tbltest where flddepartment in
> (" 
> & aArg.Join(",") & ")
> 
> Maybe I will made the Subst() method more clever, so that it does the
> previous job when handling arrays.
> 
> Regards,
> 
Hmm, maybe I did something years ago in local code. I cant see anything
in git. But strangely, it works here no matter how I try to fool it?
b
But then again we do use the "IN" constraint construct heavily and dont
seem to have these problems. Lost in history?
...

aha! I see we use a local resolution specific to in constraints. I'll
see if I can add some more tomorrow. (...sleep now)