Recent

Author Topic: Are unamed Params supported in TSQLQuery ?  (Read 4162 times)

eara

  • Jr. Member
  • **
  • Posts: 84
Are unamed Params supported in TSQLQuery ?
« on: April 16, 2014, 03:55:14 pm »
I know for named params, but what about unamed params?
e.g.

select x,y,z from table1 where x=? and y=? and z=?;

is this supported (delphi  had it, i think)
« Last Edit: April 20, 2014, 12:54:19 am by eara »



valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Are unamed Params supported in TSQLQuery ?
« Reply #3 on: April 17, 2014, 04:14:33 am »
Are you talking about Firebird Execute Block?
http://www.firebirdsql.org/refdocs/langrefupd25-execblock.html

-------------------------------------------
The next example calculates the geometric mean of two numbers and returns it to the user:
Code: [Select]
execute block (x double precision = ?, y double precision = ?)
returns (gmean double precision)
as
begin
  gmean = sqrt(x*y);
  suspend;
end


Because this block has input parameters, it has to be prepared first. Then the parameters can be set and the block executed. It depends on the client software how this must be done and even if it is possible at all – see the notes below.

Our last example takes two integer values, smallest and largest. For all the numbers in the range smallest .. largest, the block outputs the number itself, its square, its cube and its fourth power.
Code: [Select]
execute block (smallest int = ?, largest int = ?)
returns (number int, square bigint, cube bigint, fourth bigint)
as
begin
  number = smallest;
  while (number <= largest) do
  begin
    square = number * number;
    cube   = number * square;
    fourth = number * cube;
    suspend;
    number = number + 1;
  end
end
Again, it depends on the client software if and how you can set the parameter values.
-------------------------------------------

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Are unamed Params supported in TSQLQuery ?
« Reply #4 on: April 17, 2014, 06:40:12 am »
I know for named params, but what about unamed params?
e.g.

select x,y,z from table1 where x=? and y=? and z=?;

is this supported (delphi  had it, i think)
Theoretically it can work, but then you must manually add parameters to TSQLQuery.Params collection.
And it depends on SQLConnection type ... it can work for TIBConnection, but not for MySQLConnection for example.

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Are unamed Params supported in TSQLQuery ?
« Reply #5 on: April 17, 2014, 06:54:32 am »
as far as I can see the TParams.ParseSQL method supports named and unnamed parameters. Try for your self use a statement like select * from MyTable where MyField = ?and see how that goes.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

eara

  • Jr. Member
  • **
  • Posts: 84
Re: Are unamed Params supported in TSQLQuery ?
« Reply #6 on: April 17, 2014, 08:31:20 am »
thank you both for your anwsers again, i made my tries on MySQL with unamed (didn't try with Add params), and moved to named params again.

It was in a attempt to send to MySQL a lot of tag words at once but with a secure way (against sql injections,where FCL hides the mysql_real_escape_string() into a private EscapeString()). Finally i made it but as i see not at once (due to many parameters, this seems that it needs a stored procedure for that).
one query is  something like that

Code: [Select]
qry_.SQL.Text:=Format('insert ignore into tags (tag) select temp.tag from (%s) temp',[s]);
for i:=0 to value_list.count-1 do
  qry_.ParamByName(IntToStr(i)).AsString:=value_list.strings[i];

which seems to work, in my tests i managed to send 3000 tags with one query (takes some sec, due to roundtrips for 3000 params), which seems to cover my needs for the time.

« Last Edit: April 17, 2014, 09:31:40 am by eara »

eara

  • Jr. Member
  • **
  • Posts: 84
Re: Are unamed Params supported in TSQLQuery ?
« Reply #7 on: April 20, 2014, 12:53:21 am »
Quote
Theoretically it can work, but then you must manually add parameters to TSQLQuery.Params collection.
And it depends on SQLConnection type ... it can work for TIBConnection, but not for MySQLConnection for example.
Theoretically means no for me.... i made my tests with MySQL, didn't work... never mind, go fwd...
thank you all again...

 

TinyPortal © 2005-2018