Re: [sqlite] Select fails even though data is in the table.

[Hemant Shah]

Here is the snippet of the code:

int InsertIntoDatabase(char *TimeStamp, int Source, u_int32_t SeqNum, char 
*MD5Sum)
{
   int ReturnCode;
   sqlite3_stmt *InsertStmtHandle;

   strcpy(SqlString, "INSERT INTO mytable(TimeStamp, col2, col3, col4) VALUES 
(?,?,?,?)");
   ReturnCode = sqlite3_prepare_v2(DbHandle, SqlString, -1, &InsertStmtHandle, 
NULL);

   if (ReturnCode != SQLITE_OK || InsertStmtHandle == NULL)
   {
      sqlite3_reset(InsertStmtHandle);
      printf("Cannot prepare insert statement. %s\n", sqlite3_errmsg(DbHandle));
      return(2);
   }


   ReturnCode  = sqlite3_bind_text(InsertStmtHandle, 1, TimeStamp, -1, 
SQLITE_TRANSIENT);
   if (ReturnCode != SQLITE_OK)
   {
      sqlite3_reset(InsertStmtHandle);
      printf("Error binding insert statement 1. ReturnCode: %d, Error 
Message:%s, Error Code: %d\n",
             ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
      return(2);
   }

   ReturnCode  = sqlite3_bind_int(InsertStmtHandle,  2, Source);
   if (ReturnCode != SQLITE_OK)
   {
      sqlite3_reset(InsertStmtHandle);
      printf("Error binding insert statement 2. ReturnCode: %d, Error 
Message:%s, Error Code: %d\n",
             ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
      return(2);
   }

   ReturnCode  = sqlite3_bind_int(InsertStmtHandle,  3, SeqNum);
   if (ReturnCode != SQLITE_OK)
   {
      sqlite3_reset(InsertStmtHandle);
      printf("Error binding insert statement 3. ReturnCode: %d, Error 
Message:%s, Error Code: %d\n",
             ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
      return(2);
   }

   ReturnCode  = sqlite3_bind_text(InsertStmtHandle, 4, MD5Sum, -1, 
SQLITE_TRANSIENT);
   if (ReturnCode != SQLITE_OK)
   {
      sqlite3_reset(InsertStmtHandle);
      printf("Error binding insert statement 4. ReturnCode: %d, Error 
Message:%s, Error Code: %d\n",
             ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
      return(2);
   }

   ReturnCode = sqlite3_step(InsertStmtHandle);
   if (ReturnCode != SQLITE_DONE)
   {
     sqlite3_reset(InsertStmtHandle);
      if (sqlite3_errcode(DbHandle) == SQLITE_CONSTRAINT)
      {
         /*
          * Duplicate row.
          */
         sqlite3_finalize(InsertStmtHandle);
         sqlite3_exec(DbHandle, "COMMIT", NULL, NULL, NULL);
         return(1);
      }
      else
      {
         /*
          * Other error.
          */
         printf("Cannot insert into database. %s\n", sqlite3_errmsg(DbHandle));
         sqlite3_finalize(InsertStmtHandle);
         sqlite3_exec(DbHandle, "COMMIT", NULL, NULL, NULL);
         return(2);
      }
   }

   sqlite3_finalize(InsertStmtHandle);
   sqlite3_exec(DbHandle, "COMMIT", NULL, NULL, NULL);
   return(0);
}

int GetMessageFromDB(char *MD5Sum, struct ReceiveNode *FromDb)
{
   int ReturnCode;
   sqlite3_stmt *SelectStmtHandle;

   strcpy(SqlString, "SELECT TimeStamp, col2, col3, col4 FROM mytable WHERE 
col4 = ?");
   ReturnCode = sqlite3_prepare_v2(DbHandle, SqlString, -1, &SelectStmtHandle, 
NULL);
   if (ReturnCode != SQLITE_OK || SelectStmtHandle == NULL)
   { 
      sqlite3_reset(SelectStmtHandle);
      printf("Cannot prepare select statement. %s\n", sqlite3_errmsg(DbHandle));
      return(2);
   }


   ReturnCode = sqlite3_bind_text(SelectStmtHandle, 1, MD5Sum, -1, 
SQLITE_TRANSIENT);
   if (ReturnCode != SQLITE_OK)
   { 
      sqlite3_reset(SelectStmtHandle);
      printf("Error binding select statement. ReturnCode: %d, Error Message:%s, 
Error Code: %d\n",
             ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
      return(2);
   }

   ReturnCode = sqlite3_step(SelectStmtHandle);
   if (ReturnCode != SQLITE_ROW)
   { 
      sqlite3_reset(SelectStmtHandle);
      printf("Row not found. ReturnCode: %d, Error Message:%s, Error Code: 
%d\n",
             ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
      return(2);
   }

   /* Copy data to FromDb structure) */
   sqlite3_finalize(SelectStmtHandle);
   return(1);
}



/* Main */
if (InsertIntoDatabase(TimeStamp, Source, SeqNum, MD5Sum) == 1)
{ 
   if (GetMessageFromDB(MD5Sum, &FromDb) == 1)
   { 
      /* print message */
   }
   else
   { 
      /* print error message */
   }
}



Hemant Shah
E-mail: hj...@yahoo.com


--- On Wed, 12/1/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote:

> From: Black, Michael (IS) <michael.bla...@ngc.com>
> Subject: Re: [sqlite] Select fails even though data is in the table.
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Wednesday, December 1, 2010, 10:23 AM
> If you bind the wrong thing the wrong
> way the return code doesn't matter.
>  
> Any particular reason you can't just show us the whole code
> section?
>  
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>  
> 
> ________________________________
> 
> From: sqlite-users-boun...@sqlite.org
> on behalf of Hemant Shah
> Sent: Wed 12/1/2010 10:21 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] Select fails even though
> data is in the table.
> 
> 
> 
> I check for the return code after each bind call and if it
> is not SQLITE_OK then I return with error. So I do not think
> it is a bind problem.
> 
> 
> 
> Hemant Shah
> E-mail: hj...@yahoo.com
> 
> 
> 
> 
> 
> -----Inline Attachment Follows-----
> 
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


      
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users