D. Richard Hipp wrote:
Bob Gilson wrote: > I'm seeing some strange behavior with sqlite 3.0.8. > Has anyone else seen this sort of thing?
I attempted to follow all of your instructions carefully, but when I try it, it works fine.
I tracked down the strangeness. It was triggered by my usage of the API. I had a line like this in my code:
sqlite3_bind_text(stmt, col_no, buffer, capacity_of_buffer, SQLITE_STATIC);
changing it to:
sqlite3_bind_text(stmt, col_no, buffer, strlen(buffer), SQLITE_STATIC);
caused the strangeness to go away.
Suppose I had:
char buffer[20];
strcpy(buffer, "12345");
and called sqlite3_bind_text with 20, SQLite would:
select length(theColumn) from theTable ---> 5
select * from theTable where theColumn = '12345' ---> return no rows
select * from theTable where theColumn like '12345' ---> returns the desired row
So somewhere behind its API, SQLite was storing the full 20 characters of buffer is a manner that "length()" and "equals" operator can access them but "like" operator can't.
I suppose one could say in good natured jest: "use the API correctly, and quick dragging your knuckles on the ground you ....". But there's a good reason for what I was doing. We process a lot of large blocks of XML. Running strlen() on each of them is noticable performance hit. Other databases allow you to pass the buffer size as a maximum length of the string and truncate at the null.
Attached is a small program and a script which illustrate the problem. Run the script first to create the table, then run the program to create the funky row and then run the script again to see the strange "like" and "=" behavior. Replace "strlen(str)+2" with "strlen(str)" to toggle the strangeness on and off.
> It occurs with tables with an unique "primary key" index which is > varchar. Table with int primary keys work as desired.
There were no PRIMARY KEYs in the example you posted. Please post the exact text of a script or the exact code that is giving problems.
Sorry about the confusion with "primary key" verses PRIMARY KEY. I'm not using the PRIMARY KEY syntax. Instead I create a unique index with the name of the table appended with "_pk". Our database kit validates the schema its running against at connect time. It seemed a better way to get the columns compared to grepping around in the output from ".schema table". Plus "pragma index_info table" avoids case issues.
Bob Gilson
#include <stdio.h> #include <sqlite3.h>
typedef enum _bool
{
false,
true
} bool;
bool processErrorCode(int error);
void insert(void);
void select(void);
typedef enum _SQLOperation
{
SQL_CONNECTION,
SQL_SELECT,
SQL_INSERT_CMD,
SQL_UPDATE_CMD,
SQL_DELETE_CMD,
SQL_STOREDPROCEDURE,
SQL_TRANSACTIONCONTROL,
SQL_HEARTBEAT
} SQLOperation;
static SQLOperation _current_operation = SQL_CONNECTION;
static sqlite3 *db = NULL;
static struct sqlite3_stmt *stmt = NULL;
static int error = 0;
int main(void)
{
_current_operation = SQL_CONNECTION;
error = sqlite3_open("sqlite.db", &db);
if(processErrorCode(error))
{
printf("connected\n");
insert();
//select();
sqlite3_close(db);
}
return 0;
}
void insert(void)
{
const char *cmd = "insert into generic values(?, ?, ?)";
const char *useless;
error = sqlite3_prepare(db, cmd, strlen(cmd), &stmt, &useless);
if (processErrorCode(error))
{
int id = 1;
char str[100];
strcpy(str, "1234567890");
strcpy(str, "iiiiiii");
printf("insert parsed %d\n", error);
error = sqlite3_bind_int(stmt, 1, id);
processErrorCode(error);
error = sqlite3_bind_text(stmt, 2, str, strlen(str)+2, SQLITE_STATIC);
processErrorCode(error);
error = sqlite3_bind_text(stmt, 3, str, strlen(str)+2, SQLITE_STATIC);
processErrorCode(error);
error = sqlite3_step(stmt);
processErrorCode(error);
printf("insert step %d\n", error);
}
}
void select(void)
{
const char *cmd = "select user_id from generic where namespace = 'iiiiiii'";
const char *useless;
error = sqlite3_prepare(db, cmd, strlen(cmd), &stmt, &useless);
if (processErrorCode(error))
{
printf("select parsed %d\n", error);
error = sqlite3_step(stmt);
processErrorCode(error);
printf("select step %d user_id %d\n", error,
sqlite3_column_int(stmt, 1));
}
}
bool processErrorCode(int error)
{
bool isOK = true, spitError = false;
switch(_current_operation)
{
case SQL_SELECT:
{
if(error != SQLITE_OK && error != SQLITE_ROW)
{
isOK = false;
if(error == SQLITE_DONE)
{
spitError = false;
}
else
{
spitError = true;
};
}
}
break;
case SQL_DELETE_CMD:
case SQL_UPDATE_CMD:
{
if(error == SQLITE_DONE)
{
}
else if (error != SQLITE_OK)
{
isOK = false; spitError = true;
}
}
break;
case SQL_INSERT_CMD:
case SQL_STOREDPROCEDURE:
case SQL_TRANSACTIONCONTROL:
case SQL_CONNECTION:
{
if(error == SQLITE_DONE)
{
}
else if (error != SQLITE_OK)
{
isOK = false; spitError = true;
}
}
break;
case SQL_HEARTBEAT:
{
isOK = true;
if(error != SQLITE_OK)
{
spitError = true;
}
}
break;
}
if (spitError)
{
const char *err_msg = sqlite3_errmsg(db);
printf("(%d) %s\n", error, err_msg);
}
return isOK;
}
select * from generic;
select * from generic where namespace = 'iiiiiii';
select * from generic where namespace like 'iiiiiii';
select 'break1';
delete from generic;
insert into generic values(1, 'iiiiiii', 'iiiiiii');
select * from generic;
select * from generic where namespace = 'iiiiiii';
select * from generic where namespace like 'iiiiiii';
select 'break2';
drop table generic;
create table generic
(
user_id int not null,
namespace varchar(255) not null,
value text not null
);
create unique index generic_pk on generic(user_id, namespace);
insert into generic values(1, 'iiiiiii', 'iiiiiii');
select * from generic;
select * from generic where namespace = 'iiiiiii';
select * from generic where namespace like 'iiiiiii';
delete from generic;
