Passing array as a parameter to query lazarus / 16-Databases ---------------------------------------------------------------------- Packs · 2023-10-27 18:52 Create select statement Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Select * from emp where emp in (?) Create tstringlistAdd value to stringlist variable In query componentCreate parameter as widestring  And pass tstringlist ---------------------------------------------------------------------- KodeZwerg · 2023-10-27 19:55 Each new topic of you getting better and better. Bravo ---------------------------------------------------------------------- Zvoni · 2023-10-30 07:54 Quote from: Prakash on October 27, 2023, 08:52:34 pmCreate select statement Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Select * from emp where emp in (?) Create tstringlistAdd value to stringlist variable In query componentCreate parameter as widestring  And pass tstringlist      ....which is complete overkill. A simple String-Array is sufficient.... Aircode (I'm implying your Parameter is an Integer) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Uses SysUtils;Var   Q:TSQLQuery;   a:Array Of String;Begin   SetLength(a, 2);   a[0]:='13';   a[1]:='42';   Q.SQL.Text:='Select * from emp where emp in (?)'; //You really have a FieldName which is the SAME as the TableName?!?!?!?   Q.Params[0].AsString:=String.Join(a, ', ');   Q.Open; ---------------------------------------------------------------------- Packs · 2023-10-30 12:50 Yes you are correct. Use widestring in parameter. If array size is big it will give you error ---------------------------------------------------------------------- Zvoni · 2023-10-30 12:59 Quote from: Prakash on October 30, 2023, 01:50:47 pmYes 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ses SysUtils;Var   Q:TSQLQuery;   a:Array Of String;Begin   SetLength(a, 3);   a[0]:='13''';   a[1]:='''42''';   a[2]:='''97';   Q.SQL.Text:='Select * from emp where emp in (?)'; //You really have a FieldName which is the SAME as the TableName?!?!?!?   Q.Params[0].AsString:=String.Join(', ', a);   Q.Open;  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 · 2023-11-02 05:22 Based on my experience following doesn't work. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i qr1.SQL.Text := 'select * from emp where emp in (:datalist)'; qr1.parambyname('datalist').AsString := '1,2,3'; Instead I had to write: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   SELECT * FROM emp WHERE emp IN (SELECT data1 FROM table2 WHERE sales >= 100); will work. ---------------------------------------------------------------------- Zvoni · 2023-11-02 08:05 Quote from: egsuh on November 02, 2023, 06:22:02 am 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 · 2023-11-03 07:02 Actually I found following codes from a place on the web, and I use it. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ALTER PROCEDURE INTEGER_LIST (    V_INPUT VARCHAR(4096) )RETURNS (    INT_VALUE INTEGER ) ASDECLARE variable CHAR_COUNT INTEGER;DECLARE variable PARAM_LENGTH INTEGER;DECLARE variable READ_VALUE CHAR(1);DECLARE variable CURRENT_INTEGER VARCHAR(20);BEGIN    param_length = CHAR_LENGTH(v_input);    char_count = 0;    current_integer = '';    while (char_count < param_length) do BEGIN      char_count = :char_count + 1;        read_value = SUBSTRING (:v_input FROM :char_count FOR 1);        IF (:read_value <> ',') THEN BEGIN            current_integer = :current_integer || :read_value;      END ELSE IF (:read_value <> ' ') THEN  BEGIN            IF (current_integer <> '') THEN BEGIN                int_value = CAST(:current_integer AS INTEGER);               current_integer = '';               suspend;            END        END         IF (:char_count = :param_length) THEN BEGIN            IF (current_integer <> '') THEN BEGIN               int_value = CAST(:current_integer AS INTEGER);                suspend;            END        END    ENDEND I can write Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i with Query1 do begin    SQL.Text := 'select * from table1 where f1 in (select int_value from Integer_List(:list))';    ParamByName('list').AsString := '2,99,53';    Open;end;