Passing array as a parameter to query

[Packs]

Create select statement

Code: Pascal  [Select][+][-]

1. Select * from emp where emp in (?)
2.  
3. Create tstringlist
4. Add value to stringlist variable
5.  
6. In query component
7. Create parameter as widestring
8.  
9. And pass tstringlist
10.  
11.  
12.  
13.  
14.  

[KodeZwerg]

Each new topic of you getting better and better. Bravo

[Zvoni]

Create select statement

Code: Pascal  [Select][+][-]

1. Select * from emp where emp in (?)
2.  
3. Create tstringlist
4. Add value to stringlist variable
5.  
6. In query component
7. Create parameter as widestring
8.  
9. And pass tstringlist
10.  
11.  
12.  
13.  
14.  

....which is complete overkill.
A simple String-Array is sufficient....

Aircode (I'm implying your Parameter is an Integer)

Code: Pascal  [Select][+][-]

1. Uses SysUtils;
2. Var
3.    Q:TSQLQuery;
4.    a:Array Of String;
5. Begin
6.    SetLength(a, 2);
7.    a[0]:='13';
8.    a[1]:='42';
9.    Q.SQL.Text:='Select * from emp where emp in (?)'; //You really have a FieldName which is the SAME as the TableName?!?!?!?
10.    Q.Params[0].AsString:=String.Join(a, ', ');
11.    Q.Open;
12.  

[Packs]

Yes you are correct.

Use widestring in parameter. If array size is big it will give you error

[Zvoni]

Yes you are correct.

Use widestring in parameter. If array size is big it will give you error

OK, you'll have to explain that.

Are you trying to pass WideStrings as a Parameter for the IN-Clause?

You do know, that SQLite uses UTF8 per Default?

What exactly are you trying to do?

EDIT: I have to amend my Sample above.
It wouldn't work, since the Parameter would be replaced with something like
.... WHERE emp IN ('13, 42')
which would return probably nothing.

Frankly, i dislike the IN-Operator with multiple values.
In that case i'd rather use a CTE, but that one doesn't allow Parameters.
Depends if the User can enter Values, or just choose from a list

should go more like this:

Code: Pascal  [Select][+][-]

1. ses SysUtils;
2. Var
3.    Q:TSQLQuery;
4.    a:Array Of String;
5. Begin
6.    SetLength(a, 3);
7.    a[0]:='13''';
8.    a[1]:='''42''';
9.    a[2]:='''97';
10.    Q.SQL.Text:='Select * from emp where emp in (?)'; //You really have a FieldName which is the SAME as the TableName?!?!?!?
11.    Q.Params[0].AsString:=String.Join(', ', a);
12.    Q.Open;
13.  

EDIT2: Wouldn't work either, since it doubles the singlequotes to escape them

Ah, well. Would be probably go with a staging-table, if i really need to use Parameters

EDIT3: Found this: https://forum.lazarus.freepascal.org/index.php?topic=31182.0
Bottom line: It doesn't work with Parameters. Only with injection into the SQL-Text, and then a String-Array is enough

Parameters are for single-Values
the IN-Operator only makes sense if you want to compare against multiple Values, since it's a shortcut for
".... WHERE SomeCol=Value1 OR SomeCol=Value2 OR SomeCol=Value3..." etc.

All that said: I'd probably go for a combined solution of a staging-table with a CTE using an Inner Join

[egsuh]

Based on my experience following doesn't work.

Code: Pascal  [Select][+][-]

1. qr1.SQL.Text := 'select * from emp where emp in (:datalist)';
2. qr1.parambyname('datalist').AsString := '1,2,3';

Instead I had to write:

Code: Pascal  [Select][+][-]

1. qr1.SQL.Text := Format( 'select * from emp where emp in (%s)', ['1,2,3']);

I like operator "in" because I have to deal with set-type data frequently.
If the data list of "in" operator could be retrieved within database itself, it's easier.

Code: SQL  [Select][+][-]

1.   SELECT * FROM emp WHERE emp IN (SELECT data1 FROM table2 WHERE sales >= 100);

will work.

[Zvoni]

If the data list of "in" operator could be retrieved within database itself, it's easier.

There is, but you have to know beforehand, what you expect

[egsuh]

Actually I found following codes from a place on the web, and I use it.

Code: SQL  [Select][+][-]

1. ALTER PROCEDURE INTEGER_LIST (
2.     V_INPUT VARCHAR(4096) )
3. RETURNS (
4.     INT_VALUE INTEGER )
5.  
6. AS
7. DECLARE variable CHAR_COUNT INTEGER;
8. DECLARE variable PARAM_LENGTH INTEGER;
9. DECLARE variable READ_VALUE CHAR(1);
10. DECLARE variable CURRENT_INTEGER VARCHAR(20);
11. BEGIN
12.     param_length = CHAR_LENGTH(v_input);
13.     char_count = 0;
14.     current_integer = '';
15.     while (char_count < param_length) do BEGIN
16.         char_count = :char_count + 1;
17.         read_value = SUBSTRING (:v_input FROM :char_count FOR 1);
18.         IF (:read_value <> ',') THEN BEGIN
19.             current_integer = :current_integer || :read_value;
20.         END ELSE IF (:read_value <> ' ') THEN  BEGIN
21.             IF (current_integer <> '') THEN BEGIN
22.                int_value = CAST(:current_integer AS INTEGER);
23.                current_integer = '';
24.                suspend;
25.             END
26.         END
27.  
28.         IF (:char_count = :param_length) THEN BEGIN
29.             IF (current_integer <> '') THEN BEGIN
30.                int_value = CAST(:current_integer AS INTEGER);
31.                 suspend;
32.             END
33.         END
34.     END
35. END

I can write

Code: Pascal  [Select][+][-]

1. with Query1 do begin
2.    SQL.Text := 'select * from table1 where f1 in (select int_value from Integer_List(:list))';
3.    ParamByName('list').AsString := '2,99,53';
4.    Open;
5. end;