[SOLVED] Sql WHERE x IN with Parameters
lazarus / 16-Databases

----------------------------------------------------------------------
schlueter550v · 2022-03-15 08:36

Hello Forum,

I have a Problem in parameterizing a Field in an SQL Query:

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
auftragsarten:='1,2,10,12,15';SQL.Text:='SELECT * FROM mytable WHERE
auftragsart IN
(:AUFART);';ParamByName('AUFART').AsString:=auftragsarten;Open; 
if i run my code i get the following SQL Error:
QuoteError converting data type nvarchar to numeric
How to work with Parameters if the SQL uses the IN() function?

Best Regards

----------------------------------------------------------------------
Awkward · 2022-03-15 08:48

Looks like you don't understands how parameters work. In your case
better to use not parameter but usual string variable:
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
auftragsarten:='1,2,10,12,15';SQL.Text:='SELECT * FROM mytable WHERE
auftragsart IN ('+auftragsarten+');';Open;

----------------------------------------------------------------------
schlueter550v · 2022-03-15 08:57

Thanks Awkward,
I allready tried that attempt, but got confused about SQL-Injection,
as my variable auftragsarten is filled from an XML-File. Changed it
for testings.

I gues my attemp is wrong. I have to make sure, that my function
reading the XML takes care of unwanted chars.

----------------------------------------------------------------------
Zvoni · 2022-03-15 09:14

And is "Auftragsart" an Integer or a Text (VARCHAR) in the Database?
btw: Which Database?

----------------------------------------------------------------------
schlueter550v · 2022-03-15 09:34

Hello Zvoni,

"Auftragsart" is an Integer in an MSSQL Server Database. I used
odbccon to connect.

----------------------------------------------------------------------
Zvoni · 2022-03-15 09:37

Then you're running afoul of the way ParamByName.AsString works:
AFAIK, if it's a String (as you imply with "AsString") it quotes the
transmitted Value.
Basically, what arrives at your Database is
Note the quotation
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 mytable WHERE auftragsart IN ('1,2,10,12,15') 
I'd try with NativeStr or Text-Properties of Params/ParamByName
https://www.freepascal.org/docs-html/fcl/db/tparam.html

If those don't work, i might have a workaround avoiding injection, but
you're probably not gonna like it

----------------------------------------------------------------------
rvk · 2022-03-15 09:55

Quote from: schlueter550v on March 15, 2022, 09:36:54 amCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
auftragsarten:='1,2,10,12,15';SQL.Text:='SELECT * FROM mytable WHERE
auftragsart IN
(:AUFART);';ParamByName('AUFART').AsString:=auftragsarten; You can't
parameterize a complete string to individual items.
You NEED to parameterize each value from auftragsarten.

So something 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 var  Arr:
TStringArray;  Par: TStringArray;  i: integer;  B: string;begin 
auftragsarten := '1,2,10,12,15';   Arr := auftragsarten.Split(','); 
SetLength(Par, Length(Arr));  for i := 0 to Length(Arr) - 1 do Par[i]
:= 'par' + i.ToString;  B := String.Join(',', Par);  Sql := 'SELECT *
FROM mytable WHERE auftragsart IN (' + B + ');';  for i := 0 to
length(Arr) - 1 do SQLQuery1.ParamByName('par' + i.ToString).AsString
:= Arr[i];
The Sql will result in
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 mytable WHERE auftragsart IN (par0,par1,par2,par3,:par4);and
after that you can set the individual parameters with a for loop on
the parameters.

(Depending if the values are strings or numbers you need to set the
parameter correctly with .AsString or .AsInteger or something)

----------------------------------------------------------------------
Zvoni · 2022-03-15 09:57

