How to pass Array as TSQLQuery Parameter
lazarus / 16-Databases

----------------------------------------------------------------------
heejit · 2016-01-22 10:36

Hello,

I am new to FreePascal

How I can pass array to tsqlquery parameter

array = [1, 2, 3];

sql  is:
select
  id, name
from
  table
where
  id in(:P1)

TSqlQuery.SQL.Text := sql

TSqlQuery.Params.ParamByName(:P1).As???? := array

Or do I have to convert array into comma separated string

Thanks

----------------------------------------------------------------------
mangakissa · 2016-01-22 12:27

You gave the answer by yourself.
You can create your own string from an array (I asume concat(array) is
not available for FPC) but you also can use TStringlist which has
property commatext to get a full string with comma's.

----------------------------------------------------------------------
heejit · 2016-01-22 16:57

If I set parameter as string it become
id in('1,2,3')
instead
id in(1,2,3)

----------------------------------------------------------------------
LacaK · 2016-01-22 19:12

You can not set it using Params, you must "inject" it directly into
SQL.Text

----------------------------------------------------------------------
heejit · 2016-01-22 19:29

ok, thanks

----------------------------------------------------------------------
rvk · 2016-01-22 20:04

You might want to look at this answer for a ready-made function to
convert your array to text to inject it into your sql-statement:
http://stackoverflow.com/a/10091877/1037511

----------------------------------------------------------------------
heejit · 2016-01-23 09:44

I am using generic list of variant and following function do join
date, number and string
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 function
list.join(by_str : string; quoted : boolean=False; is_date :
boolean=False;                   date_format:string=''): string;var  
alist   : TStringList;    end_pos : integer;    I       : integer;   
adate   : TDateTime;begin    alist := TStringList.Create;    if
is_date = True then begin        for I := 0 to self.Count-1 do begin 
         adate := VarToDateTime(self.Items[I]);           
alist.Add(FormatDateTime(date_format, adate));        end;    end else
begin        for I := 0 to self.Count-1 do begin           
alist.Add(VarToStr(self.Items[I]));        end;    end;     if quoted
= True then begin        alist.LineBreak := ''',''';        end_pos :=
Length(alist.Text) - Length(RightStr(alist.Text,
Length(alist.LineBreak)));        Result  := '''' + MidStr(alist.Text,
1, end_pos) + '''';    end else begin        alist.LineBreak :=
by_str;        end_pos := Length(alist.Text) -
Length(RightStr(alist.Text, Length(alist.LineBreak)));        Result
 := MidStr(alist.Text, 1, end_pos);    end;end;