Parameter problems

[Schlingel]

Table:

Code: Select all

-- ----------------------------
-- Table structure for `testtbl`
-- ----------------------------
DROP TABLE IF EXISTS `testtbl`;
CREATE TABLE `testtbl` (
  `ReadAccess` tinyint(1) DEFAULT NULL,
  `WriteAccess` tinyint(1) DEFAULT NULL,
  `Format` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Format`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of testtbl
-- ----------------------------
INSERT INTO `TestTbl` VALUES (null, null, '0');
INSERT INTO `TestTbl` VALUES (null, null, '1');
INSERT INTO `TestTbl` VALUES (null, null, '22');
INSERT INTO `TestTbl` VALUES (null, null, '33');
INSERT INTO `TestTbl` VALUES (null, null, '44');
INSERT INTO `TestTbl` VALUES (null, null, '55');
INSERT INTO `TestTbl` VALUES (null, null, '66');

Delphi-Code:

Code: Select all

  MyQuery.SQL.Text := 'UPDATE `TestTbl` SET ' +
    '(`ReadAccess`,`WriteAccess`) VALUES (:`ReadAccess`,:`WriteAccess`) ' +
    'WHERE (`Format` = :`Format`)';

  for i := 0 to 255 do
  begin

    MyQuery.ParamByName('Format').AsInteger := i;

    MyQuery.ParamByName('WriteAccess').AsBoolean := (i and (1 shl 7))  0;
    MyQuery.ParamByName('ReadAccess').AsBoolean := (i and (1 shl 6))  0;

    MyQuery.Execute;

  end;

Info from DBMonitor:

Code: Select all

SQL:
UPDATE `TestTbl` SET (`ReadAccess`,`WriteAccess`) VALUES (:`ReadAccess`,:`WriteAccess`) WHERE (`Format` = :`Format`)

Parameters:
 Name          Type      Data Type    In Value  Out Value
 ReadAccess              Boolean      False
 Write Access            Boolean      False
 Format                  Integer      0

The problem is that I get a syntax error in the IDE. But in the "DBMonitor" everything looks quite reasonable.

My pattern:
http://www.devart.com/dotconnect/oracle ... l#examples

Thanks for help

[AndreyZ]

Hello,

You are using a wrong SQL statement for the UPDATE operation. The correct code is the following:

Code: Select all

MyQuery1.SQL.Text := 'UPDATE `TestTbl` SET ' +
   '`ReadAccess`=:`ReadAccess`, `WriteAccess`=:`WriteAccess` ' +
   'WHERE (`Format` = :`Format`)';

You can find the correct UPDATE syntax at http://dev.mysql.com/doc/refman/5.0/en/update.html

[Schlingel]

This solution works perfectly.

[AndreyZ]

Feel free to contact us if you have any further questions about MyDAC.