Page 1 of 1
Editing and posting to a MyQuery component
Posted: Tue 23 Dec 2014 11:06
by 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.
Re: Editing and posting to a MyQuery component
Posted: Tue 23 Dec 2014 13:30
by 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.
Re: Editing and posting to a MyQuery component
Posted: Tue 23 Dec 2014 13:54
by 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
Re: Editing and posting to a MyQuery component
Posted: Wed 24 Dec 2014 13:49
by 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
Re: Editing and posting to a MyQuery component
Posted: Mon 29 Dec 2014 09:41
by ads42
Perfect, thanks Viktor
Re: Editing and posting to a MyQuery component
Posted: Mon 29 Dec 2014 09:51
by ViktorV
Feel free to contact us if you have any further questions about MyDAC.
Re: Editing and posting to a MyQuery component
Posted: Thu 15 Jan 2015 14:25
by 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.
Re: Editing and posting to a MyQuery component
Posted: Fri 16 Jan 2015 11:29
by 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
Re: Editing and posting to a MyQuery component
Posted: Fri 16 Jan 2015 13:40
by 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.
Re: Editing and posting to a MyQuery component
Posted: Fri 16 Jan 2015 14:25
by 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.
Re: Editing and posting to a MyQuery component
Posted: Fri 16 Jan 2015 14:44
by ads42
Ok I will send you a sample.
Re: Editing and posting to a MyQuery component
Posted: Thu 29 Jan 2015 11:21
by ViktorV
When you get any results concerning this issue, please let us know.