Shouldn't line 12Quote from: rvk on March 15, 2022, 10:55:33 amCode:
Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  Arr:
TStringArray;  Par: TStringArray;  i: integer;  B: string;begin 
auftragsarten := '1,2,10,12,15';   Arr := auftragsarten.Split(','); 
SetLength(Par, Length(Arr));  for i := 0 to Length(Arr) - 1 do Par[i]
:= 'par' + i.ToString;  B := String.Join(',', Par);  Sql := 'SELECT *
FROM mytable WHERE auftragsart IN (' + B + ');';  for i := 0 to
length(Arr) - 1 do SQLQuery1.ParamByName('par' + i.ToString).AsString
:= Arr[i];
The Sql will result in
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 mytable WHERE auftragsart IN (par0,par1,par2,par3,:par4);and
after that you can set the individual parameters with a for loop on
the parameters.

Shouldn't Line 12 be (note the double colon after comma)
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 B :=
String.Join(',:', Par);

----------------------------------------------------------------------
rvk · 2022-03-15 09:59

Quote from: Zvoni on March 15, 2022, 10:57:54 amShouldn't Line 12 be
(note the double colon after comma)
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 B :=
String.Join(',:', Par);Woops, yes, good catch.

(I had two similar lines of which I removed the wrong one at the last
moment)

You can also do
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 for i := 0
to Length(Arr) - 1 do Par[i] := ':par' + i.ToString;

----------------------------------------------------------------------
Zvoni · 2022-03-15 10:22

Quote from: rvk on March 15, 2022, 10:59:59 amQuote from: Zvoni on
March 15, 2022, 10:57:54 amShouldn't Line 12 be (note the double colon
after comma)
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 B :=
String.Join(',:', Par);Woops, yes, good catch.

(I had two similar lines of which I removed the wrong one at the last
moment)

You can also do
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 for i := 0
to Length(Arr) - 1 do Par[i] := ':par' + i.ToString;
Arrrghh.... Nope. My proposal doesn't work, since the first Param
would be missing the ":"
It has to be done in a loop before Joining the Array to a String

EDIT: Untested. No juggling of Strings, Arrays, whatever.
No regards to Transactions, commits and so on.
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 Var 
Arr:Array[0..4] Of Integer;  i:Integer;Begin  Arr[0]:=1;  Arr[1]:=2; 
Arr[2]:=10;  Arr[3]:=12;  Arr[4]:=15;  Query.SQL.Clear; 
Query.SQL.Text:='CREATE TABLE IF NOT EXISTS tmp_auftragsarten ("ID"
INT);';  Query.ExecSQL;  Query.SQL.Clear;  Query.SQL.Text:='TRUNCATE
tmp_auftragsarten;';  Query.ExecSQL;  Query.SQL.Clear; 
Query.SQL.Text:='INSERT INTO tmp_auftragsarten (ID) VALUES
(:Parameter);';  For i:=0 To 4 Do         Begin              
Query.ParamByName('Parameter').AsInteger:=Arr[i];         
Query.ExecSQL;        End;  Query.SQL.Clear;  Query.SQL.Text:='SELECT
MyTable.* FROM MyTable INNER JOIN tmp_auftragsarten ON
MyTable.auftragsart=tmp_auftragsarten.ID;';  Query.Open;  //Do
Something with the Result  Query.SQL.Clear;  Query.SQL.Text:='TRUNCATE
tmp_auftragsarten;';  Query.ExecSQL;End.

----------------------------------------------------------------------
rvk · 2022-03-15 11:13

Quote from: Zvoni on March 15, 2022, 11:22:48 amEDIT: Untested. No
juggling of Strings, Arrays, whatever.
No regards to Transactions, commits and so on.
That's also possible. But it's using a temp-table for this.
I would only use this when there are a lot of parameters. For example
a few hundred.
For just a few parameters this method would create a lot more overhead
on the databaseserver and using the string-method seems more
preferable.

----------------------------------------------------------------------
Zvoni · 2022-03-15 11:27

