TSQLQuery & Paramterized SQL
lazarus / 16-Databases

----------------------------------------------------------------------
thaimann · 2008-10-27 01:52

Ok, I am trying to understand how to use tsqlquery to update my data
(using mysql.':?')  It doesn't work, but I think I am on the right
idea.  I have never used this paraterized sql before, so bear with me.

This is what I have got:


I Have a TsqlQuerry, lets say its name is MyQuery.
So MyQuery.UpdateSQL :=

   Update MyTable set MyCol = :MyCol
   Where MyIdx = :MyIdx 

Now in

procedure TDatabaseForm.MyQueryAfterPost(DataSet: TDataSet);
begin
  MyQuery.Params.CreateParam(ftString, 'MyIdx', ptinput);
  MyQuery.Params.ParamByName('MyIdx').AsString := 'Idx';
  MyQuery.Params.CreateParam(ftString, 'MyCol', ptinput);
  MyQuery.Params.ParamByName('MyCol').AsString := 'Test Value';
  ConstQuery.ApplyUpdates;
end;

I am using Updatesql because when I tried it first it said my
Updatesql was invalid.
I am assuming that I will have to do something like this with
Insertsql & deletesql too. :?

----------------------------------------------------------------------
tatamata · 2008-10-28 13:37

Hi.

I don't use standard Lazarus/FPC SQL components, but Zeoslib instead.
 But I think basic principles should work the same.

Let's say you have a ZQuery based on joined table and view, such as:
SELECT ORDRS.ORDRS_ID, ORDRS.PLNT, ORDRS.ORDR, ORDRS.ORDR_TYPE,
ORDRS.ORDR_ACTV,
    ORDRS.PRDCT, ORDRS.PRDCT_DSCR, ORDRS.PRDCT_TARG_QTY,
    ORDRS.PRDCT_TARG_QTY_UNT, ORDRS.ORDR_PLAN_STRT,
    ORDRS.ORDR_PLAN_FNSH, ORDRS.ORDR_PRTY,
    ORDRS.ORDR_CONF_FNL, ORDRS.ORDR_CONF_PRT,
    ORDRS.PRDCT_CONF_QTY, ORDRS.PRDCT_DELV_QTY,
    ORDRS.ORDR_CRTD, ORDRS.ORDR_RLSD,
    ORDRS.ORDR_ACTL_STRT, ORDRS.ORDR_ACTL_FNSH, ORDRS.ORDR_STTS,
    VW_ORDRS_CMPNTS_SHRTGS.ORDR_CMPNT_SHRTG,
    VW_ORDRS_CMPNTS_SHRTGS.ORDR_CMPNT_SHRTG_CNT,
    VW_STCKS_SUM.MTRL_STCK_UNRS AS PRDCT_STCK_UNRS,
    VW_STCKS_SUM.MTRL_STCK_RSTR AS PRDCT_STCK_RSTR,
    VW_STCKS_SUM.MTRL_STCK_QINS AS PRDCT_STCK_QINS,
    VW_STCKS_SUM.MTRL_STCK_BLCK AS PRDCT_STCK_BLCK,
    VW_STCKS_SUM.MTRL_STCK_RTRN AS PRDCT_STCK_RTRN,
    VW_STCKS_SUM.MTRL_STCK_TRNS AS PRDCT_STCK_TRNS,
    VW_STCKS_SUM.MTRL_STCK_TOT AS PRDCT_STCK_TOT,
    VW_STCKS_SUM.MTRL_STCK_UNT AS PRDCT_STCK_UNT
FROM ((ORDRS ORDRS LEFT JOIN
    VW_STCKS_SUM VW_STCKS_SUM ON ORDRS.PRDCT =
    VW_STCKS_SUM.MTRL AND ORDRS.PLNT =
    VW_STCKS_SUM.PLNT)
    INNER JOIN VW_ORDRS_CMPNTS_SHRTGS ON (ORDRS.PLNT =
VW_ORDRS_CMPNTS_SHRTGS.PLNT)
    AND (ORDRS.ORDR = VW_ORDRS_CMPNTS_SHRTGS.ORDR)
    AND (ORDRS.PRDCT = VW_ORDRS_CMPNTS_SHRTGS.PRDCT))
WHERE ORDR_CONF_FNL=0
AND ORDR_PLAN_STRT BETWEEN _STRT_DATE AND _END_DATE
ORDER BY PLNT, PRDCT, ORDR
;

You want to make this ZQuery updateable (which is not by default, if
you have several tables) joined in your sql expression. In order to
make it updateable you must attach appropriate ZUpdateSQL component to
UpdateObject property of the ZQuery.
The ZUpdateSQL component has ModifySQL property (which should be
equivalent to UpdateSQL property of standard SQLQuery) like this:
UPDATE ORDRS SET
  ORDRS.PLNT = :PLNT,
  ORDRS.ORDR = :ORDR,
  ORDRS.ORDR_TYPE = :ORDR_TYPE,
  ORDRS.ORDR_ACTV = :ORDR_ACTV,
  ORDRS.PRDCT = :PRDCT,
  ORDRS.PRDCT_DSCR = :PRDCT_DSCR,
  ORDRS.PRDCT_TARG_QTY = :PRDCT_TARG_QTY,
  ORDRS.PRDCT_TARG_QTY_UNT = :PRDCT_TARG_QTY_UNT,
  ORDRS.ORDR_PLAN_STRT = :ORDR_PLAN_STRT,
  ORDRS.ORDR_PLAN_FNSH = :ORDR_PLAN_FNSH,
  ORDRS.PRDCT_CONF_QTY = :PRDCT_CONF_QTY,
  ORDRS.PRDCT_DELV_QTY = :PRDCT_DELV_QTY,
  ORDRS.ORDR_CRTD = :ORDR_CRTD,
  ORDRS.ORDR_RLSD = :ORDR_RLSD,
  ORDRS.ORDR_ACTL_STRT = :ORDR_ACTL_STRT,
  ORDRS.ORDR_ACTL_FNSH = :ORDR_ACTL_FNSH,
  ORDRS.ORDR_STTS = :ORDR_STTS,
  ORDRS.ORDR_CONF_FNL = :ORDR_CONF_FNL,
  ORDRS.ORDR_CONF_PRT = :ORDR_CONF_PRT,
  ORDRS.ORDR_PRTY = :ORDR_PRTY
WHERE
  (ORDRS.ORDRS_ID = :ORDRS_ID)

That's all you need. You don't have to create parameters for the
ModifySQL property through code, because the component handles this
automatically. Also, you don't need AfterPost code, either. When you
enter something through a datagrid, these two components will update
table ORDRS automatically. Note that the ModifySQL has an expression
only for the updateable part of the SQL query, which is the table
ORDRS, not the whole query.
I suppose that standard Lazarus SQLQuery component handles this in
similar way. You are not supposed to write AfterPost code if you
provide correct UpdateSQL property.

I hope this helps.

Zlatko