Sign in to follow this  
Followers 0
kylomas

sqlite constraint violation console message

9 posts in this topic

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

Share this post


Link to post
Share on other sites



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 here
RegExp tutorial: enough to get started
PCRE 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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (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 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 here
RegExp tutorial: enough to get started
PCRE 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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#6 ·  Posted (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 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 here
RegExp tutorial: enough to get started
PCRE 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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#8 ·  Posted (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 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 here
RegExp tutorial: enough to get started
PCRE 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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0