Re: [Q] Pro*C/Dynamic SQL, can the WHERE clause be dynamic?

Dale Edgar (dedgar_at_fearless.csi.nb.ca) wrote:
: Hi All
 

: Can the WHERE clause be dynamic in Pro*C (method 3 or 4?).
 

: What I'd like to do is be able to code up a routine able to process runtime
: created WHERE statements like:
 

: SELECT :empno, :empname FROM emp WHERE empname = :stringvar;
 

: and something like
 

: SELECT :empno, :empname FROM emp WHERE SOUNDEX(empname) = SOUNDEX(:stringvar);
 

: The select-list items are always the same, its the construction of the WHERE
: part of the statement that will vary according to the users inputs.
 

: Is this do-able or does one hard code a bunch of regular Pro*C SQL querys
: for the job.
 

: Many Thanks in Advance
: Dale Edgar
: Cybersmith Inc.
: dedgar_at_csi.nb.ca

• code snipt --------------------------

static  varchar         insert_clause [256] ;           /* argument insert_clause storage      +
static  varchar         linenum_col_name [41] ;         /* argument number_column storage      +
static  long int        line_num ;                      /* text line number                    +
static  varchar         order_by [256] ;                /* argument order_by storage           +
static  varchar         sql_insert [256] ;              /* sql insert text storage             +
static  varchar         sql_select [256] ;              /* sql select statement storage        +
static  varchar         table_name [41] ;               /* argument table_name storage         +
static  varchar         text [256] ;                    /* text string + null                  +
static  short int       text_i ;                        /* text iddicator                      +
static  varchar         text_col_name [41] ;            /* argument text column name storage   +
static  varchar         value_contents [256] ;          /* argument value_contents storage     +
static  varchar         where_clause [256] ;            /* argument where_clause storage       +


    sql_select.len = sprintf ((char *) sql_select.arr,
                "select %s from %s where %s order by %s, %s" ,
                (char *) text_col_name.arr, (char *) table_name.arr,
                (char *) where_clause.arr, (char *) order_by.arr,
	         (char *) linenum_col_name.arr+
 
 
 
        EXEC SQL PREPARE select_text_string FROM :sql_select ;
        if (sqlca.sqlcode != 0)
          your_crash_code here  

      EXEC SQL DECLARE temp_text CURSOR FOR select_text_string ;

 
      EXEC SQL OPEN temp_text ;       /* open the cursor              */
        if (sqlca.sqlcode != 0)
           your_crash_code_here
 
 
 
 
        EXEC SQL FETCH temp_text INTO :text :text_i ;