Oracle DECODE

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle DECODE

Drew, Stephen
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);
   }
Reply | Threaded
Open this post in threaded view
|

Re: Oracle DECODE

D. Richard Hipp
"Drew, Stephen" <[hidden email]> wrote:

> 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....).


There is no such API as sqlite3_set_result_string().  There was
an API sqlite_set_result_string() in SQLite version 2, which you
appear to be using.  But it is certainly not safe to mix APIs.
I'm surprised this does not segfault on you.

Use sqlite3_result_text() instead.  See

  http://www.sqlite.org/capi3ref.html

for additional information.  And/or use the function implementations
in the func.c source file

  http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c

as examples.

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: Oracle DECODE

Drew, Stephen
In reply to this post by Drew, Stephen
Richard,

Thanks for the response.

I do apologise, I made a mistake in my original mail - I have a macro
which allows me to easily switch between using 2 and 3 in my apps:

For SQLite 3:

#   define sqlite_set_result_string(a,b,c)
sqlite3_result_text(a,b,c,NULL)

This just allows me to not change my code to use either version, and I
mistakenly assumed the function was sqlite3_set_result_string without
double-checking first...

Regards,
Steve

 

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: 12 April 2006 17:30
To: [hidden email]
Subject: Re: [sqlite] Oracle DECODE

"Drew, Stephen" <[hidden email]> wrote:

> 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....).


There is no such API as sqlite3_set_result_string().  There was an API
sqlite_set_result_string() in SQLite version 2, which you appear to be
using.  But it is certainly not safe to mix APIs.
I'm surprised this does not segfault on you.

Use sqlite3_result_text() instead.  See

  http://www.sqlite.org/capi3ref.html

for additional information.  And/or use the function implementations in
the func.c source file

  http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c

as examples.

--
D. Richard Hipp   <[hidden email]>