More SQL(ite) questions - Exceptions lazarus / 16-Databases ---------------------------------------------------------------------- Pascaluvr · 2012-04-03 11:01 OK, I'm doing well with my progress into SQL, I have defined and populated a table with real data.  Out of interest, I ran the 'populate' routine a second time and it quite correctly told me there was an EDataBaseError that a column was not unique.  Happy with that, so no problem. Now the questions: I have try   sqlconnect.ExecuteDirect('INSERT INTO myTable ..... EXCEPT   On E: Exception do     ShowMessage(.................. end; 1) The debugger reported the problem as happening on the ExecuteDirect statement - not by my ON E: Exception ..... ShowMessage. How do I trap the exception in my EXCEPT clause and isolate the actual condition - Column not unique? My existing application (based on an Isam file) simply replaces the record: IsamResult := AddRecord(................ If IsamResult <> IsamOK then   ReplaceRecord(............. How do I do this with SQL? Is it best to test for the Record(errr... Row) existence first ... or is it acceptable to blindly Add a row and then handle the exception?  (I obviously only have one Primary key, but I do have 2 fields that are defined as UNIQUE). 2) Since I have 2 fields that are UNIQUE, but only one is a PRIMARY KEY, can I assume that there is still an index (secondary index) on the second UNIQUE field? 3) Finally, what are the advantages or disadvantages of using ExecuteDirect (vs) sqlQuery? Thanks in advance. ---------------------------------------------------------------------- BigChimp · 2012-04-03 11:18 1) You're catching a general exception (On E: Exception). There is also an sqldb level exception, and probably a sqlite level exception. This will give you more info (better error message, maybe an error code etc). To see an example of how this is done with Firebird, see the wiki article SQLDB Tutorial1. And the debugger breaking on the exception is normal... I think you can tell it to continue. 1b) Replacing instead of inserting: look up the UPDATE statement. You'll have to specify a WHERE clause that uniquely identifies your record. 2) I suppose so. Don't know how sqlite would enforce uniqueness otherwise... Perhaps more detailed error messages from 1) will help you here. 3) You mean ExecuteDirect vs SQLQuery.Execute? Dunno. Maybe that it's one less extra object to work with? ---------------------------------------------------------------------- ludob · 2012-04-03 12:02 1) debuggers are first line exception handlers and will catch the exception before anything else. Continue the program and the showmessage will give you the error message from sqlite. 1b) sqlite has the INSERT OR REPLACE INTO statement for this: http://sqlite.org/lang_insert.html . It also accepts the shorter REPLACE INTO statement (MySQL compatibility) 2) Yes UNIQUE constraints are implemented internally in SQLite as an index. 3) ExecuteDirect: no TSQLQuery object to create but no support for parameters and obviously no result set. It skips also the prepare stage. ---------------------------------------------------------------------- Pascaluvr · 2012-04-04 06:21 Thanks for the great replies. "1) debuggers are first line exception handlers and will catch the exception before anything else. Continue the program and the showmessage will give you the error message from sqlite." Thank you.  I would have thought that an exception handler was first in line - but never mind, since I now know, no big deal. "1b) Replacing instead of inserting: look up the UPDATE statement. You'll have to specify a WHERE clause that uniquely identifies your record."  That fixed that issue, but REPLACE will not always be the option.  I will worry about that when I get to it. "2) Yes UNIQUE constraints are implemented internally in SQLite as an index." That makes sense 3) Finally, what are the advantages or disadvantages of using ExecuteDirect (vs) sqlQuery? I think I found it.  While I can store data in my database. I haven't found a way to get my data back to my program.  There must be a way, since it can be done in FPC/Laz ...   but I can't find it.  (the old assembler programmer in me likes to know how things happen) Thanks for all the help guys, truly appreciated. ---------------------------------------------------------------------- BigChimp · 2012-04-04 06:45 Quote from: Pascaluvr on April 04, 2012, 08:21:44 amThanks for the great replies. Always nice to answer intelligent, well-defined questions.. Quote from: Pascaluvr on April 04, 2012, 08:21:44 am"1) debuggers are first line exception handlers and will catch the exception before anything else. Continue the program and the showmessage will give you the error message from sqlite." Thank you.  I would have thought that an exception handler was first in line - but never mind, since I now know, no big deal. I think normally that would be the case... but suppose you want to debug your exception handling? Also, Ludo probably know much more about this, but I suppose the debugger inserts itself into the Operating System exception handling chain.. this way it works on various higher level languages without the debugger needing to be aware of the specifics. I'm sure he'll correct me if I'm wrong Quote from: Pascaluvr on April 04, 2012, 08:21:44 am"1b) Replacing instead of inserting: look up the UPDATE statement. You'll have to specify a WHERE clause that uniquely identifies your record." That fixed that issue, but REPLACE will not always be the option.  I will worry about that when I get to it. Ok. Didn't know about "INSERT OR REPLACE INTO" in sqlite, just FYI:  MERGE is the ISO SQL standard way to do INSERT OR UPDATE, e.g.: http://en.wikipedia.org/wiki/Merge_%28SQL%29 Unfortunately not every db producer follows that standard (quick look through sqlite docs didn't indicate they follow it either)... but using the standard syntax if available will increase portability of your code across databases. Quote from: Pascaluvr on April 04, 2012, 08:21:44 am3) Finally, what are the advantages or disadvantages of using ExecuteDirect (vs) sqlQuery? I think I found it.  While I can store data in my database. I haven't found a way to get my data back to my program.  There must be a way, since it can be done in FPC/Laz ...   but I can't find it.  (the old assembler programmer in me likes to know how things happen) Assuming you're writing a GUI/Lazarus program, not a console program. Have a look at e.g. http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1 (I keep bringing it up because I managed to extend it a bit). It shows how TSQLQueries interact with the database and the GUI components. As Ludo indicated in another thread, you could do it various ways: Using the RAD/data (TDBEdit etc) bound controls with a TSQLQuery. See http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery for details.Use regular controls; load and save data yourself by issuing SELECT/INSERT/UPDATE/MERGE/INSERT OR REPLACE INTO etc SQL statements. You should use .ExecuteDirect or SQLQuery.Execute for the "action" queries that don't return any data. If a query (e.g. SELECT...) returns any data, you'd use a SQLQuery object, and parse the returning dataset. There are some examples in FPC that show that Edit: I've started some more documentation on the interaction between the various database objects: http://wiki.lazarus.freepascal.org/SQLdb_Programming_Reference Corrections/additions welcome... ---------------------------------------------------------------------- Pascaluvr · 2012-04-04 16:46 "Always nice to answer intelligent, well-defined questions." Well thanks....  but I gotta say when I read back over my questions a few days later I feel like an idiot  Thank you for those wiki pages ( I didn't realise you were the author) - I have referred to them often. As a newbie I don't have much I can suggest as changes/additions except: I know this is mentioned, but it needs to be highlighted ....  if the command is 'SELECT ........ ', you only only need SQLQuery.Open, but for other things it seems (still learning, so forgive if wrong) you need SQLQuery.Execute.  This totally screwed me up in the initial stages of my learning.  (Now that I have had 1 week's experience I am an expert On a side issue, for my own application that I am working on, I have created a 'SQL Debug Unit' that all calls to SQL go through, either ExecuteDirect or sqlQuery.  All commands are logged, and if a 'select...' is the command, the result table is also displayed in a TMemo.  (I didn't bother with a DBgrid, it is just for debugging).  Exceptions are recorded, plus I have the standard 'feature' of issuing a command directly.  This has been invaluable to me during the learning process.  Once I get further through I will make this unit available ...  but ...  people will have to accept my own weird coding conventions:) Again ...  thanks for all the help I have received here ... I probably would have just given up without it. ---------------------------------------------------------------------- chrnobel · 2012-04-05 21:13 In my experience I would not use ExecuteDirect. Instead I have made two standard procedures I use, depending on whether I am doing a select or an update: Code: [Select]procedure executesql; begin   try   SQLite3Connection1.Open;   DataSource1.DataSet.Close;   SQLQuery1.SQL:=sqlsentence;   SQLQuery1.Active:=true;   DataSource1.DataSet.Open;   DataSource1.DataSet.First;   except     on  E: Exception do       //set a flag, make a popup or what you like   end; end; procedure updatesql; begin   try     SQLite3Connection1.Open;     SQLQuery1.Active:=false;     SQLQuery1.SQL:=sqlsentence;     SQLQuery1.ExecSQL;     SQLTransaction1.Commit;     except       on E: Exception do         //set a flag, make a popup or what you like   end; end;  This way I always prepare my sqlsentence first, and the call the needed procedure - and remember to make the commitment. Also I you plan to do some multiuser programming (SQLite can be used for that, if not used to heavily - actually the new WAL mode works quite good), it can be a good idea to release the connection: Code: [Select]procedure releasesql; begin   DataSource1.DataSet.Close;   SQLQuery1.Active:=false;   SQLite3Connection1.Close; end; ---------------------------------------------------------------------- Pascaluvr · 2012-04-06 13:19 hi chrnobl, I like that and my code is evolving to much the same.  What I have different to you is that I only have one Procedure.  If the FIRST word is 'SELECT' then ....  ELSE  ......  This way I have only one procedure to call regardless. Is there other keywords I should include with 'Select'? ---------------------------------------------------------------------- ludob · 2012-04-06 13:51 QuoteIs there other keywords I should include with 'Select'?'Pragma' (http://www.sqlite.org/pragma.html) used amongst others to get db metadata.