How to connect with SQLDB comunity members?
lazarus / 16-Databases

----------------------------------------------------------------------
vladvons · 2013-10-18 08:48

I want ot extend TSQLQuery class functionality.
Who is the lead supporter to discuss about it?

----------------------------------------------------------------------
marcov · 2013-10-18 08:54

There are several, and all are on this forum.

----------------------------------------------------------------------
vladvons · 2013-10-18 13:17

but, nevertheless more precisely please

----------------------------------------------------------------------
BigChimp · 2013-10-18 13:29

More precisely: just post on the forum

----------------------------------------------------------------------
BigChimp · 2013-10-18 13:30

See e.g. this thread
http://forum.lazarus.freepascal.org/index.php/topic,22365.0.html
where somebody is proposing with improvements for the Lazarus DB
controls. He just posted

----------------------------------------------------------------------
BigChimp · 2013-10-19 13:53

So - any information about what kind of improvements you propose?

----------------------------------------------------------------------
vladvons · 2013-10-19 14:48

TOPIC
http://forum.lazarus.freepascal.org/index.php/topic,22177.msg130492.ht
ml

There is no MACROS support in TSQLQuery, which i use oft.
Following Query code doesn't work:

SQLQuery.Params.ParamByName('InTableName').AsString   := 'MyTable';
Code: [Select]SELECT
  *
FROM
   :InTableName

SQLQuery.Params.ParamByName('InIDs').AsString   := '1,2,3,4,5';
Code: [Select]SELECT
  *
FROM
   MyTable
WHERE
  id IN (:InIDs)

Database accsessing component from Devart for such reason has
TUniQuery.MacroByName() method.
UniQuery1.MacroByName('InTableName').Value := TableName;
(note that variable in SQL should have a '&' prefix)

Now i use silly tricks with  ReplaceStr etc, and it makes me angry.
I think over imlimenting MACROS support in TSQLQuery.

Has anybody such needs as i and perhaps there is some achievement?

----------------------------------------------------------------------
marcov · 2013-10-19 15:07

The current :xxxx support is parameter support, which are always
values, and thus don't preclude a db engine from preparing the query.
The database client libs often have explicit support for
that(preparing and parameters), and it is thus not a simple text
substitution.

----------------------------------------------------------------------
LacaK · 2013-10-22 05:25

Quote from: vladvons on October 19, 2013, 04:48:54 pmThere is no
MACROS support in TSQLQuery, which i use oft.

Database accsessing component from Devart for such reason has
TUniQuery.MacroByName() method.
UniQuery1.MacroByName('InTableName').Value := TableName;
(note that variable in SQL should have a '&' prefix)

Now i use silly tricks with  ReplaceStr etc, and it makes me angry.
I think over imlimenting MACROS support in TSQLQuery.

Has anybody such needs as i and perhaps there is some achievement?
I do not have such need. But nothing stop you create f.e. class helper
and add support for your own needs.

When I need substitute table or column names I use something like:
1.) SQL.Text:=format('select * from %s order by %s',
['TableName','2,1']);
or
2.) SQL.Text:='select * '#13'from'#13#13'order by '#13#13;
     SQL[2]:='TableName';
     SQL[4]:='1,2';
Of course it is applicabele in special cases, where I have full
control over supplied SQL statements.

----------------------------------------------------------------------
vladvons · 2013-10-22 13:24

Thanks for idea with HELPER.
So, here is SetParam() helper for TSQLQuery class and you can store
SQL text in LFM.
It replace space between two commented blocks with new value.
In this case signature is /*InTable*/

Code: [Select]Query1 := TSQLQuery.Create(nil);
Query1.SQL := 'SELECT * FROM /*InTable*/this block will be
replaced/*InTable*/';

Query1.SetParam('InTable', 'Users');
//SQL.Text now is: 'SELECT * FROM Users';

Query1.SetParam('InTable', 'Orders');
//SQL.Text now is: 'SELECT * FROM Orders';

Code: [Select]uses
  SQLDB, RegExpr;

procedure TSQLQueryHelper.SetParam(const aFind, aReplace: String);
var
  RegExpr: TRegExpr;
  FindNor, FindReg :String;
begin
  FindNor := '/*' + aFind +'*/';
  FindReg := '/\*' + aFind +'\*/';

  RegExpr := TRegExpr.Create();
  RegExpr.Expression  := FindReg + '(.*)' + FindReg;
  SQL.Text       := RegExpr.Replace(SQL.Text, FindNor + aReplace +
FindNor, false);
  DeleteSQL.Text := RegExpr.Replace(DeleteSQL.Text, FindNor + aReplace
+ FindNor, false);
  InsertSQL.Text := RegExpr.Replace(InsertSQL.Text, FindNor + aReplace
+ FindNor, false);
  UpdateSQL.Text := RegExpr.Replace(UpdateSQL.Text, FindNor + aReplace
+ FindNor, false);
  RegExpr.Free();
end;