creating dynamic tables with embedded SQL

From: <shockerman_at_my-deja.com>
Date: Tue, 13 Jul 1999 15:43:43 GMT
Message-ID: <7mfmrb$m3c$1_at_nnrp1.deja.com>

I'm going to have people using this program simultaneously on the web so every time they run the program, I need to create a unique table for them. I thought of using the userenv('sessionid') from dual and then doing a command like this. It ends up making a table name called sorted_sessionid. Pro*C won't let me do
create table sorted_:sessionid which would make my life so much easier.

int main(){
..
EXEC SQL select userenv('sessionid') into :sessionid from dual; maketable(sessionid);
..
return 0; }

void maketable(long int sessionid, lots of other variables) {

		sprintf(buffer, "create table temp_sorted_%d "
			        "(rid number,"
				"pid number,"
				"ddate date);", sessionid);
		sql(buffer);

do {c = fscanf(fp1, "%s %d %s %d %s %s %s %s %s\n", string1, &rid, string2, &pid, string3, date, time, ip1, ip2);

	sprintf(buffer, "INSERT INTO sorted_%d (rid, pid, ddate)"
					"VALUES (:rid,"
					":pid,"
					"TO_DATE(:date, 'MM/DD/RR'));",
sessionid);
		sql(buffer);
	}

while (c != EOF);
}

void sql(char *_comm) {

	EXEC SQL BEGIN DECLARE SECTION;
		char *comm = _comm;
	EXEC SQL END DECLARE SECTION;
	EXEC SQL EXECUTE IMMEDIATE :comm;
	EXEC SQL commit work;

}

The problem is, after this. I have to query it and use variables that I get from this so I'm getting confused with where I need to declare variables and its getting extremely annoying. Does anyone know of an easier way that I could accomplish this task? Please help.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 13 1999 - 17:43:43 CEST