Quote from: rvk on March 15, 2022, 12:13:22 pmQuote from: Zvoni on
March 15, 2022, 11:22:48 amEDIT: Untested. No juggling of Strings,
Arrays, whatever.
No regards to Transactions, commits and so on.
That's also possible. But it's using a temp-table for this.
I would only use this when there are a lot of parameters. For example
a few hundred.
For just a few parameters this method would create a lot more overhead
on the databaseserver and using the string-method seems more
preferable.
Agreed. But we don't know what the "real" use-case is for OP
As always in life: Many roads lead to Rome.....

----------------------------------------------------------------------
schlueter550v · 2022-03-15 12:05

Hello and thanks for your help,

i did it now in a similar way rvk explained (hope i get it right)
Con and Trans are ignored here:

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 var 
l:Integer;  auftragsarten:String;  auftragsartenArray:TStringArray; 
ParamString:String;begin        auftragsarten:='1,2,10,12,15';       
auftragsartenArray:=auftragsarten.Split(',');        ParamString:=''; 
      for l:=0 to Length(auftragsartenArray)-1 do        begin       
  if l = (Length(auftragsartenArray)-1) then ParamString:=ParamString
+ ':AUFNR' + l.ToString else ParamString:=ParamString + ':AUFNR' +
l.ToString + ',';        end;        with Query do        begin      
  SQL.Text:='SELECT * FROM mytable WHERE auftragsart IN (' +
ParamString + ');';                    for l:=0 to
Length(auftragsartenArray)-1 do ParamByName('AUFNR' +
l.ToString).AsInteger:=auftragsartenArray[l].ToInteger;         
DebugLn(SQL.Text);          Open;        end;end;
I am quite new into writing code, and get easy confused with Arrays.

Best Regards

----------------------------------------------------------------------
rvk · 2022-03-15 13:06

Quote from: schlueter550v on March 15, 2022, 01:05:14 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if l =
(Length(auftragsartenArray)-1) then ParamString:=ParamString +
':AUFNR' + l.ToString else ParamString:=ParamString + ':AUFNR' +
l.ToString + ',';Yes, on first glance this looks correct.

But that's why I worked with a TStringArray, so you can easily use
String.Join() in which case you don't have that "if" structure.

My version (with just a few changes highlighted to work with a
TStringArray for Param):
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 var  l:
integer;  auftragsarten: string;  auftragsartenArray: TStringArray; 
Param: TStringArray;begin  auftragsarten := '1,2,10,12,15'; 
auftragsartenArray := auftragsarten.Split(',');  SetLength(Param,
Length(auftragsartenArray));  for l := 0 to Length(auftragsartenArray)
- 1 do Param[l] := ':AUFNR' + l.ToString;  with Query do  begin   
SQL.Text := 'SELECT * FROM mytable WHERE auftragsart IN (' +
string.Join(',', Param) + ');';    for l := 0 to
Length(auftragsartenArray) - 1 do ParamByName('AUFNR' +
l.ToString).AsInteger := auftragsartenArray[l].ToInteger;   
DebugLn(SQL.Text);    Open;  end;end;
But your versions works too.

----------------------------------------------------------------------
Sandokhane · 2022-03-18 05:49

i have do the same thing in my application ...i used TStringList

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 Var 
MatList : TStringList;  Mat : string;  F : byte;Begin    MatList :=
TStringList.Create;    MatList.Sorted := true;    Matlist.Duplicates
:= dupIgnore;    For F := 0 to XLS.Count-1 do  
 MatList.Add(XLS[F].AsString[2,5]); // i filled matList with Excel
Pages Names //    MatList.Add(intToStr(Random(20))); //for testing
MatList     //Matlist.Delete(0);    Matlist.Delimiter := ',';   
Matlist.QuoteChar := #0; // for higher delphi versions    Mat :=
Matlist.DelimitedText; // fill the varriable Mat with the contents of
MatList    MatList.Clear;    ShowMessage(Mat);      // and in
query...   SQL.Text := 'SELECT * FROM MATIERE WHERE CODE_MAT IN (' +
Mat + ')';