Fixing problem with table getting change after compiling when using table.*

Hi,

I'm looking into fixing the compiler to fix a problem that is present
in aubit 4gl and informix 4gl. I already using informix 4gl and I want
to switch to aubit 4gl, but I want to fix that pesky problem before
migrating.

So the problem is when you do a "select * into var.* from table1 " and
that after the program is compiled, you add a field in your database,
that extra field can cause problem if it's in the middle of the table
and can make your data corrupt. You could recompile your program and
you wil be good, but I my case, recompiling all the program is an
impossible task as some program as to be kept running and I need to be
able to alter my table without making those program crash.

So let's start with a table like this:
CREATE TABLE customer (
  id int(11) NOT NULL,
  name char(50) NOT NULL,
  last_update date NOT NULL,
  updated_by char(20) NOT NULL
)

With the fallowing 4gl code
   define
      lv_c record like customer.*,
      lv_string char(255)

   let lv_string = "select * from customer"
   prepare pr_c from lv_string
   declare cu_c cursor for pr_c
   open cu_c
   fetch cu_c into lv_c.*
   close cu_c
   free cu_c

When you compile that 4gl code, it give something like this in c:
   obind[0].ptr= & lv_c.id;
   obind[1].ptr= & lv_c.name;
   obind[2].ptr= & lv_c.last_update;
   obind[3].ptr= & lv_c.update_by;

Now the issue come when you add a field in your database such as
last_name after the field name, so the table will look like this:
CREATE TABLE customer (
  id int(11) NOT NULL,
  name char(50) NOT NULL,
  last_name char(50) NOT NULL, <-- new field
  last_update date NOT NULL,
  updated_by char(20) NOT NULL
)

Then the C code will start having problem:
   obind[0].ptr= & lv_c.id;
   obind[1].ptr= & lv_c.name;
   obind[2].ptr= & lv_c.last_update; <-- lv_c.last_update will
actually get the last_name data instead
   obind[3].ptr= & lv_c.update_by; <-- lv_c.update_by will get the
data from last_update data instead

To fix this, one way is to make it so that the program will get data
by field name, so the c look more like this (not compiling, just
example):
   lv_c.id = sqlrow.GetDataByField("id");
   lv_c.name = sqlrow.GetDataByField("name");
   lv_c.last_update = sqlrow.GetDataByField("last_update");
   lv_c.updated_by = sqlrow.GetDataByField("updated_by");

That is super nice and in most other program I made in other language,
it works perfectly. But since I'm trying to change the compiler here,
I have to put more thinking into it.

So, with the last method, it works well with simple query. But if you
start to add more complex stuff, it start to fell apart. For example,
if I take this query instead:
   select
       (select count(*) from purchase where purchase.customer_id = customer.id),
       *
   from customer

When doing that, the inner select get called "(expression)"
("(expression)" come from the function GetSchemaTable, it's the name
giving for the column by the database). If you only have one, it's ok,
but if you have multiple one like the following, it stop working:
   select
       (select count(*) from purchase where purchase.customer_id = customer.id),
       (select count(*) from wish_list where wish_list.customer_id =
customer.id),
       *
   from customer

... and I can't do:
   lv_c.count_purchase = sqlrow.GetDataByField("(expression)");
   lv_c.count_wish_list = sqlrow.GetDataByField("(expression)");

Considering that I redefined the lv_c as:
  define lv_c record
       count_purchase   int,
       count_wish_list    int,
       id                          int,
       name                    char(50),
       last_update          date,
       update_by            char(20)
    end record

So I wanted to know if you had any thought on how to do this....

Jo

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot