Recent

« previous next »
Pages: [1]

Author Topic: Problems with ZUpdateSQL  (Read 5513 times)

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Problems with ZUpdateSQL
« on: October 15, 2007, 06:48:49 pm »
Hello,
Lazarus, Zeoslib, Firebird.
I was trying to make a ZQuery updateable, by providing a ZUpdateSQL.
Unfortunately, it doesn't work in my case. When trying to update a row, I get the following error:
“SQL error. Dynamic SQL error SQL error code= -804 Data type unkown. An error was found in the application program input parameters for the SQL statement...”, following by SQL statement...Well, obviously it is something with input parameters definition, but I just can't find what is wrong.

The ZQuery SQL:

SELECT 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 :P_STRT_DATE AND :P_END_DATE
ORDER BY PLNT, PRDCT, ORDR;

Only one underlying table has to be updateable in the query, the table ORDRS:

CREATE TABLE ORDRS (
  ORDRS_ID INTEGER NOT NULL,
  PLNT VARCHAR(255) CHARACTER SET NONE DEFAULT '' COLLATE NONE,
  ORDR VARCHAR(255) CHARACTER SET NONE DEFAULT '' COLLATE NONE,
  ORDR_TYPE VARCHAR(255) CHARACTER SET NONE DEFAULT '' COLLATE NONE,
  ORDR_ACTV INTEGER,
  PRDCT VARCHAR(255) CHARACTER SET NONE DEFAULT '' COLLATE NONE,
  PRDCT_DSCR VARCHAR(255) CHARACTER SET NONE DEFAULT '' COLLATE NONE,
  PRDCT_TARG_QTY NUMERIC(15, 2),
  PRDCT_TARG_QTY_UNT VARCHAR(255) CHARACTER SET NONE DEFAULT '' COLLATE NONE,
  ORDR_PLAN_STRT DATE,
  ORDR_PLAN_FNSH DATE,
  PRDCT_CONF_QTY NUMERIC(15, 2),
  PRDCT_DELV_QTY NUMERIC(15, 2),
  ORDR_CRTD DATE,
  ORDR_RLSD DATE,
  ORDR_ACTL_STRT DATE,
  ORDR_ACTL_FNSH DATE,
  ORDR_STTS VARCHAR(255) CHARACTER SET NONE DEFAULT '' COLLATE NONE,
  ORDR_CONF_FNL INTEGER DEFAULT 0,
  ORDR_CONF_PRT INTEGER DEFAULT 0,
  ORDR_PRTY INTEGER DEFAULT 999);

ALTER TABLE ORDRS ADD PRIMARY KEY (ORDRS_ID);

ALTER TABLE ORDRS ADD CONSTRAINT UQ_ORDRS_PLNT_ORDR_PRDCT UNIQUE (PLNT,ORDR,PRDCT);

Therefore, I provided the following ModifySQL to the ZUpdateSQL:

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.PLNT IS NULL AND :OLD_PLNT IS NULL) OR (ORDRS.PLNT = :OLD_PLNT)) AND
  ((ORDRS.ORDR IS NULL AND :OLD_ORDR IS NULL) OR (ORDRS.ORDR = :OLD_ORDR)) AND
  ((ORDRS.PRDCT IS NULL AND :OLD_PRDCT IS NULL) OR (ORDRS.PRDCT = :OLD_PRDCT))

