Re: simple embedded C/SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 24 Jun 1998 13:15:44 GMT
Message-ID: <3591fb9b.1610055@192.86.155.100>

A copy of this was sent to David Doll <djd_at_saul7.u.washington.edu> (if that email address didn't require changing) On 23 Jun 1998 16:50:20 -0700, you wrote:

>
>Hello, I have a simple C code program with some embedded SQL in it. I
>have something like this:
>
>...
>char *query_string;
>....
>query_string = getenv("QUERY_STRING");
>....
>
>EXEC SQL DECLARE est_cursor CURSOR FOR
> SELECT library, ESTs
> FROM cluster_annotation
> WHERE query_string;
>
>Note:query_string would look something like db_id=1234
[snip]

it could look like this:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     ename[50];
    int         empno;
    varchar     sqlstmt[512];
    char        * query_string = getenv( "QUERY_STRING" );

EXEC SQL END DECLARE SECTION;     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    sprintf( sqlstmt.arr,

            "select ename, empno from emp %s %s",
             query_string?"where":"",
             query_string?query_string:"" );

    sqlstmt.len = strlen(sqlstmt.arr);

    printf( "%.*s\n", sqlstmt.len, sqlstmt.arr );

    EXEC SQL PREPARE S FROM :sqlstmt;
    EXEC SQL DECLARE C CURSOR FOR S;
    EXEC SQL OPEN C;     for( ;; )
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C INTO :ename, :empno;
        printf( "%.*s %d\n", ename.len, ename.arr, empno );

    }
    EXEC SQL CLOSE C;
}

that uses a little dynamic sql to build the query. If you don't know the where clause at precompile time, we have to dynamically build it.

So, if you just run it as such:

$ setenv QUERY_STRING "ename='KING'"

$ ./test userid=scott/tiger

Connected to ORACLE as user: scott/tiger

select ename, empno from emp where ename='KING' KING 7839
>David
>djd_at_u.washington.edu
>
>p.s. system info: SunOS 5.5.1, oracle server: 7.2.3.0.0
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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. Received on Wed Jun 24 1998 - 08:15:44 CDT