pro*c: dynamic sql method 2 and host array

From: Scott Dudley <scott_at_telesoft.com>
Date: Fri, 27 Jul 2001 11:06:04 -0700
Message-ID: <3B61AD8C.D7963A04_at_telesoft.com>


i've written a pro*c program to delete rows from a table using dynamic sql method 2 and a host array. the following code executes without error or warning however, using the host array, it fails to delete anything. if instead, i address each member of the array individually, they are successfully deleted. i've been coding in pro*c for several years but this is the first time i've attempted to use arrays with method 2 and am perplexed by this behavior. i'm using oracle 8.0.5.1.0 on red hat 6.2.

here's a snippet to illustrate what i'm doing:

    int id[32767];

    sprintf(delstr, "delete from %s where %s = :id", table_name, column_name);

    exec sql prepare delstmt from :delstr;

    exec sql declare c cursor for select column_name from table_name ...

    exec sql open c;

    totalrows=0;

    do {
      exec sql fetch c into :id;

      status=sqlca.sqlcode;

      rowsfetched=sqlca.sqlerrd[2]-totalrows;
      totalrows=sqlca.sqlerrd[2];

      if (rowsfetched == 0)
        break;

      exec sql for :rowsfetched execute delstmt using :id;

      exec sql commit;

    } while (status != NOTFOUND);

if i replace the line "exec sql for :rowsfetched ..." with the following, etc works.

for (i=0; i<rowsfetched; i++)
  exec sql execute delstmt using :id[i];

any ideas?

--

    .~.
    /V\
   // \\   Microsoft gives you Windows but Linux gives you the whole
house!
  /(   )\
   ^^-^^
Received on Fri Jul 27 2001 - 20:06:04 CEST

Original text of this message