kylomas Posted March 29, 2012 Share Posted March 29, 2012 SQLite Experts, IS there some way to suppress console output when a constraint violation (unique) is encountered for an insert operation? Script is NOT compiled and run from SCITE during development. Thanks, kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
jchd Posted March 29, 2012 Share Posted March 29, 2012 Yes, look at the latest version: there is an additional parameter for disabling console output. No, depending on the schema and the operation performed, you may use some clause to suppress the constraint check error. Post more info about schema and offending operation. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
kylomas Posted March 29, 2012 Author Share Posted March 29, 2012 jchd, Thanks for the prompt reply. The error message is! SQLite.au3 Error --> Function: _SQLite_Exec --> Query: insert into PLAYER_BOXES ([PNAME],[POS],[DNP Reason],[GDTE]) values("James Jones",", SF","DNP COACH'S DECISION","2004-01-02"); --> Error: columns PNAME, GDTE are not unique The DDL for the table is CREATE TABLE [PLAYER_BOXES] ( [PNAME] char, [POS] char, [MIN] char, [FGM] INT(2), [FGA] INT(2), [3PM] INT(2), [3PA] INT(2), [FTM] INT(2), [FTA] INT(2), [OREB] INT(2), [DREB] INT(2), [REB] INT(2), [AST] INT(2), [STL] INT(2), [BLK] INT(2), [TOVR] INT(2), [PF] INT(2), [Plus-Minus] INT(2), [PTS] INT(3), [S-B] BOOLEAN, [GDTE] DATE, [DNP Reason] CHAR(50), UNIQUE([PNAME], [GDTE]) ON CONFLICT FAIL); Thanks, kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
jchd Posted March 29, 2012 Share Posted March 29, 2012 (edited) OK so the next question is (beyond the fore-mentionned _SQLite_Open parameter): do you need/want to be informed of a tentative of insertion of a duplicate key? If no, just change the ON CONFLICT FAIL clause into ON CONFLICT IGNORE. That, or remove the on conflict clause, and use INSERT OR IGNORE (or INSERT OR REPLACE). Note that in the last option, SQLite will first delete the existing row and insert the new one. This may make a huge difference if you have foreign key(s) pointing to some ID in the deleted row! In short, you seem to have more options than you thought. Not uncomfortable for once: most of the times it's the other way round. EDIT: please make SQLite (and yourself) a favor by using only single quotes around literal strings. Yet better, _SLite_Fast_Escape() them to avoid pitfalls. Remember that double quotes are used for schema names. While SQLite makes a generally good job at correcting single vs. double quotes (or vice-versa) it isn't failproof and that could easily corrupt the content of your DB silently. Edited March 29, 2012 by jchd This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
kylomas Posted March 29, 2012 Author Share Posted March 29, 2012 jchd, Thanks, I'm an idiot...I knew about the ON CONFLICT IGNORE but forgot about it. Did not know about INSERT OR REPLACE. I've registered on the SQLite Expert forum and the SQLite.org mailing list. Can you recommend a forum for fledgling DB developers? Again, Thanks, kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
jchd Posted March 29, 2012 Share Posted March 29, 2012 (edited) REPLACE is by itself a synonym of INSERT OR REPLACE.I've a good forum reference in French (mostly for Orable, PostGreSQL, ... professional users who disregard SQLite as being a toy). Some very top-notch hints there, like the top 20 bugs in MySQL which make it unusable in serious applications and such.Read the SQLite mailing list (you can access all archives freely here: http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users). There are many subjects of interest and in the long run, you learn a lot about good design, surprising uses, SQL[ite] tricks, aso. OTOH this isn't a low-volume list!SQLite Expert forum is only a support forum with very little audience. I was the first member IIRC when Bogdan asked me to go there, but honestly I never used it. I always talk with Bogdan directly and I look forward his next settlement in France. Edited March 29, 2012 by jchd This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
kylomas Posted March 30, 2012 Author Share Posted March 30, 2012 jchd, Tried _SQLite_Startup ($sprintcallback = "") to no avail. I am still getting console output. I can change my conflict result to ignore but then do not know when a conflict occurs. In the past I've used a "select, if no rows then insert" technique but this seems primitive. I was hoping that the ON CONFLICT IGNORE would set $SQLITE_CONSTRAINT but this is not the case. During each update run I would like to get a count of all good updates and all failed updates with the reason that they failed (uniqueness being one of the reasons). I'm an OS guy with zero apps/DB professional experience, so if I'm looking at this foolishly please say so. Thanks, kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
jchd Posted March 30, 2012 Share Posted March 30, 2012 (edited) You're no setting the correct parameter. Look again at the function definition: Func _SQLite_Startup($sDll_Filename = "", $bUTF8ErrorMsg = False, $bForceLocal = 0, $sPrintCallback = $g_sPrintCallback_SQLite) ; The $sPrintCallback parameter may look strange to assign it to $g_sPrintCallback_SQLite as ; a default. This is done so that $g_sPrintCallback_SQLite can be pre-initialized with the internal ; callback in a single place in case that callback changes. If the user overrides it then ; that value becomes the new default. An empty string will suppress any display. $g_sPrintCallback_SQLite = $sPrintCallback Are you using the latest stable release (or the beta)? Or something much older? Or... are you confusing with some other language and you believe you can name parameters on invokation? Nonono, AutoIt parameters are positional and non-empty... Edited March 30, 2012 by jchd This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
kylomas Posted March 30, 2012 Author Share Posted March 30, 2012 jchd, Thanks, works perfectly now that sleep and your help has cleared my brain fart. kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now