[SOLVED] SQLdb Tutorial2 - Problem with committing updated dbgrid fields? lazarus / 16-Databases ---------------------------------------------------------------------- WizardNo.7 · 2014-03-13 14:57 Hi all,  I was following the tutorial http://wiki.freepascal.org/SQLdb_Tutorial2#Editing and trying to replicate the below code: Code: [Select]begin     SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...     SQLTransaction1.Commit; //... and commit them using the transaction.     //SQLTransaction1.Active now is false end; My code: Code: [Select]Begin     Sqlquery1.ApplyUpdates;     Sqltransaction1.Commit;     dbgDictionary.ReadOnly := End; But I keep getting an EDatabseError and EUpdateError(Attached images) when trying to save/update my changes?  Any help will be greatly appreciated! ---------------------------------------------------------------------- BigChimp · 2014-03-13 15:07 My guess is you're not exactly following the tutorial but have your own tables and queries? Am I right? The error message seems to indicate your select sql is either not ok or too difficult for the built in parser to convert to UpdateSQL. You can write your own UpdateSQL http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#TSQLQuery.In sertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_P arameters ---------------------------------------------------------------------- WizardNo.7 · 2014-03-13 15:13 BigChimp I am indeed using my own table and query yes, although it's rather basic and only has two fields haha.  I'll have a look at the link you posted as soon as I can. However even though I am using my own table/database everything is every bit as simple as in the tutorial. What may be the reason for mine acting up?  Thanks anyway! ---------------------------------------------------------------------- BigChimp · 2014-03-13 15:30 Post the query pls, also pls post the Lazarus version and operating system (or you can put it in your forum signature, makes it even easier)... ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 05:52 Okay, I've updated my signature to include my OS and Lazarus details. With QuotePost the query pls,what exactly do you mean? Which part of the query are you referring to? Sorry. ---------------------------------------------------------------------- BigChimp · 2014-03-14 06:11 The test of your SELECT query, i.e. the query's SQL property... ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 06:35 Okay, sorry, I just wanted to confirm so I don't give useless info... So basically what I do is, I have a form with aRadioGroup, DBGrid and a TEdit... Whenever something is typed into the TEdit it immediately starts filtering the DBGrid's data for matching text in the column you specified with the RadioGroup... Code: [Select]Procedure Tfrmlatinlawdictionary.Dynamicinput; Begin     CASE Rgpinputlanguage.ItemIndex OF         0 : Begin                 Sqlquery1.Close;                 Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE LATIN SIMILAR TO ''%'                                        + Edtliveinput.Text + '%''';                 Sqlquery1.Open;             End;         1 : Begin                 Sqlquery1.Close;                 Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE AFRIKAANS SIMILAR TO ''%'                                        + Edtliveinput.Text + '%''';                 Sqlquery1.Open;             End;     End; End; You can then read through the results in the DBGrid... When you want to edit a record(s) you press and EDIT button: Code: [Select]Procedure Tfrmlatinlawdictionary.Btneditrecordclick(Sender : Tobject); Begin     dbgDictionary.ReadOnly := False;     btnEditRecord.Enabled := False;     btnEditRecord.Visible := False;     btnSaveRecord.Visible := True;     btnSaveRecord.Enabled := True; End; And basically it just turns ReadOnly for the DBGrid off... When you want to commit the changes I follow the mentioned tutorial: Code: [Select]Procedure Tfrmlatinlawdictionary.Btnsaverecordclick(Sender : Tobject); Begin     IF MessageDlg('Save Changes', 'Are you sure you want to save changes?',                   mtConfirmation, [mbYes, mbNo],0) = mrYes         THEN             SaveChanges //Procedure that commits the changes         ELSE             Begin                 dbgDictionary.ReadOnly := True;                 Sqlquery1.Close; // Reset the data in the DBGrid so that                 Sqlquery1.Open; // unsaved changes are then not shown                 btnSaveRecord.Visible := False;                 btnSaveRecord.Enabled := False;                 btnEditRecord.Enabled := True;                 btnEditRecord.Visible := True;             End; End; And runs the SaveChanges procedure: Code: [Select]Procedure Tfrmlatinlawdictionary.Savechanges; Begin     IF Sqltransaction1.Active then // I have NO Try - Except because I want to find out why I get this error...         Begin              Sqlquery1.ApplyUpdates; // This is from the Tutorial and also where my problems start              Sqltransaction1.Commit;  // I have also tried adding Sqlquery1.Post as per another              dbgDictionary.ReadOnly := True; // post but it did not work...         End; End; I hope this is enough info... Thanks for the help! ---------------------------------------------------------------------- BigChimp · 2014-03-14 07:02 Ave/more more Wizard Yep, your select queries are too complicate for the simple parser in sqldb to understand [1] Code: [Select]Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE AFRIKAANS SIMILAR TO ''%'  + Edtliveinput.Text +  '%'''; It's SIMILAR TO that throws a spanner in the works. Try manually specifying the query's updatesql property to something like (untested): Code: [Select]Sqlquery1.UpdateSQL.Text := 'update DICTIONARY SET AFRIKAANS:=AFRIKAANS, LATIN:=LATIN where AFRIKAANS:=:OLD_AFRIKAANS' //or possibly OLD_LATIN, depending on what gets edited... => this is why you need a primary key, see below  '%'''; which will probably net work therefore... .. I'd suggest adding a primary autoincrement/generator key in any case (conventially called ID)... that will then give something like this: Code: [Select]Sqlquery1.SQL.Text := 'SELECT ID,AFRIKAANS,LATIN FROM DICTIONARY WHERE AFRIKAANS SIMILAR TO ''%'  + Edtliveinput.Text +  '%'''; Code: [Select]// I think this should be it, but less sure about this one... Sqlquery1.UpdateSQL.Text := 'update DICTIONARY SET AFRIKAANS:=AFRIKAANS, LATIN:=LATIN where ID:=:OLD_ID'  '%'''; [1] Actually, writing a perfect SQL parser is just about impossible so sqldb takes the approach of having a fairly simple parser that covers say 90% of the cases for SELECT queries and lets the user deal with the rest. Groete, GrootAap ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 07:36 Thanks BigChimp! Okay, so I initially had an ID field in my DB(Firebird and only recently started puzzling that out too...) So I use FlameRobin to interface with Firebird... I'll add a field ID to my current table, only one question: Can you maybe instruct me the correct way to insert this ID column with the AutoGenerate/PrimaryKey function with FlameRobin? Also, I already have 3 records in this table... so will they also be autopopulated? I'm totally n00b with Firebird and FlameRobin and really want to learn how to use it correctly...  And since you're such a big help I think I'll be better off asking you than I am by following stuff I searched(didn't work for me...) Thanks! ---------------------------------------------------------------------- BigChimp · 2014-03-14 07:52 I'll probably repeat the same stuff the other sites told you to... off the top of my head, in Flamerobin: 1. Add a column called ID, integer. Commit the change 2. do a select * from dictionary; you should see id,afrikaans,latin and the data in the grid. Edit the id fields to read 1,2,3... etc. Click on the commit button (F5, checkmark) to commit these changes to the db 3. change the ID column, tick the Not null field, commit 4. in constraints, add primary key, fill out, commit. Now you have a primary key but it is not yet autogenerated. For that you'll need a generator (=sequence) and trigger see e.g. http://www.firebirdfaq.org/faq29/ once again, haven't tested this: generator: Code: [Select]CREATE GENERATOR gen_dictionary_id; SET GENERATOR gen_dictionary_id TO 0; Now create the trigger, have it look like: Code: [Select]set term !! ; CREATE TRIGGER TRIG_dictionary_BI FOR dictionary ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (NEW.ID is NULL) then NEW.ID = GEN_ID(gen_dictionary_id, 1); END!! set term ; !! Now each insert that has a NULL value for ID should still work... ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 08:53 Thanks! Although it's similar, your detailed instructions were very helpful! I now have a ID column that I tested and saw that it does increment when new lines are inserted. NOW, in the SaveChanges procedure: Code: [Select]Sqlquery1.UpdateSQL.Text := 'update DICTIONARY SET AFRIKAANS:=AFRIKAANS,' +                                 ' LATIN:=LATIN where ID:=:OLD_ID''%'''; Sqlquery1.ApplyUpdates; Sqltransaction1.Commit; dbgDictionary.ReadOnly := True;    Is this the correct usage of that statement? I noticed that it just updates where "ID:=:OLD_ID" which means I don't need to test for which column (Afrikaans/Latin) it was... I get a "Operation cannot be performed on an active dataset" error message ---------------------------------------------------------------------- BigChimp · 2014-03-14 09:06 I'd think this: Code: [Select]Sqlquery1.UpdateSQL.Text := 'update DICTIONARY SET AFRIKAANS:=AFRIKAANS,' +                                 ' LATIN:=LATIN where ID:=:OLD_ID''%'''; should be something like this: Code: [Select]Sqlquery1.UpdateSQL.Text := 'update DICTIONARY SET AFRIKAANS:=AFRIKAANS,' +                                 ' LATIN:=LATIN where ID:=:OLD_ID '; QuoteI noticed that it just updates where "ID:=:OLD_ID" which means I don't need to test for which column (Afrikaans/Latin) it was... Yes, correct. QuoteI get a "Operation cannot be performed on an active dataset" error message   As that Sqlquery1.UpdateSQL.Text  won't change, you can put it in the formcreate event, or just set it in the object inspector yourself... so probably before the query is active/opened ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 09:28 Okay I noticed I left a bit of code in there haha... It's gone now... SaveChanges : Code: [Select]Sqlquery1.ApplyUpdates; Sqltransaction1.Commit; dbgDictionary.ReadOnly := True;    And in my SQLQuery's UpdateSQL field in the Object Inspector: Code: [Select]UPDATE DICTIONARY SET AFRIKAANS:=AFRIKAANS, LATIN:=LATIN WHERE ID:=:OLD_ID Following Errors(Attached Images)  I'm starting too look like I don't know shit about programming  Thanks for all the help BigChimp! ---------------------------------------------------------------------- BigChimp · 2014-03-14 09:34 Quote from: WizardNo.7 on March 14, 2014, 10:28:36 amCode: [Select]UPDATE DICTIONARY SET AFRIKAANS:=AFRIKAANS, LATIN:=LATIN WHERE ID:=:OLD_ID AAAAAAAAAAAAAAARGGGHGH.. didn't catch that the previous time. Edit: actually, I made that mistake myself. Blegh. Remove the : in :=... and move them to the proper positions. Code: [Select]UPDATE DICTIONARY SET AFRIKAANS=:AFRIKAANS, LATIN=:LATIN WHERE ID=:OLD_ID You're saying SET AFRIKAANS = :AFRIKAANS Pascal, SQL <> Pascal, repeat 1000x Tip: when you see error messages like this, paste the SQL in a firebird query window and try to run it... often you'll get the exact location of the error (it's actually in the error message screenshot you pasted, but you have to count it out etc). Of course, mind what you're actually running; best to have a backup of your db in advance etc Quote from: WizardNo.7 on March 14, 2014, 10:28:36 amI'm starting too look like I don't know shit about programming  Thanks for all the help BigChimp! Well, so am I, but I'm not ashamed of it - I just keep trying until it works No problem... ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 09:51 HEY! You fixed it!  Hahah Thanks a lot! I sure learned some new things here... Your help is greatly appreciated BigChimp! Attached is a picture for your viewing pleasure ---------------------------------------------------------------------- BigChimp · 2014-03-14 09:58 Glad it works PS: Interested in porting this to Lazarus/FreePascal http://archives.nd.edu/whitaker/words.htm ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 10:06 Do you mean to say YOU are interested in porting this to Lazarus/FreePascal OR that I may be interested? Either way it seems like a sound idea!(Although just confirm with me what you meant ) If it's the first one, I'd be glad to help you(or try for what it matters...) ---------------------------------------------------------------------- BigChimp · 2014-03-14 10:48 Well, it's on my to do list of fun projects (think of that what you will).... but very low down & I'll probably not get to it. So I'm trying to subvert others get other people interested (Though I'd be glad to help, offer suggestions etc... the wimpy way of saying you won't put too much effort something but still would like the result, I know) It's written in ADA; there's already a Java port: https://sourceforge.net/projects/jwordslatin/?source=directory ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 11:15 Sounds like a fun(and something to improve my skills) project! I'll look into it and then get back to you! I was wondering though if you might maybe know of a way I can implement this: Code: [Select]IF MessageDlg('Save Changes', 'Are you sure you want to save changes?',                   mtConfirmation, [mbYes, mbNo],0) = mrYes         THEN             SaveChanges         ELSE             ReloadDBGrid; into an InputBox statement to see if the user has for example chosen mrCancel instead of mrOK ? I tried: Code: [Select]IF LatinInput := InputBox('LATIN', 'Latin word or phrase:',                               'Word OR Phrase') = mrCancel         THEN             Showmessage('Cancelled')         ELSE             Showmessage('Succeeded'); To see if it would work but as I expected it did not... Any views or hints? Thanks! ---------------------------------------------------------------------- BigChimp · 2014-03-14 11:23 Yep, hover your mouse over the inputbox word and see what kind of results it should return. Or rightclick, find declaration, then you'll see the function signature, go to the return value and find declaration again. Or click on the inputbox word, press f1 and see if there is context- sensitive help Note: not a GUI guru at all & haven't looked, but I suspect inputbox just returns a string... which obviously won't work. For this kind of thing a neater solution would be: 1. another dialog control that combines inputbox and ok/cancel buttons. maybe it exists, maybe not 2. a modal form with textbox, yes/no buttons etc, basically simulating 1. I'd suggest opening a new thread for this new question in the relevant subsection (programming/lcl?) so the guys who know this stuff much better than I can answer... ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 12:04 Ah don't worry... I came right with InputQuery... Just didn't want too throw away what I knew but it's not too bad haha! Thanks anyway! ---------------------------------------------------------------------- BigChimp · 2014-03-14 12:11 Bly jy't reggekom (Glad it works) ---------------------------------------------------------------------- WizardNo.7 · 2014-03-14 12:12 Okay, new problem (DB Related...) With my DynamicUpdate procedure(whenever something is typed into the tEdit component) it runs a query for every KeyUp event... Code: [Select]CASE Rgpinputlanguage.ItemIndex OF         0 : Begin                 Sqlquery1.Close;                 Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE LATIN' +                                       ' SIMILAR TO ''%' + Edtliveinput.Text +                                       '%''';                 Sqlquery1.Open;             End;         1 : Begin                 Sqlquery1.Close;                 Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE AFRIKAANS'                                       + ' SIMILAR TO ''%' + Edtliveinput.Text +                                       '%''';                 Sqlquery1.Open;             End;     End; I've noticed that whenever I type a "(" ")" or "*" character I get a DB Error(Attached images) What to do? ---------------------------------------------------------------------- BigChimp · 2014-03-14 13:38 Please open a new thread for new questions - makes it much clearer and may attract other people to answer as well, thanks...