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

[Gianluigi]

Il giorno mar 10 mag 2022 alle ore 12:58 Benoît Minisini <g4mba5 at gmail.com>
ha scritto:

> 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
>
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----
>

Yes so it seems to work fine :-)

Public Sub Button2_Click()

  Dim res As Result

  res = conn.Exec("select fldtitle from tbltest where flddepartment in (" &
"'" & xCountry.Join("','") & "'" & ");")

  If res.Available Then
    ListBox1.List = res.All("fldtitle")
  Endif

End

Regards
Gianluigi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20220510/906b6590/attachment.htm>