Hi Rohit, I understand what you are saying. Helen Borrie also showed a better way to do this via executing a DML statement, not opening a dataset.
Fortunately this is a little standalone application that is not multi-user and released the resource when it is shutdown right after executing. So, all thought this is bad practice, it worked find for what I needed. Live and learn. You can be assured my large scale (actually ALL) future application will adopt this standard practice. DML is for executing actions on the database, datasets are for caching the data to be examined or displayed for the user. Regards, Eric Eric Tishler Software Architect Resolute Partners, LLC Phone: 203.271.1122 Fax: 203.271.1460 [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rohit Gupta Sent: Monday, April 05, 2004 8:50 AM To: NZ Borland Developers Group - Delphi List Subject: Re: [DUG] How to commit and update using TIB_Query from code Eric, DO NOT use autocommit. The guy that invented it should hung by dangly bits. Autocommit holds the transaction open - with more than one user these can get entangled and the server will slow down hour after hour, day after day... until only a backup and restore will fix the problem. Use explicit transaction control, otherwise, sometime in the future you will have to go through your app fixing them. Eric Tishler wrote: >I am using SQL dialect 3 and building the SQL string directly in code (instead of >hard coded SQL in TIB_Query with ParamByName substitutions). > >The end result is the same. That is my SQL statement would appear as follows prior to >execution: > >UPDATE "UserAccounts" SET "Credits" = 100 WHERE "AccountID" = 12345 > >In SQL dialect 3 field names with differentiated upper and lower case letters must be >double quote delimited. > >If you look at the bottom of my original post you will see that I was trying >different values for the CommitAction property, so you can see I was already >suspecting that I was not getting my update to commit. > >Eventually I found the problem. Event though I am not performing transaction based >activity, my TIB_Transaction AutoCommit property was set to false. Once I set this to >true everything worked fine. > >Regards, >Eric > >Eric Tishler >Software Architect >Resolute Partners, LLC >Phone: 203.271.1122 >Fax: 203.271.1460 >[EMAIL PROTECTED] > > -----Original Message----- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Craig Goodall >Sent: Saturday, April 03, 2004 8:21 PM >To: NZ Borland Developers Group - Delphi List >Subject: Re: [DUG] How to commit and update using TIB_Query from code > >Hi Eric, > >Your SQL statements look a little strange to me with the quotes etc. Are >you commiting your update? I'm more familiar with IBX where I would use >a leaner TIBSQL instead of a TIBQuery for an update or delete. Something >like: > >procedure TForm1.Button1Click(Sender: TObject); >begin > IBTransaction2.StartTransaction; > IBSQL1.SQL.Add('Update Office'); > IBSQL1.SQL.Add('Set OfficeName = ''Wellington'''); > IBSQL1.SQL.Add('Where OfficeID = 9'); > IBSQL1.ExecQuery; > IBTransaction2.Commit; >end; > >HTH, >Craig. > > > >>I am running an update query in code using Delphi 6 and IB Objects. I construct the >>query manually and then run it using ExecSQL. But the update never seems to take >>place >> >>This is my code (where qryTemp is a TIB_Query) >> >>With DataAccessMod.qryTemp do >>Begin >> Active := False; >> SQL.Clear; >> >> // construct the query >> SQL.Add('UPDATE "UserAccounts" SET '); >> SQL.Add('"Credits" = ' + IntToStr(iAmount) + ' '); >> SQL.Add('WHERE "AccountID" = ' + IntToStr(iUserID)); >> >> // run the query >> ExecSQL; >> >> // After doing this I have tried each of the following: (one at a time) >> >> ApplyUpdates; >> >> CommitAction := caRefresh; >> >> CommitAction := caClose; >> >> > > _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi
