Recent Posts

Pages: [1] 2 3 ... 10
1
Noticed SCAN instead of SEARCH using EXPLAIN QUERY PLAN since no parent2 (mother) index

explain query plan  SELECT childtable.mother from childtable,mothertable where MotherTable.Mother = 2;

These are in no way required:
There is no index on child other than the rowed so added 3rd column
There is no mother index so added

Might be good to  explain query plan select ... with different select statements to see if indexes are used.


drop table if exists ChildTable;
drop table if exists FatherTable;
drop table if exists MotherTable;
create table FatherTable(father integer primary key autoincrement);
create table MotherTable(mother integer primary key autoincrement);
create table ChildTable(child integer primary key autoincrement, father Integer, mother Integer, foreign key(Father) references FatherTable(father),foreign key(Mother) references MotherTable(mother));
create index ChildTableFatherIndex on ChildTable(father);
create index ChildTableMotherIndex on ChildTable(mother);
insert into FatherTable values(1);insert into FatherTable values(2);insert into FatherTable values(3);
insert into MotherTable values(1);insert into MotherTable values(2);insert into MotherTable values(3);
insert into ChildTable values(null,1,1);insert into ChildTable values(null,1,1);insert into ChildTable values(3,3,3);
-------------------------------------------------------------------------------------------------------
explain query plan    SELECT child,father,mother from childtable where Child = 2;

SEARCH TABLE childtable USING INTEGER PRIMARY KEY (rowid=?)
-------------------------------------------------------------------------------------------------------

explain query plan    SELECT childtable.mother from childtable,mothertable where MotherTable.Mother = 2;

SEARCH TABLE mothertable USING INTEGER PRIMARY KEY (rowid=?)   
SCAN TABLE childtable USING COVERING INDEX ChildTableMotherIndex
-------------------------------------------------------------------------------------------------------

explain query plan    SELECT childtable.mother from childtable,fathertable where FatherTable.Father = 2;

SEARCH TABLE fathertable USING INTEGER PRIMARY KEY (rowid=?)   
SCAN TABLE childtable USING COVERING INDEX ChildTableMotherIndex

-------------------------------------------------------------------------------------------------------

select * from childtable;
child   father   mother

1   1   1   
2   1   1   
3   3   3
2
You've got Questions? We've got Answers! / Re: 19 = foreign key constraint failed
« Last post by cj on April 24, 2017, 07:36:57 PM »
Return error value in an argument, uses replace statement and an optional trace

%TraceOn = 0
#INCLUDE "sqlitening.inc" 'foreigncc.bas
FUNCTION PBMAIN () AS LONG
 LOCAL result,c1,c2 AS LONG
 slOpen "sample.db3","C"
 slexe "drop table if exists child"     'DROP CHILDREN FIRST REQUIRED
 slexe "drop table if exists parent1"   'DROP PARENTS
 slexe "drop table if exists parent2"   'DROP PARENTS
 slexe "create table parent1(p1 int primary key)"
 slexe "create table parent2(p2 int primary key)"
 slexe "create table child(c1 int primary key, c2, foreign key(c1) REFERENCES parent1(P1), foreign key(c2) REFERENCES parent2(p2))
slexe "insert into parent1 values(1)"
 slexe "insert into parent2 values(2)"
 IF InsertChild(c1,c2,result) THEN ShowResult(c1,c2,result) ELSE ? USING$("Insert (#_,#)",c1,c2),,"Success"
 c1=1
 c2=2
 InsertChild c1,c2,result
 IF result THEN ShowResult(c1,c2,result) ELSE ? USING$("Insert (#_,#)",c1,c2),,"Success"
END FUNCTION

FUNCTION InsertChild(c1 AS LONG, c2 AS LONG,result AS LONG) AS LONG

 LOCAL sql AS STRING
 result = 0 'init required

 sql = "insert into child values(@c1,@c2)"
 REPLACE "@c1" WITH FORMAT$(c1) IN sql
 REPLACE "@c2" WITH FORMAT$(c2) IN sql
 slexe sql,"E0"

 IF slGetChangeCount <> 1 THEN
  sql = "select p1 from parent1 where p1 = @c1"
  REPLACE "@c1" WITH FORMAT$(c1) IN sql
  slSel sql
  IF ISFALSE(slGetRow) THEN result = 1

  sql = "select p2 from parent2 where p2 = @c2"
  REPLACE "@c2" WITH FORMAT$(c2) IN sql
  slSel sql
  IF ISFALSE(slGetRow) THEN result+=2

  sql = "select c1 from child where c1 = @c1 and c2 = @c2"
  REPLACE "@c1" WITH FORMAT$(c1) IN sql
  REPLACE "@c2" WITH FORMAT$(c2) IN sql
  slSel sql
  IF slGetRow THEN result+=4
  FUNCTION = result
 END IF
 IF %TraceOn THEN ShowResult(c1,c2,result)
END FUNCTION

SUB ShowResult(c1 AS LONG,c2 AS LONG,result AS LONG)
 SELECT CASE result
  CASE 0:? USING$("c1=# c2=#",c1,c2),,"Error 0 - Insert Success"
  CASE 1:? USING$("Parent1 (#) not found",c1),,"Error 1"
  CASE 2:? USING$("Parent2 (#) not found",c2),,"Error 2"
  CASE 3:? USING$("Parent1 (#) and Parent2 (#) not found",c1,c2),,"Error 3"
  CASE 4:? USING$("child c1=(#) c2=(#)",c1,c2),,"Duplicate child, Error 4"
  CASE ELSE:? "Error" + STR$(result),,"Untrapped error"
 END SELECT
END SUB
3
You've got Questions? We've got Answers! / Re: 19 = foreign key constraint failed
« Last post by Fim on April 24, 2017, 09:43:08 AM »
Thank you cj.
But to me it's easier to solve it programmatically in PowerBasic.
/Fim W.
4
You've got Questions? We've got Answers! / Re: 19 = foreign key constraint failed
« Last post by Fim on April 24, 2017, 09:19:14 AM »
"SQLite error handling will not provide more detail or explanation.  You will have to analyze the situation yourself to determine the specific cause for the error."
I suspect it was that way.
I probably will not work with foreign keys.
Thanks for the help.
/Fim W.
5
You've got Questions? We've got Answers! / Re: 19 = foreign key constraint failed
« Last post by Bern Ertl on April 24, 2017, 09:05:35 AM »
SQLite error handling will not provide more detail or explanation.  You will have to analyze the situation yourself to determine the specific cause for the error.

There is a potential option for helping to debug the issue if you can't see it by examining the SQL statement and the data in the database.  You could try defining one of the foreign keys as DEFERRED*, wrap your insert within a transaction and test for errors immediately after the insert and then again after the COMMIT.  It should help you figure out which key is causing the error.

* see section 4.2: http://www.sqlite.org/foreignkeys.html
6
You've got Questions? We've got Answers! / Re: 19 = foreign key constraint failed
« Last post by Fim on April 24, 2017, 03:51:44 AM »
#COMPILE EXE "W:\TEST6.EXE"
#COMPILER PBCC
#INCLUDE "C:\POWERBASIC\SQLITENING\INC\SQLITENING.INC"

FUNCTION PBMAIN () AS LONG
    SLSETPROCESSMODS "E0"
    SLOPEN "SAMPLE.DB3","C"
    SLEXE "DROP TABLE IF EXISTS CHILD"  'DROP CHILDREN FIRST REQUIRED
    SLEXE "DROP TABLE IF EXISTS PARENT1" 'DROP PARENTS
    SLEXE "DROP TABLE IF EXISTS PARENT2" 'DROP PARENTS
    SLEXE "CREATE TABLE PARENT1(P1 PRIMARY KEY)"
    SLEXE "CREATE TABLE PARENT2(P2 PRIMARY KEY)"
    SLEXE "CREATE TABLE CHILD (C1 PRIMARY KEY, C2, FOREIGN KEY(C1) REFERENCES PARENT1(P1), FOREIGN KEY(C2) REFERENCES PARENT2(P2))

    SLEXE "INSERT INTO PARENT1  VALUES(1)"

    SLEXE "INSERT INTO CHILD  VALUES(1, 2)"
    PRINT SLGETERROR  '19 = FOREIGN KEY CONSTRAINT FAILED

END FUNCTION
But which foreign key, C1 or C2?

/Fim W.
7
You've got Questions? We've got Answers! / 19 = foreign key constraint failed
« Last post by Fim on April 22, 2017, 05:24:19 AM »
But which foreign key?
Somtimes you have more than one foreign key.

/Fim W
8
Never mind.  SQLiteningProcsT.dll had some functions that required (depended upon) another dll that I failed to include in the distribution.  All is well.
9
I created a distribution of the app that I've been developing for the last few years.  When I test installing the app on a new computer (ie. not my development computer), the first time the client app attempts to call a function in SQLiteningProcsT.Dll (slRunProc "TmyFunc" ...), the slRunProc call is failing on the LoadLibrary call.  LoadLibrary is not finding SQLiteningProcs.T.dll even though it's in the folder where the server app is installed.  Anyone have any ideas on what might be wrong?
10
You've got Questions? We've got Answers! / Re: Null instead of space/0.
« Last post by Paul Squires on April 03, 2017, 07:56:49 PM »
I never use NULL either. I find that it complicates things unnecessarily.
Pages: [1] 2 3 ... 10