SQLite Forum

select match though row does not exists

select match though row does not exists

(1) By agraeper on 2020-11-04 10:15:37 [link]

building e(etage/floor). axis x-y where x is int and y char 1-A .. 12-I 

table axis ( 
 r integer primary key -- is rowid 
 e integer
 x integer 
 y varchar    -- i tried 'char' and 'text' before 

i check for (0,1,"A"). does not exists and i insert. check again. ok rowid=1
i check for (0,1,"B"). does not exists and i insert. check again. ok rowid=2 
i check for (0,1,"C") and it finds rowid 1 and 2. so (*,*,C) is not inserted. 

i check for (*,*,"x") with x unequal C works. for all e,x the same. 

now, i use integer for y too and use something like i=ord(c) c=chr(i) to convert,
but i like to know.

thanks in advance,

(2) By Gunter Hick (gunter_hick) on 2020-11-04 11:25:37 in reply to 1

please show the SQL you are executing and its results. It is not clear what you mean by "check".

Changing the declared type of y is unlikely to make a difference, especially as all your attempts are interpreted exactly the same way.

(4) By agraeper on 2020-11-04 12:49:34 in reply to 2 [link]

now: ( int = c - 'A' not precisely what ord() chr() would do. merely the 'A'-offset)
snprintf(q,127,"select x from achse where e=%d and i=%d and c=%d;",e,i,ORD(c));
this works. 

snprintf(q,127,"select x from achse where e=%d and i=%d and c=\"%c\";",e,i,c);   // int e,i; char c;

check (select x from t where .. and .. and ..;) : 
 returning the last rowid (>0) if there is at least one match. 
 otherwise returning 0

the select finds all (e,i,*) if c is "C".

xubuntu 18.04 ( and debian 10.3, sqlite3-version i cannot check this moment ) 
sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

new problem : 

sqlite3 x.db 
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .schema kostenstelle
CREATE TABLE kostenstelle (
  x integer primary key -- 
, k integer unique not null --
sqlite> select * from kostenstelle ;

// c-output 
query 'select x from kostenstelle where k=456;'
error (17/database schema has changed) 'no such column: x'
query 'insert into kostenstelle(k) values(456);'
error (19/constraint failed) 'UNIQUE constraint failed: kostenstelle.k'

obviously columns x,k exists and there is an entry.

error-message contains 
 result of sqlite3_exec() and
 sqlite3_errstr(result) and 

new question: when i inside one c-funktion open,exec,close the most thing works fine. when i use three functions : h=xopen() returning a handle and xexec(h), xclose(h) using it, then inside xexec() tables cannot be found. is there a difference ? 
i guess, someone has installed different versions, builds from source. i tried to uninstall everything and install debian-packages again. but still the same error. 


(5) By Gunter Hick (gunter_hick) on 2020-11-04 13:43:50 in reply to 4 [link]

You are misusing quotes. SQLite uses single quotes for string literals and double quotes for identifiers (sometimes, if there is no identifier, SQLite will assume you misused the quotes and silently accept a string literal in double quotes). The condition c="C" compares field c (case insensitive names) with itself, which is always true.

The "new" probem is interference from several connections to the same database file. Apparently the schema has been changed (column x renamed) between preparing and executing the select statement. Your error handling is questionable, as getting "no such column:x" is not indicative of a successful result but you are inserting the value anyway, which correctly results in a constraint failure.

Your new question is probably caused by not passing the database handle correctly between functions.

(7) By agraeper on 2020-11-05 13:30:27 in reply to 5 [link]

the first problem c="C" is gone now. c='C' works fine. thanks! 

but the others still resist. 

i have only one process/no threads and i check the db-file before and after and check everything i try in c with sqlite3-client before.

when i use exec() to select and use the void*udata (first argument to callback) to return the rowid (if there is a match) then it works. 

but prepare()/v2/v3 returns with error 

select x from t where .. ;  -> result=17 schema changed. no column 'x'
select * from t where .. ;  -> result= 1 no tables specified 

i have dropped all 'x integer primary key' and use rowid directly
select rowid from t where .. ;  -> result=17 schema changed. no column 'r'

(8) By Gunter Hick (gunter_hick) on 2020-11-05 15:10:38 in reply to 7 [link]

Changing your schema is highly unlikely to fix your broken C code; neither is describing your code as opposed to posting the parts that don't work.

(9) By agraeper on 2020-11-05 16:23:18 in reply to 8 [link]

i take everyone`s advice seriously .. all my life.
ok. i understand this as an invitation to post my code. hope, i am not totally wrong. 

what i do is: (T task) 
 T1 check wether date/row exist in db 
 T2 if not, insert (i could get rowid with lastinserted here) 
 T3 check again and get rowid (redundant, could have been done in T2) 

 two way to get a task done 
  A exec() 
  B prepare() step()+ finalize() 

 (1) T1:A,T2:A,T3:A        this works 
 (2) T1:B,T2:A,T3:B        this (B) does not work 

 in c-code merely 
 # if 0       <- this 0 has to be exchanged by 1 (i.e.) 
  .. (1) .. good case
 # else
  .. (2) .. bad case 
 # endif 


# include <stdio.h>
# include <stdlib.h>
# include <sqlite3.h>

# define os(s)   printf("%s\n",s) 
# define oe(s,e) printf("error (%s) %d/%s %s\n",s,e,sqlite3_errstr(e),sqlite3_errmsg(h))

int c(void*u,int n,char **a,char **b){ int i;
 for(i=0;i<n;++i){ printf(" %d %s %s\n",i,*(a+i),*(b+i)); if(u){ (*(long int *)u)=atoi(*(a+0)); } }
 return 0; // <>0 -> error 

// sqlite3_prepare   ()
// sqlite3_prepare_v2()
// sqlite3_prepare_v3()
long int t(sqlite3*h,char const *q){ sqlite3_stmt*s=0; char const *q0; int e,x; long int r=-1;
 if(SQLITE_OK==(e=sqlite3_prepare_v2(h,q,sizeof(q),&s,&q0))){ r=0;
    case SQLITE_BUSY   : os("bsy"); oe("step",e); x=0; break;
    case SQLITE_DONE   : os("don"); oe("step",e); x=0; break;
    case SQLITE_ERROR  : os("err"); oe("step",e); x=0; break;
    case SQLITE_MISUSE : os("mis"); oe("step",e); x=0; break;
    case SQLITE_ROW    : os("row"); r=sqlite3_column_int64(s,0); break;
 } else { oe("prepare",e); }
 return r;

# define E 0
# define M 0
# define R 1 
int main() { sqlite3*h=0; char q0[128],q1[128],q2[128]; char*m=0; int e; long int r;

 if((SQLITE_OK==sqlite3_initialize()) && (SQLITE_OK==sqlite3_open("x.db",&h))){

  snprintf(q0,127,"select rowid from raum;");
  snprintf(q1,127,"select rowid from raum where e=%d and m=%d and r=%d;",E,M,R);
  snprintf(q2,127,"insert into raum values(%d,%d,%d);",E,M,R);

  # if 0 
  // test with exec() only. THIS WORKS  
  } else if(r<=0) {
   } else if(SQLITE_OK!=(e=sqlite3_exec(h,q1,c,&r,&m))){
   } else if(r<=0){
   } else { printf("j2 r=%ld\n",r); }
  }  else { printf("j1 r=%ld\n",r); }
  # else
  // test with prepare/step/finalize inside t().  THIS DOES NOT WORK 
   } else if((r=t(h,q1))<=0){ printf("ne\n");
   } else { printf("j2 %ld\n",r); } // exists after insert 
  }  else { printf("j1 %ld\n",r); } // already exists

  # endif 
  sqlite3_close(h); sqlite3_shutdown(); } return 0;

thanks in advance, 

(10) By Gunter Hick (gunter_hick) on 2020-11-05 17:13:28 in reply to 9 [link]

Please check what the sizeof() builtin function does. Maybe you meant strlen() instead? If so, just pass -1 and don't bother to count the string length.

Using the actual length is only faster if your compiler does the counting at compile time. As in

char sql[] = "SELECT 1";

sqlite3_prepare( .. sql, sizeof(sql), .. )

(6) By Gunter Hick (gunter_hick) on 2020-11-04 13:52:56 in reply to 4 [link]

I strongly suggest you try out your SQL in the sqlite shell first, before running it from your C program. And making sure that your c program is single threaded and each copy is running against a different database file.

That way you can differentiate between incorrect SQL, wrong C programming and multiuser interference.

(3) By Ryan Smith (cuz) on 2020-11-04 11:51:56 in reply to 1 [link]

What does "i check for..." mean?

We cannot possibly know what you are trying to do or implying even.

Please show ALL the actual SQL calls that you do, both with what you expect the result should be, and what you are seeing.

Also, which version of SQLite? Which OS?

Alternatively, show any simple SQL script that any of us can run, which doesn't work, or fail, or return unexpected results, then we might be able to help.