I am using ESQL/C code to provide backend support for a UI, connecting to an Informix database. I am creating temp table inside my code. But, I guess that if multiple users use this UI at the same time then temp table might already exist in the database which can create problem. So, can someone suggest if I can create temp table with PID as suffix inside my ESQL/C code
create temp table tabname_PID (name char(10));
In shell script I generally use tabname_$$.
You can create the table with the PID embedded in it, but it isn't necessary. Any temporary table is only visible in the session that creates it, so you can use the same table name in each session (separate but concurrently executing ESQL/C program) without any fear of conflict.
If, despite the reassurances that it is unnecessary, you still want to do it, then you'll have to PREPARE and EXECUTE (or DECLARE, OPEN, FETCH, CLOSE) the statements from a string:
snprintf(sql, sizeof(sql), "CREATE TEMP TABLE tabname_%d(name CHAR(10))", (int)getpid());
EXEC SQL PREPARE s FROM :sql;
EXEC SQL EXECUTE s;
or use EXECUTE IMMEDIATE (which is the obvious winner here):
EXEC SQL EXECUTE IMMEDIATE :sql;
You will also then have to prepare all the queries; one distinct advantage of using the fixed-name temporary table is that you don't have to prepare everything that references the temp table if you don't want to (though there are often advantages to using PREPARE etc).
You don't have to use $$ in shell scripts either, for the same reason — temporary tables are private to a session.