Hello all,
I have written a function to replicate the Oracle DECODE function, which
I register whenever I open a SQLite3 (3.2.6) connection (see bottom of
email).
However it seems that certain decodes fail due to corrupted data. I
have stepped through my code and seen that the value is correctly set
when the decode function is called, but when the value is retrieved
using sqlite3_column_value, it comes back as gibberish.
This does not always happen - the particular case involves five decode
statements in the SELECT list. If I move some of the DECODE statements
around or remove them, it works ok.
So - here are some questions:
* - Is it safe to call sqlite3_set_result_string( ) as I am doing, i.e.
with the value returned from sqlite3_value_text on the arguments? (I
have included my decode function at the bottom of this email, and
happily release into the public domain....).
* - My decode appears to not work correctly on numeric decodes e.g.
DECODE ( <column_name>, -42, 1000000000, <column_name> )
doesn't work, but this does:
DECODE ( <column_name>, '-42', '1000000000', <column_name> )
and all other text decodes work fine.
Can anyone offer any tips on what direction I should continue to
investigate this problem?
Cheers,
Steve
------------------------------------------------------------------------
------------
void decode_sql_function(sqlite_func* context, IntM argc,
sqlite_value** argv)
{
int pos=1;
if (argc>2)
{
sqlite_char* ach0 = sqlite_value_text(argv[0]);
while (pos < argc)
{
if ((pos+1) < argc)
{
sqlite_char* achp =
sqlite_value_text(argv[pos]);
if (ach0==NULL || achp==NULL)
{
if (ach0==NULL &&
achp==NULL) {
sqlite_set_result_string(context,(const
char*)sqlite_value_text(argv[pos+1]),-1);
return;
}
}
else if (strcmp((const
char*)ach0,(const char*)achp)==0)
{
sqlite_set_result_string(context,(const
char*)sqlite_value_text(argv[pos+1]),-1);
return;
}
}
else
{
sqlite_set_result_string(context,(const
char*)sqlite_value_text(argv[pos]),-1);
return;
}
pos+=2;
}
}
sqlite_set_result_error(context,"Not enough arguments
for DECODE",-1);
}
