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

[Benoît Minisini]

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,

-- 
Benoît Minisini