The ZQuery object and persistent fields in .lfm file:

  object ZQueryQryOrdrs: TZQuery
    Connection = ZConnectionPlaningz
    SortedFields = 'PLNT;PRDCT;ORDR'
    UpdateObject = ZUpdateSQLQryOrdrs
    CachedUpdates = True
    AfterPost = ZQueryQryOrdrsAfterPost
    SQL.Strings = (
      'SELECT 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 :P_STRT_DATE AND :P_END_DATE'
      'ORDER BY PLNT, PRDCT, ORDR'
      ';'
    )
    Params = <    
      item
        DataType = ftDate
        Name = 'P_STRT_DATE'
        ParamType = ptInput
      end    
      item
        DataType = ftDate
        Name = 'P_END_DATE'
        ParamType = ptInput
      end>
    IndexFieldNames = 'PLNT Asc;PRDCT Asc;ORDR Asc'
    left = 336
    top = 1
    ParamData = <    
      item
        DataType = ftDate
        Name = 'P_STRT_DATE'
        ParamType = ptInput
      end    
      item
        DataType = ftDate
        Name = 'P_END_DATE'
        ParamType = ptInput
      end>
    object PLNT: TStringField
      DisplayLabel = 'PLANT'
      DisplayWidth = 255
      FieldName = 'PLNT'
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
    object ORDR: TStringField
      DisplayLabel = 'ORDER'
      DisplayWidth = 255
      FieldName = 'ORDR'
      Index = 1
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
    object ORDR_TYPE: TStringField
      DisplayLabel = 'ORDER TYPE'
      DisplayWidth = 255
      FieldName = 'ORDR_TYPE'
      Index = 2
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
    object ORDR_ACTV: TLongintField
      AlignMent = taRightJustify
      DisplayLabel = 'ACTIVE'
      DisplayWidth = 10
      FieldName = 'ORDR_ACTV'
      Index = 3
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object PRDCT: TStringField
      DisplayLabel = 'PRODUCT'
      DisplayWidth = 255
      FieldName = 'PRDCT'
      Index = 4
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
    object PRDCT_DSCR: TStringField
      DisplayLabel = 'PRODUCT DESCRIPTION'
      DisplayWidth = 255
      FieldName = 'PRDCT_DSCR'
      Index = 5
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
    object PRDCT_TARG_QTY: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'TARGET QUANTITY'
      DisplayWidth = 10
      FieldName = 'PRDCT_TARG_QTY'
      Index = 6
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_TARG_QTY_UNT: TStringField
      DisplayLabel = 'UNITS'
      DisplayWidth = 255
      FieldName = 'PRDCT_TARG_QTY_UNT'
      Index = 7
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
    object ORDR_PLAN_STRT: TDateTimeField
      DisplayLabel = 'PLANNED START'
      DisplayWidth = 10
      FieldName = 'ORDR_PLAN_STRT'
      Index = 8
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_PLAN_FNSH: TDateTimeField
      DisplayLabel = 'PLANNED FINISH'
      DisplayWidth = 10
      FieldName = 'ORDR_PLAN_FNSH'
      Index = 9
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_PRTY: TLongintField
      AlignMent = taRightJustify
      DisplayLabel = 'PRIORITY'
      DisplayWidth = 10
      FieldName = 'ORDR_PRTY'
      Index = 10
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_CONF_FNL: TLongintField
      AlignMent = taRightJustify
      DisplayLabel = 'CONFIRMED FINALLY'
      DisplayWidth = 10
      FieldName = 'ORDR_CONF_FNL'
      Index = 11
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_CONF_PRT: TLongintField
      AlignMent = taRightJustify
      DisplayLabel = 'CONFIRMED PARTIALY'
      DisplayWidth = 10
      FieldName = 'ORDR_CONF_PRT'
      Index = 12
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object PRDCT_CONF_QTY: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'CONFIRMED QUANTITY'
      DisplayWidth = 10
      FieldName = 'PRDCT_CONF_QTY'
      Index = 13
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_DELV_QTY: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'DELIVERED QUANTITY'
      DisplayWidth = 10
      FieldName = 'PRDCT_DELV_QTY'
      Index = 14
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object ORDR_CRTD: TDateTimeField
      DisplayLabel = 'CREATED'
      DisplayWidth = 10
      FieldName = 'ORDR_CRTD'
      Index = 15
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_RLSD: TDateTimeField
      DisplayLabel = 'RELEASED'
      DisplayWidth = 10
      FieldName = 'ORDR_RLSD'
      Index = 16
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_ACTL_STRT: TDateTimeField
      DisplayLabel = 'ACTUAL START'
      DisplayWidth = 10
      FieldName = 'ORDR_ACTL_STRT'
      Index = 17
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_ACTL_FNSH: TDateTimeField
      DisplayLabel = 'ACTUAL FINISH'
      DisplayWidth = 10
      FieldName = 'ORDR_ACTL_FNSH'
      Index = 18
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_STTS: TStringField
      DisplayLabel = 'STATUS'
      DisplayWidth = 255
      FieldName = 'ORDR_STTS'
      Index = 19
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
    object ORDR_CMPNT_SHRTG: TLongintField
      AlignMent = taRightJustify
      DisplayLabel = 'SHORTAGES'
      DisplayWidth = 10
      FieldName = 'ORDR_CMPNT_SHRTG'
      Index = 20
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object ORDR_CMPNT_SHRTG_CNT: TLongintField
      AlignMent = taRightJustify
      DisplayLabel = 'SHORTAGES COUNT'
      DisplayWidth = 10
      FieldName = 'ORDR_CMPNT_SHRTG_CNT'
      Index = 21
      ProviderFlags = [pfInUpdate, pfInWhere]
    end
    object PRDCT_STCK_UNRS: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'STOCK UNRESTRICTED'
      DisplayWidth = 10
      FieldName = 'PRDCT_STCK_UNRS'
      Index = 22
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_STCK_RSTR: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'STOCK RESTRICTED'
      DisplayWidth = 10
      FieldName = 'PRDCT_STCK_RSTR'
      Index = 23
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_STCK_QINS: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'STOCK ON QUALITY INSPECTION'
      DisplayWidth = 10
      FieldName = 'PRDCT_STCK_QINS'
      Index = 24
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_STCK_BLCK: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'STOCK BLOCKED'
      DisplayWidth = 10
      FieldName = 'PRDCT_STCK_BLCK'
      Index = 25
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_STCK_RTRN: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'STOCK RETURNS'
      DisplayWidth = 10
      FieldName = 'PRDCT_STCK_RTRN'
      Index = 26
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_STCK_TRNS: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'STOCK IN TRANSFER'
      DisplayWidth = 10
      FieldName = 'PRDCT_STCK_TRNS'
      Index = 27
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_STCK_TOT: TFloatField
      AlignMent = taRightJustify
      DisplayLabel = 'STOCK TOTAL'
      DisplayWidth = 10
      FieldName = 'PRDCT_STCK_TOT'
      Index = 28
      ProviderFlags = [pfInUpdate, pfInWhere]
      Precision = 0
    end
    object PRDCT_STCK_UNT: TStringField
      DisplayLabel = 'STOCK UNITS'
      DisplayWidth = 255
      FieldName = 'PRDCT_STCK_UNT'
      Index = 29
      ProviderFlags = [pfInUpdate, pfInWhere]
      Size = 255
    end
  end                                            

