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

[Gianluigi]

Il giorno mar 10 mag 2022 alle ore 13:57 Gianluigi <bagonergi at gmail.com> ha
scritto:

>
>
> 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
>

I exaggerated with the '&' but that's the concept :-D

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

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