Problems with MyDac 7.1

[lucasbarao]

Hi,

I've upgraded my MyDAC version from 3.0 to 7.1. After the upgrade, three problems apeared:

1 - In MyQuery, I write my SQL statement then click the Generate SQL button. In the version 3, the "where" clauses in update, delete, lock and refresh aways brought the key field of the updating table. In version 7 this where clause seems to bring a random field and not the key. Why this happen?

2 - In version 3, it was possible to make a SQL statemente bringing only the table fields I want to update, sure, including the key field. But in version 7, I have to bring all table fields in select and then, in the field list, select the updatable fields. Is this correct?

3 - Some queries during the post command are returning the message: "Update failed. Found xx records". This only happens in version 7. Why does this happen?

I am currently using Mydac 7.1, MySQL Server 5.5.6 rc and Embarcadero Rad Studio 2010.

Thanks and regards!

Lucas

[AndreyZ]

Hello,

1,2. Please try using MyDAC 7.1.6 or higher and check if these problems persist. In MyDAC 7.1.6, we fixed a bug with SQL Generator in the design-time.

3. The 'Update failed. N records found' error occurs if the number of updated or deleted records is not equal to 1. This problem usually occurs when a table has no Primary Key. Please check that your tables has Primary Key and that all fields Primary Key consists of are included in your queries.

[lucasbarao]

Hi, happy new year!!

I just upgraded to version 7.1.6 and the same problems still persists.

About the "update failed" message, it was fixed in my program. Thanks.

[AndreyZ]

Please specify the following:
- the script to create your table;
- update, delete, lock, and refresh SQL statements that MyDAC generates when you generate them in design-time;
- update, delete, lock, and refresh SQL statements that you want MyDAC to generate.

[lucasbarao]

All right, here comes:

My Create Table Statement:

CREATE TABLE `tabusuarios` (
`CODUSU` INT(10) NOT NULL,
`CODUSUCRM` INT(10) NOT NULL,
`CODCLI` INT(10) NOT NULL,
`NOMEUSU` VARCHAR(50) NOT NULL,
`NOMECOMPUSU` VARCHAR(100) NULL DEFAULT NULL,
`EMAILUSU` VARCHAR(100) NULL DEFAULT NULL,
`OBS` BLOB NULL,
PRIMARY KEY (`CODUSU`),
INDEX `FK_TABUSUARIOS_tabcliente` (`CODCLI`),
CONSTRAINT `FK_TABUSUARIOS_tabcliente` FOREIGN KEY (`CODCLI`) REFERENCES `tabcliente` (`CODCLI`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;


My SQL Statement in MyQuery component:

SELECT * FROM TABUSUARIOS
WHERE CODCLI = :CODCLI


Update, delete, lock and refresh that MyDac gererates with the button Generate SQL:

Update:
UPDATE tabusuarios
SET
OBS = :OBS, EMAILUSU = :EMAILUSU, NOMECOMPUSU = :NOMECOMPUSU, NOMEUSU = :NOMEUSU, CODCLI = :CODCLI, CODUSUCRM = :CODUSUCRM, CODUSU = :CODUSU
WHERE
OBS = :Old_OBS


Delete:
DELETE FROM tabusuarios
WHERE
OBS = :Old_OBS


Lock:
SELECT * FROM tabusuarios
WHERE
OBS = :Old_OBS
FOR UPDATE


Refresh:
SELECT OBS, EMAILUSU, NOMECOMPUSU, NOMEUSU, CODCLI, CODUSUCRM, CODUSU FROM tabusuarios
WHERE
OBS = :OBS


Update, delete, lock and refresh statements I want MyDac to generate:

Update:
UPDATE tabusuarios
SET
OBS = :OBS, EMAILUSU = :EMAILUSU, NOMECOMPUSU = :NOMECOMPUSU, NOMEUSU = :NOMEUSU, CODCLI = :CODCLI, CODUSUCRM = :CODUSUCRM, CODUSU = :CODUSU
WHERE
CODUSU = :Old_CODUSU


Delete:
DELETE FROM tabusuarios
WHERE
CODUSU = :Old_CODUSU


Lock:
SELECT * FROM tabusuarios
WHERE
CODUSU = :Old_CODUSU
FOR UPDATE

Refresh:
SELECT OBS, EMAILUSU, NOMECOMPUSU, NOMEUSU, CODCLI, CODUSUCRM, CODUSU FROM tabusuarios
WHERE
CODUSU = :Old_CODUSU



Thanks again!

[AndreyZ]

Please make sure that the TMyQuery.KeyFields property is empty. Also, open TMyQuery Editor, go to the SQL Generator tab, and make sure that in "Key Fields" only the CODUSU field is selected, and in "Update Fields" all fields are selected. After this, click the Generate SQL button and check if all generated SQL statements are correct.

[lucasbarao]

I've tried this yet. Doesn't work. The statements still come incorrect.

[AndreyZ]

I cannot reproduce the problem. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com .

[lucasbarao]

Thanks! I'll do it ASAP!

[AndreyZ]

I am looking forward to receiving your sample.