Re: dyamic table names in embedded SQL in C

Does your application log into the database under a common user name (ie, all users that run the application are automatically logged into the database as APP_USER or something like that) or do they each have their own user account on the Oracle database? If they each have their own user account, you could create a table with the same name (such as SESSION_INFO or whatever) and that table would be created using the username as the schema qualifier (if user JDOE logged into the database and created this table, it would be JDOE.SESSION_INFO and if JSMITH logged in and created it, it would be JSMITH.SESSION_INFO). If the user then did select * from session_info;, it would resolve to the table in his/her schema (because if there was a public synonym with that name, it wouldn't create the table). If you are using a common user login, you might want to look up Dynamic SQL Methods 3 & 4 that way, you could place bind variables in the sql string pass in the appropriate values during the FETCH. I hope some of this helps.

Jeff S

<shockerman_at_my-deja.com> wrote in message news:7mgbbj$v96$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 Wed Jul 14 1999 - 17:15:12 CDT