Recent

Author Topic: Conceptual issues with TSQLQuery  (Read 5498 times)

iru

  • Sr. Member
  • ****
  • Posts: 321
Conceptual issues with TSQLQuery
« on: March 11, 2015, 04:18:29 am »
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

  • Hero Member
  • *****
  • Posts: 5368
Re: Conceptual issues with TSQLQuery
« Reply #1 on: March 11, 2015, 04:27:59 am »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Conceptual issues with TSQLQuery
« Reply #2 on: March 11, 2015, 04:48:54 am »
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

  • Hero Member
  • *****
  • Posts: 5368
Re: Conceptual issues with TSQLQuery
« Reply #3 on: March 11, 2015, 05:48:37 am »
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
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Conceptual issues with TSQLQuery
« Reply #4 on: March 11, 2015, 09:04:40 am »
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.
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

  • Sr. Member
  • ****
  • Posts: 321
Re: Conceptual issues with TSQLQuery
« Reply #5 on: March 11, 2015, 10:35:40 am »
Gentlefolk,

Thanks for the responses, I will try a few things.

Ian

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Conceptual issues with TSQLQuery
« Reply #6 on: March 12, 2015, 11:22:37 am »
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

  • Hero Member
  • *****
  • Posts: 8746
  • Programming + Glam Metal + Tae Kwon Do = Me

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Conceptual issues with TSQLQuery
« Reply #8 on: March 19, 2015, 11:33:27 am »
Gentlefolk,

I have read the link: http://wiki.freepascal.org/ Working_With_TSQLQuery#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

  • Hero Member
  • *****
  • Posts: 5368
Re: Conceptual issues with TSQLQuery
« Reply #9 on: March 19, 2015, 12:38:56 pm »
Gentlefolk,

I have read the link: http://wiki.freepascal.org/ Working_With_TSQLQuery#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
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Conceptual issues with TSQLQuery
« Reply #10 on: March 19, 2015, 10:08:59 pm »
Thank you taazz,Ian

 

TinyPortal © 2005-2018