TUpdateSQL object:

  object ZUpdateSQLQryOrdrs: TZUpdateSQL
    DeleteSQL.Strings = (
      'DELETE FROM ORDRS'
      'WHERE'
      '  ((ORDRS.PLNT IS NULL AND :OLD_PLNT IS NULL) OR (ORDRS.PLNT = :OLD_PLNT)) AND'
      '  ((ORDRS.ORDR IS NULL AND :OLD_ORDR IS NULL) OR (ORDRS.ORDR = :OLD_ORDR)) AND'
      '  ((ORDRS.PRDCT IS NULL AND :OLD_PRDCT IS NULL) OR (ORDRS.PRDCT = :OLD_PRDCT))'
    )
    InsertSQL.Strings = (
      'INSERT INTO ORDRS'
      '  (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.PRDCT_CONF_QTY, ORDRS.PRDCT_DELV_QTY, ORDRS.ORDR_CRTD, '
      '   ORDRS.ORDR_RLSD, ORDRS.ORDR_ACTL_STRT, ORDRS.ORDR_ACTL_FNSH, ORDRS.ORDR_STTS, '
      '   ORDRS.ORDR_CONF_FNL, ORDRS.ORDR_CONF_PRT, ORDRS.ORDR_PRTY)'
      'VALUES'
      '  (:PLNT, :ORDR, :ORDR_TYPE, :ORDR_ACTV, :PRDCT, :PRDCT_DSCR, :PRDCT_TARG_QTY, '
      '   :PRDCT_TARG_QTY_UNT, :ORDR_PLAN_STRT, :ORDR_PLAN_FNSH, :PRDCT_CONF_QTY, '
      '   :PRDCT_DELV_QTY, :ORDR_CRTD, :ORDR_RLSD, :ORDR_ACTL_STRT, :ORDR_ACTL_FNSH, '
      '   :ORDR_STTS, :ORDR_CONF_FNL, :ORDR_CONF_PRT, :ORDR_PRTY)'
    )
    ModifySQL.Strings = (
      '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.PLNT IS NULL AND :OLD_PLNT IS NULL) OR (ORDRS.PLNT = :OLD_PLNT)) AND'
      '  ((ORDRS.ORDR IS NULL AND :OLD_ORDR IS NULL) OR (ORDRS.ORDR = :OLD_ORDR)) AND'
      '  ((ORDRS.PRDCT IS NULL AND :OLD_PRDCT IS NULL) OR (ORDRS.PRDCT = :OLD_PRDCT))'
    )
    left = 336
    top = 64
    ParamData = <    
      item
        DataType = ftString
        Name = 'PLNT'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'ORDR'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'ORDR_TYPE'
        ParamType = ptInput
      end    
      item
        DataType = ftInteger
        Name = 'ORDR_ACTV'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'PRDCT'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'PRDCT_DSCR'
        ParamType = ptInput
      end    
      item
        DataType = ftFloat
        Name = 'PRDCT_TARG_QTY'
        NumericScale = 2
        ParamType = ptInput
        Precision = 15
      end    
      item
        DataType = ftString
        Name = 'PRDCT_TARG_QTY_UNT'
        ParamType = ptInput
      end    
      item
        DataType = ftDateTime
        Name = 'ORDR_PLAN_STRT'
        ParamType = ptInput
      end    
      item
        DataType = ftDateTime
        Name = 'ORDR_PLAN_FNSH'
        ParamType = ptInput
      end    
      item
        DataType = ftFloat
        Name = 'PRDCT_CONF_QTY'
        NumericScale = 2
        ParamType = ptInput
        Precision = 15
      end    
      item
        DataType = ftFloat
        Name = 'PRDCT_DELV_QTY'
        NumericScale = 2
        ParamType = ptInput
        Precision = 15
      end    
      item
        DataType = ftDateTime
        Name = 'ORDR_CRTD'
        ParamType = ptInput
      end    
      item
        DataType = ftDateTime
        Name = 'ORDR_RLSD'
        ParamType = ptInput
      end    
      item
        DataType = ftDateTime
        Name = 'ORDR_ACTL_STRT'
        ParamType = ptInput
      end    
      item
        DataType = ftDateTime
        Name = 'ORDR_ACTL_FNSH'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'ORDR_STTS'
        ParamType = ptInput
      end    
      item
        DataType = ftInteger
        Name = 'ORDR_CONF_FNL'
        ParamType = ptInput
      end    
      item
        DataType = ftInteger
        Name = 'ORDR_CONF_PRT'
        ParamType = ptInput
      end    
      item
        DataType = ftInteger
        Name = 'ORDR_PRTY'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'OLD_PLNT'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'OLD_ORDR'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = 'OLD_PRDCT'
        ParamType = ptInput
      end>
  end

Does anybody have an idea what is wrong?

Thank you in advance!

Regards,

Zlatko
Logged

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Problems with ZUpdateSQL
« Reply #1 on: October 17, 2007, 05:57:22 pm »
I received the answer that solved the problem, from a local Croatian Delphi newsgroup. The solution is quite simple: I just had to remove all "IS NULL" comparisons in WHERE clause of ModifySQL. Therefore, the new ModifySQL now has the following WHERE clause:
"WHERE
(ORDRS.PLNT = :OLD_PLNT) AND
(ORDRS.ORDR = :OLD_ORDR) AND
(ORDRS.PRDCT = :OLD_PRDCT)"
instead of
"WHERE
((ORDRS.PLNT IS NULL AND :OLD_PLNT IS NULL) OR (ORDRS.PLNT = :OLD_PLNT)) AND
((ORDRS.ORDR IS NULL AND :OLD_ORDR IS NULL) OR (ORDRS.ORDR = :OLD_ORDR)) AND
((ORDRS.PRDCT IS NULL AND :OLD_PRDCT IS NULL) OR (ORDRS.PRDCT = :OLD_PRDCT)) "
It solved the problem!
The guy that told me the solution said that he experienced the same behavior in Delphi and that the same parameter can't be referenced in ModifySQL more than once...(?). I don't know whether this behavior is Firebird specific....

Best regards,

Zlatko
Logged
Pages: [1]
« previous next »
 

TinyPortal © 2005-2018