[sqlite] Is it possible to determine if data is string or numeric inside a callback in C?

[Downey, Shawn]

Does anyone know if it is possible to determine if data is string or
numeric inside a callback in C++?  I am using sqlite 3.2.7.

 

Background:

 

A table with no data type specified for a field may be populated with an
unquoted number.  For example:

 

create table t1 (a);

insert into t1 values(1.5);

 

Sqlite 3.2.7 does not return anything from the query:

 

select * from t1 where a='1.5';

 

But does return the unquoted data if queried correctly:

 

select * from t1 where a=1.5;

1.5

 

Because of this sensitivity, I have the following problem:

 

I am using a C++ interface with sqlite3_exec() and a callback.  The
purpose of the callback is to create an SQL UPDATE statement where each
of the field name/value pairs are in the WHERE clause.

 

I need to know if I should generate quoted data or unquoted data in the
WHERE clause in order to match these fields.  Is there any way to tell
this in the callback?

 

My code follows:

 

typedef struct tdata {

  char table[TABLE_NAME_LEN];

  char field[FIELD_NAME_LEN];

  char old_id[COMPT_ID_LEN];

  char new_id[COMPT_ID_LEN];

} TdataType;

 

static int cb(void *td, int argc, char **argv, char **azColName){

  int i;

 

  fprintf(stdout, "/* Update the %s field of the %s table */\n\n",

          (char *)((TdataType *)td)->field, (char *)((TdataType
*)td)->table);

 

  fprintf(stdout, "UPDATE %s SET %s=\'%s\' WHERE \n", (char
*)((TdataType *)td)->table, 

          (char *)((TdataType *)td)->field, (char *)((TdataType
*)td)->new_id);

 

  for(i=0; i<argc; i++){

    if (argv[i] == 0) {

      fprintf(stdout, "%s.%s is null ", (char *)((TdataType
*)td)->table, azColName[i]);

    }

    else {

      fprintf(stdout, "%s.%s=\'%s\' ", (char *)((TdataType *)td)->table,
azColName[i], argv[i]);

    }

    if (i<argc-1){

      fprintf(stdout, "and \n");

    }

  }

  fprintf(stdout, ";\n\n");

  return 0;

}

 

void make_sql(sqlite3 *db, char *old_id, char *new_id, char *table, char
*field) {

  char *zErrMsg = 0;

  int rc;

  char cmd[CMD_LEN];

  TdataType td;

 

  strncpy(td.table, table, TABLE_NAME_LEN);

  strncpy(td.field, field, FIELD_NAME_LEN);

  strncpy(td.old_id, old_id, COMPT_ID_LEN);

  strncpy(td.new_id, new_id, COMPT_ID_LEN);

 

  sprintf(cmd, "select * from %s where %s=\'%s\'\0", table, field,
old_id);

  rc = sqlite3_exec(db, cmd, cb, &td, &zErrMsg);

  if( rc!=SQLITE_OK ){

    fprintf(stderr, "SQL error: %s\n", zErrMsg);

  }

}

 

This code assumes that all the data was inside single quotes when it was
inserted.  If numeric was entered unquoted, then the callback produces
an UPDATE statement with a WHERE clause which will fail to find any
matches.  There does not appear to be any difference in the data passed
to the callback function between numbers or strings (both appear to be
strings).

 

Thank you for any help.

 

Shawn M. Downey

MPR Associates

10 Maxwell Drive, Suite 204

Clifton Park, NY 12065

518-371-3983 x113 (work)

860-508-5015 (cell)