Conceptual issues with TSQLQuery lazarus / 16-Databases ---------------------------------------------------------------------- iru · 2015-03-11 03:18 Gentlefolk, I am having some difficulty understanding how the various insert/delete/update mechanisms work on a TSQLQuery I have read a lot of forum entries and the tutorials but I am struggling.... An SQL 'select' is OK, set the SQL string, set the parameter/s and set the SQLQuery active. Data from the database is in the SQLQuery. The SQLQuery has a DeleteString, UpdateString and a InsertString. SQLQuery has an 'Insert' procedure and a 'Delete' procedure which from experimenting appear to execute the SQL statement in the related InsertSQL or DeleteString. There is no 'Update' procedure. Examples I have seen load an 'Update' SQL string into the 'SQLString' and and execute a 'ExecSQL' to perfor the SQL statement. What have I missed? How does an update occur in the SQLQuery? How can perform one without loading the SQLString and ExecSQL, etc. Any help appreciated, Ian. ---------------------------------------------------------------------- taazz · 2015-03-11 03:27 In my 1.2.4 lazarus there is an updatesql property where I can define my own update command if necessary. If it is empty then the class uses its internal buffer to find out which fields have been changed and auto build an update command on the fly for you. ---------------------------------------------------------------------- iru · 2015-03-11 03:48 Taazz, Thank you for the prompt response. I am Laz 1.2.4 and have the UpdateSQL My problem is how do you tell the SQLQuery to perform an update? Is it done automatically, if so why and when? Ian ---------------------------------------------------------------------- taazz · 2015-03-11 04:48 Quote from: iru on March 11, 2015, 04:48:54 amTaazz, Thank you for the prompt response. I am Laz 1.2.4 and have the UpdateSQL My problem is how do you tell the SQLQuery to perform an update? Is it done automatically, if so why and when? Ian 1) You call sqlQuery1.Edit to put the query in edit mode on the active record. 2) Make you changes to the active record. 3) Call sqlQuery1.Post to finalize your changes and get out of the edit mode. 4) change the active record and repeat the above process until all records have bee changed 5) call sqlQuery1.ApplyUpdates for the query to update the server. I do not recall if the 5th is a mandatory step or can be automated some how in lazarus, but in delphi you could set up the query in such a way that every time you called the post method the data wend back to the server. In Lazarus I can't really remember at this time. ---------------------------------------------------------------------- LacaK · 2015-03-11 08:04 Quote from: taazz on March 11, 2015, 05:48:37 amI do not recall if the 5th is a mandatory step or can be automated some how in lazarus, but in delphi you could set up the query in such a way that every time you called the post method the data wend back to the server. In Lazarus I can't really remember at this time. There is in TRUNK version of TSQLQuery new property named Options. You can use: sqoAutoApplyUpdates And interesting may be also: sqoAutoCommit (from name of option you can guess what they do ;-) ) ---------------------------------------------------------------------- iru · 2015-03-11 09:35 Gentlefolk, Thanks for the responses, I will try a few things. Ian ---------------------------------------------------------------------- iru · 2015-03-12 10:22 Gentlefolk, I have tried a few things. Wrote some code that updated rows in the TSQLQuery. Performed am ApplyUpdates and a transaction commit at the end. It worked, I can see the results in the FireBird database using "FlameRobin". Step 3 in the post by taazz, the ".Post" was not necessary. Updates worked with out it! Ok, so things work, but what is the purpose of the TSQLQuery.UpdateSQL???? Thanks, Ian ---------------------------------------------------------------------- Leledumbo · 2015-03-12 11:30 Quote from: iru on March 12, 2015, 11:22:37 amOk, so things work, but what is the purpose of the TSQLQuery.UpdateSQL???? http://wiki.freepascal.org/Working_With_TSQLQuery#TSQLQuery.InsertSQL. 2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_Parameter s ---------------------------------------------------------------------- iru · 2015-03-19 10:33 Gentlefolk, I have read the link: http://wiki.freepascal.org/ Working_With_TSQLQue ry#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL: _Basic_Use_of_Parameters. My question still stands: What is the purpose of the TSQLQuery.UpdateSQL???? For an Insert there is tsqlquery.insert, for a delete tsqlquery.delete but I can find no tsqlquery.update. If I load a SQL statement into tsqlquery.updateSQL how do i execute the SQLin tsqlquery.updateSQL???? Ian ---------------------------------------------------------------------- taazz · 2015-03-19 11:38 Quote from: iru on March 19, 2015, 11:33:27 amGentlefolk, I have read the link: http://wiki.freepascal.org/ Working_With_TSQLQue ry#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL: _Basic_Use_of_Parameters. My question still stands: What is the purpose of the TSQLQuery.UpdateSQL???? For an Insert there is tsqlquery.insert, for a delete tsqlquery.delete but I can find no tsqlquery.update. If I load a SQL statement into tsqlquery.updateSQL how do i execute the SQLin tsqlquery.updateSQL???? Ian it is used by the applyupdates ---------------------------------------------------------------------- iru · 2015-03-19 21:08 Thank you taazz,Ian