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

[bb]

On Tue, 2022-05-10 at 11:04 +0200, Gianluigi wrote:
> Il giorno mar 10 mag 2022 alle ore 05:18 Safiur Rahman
> <isafiur at gmail.com>
> ha scritto:
> 
> > Hi
> > 
> > That cannot be the answer. The array can be of variable count and
> > count
> > may go to twenty or thirty.
> > 
> > 
> I thought it was implied, you can do something like this:
> 
> Public Sub Button2_Click()
> 
>   Dim aRes As New Result[11]
> 
>   For i As Integer = 0 To 10
>     aRes[i] = conn.Find("tbltest", "flddepartment = &1 ",
> xCountry[i])
>     If aRes[i].Available Then ListBox1.Add(aRes[i]!fldtitle)
>   Next
> 
> End
> 
> Regards
> Gianluigi
> 
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----
Gianluigi,
That is still overcomplicating the problem. He is not constructing the
WHERE part of the call properly.
To cut it short, 

res = conn.Exec("select fldtitle from tbltest where flddepartment in 
&1", ["Austria", "Azerbaijan"])

is missing the "()" that are needed for the SQL "in" construct.

The answer is 

res = conn.Exec("select fldtitle from tbltest where flddepartment in 
(&1)", ["Austria", "Azerbaijan"])

or possibly better in order to keep database independance truely true,

res = conn.Exec("select fldtitle from tbltest where flddepartment in 
&1", "("&["Austria", "Azerbaijan"].Join(",")&")")

i.e convert the string array to a comma delimited string enclosed in
"()". There should be no problemos then whether the array contains 1 or
10000000 items.

(but there may be a problem if the array contains 0 items :-) )
b

p.s. db.debug, or the less convenient IDE option (because it typically
produces more information than comprehensible) is the best way to look
at problems like this. I don't know how many other people use this
handy tool but I sure do. Probably every third time I change the shape
of the  parameters I am using in a connection.Exec() call I stuff up
something and have to resort to db.debug to find out where I have
"assumed" that Gambas is not as smart as I am .... as in, I know what I
meant, how come Gambas doesn't? 
p.p.s I am not denigrating the IDE option. It's just that usually
(hahaha) I do know the exact line I have got wrong.
p.p.p.s The last time was about 40 minutes ago when I quoted a
constraint value badly. :-(