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

This wont work - its not always possible to get a name for the returns from
a "select" - imagine an "execute procedure" for example.
Also - in this case - you'd have to interpret the string to get a sql
statement - which could be generated at runtime, but the getDataByFields
are generated at compile time.

Normally - the approach is to

select col1,col2,col3 from ...

instead of
select * from ...

Now - there is a "shortcut" in aubit4gl for this - if you set

$export EXPAND_COLUMNS=Y


It should automatically expand any "select * ..." etc it finds *in normal
select statements*

It cannot detect them in PREPARE'd statements - so you'll still need to fix
those - but its a good start..




On Fri, 6 Jul 2018 at 16:15, Josué Ratelle <[email protected]> wrote:

> 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
> _______________________________________________
> Aubit4gl-discuss mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss
>

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

_______________________________________________
Aubit4gl-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss