Re: Pro *C problem...

A copy of this was sent to Shashi Bhushana <sbhusha_at_uswest.com> (if that email address didn't require changing) On Tue, 16 Feb 1999 16:41:10 -0700, you wrote:

>Hello,
>
>I am trying to extract a substring of a column, using SELECT
>statement...
>
>VARCHAR Region[2];
>VARCHAR Temp[4];
>(Temp is containing value like '123' before execution of this statement)
>
>(VALUE is a column in the table STABV and will contain values like
>'123=W'
> , '456=E', etc...I am trying to extract value 'E' or 'W', and so on)
>
> EXEC SQL
> SELECT substr(VALUE,5,1)
> INTO :Region
> FROM STABV
> WHERE NAME = 'MC REGION'
> AND VALUE LIKE :Temp || '%';
>
>I tried to execute the same statement at sql prompt. It is working
>fine....But in Pro *C
>it is not giving any value...Is there any other method to do this task
>?? Any help
>would be highly appreciated...
>
>Thanks in advance,
>SB

You haven't posted all of your code, but perhaps the way you are setting Temp is wrong -- here is an example that works:

static void process()
{
VARCHAR Region[2];
VARCHAR Temp[4];

    EXEC SQL WHENEVER SQLERROR CONTINUE;     exec sql drop table stabv;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     exec sql create table stabv( name varchar2(25), value varchar2(5) );

    exec sql insert into stabv values ( 'MC REGION', '123=W' );

    exec sql commit;

    strcpy( Temp.arr, "123" );
    Temp.len = strlen( Temp.arr );

    EXEC SQL SELECT substr(VALUE,5,1)

               INTO :Region
               FROM STABV
              WHERE NAME = 'MC REGION'
                AND VALUE LIKE :Temp || '%';

    printf( "region = %.*s\n", Region.len, Region.arr ); }

it prints:

region = W

hows about posting a large code fragment with all of the relevant code snippets (and the sqlplus session screen capture to see what the query looks like in plus)....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.