SQL Update Query does nothing, how to preview whats been submitted. lazarus / 16-Databases ---------------------------------------------------------------------- funk247 · 2013-07-29 16:30 Hi I have a form setup using TSQL51Connection, TSQLTransaction & TSQLQuery modules, it pulls back info from the DB without issue but I'm having problems with updates. The DB is pretty large so I'm starting with just the 1st couple of rows, once theyre working ill extend the query. My TSQLQuery.UpdateSQL is as follows Code: [Select]UPDATE table SET ContactFirst = :ContactFirst, ContactSur = :ContactSur WHERE AccountID = :AccountID Then I have a button in my form, the onCLick event contains: Code: [Select]begin     accSelect.Edit;     accSelect.Post;     accSelect.ApplyUpdates;     dbTransaction.CommitRetaining;     sqlbl1.Caption := accSelect.UpdateSQL; end; Clicking the buttons does precisely nothing with the DB, it generates no error messages, just updates the caption on sqlbl1, obviously the values arent being posted to the caption. I tried adding a logging event to the SQLConnection but couldnt work out how to get it generating logs. I also adding a breakpoint and stepping through but that left me none the wiser. Lazarus is a wonderful tool but sometimes it can be a bit obtuse. Does anyone have any suggestions for how to find out what the UpdateSQL is sending? ---------------------------------------------------------------------- taazz · 2013-07-29 16:42 try this http://www.howtogeek.com/howto/database/monitor-all-sql- queries-in-mysql/ and see if any sql command lands in mysql assuming you are using mysql if not please specify the server you are using. ---------------------------------------------------------------------- funk247 · 2013-07-29 17:43 Thanks for the tip, Dev box is turned off now so I'll have a look first thing and let you know how I get on. Just checked and thats for MS SQL, Im using Oracle/Apache but I presume a similar functionality will be present? ---------------------------------------------------------------------- Mike.Cornflake · 2013-07-29 18:54 Can you confirm you've also got the TSQLQuery.SQL property set with a suitable Select statement?  I don't think it'll work if you only have UpdateSQL set. I presume you've got ParseSQL set to false?  If not, then maybe your UpdateSQL is being overwritten? A quick search of the wiki revealed this for your logging query: http://wiki.freepascal.org/SqlDBHowto#Troubleshooting:_TSQLConnection_ logging ---------------------------------------------------------------------- Mike.Cornflake · 2013-07-29 19:02 Just looked at your code again. Code: [Select]    accSelect.Edit;     accSelect.Post;I might be missing something obvious, but you're not making any changes?  Try Code: [Select]    accSelect.Edit;     accSelect.FieldByName('ContactFirst').AsString := 'Fred';     accSelect.Post; ---------------------------------------------------------------------- funk247 · 2013-07-30 10:11 I've added the SQL in the UpdateSQL property of the component, If I use a syntax error then the query fails so I know that bits working. If I can find out whats being passed then I can diagnose the error. I don't think my codes at fault, more likely the query! ---------------------------------------------------------------------- BigChimp · 2013-07-30 10:19 Strange. I was thinking your code is at fault - see e.g. Mike's post. No idea if using .UpdateSQL without a regular .SQL works but if you wnat to experiment with that, no problem. Why don't you try connection logging at the sqldb logging - you said you tried it but it didn't work. What code do you use? What doesn't work (symptoms, error messages etc). Finally: you can certainly monitor all incoming sql statements using Oracle; probably using something like the V$SQL_MONITOR [1] view... which I have never tried though. Some googling should surely give a good result [1] e.g. http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3048.ht m PS: While I'm nitpicking: mysql is not the same as MS SQL Server... ---------------------------------------------------------------------- funk247 · 2013-07-30 10:47 I got it working! Instead of using the UpdateSQL property I created a new query and dropped the code into the SQL property, then called the Parameters in my button click code and used ExecSQL to carry out the instruction. Works perfectly! But feels like a bit of a bodge since I suspect Lazarus should be able to generate the query strings automatically if everythings setup correctly. Heres the code; Code: [Select]procedure TForm1.saveRecordBttnClick(Sender: TObject); begin     try         accUpdate.Close;         accUpdate.Params.ParamByName('ContactFirst').Value := docsEdit1.Text;         accUpdate.Params.ParamByName('ContactSur').Value := docsEdit2.Text;         accUpdate.Params.ParamByName('AccountID').Value := selected;         accUpdate.ExecSQL;     except on e: Exception do         ShowMessage(e.Message);     end; end; ---------------------------------------------------------------------- taazz · 2013-07-30 11:13 For this to work automatically SQLDB (to put it simple) must have a working connection in the server and the dataset must keep track of changes on the data. If those criteria are met then you probably don't have to define your update sql as well but this works in the most basic sql commands where the editing done is from a single table if it has multiple tables like customer main data and customer details at the same time then you need the manual updates you have just written within a transaction most probable. Also keep in mind if you do not use the automatic data management of the dataset then you must take responsibility for the update as well. That means that the dataset that has the data that are shown on the screen needs a manual requery call to refresh the data from the server. ---------------------------------------------------------------------- BigChimp · 2013-07-30 11:26 Perhaps go through these http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1 http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2 ... those show how to set up Lazarus to do things as "automated" as possible. ---------------------------------------------------------------------- funk247 · 2013-07-30 15:28 I've spent days going through the Wiki! Ill definitely have viewed both those tutorials, taazz mentioned that if the database has multiple tables then the automatic update won't work, I am using LEFT JOIN to link multiple tables together but thought I had followed the correct conventions to help Lazarus pick out the focal table. Anyway its working now so moving onto the next thing... how can I create a docx file? I've managed to create text files and doc files, but not docx, is it something Lazarus supports out of the box so to speak? ---------------------------------------------------------------------- BigChimp · 2013-07-30 15:33 It never hurts to ask though... perhaps asking smaller questions could have gotten you to a good result earlier... but who knows. Glad it's working! Quote from: funk247 on July 30, 2013, 05:28:05 pmAnyway its working now so moving onto the next thing... how can I create a docx file? I've managed to create text files and doc files, but not docx, is it something Lazarus supports out of the box so to speak? Please open a new thread for a new question in the appropriate subforum. Keeps things organized and lets interested people (drawn by the topic title) take a look. That said: I don't know of any way except 1. write it yourself. 2. get MS Word (or perhaps LibreOffice/OpenOffice) to write it using automation: http://wiki.lazarus.freepascal.org/Office_Automation#Using_COM_Automat ion_to_interact_with_OpenOffice_and_Microsoft_Office Perhaps somebody else has other solutions...