sqlpostgresqlinsertnullecpg

ecpg insert null with host variable (psotgreSQL)


I want to insert a null value into psql table with ecpg host variable, but I have no idea how to do this, it is a simple example below:

EXEC SQL BEGIN DECLARE SECTION;
char var1; 
int var2;
EXEC SQL END DECLARE SECTION;

int main(){
  EXEC SQL CONNECT TO .....
  create();
  insert();
  EXEC SQL COMMIT WORK;
  return 0;
}
void create(){ 
  CREATE TABLE mytable(var1 char(10), var2 int );
}

void insert(){
  EXEC SQL INSERT INTO mytable (var1, var2 ) VALUE (:var1, :var2);
}

I want to insert NULL into var1 and var2 in Database, do anyone know how to do that with host variables (:var1, :var2)

*to replace ":var1" to "NULL" works fine, but it seems no a good method.

*I have known that it can determine whether the variable is null by indicator http://www.postgresql.org/docs/8.3/static/ecpg-variables.html but it doesn't tell me how to insert or update the value with this method?


Solution

  • yooooo

    I tried that "insert" can use indicator too, if you want to like this:

    short var1_ind, var2_ind;
    
    void insert(){
      EXEC SQL INSERT INTO mytable (var1, var2 ) 
      VALUE (:var1 INDICATOR :var1_ind, :var2 INDICATOR :var2_ind);
    }
    

    If you want to insert NULL into var1, just make indicator < 0:

    var1_ind = -1
    

    after assign -1 to var1_ind, it would insert NULL to var1 in DB whetever the value of :var1

    it is some information from the manual

    The indicator variable val_ind will be zero if the value was not null, and it will be negative if the value was null.

    The indicator has another function: if the indicator value is positive, it means that the value is not null, but it was truncated when it was stored in the host variable.