SQLite Forum

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 

-c----------------------------------------------------------------------------

# 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;
  x=1;while(x){
   switch((e=sqlite3_step(s))){
    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;
  }}
  if(SQLITE_OK!=(e=sqlite3_finalize(s))){
   oe("finalize",e);
  }
 } 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  
  r=-2;   
  if(SQLITE_OK!=(e=sqlite3_exec(h,q1,c,&r,&m))){
   oe("A",e); 
  } else if(r<=0) {
   if(SQLITE_OK!=(e=sqlite3_exec(h,q2,c,0,&m))){
    oe("B",e); 
   } else if(SQLITE_OK!=(e=sqlite3_exec(h,q1,c,&r,&m))){
    oe("C",e);
   } else if(r<=0){
    os("ne"); 
   } 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 
  if((r=t(h,q1))<=0){
   if(SQLITE_OK!=(e=sqlite3_exec(h,q2,c,0,&m))){
    oe("insert",e);
   } 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, 
andreas