Re: SQL - 4gl Vs. Dbaccess
On Thu, 18 Dec 1997, W. H. SMITH wrote:
} Nagesh Daliparthy wrote:
} > I have a 4gl program in which I am using INSERT ... SELECT.
} >
} > I am getting error 201 just after the SQL in question. If I run the SQL
} > WITH NO CHANGES either in dbaccess or in isql it is working fine. There are
} > no contro characters in the SQL. In other programs and in the same program
} > INSERT ... SELECT is working fine.
} >
} > Environmet. Informix 7.23, HP UX 10 4GL 6.x
} >
} > Can any one help me with some solution.
}
} Could you post the SQL?
He could, probably, but his problem has been resolved. I sent him the
message below:
}> Can any one help me with some solution.
}
}No -- you haven't shown us the code which has the problem.
}
}I'd hazard a guess that you've got a variable with the same
}name as one of the columns you are referencing. You'd do best
}to look at the generated ESQL/C code (c4gl -e) and see what the
}statement looks like in there.
This supposition was pretty much accurate. The code sent is shown in
my final explanation to Nagesh:
}> INSERT INTO table_3
}> SELECT area_key,
}> T.id,
}> " ",
}> "abcd",
}> "Y",
}> "",
}> "xx",
}> CURRENT,
}> CURRENT
}> FROM table_1 T, table_2 P
}> WHERE T.bkr = P.bkr
}>
}> area_key is a vriable in 4gl. No variables are the same as column names.
}
}OK: as I suspected, it's the variable which is causing the trouble. You
}cannot have a bare variable in the SELECT list (though you could have
}one as the argument to an SP, I think).
}
}> The following is the from .c file
}>
}> static const char *sqlcmdtxt[] =
}> {
}> " insert into table_3 ( col1 , col_2 , col_3 ,
}> col_4 , col5 ) select ? , t . issuer_id , \\"abcd\\" , \\"Y\\" , \\
}> "xx\\" from table_1 t , table_2 p where t . bkr = p .bkr",
}> 0
}> };
}
}Note that question mark -- it is the cause of the problem. It doesn't
}show up in ISQL/DB-Access because you can't have question marks in those
}statements; you provided the actual value in the prepared statement.
}
}> static _SQSTMT _SQ0 = {0};
}> static struct sqlvar_struct _sqibind[] =
}> {
}> { 103, sizeof(area_key), 0, 0, 0, 0, 0, 0, 0 },
}> };
}>
}> _sqibind[0].sqldata = (char *) &area_key;
}> _iqstmnt(&_SQ0, sqlcmdtxt, 1, _sqibind, (struct value *) 0);
}>
}> }
}> status = sqlca.sqlcode;
}> if (status < 0)
}> {
}> fgl_fatal(fgl_modname, 126, status);
}> }
}>
}> I wonder how "CURRENT" and its corresponding last 2 columns are not in code
}> of .c file
}
}Me too; I think you must be looking at a the output for a different
}(albeit similar) INSERT statement, simply because I don't believe that
}I4GL would expand the T.id into T.issuer_id, nor the column-less insert
}into one where the 5 columns are specified by name. There are other
}discrepancies, too, in the blank string and the empty string.
}
}OK; we've diagnosed the problem. Now, what about solutions...
}
}Funnily enough, I don't have a pre-canned solution. One possibility
}which occurs to me is to create an SP echo() as in:
}
}CREATE PROCEDURE echo(v VARCHAR(255)) RETURNING VARCHAR(255);
} RETURN v;
}END PROCEDURE;
}
}This works because most variable types can be converted to VARCHAR and
}back again pretty much automatically. It isn't desparately fast, but it
}probably does work. You can then, hypothetically write:
}
}INSERT INTO table_3
} SELECT echo(area_key), T.id, " ", "abcd", "Y", "", "xx", CURRENT,
} CURRENT
} FROM table_1 T, table_2 P
} WHERE T.bkr = P.bkr
}
}I've not formally verified this, so your feedback would be appreciated.
}If that doesn't work, we're going to have to think harder. Maybe:
}
}CREATE TEMP TABLE AreaKey(Value INTEGER);
}INSERT INTO AreaKey VALUES(areakey);
}
}INSERT INTO table_3
} SELECT area_key.value, T.id, " ", "abcd", "Y", "", "xx", CURRENT,
} CURRENT
} FROM table_1 T, table_2 P, area_key
} WHERE T.bkr = P.bkr
}
}Because there's just one row in the AreaKey table, it doesn't matter
}that there is no join condition for it.
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>