Informix: ESQL/C syntax issue
Posted in 2026
Apologies if this gets double-posted. First attempt did not seem to work.
Using Informix 14.10 on RH 8, I'm updating some ancient ESQL/C code and running into a problem. Bear in mind, this code is 20+ years old, and the coders back in the day resorted to hardcoding rather than making things flexible. So we have things like:
select emp_id, emp_name, email_address, dept
from region01_dev:employee
where state = ?
union all
select emp_id, emp_name, email_address, dept
from region02_dev:employee
where state = ?
union all
...
select emp_id, emp_name, email_address, dept
from region20_dev:employee
where state = ?
into temp tmp_state_emp;
Notice that "_dev" in the database name? Yeah, that's the problem I'm trying to solve. We have dev, test, uat, and prod environments, and the environment is included as part of the database name. Thus, they had to update all of the "_dev:" references to "_test:" or "_prod:" and recompile for each environment.
What I want to do is use variables for the base database name, and at runtime use getenv() to get the dev/test/uat/prod value from an environment variable, then string those together to get the actual database name. Then, in the SELECT statement, use the variable name. Something like:
char *app_env;
$char r01_dbname[129];
$char r02_dbname[129];
$char r03_dbname[129];
app_env = getenv("MY_ENVIRON");
sprintf(r01_dbname, "region01_%s", app_env);
sprintf(r02_dbname, "region02_%s", app_env);
sprintf(r03_dbname, "region03_%s", app_env);
$SELECT emp_id, emp_name, email_address, dept
FROM $r01_dbname:employee
WHERE state = ?
UNION ALL
SELECT emp_id, emp_name, email_address, dept
FROM $r01_dbname:employee
WHERE state = ?
UNION ALL
...
SELECT emp_id, emp_name, email_address, dept
FROM $r01_dbname:employee
WHERE state = ?
INTO TEMP tmp_state_emp;
Yes, I know, I can (and may have to) rewrite this to do a PREPARE and EXECUTE, but the SQL Syntax manual says that I can use a variable (db_var) for the database name portion of an identifier, so ...
When I try to compile this, I get "esqlc: "state_emp.ec", line 5334: Error -33051: Syntax error on identifier or symbol '$'." So I tried removing the '$', even though that is one of the ways you identify r01_dbname as a host variable. Recompiling, I get a clean compile, but when I run, I get a segmentation violation and core dump. Then it occurred to me that the "from r01_dbname:employee" looks exactly like what you'd code if you wanted to use an indicator variable named "employee".
So I tried modifying the sprintf() statements to include the database name, colon, and tablename all in the one host variable:
sprintf(r01_dbname, "region01_%s:employee", app_env);
and I modified the FROM clause to read "FROM r01_dbname". This also gets a clean compile, but a segmentation fault and core dump. And, just to be sure, making the FROM clause "FROM $r01_dbname" resulted in the same syntax error on '$' that was listed in my first attempt.
So, after all of this background, what is the correct way to use a host variable for the database name and a table name in this situation?
Thanks.
Mark
------------------------------
mark collins
------------------------------
Mark: This is going to be ugly, but here it is: char base_qry[500], build_qry[500]. full_qry[65000]; char which_db[10]; int num; snprintf( which_db, sizeof which_db, getenv( "WHICH_ENV" ) ); snprintf( base_qry, sizeof base_qry, "select emp_id, emp_name, email_address, dept " "from region%%02.2d_%s:employee " "where state = ? ", which_db ); for (num=1; num <= 20; num++) { snprintf( build_qry, sizeof build_qry, base_qry, num ); strcat( full_qry, build_qry ); if (num < 20) strcat( full_qry, "UNION ALL " ); } strcat( full_qry, ";" ); $PREPARE p_query FROM $fill_qry; $DECLARE c_query CURSOR FOR p_query USING $state1, $state2, .... $state220; ... Build the entire query dynamically using the passed in database name tail and the database numbers. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Hi Art. I was figuring that I'd have to resort to building the statement and doing the PREPARE and EXECUTE. I just hoped that maybe there was some other option I had missed. Thanks for confirming that PREPARE/EXECUTE is the only way for this to work. Mark ------------------------------ mark collins ------------------------------