Editing and posting to a MyQuery component

[ads42]

Hi,
I have never used a TMyQuery for posting data (only reading).
But for a specific case I will have to.

The SQL I use for displaying datas is like this :

Code: Select all

SELECT 
  Movies.id_movie,
  Movies.movie_titre as Titre,
  Movies.movie_realisateurs as Realisateurs,
  Movies.movie_acteurs as Acteurs,
  Movies.movie_annee as Annee,
  Movies.movie_pays as Pays,
  Movies.movie_duree as Duree,
  Movies.movie_genre as Genre,
  Movies.film_dvd,
  Movies.film_bluray,
  Movies.film_vhs,
  Movies.film_adav,
  Movies.film_supp16,
  Movies.film_supp35,
  Movies.film_ba,
  Movies.film_photos,
  Movies.film_presse,
  Movies.film_pub_divers,
  Movies.film_muet,
  Movies.film_nb,
  Affiches.affiche_photo
FROM
  Affiches
  INNER JOIN Movies ON (Affiches.id_movie = Movies.id_movie)
ORDER by Movies.movie_titre

I have some DBEdit components connected to the Movies fields not the Affiches one.
I would like to switch to edit mode and modify the Movies fields (only) and post my modifications.Same thing with deletion of records and appending of new records.
Probably by using SQLinsert, SQLDelete, SQLUpdate but how ?

Thanks for your help.

[ViktorV]

To solve your question, you should specify the name of this table in the TMyQuery.UpdatingTable property equal to "Movies". Otherwise, you can specify the correct data modification queries in the SQLInsert/SQLUpdate/SQLDelete properties of TMyQuery.

[ads42]

Thanks Viktor for your reply.
in my SQL I have things like

Movies.movie_titre as Titre

Will it try to write data in the field movie_titre or Titre ?
Where can I find sample or doc about SQLInsert/SQLUpdate/SQLDelete properties of TMyQuery ?
Regards

[ViktorV]

1. Since column aliases are used in the SQL query, then, when generating update queries to the MySQL server, MyDAC detects fields that match aliases and uses the original field name in the query. In your case, data will be inserted to the "Movies" table in the "movie_titre" column.
2. You can see an example of using SQLInsert/SQLUpdate/SQLDelete properties in our MyDacDemo project, on the 'Working with components\UpdateSQL' tab. You can find the demo project in the %MyDACDemos%\MyDacDemo directory. %MyDACDemos% is the MyDAC Demo projects installation path on your computer. Also, you can find the detailed description of SQLInsert/SQLUpdate/SQLDelete properties in the MyDAC help:
http://www.devart.com/mydac/docs/devart ... et_sql.htm

[ads42]

Perfect, thanks Viktor

[ViktorV]

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

[ads42]

Hum I have another problem this time. This Query is filtered and have an autoinc field.
it is a master table, a detail table is linked to this one by the autoinc field, posting data to this detail table raises an error because the field is null.
I have tryed to set cachedeupdate to true to master and apply update after the master post but no way.

[ViktorV]

When establishing master-detail relationship, before calling the Post method of the detail dataset, it is necessary to call the Post method of the master dataset. The detailed information about master-detail relationship can be found at http://www.devart.com/mydac/docs/work_md.htm

[ads42]

That's what I do, insert and post in Mast then insert and post in detail. but no way.
Note that master is a TMyQuery filtered.
I have to refresh first the master, if not, the autoinc field is null. With or without cacheUpdate on the master same problem.

[ViktorV]

Make sure you have correctly filled in the TMyQuery.MasterFields and TMyQuery.DetailFields properties of the detail dataset. If this doesn't help solve the problem, please send us a small sample to demonstrate the issue to viktorv*devart*com, including a script to create and fill in the test database object.

[ads42]

Ok I will send you a sample.

[ViktorV]

When you get any results concerning this issue, please let us know.