sqlite constraint violation console message

[kylomas]

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

[jchd]

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.

[kylomas]

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

[jchd]

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

[kylomas]

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

[jchd]

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

[kylomas]

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

[jchd]

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

[kylomas]

jchd,

Thanks, works perfectly now that sleep and your help has cleared my brain fart.

kylomas