U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » U++ Library support » U++ SQL » PATCH/BUGFIX Oracle global temporary table

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

PATCH/BUGFIX Oracle global temporary table [message #45235]

Wed, 14 October 2015 10:15

wqcmaster
Messages: 37
Registered: March 2013

Member

Hi,

Oracle Definition for global temporary table: create global temporary table <tablename> on commit <...> as ( <select_cmd> );
PostgreSQL Definition for global temporary table: create [global] temporary table <tablename> on commit <...> as ( <select_cmd> );

so, it is not the great solution, but a simple sourcecode extension:

Sqlexp.h:

struct SqlCreateTable {
  SqlId  table;
  bool   permanent;
  bool   transaction;
  enum { TRANSACTION, SESSION, PERMANENT };
public:
  SqlCreateTable& Permanent()   { permanent = true; return *this; }
  SqlCreateTable& Transaction() { transaction = true; return *this; }
  SqlStatement As(const SqlSelect& select);
  SqlCreateTable(SqlId table) : table(table) { permanent = false; transaction = false; }
};

SqlStatement.cpp:

SqlStatement SqlCreateTable::As(const SqlSelect& select)
{
  String text = "create ";
  if(!permanent)
    text << SqlCase(ORACLE | PGSQL, "global temporary ")("temporary ");
  text << "table " << table.Quoted();
  if(!permanent){
    if (transaction)
      text << SqlCase(ORACLE | PGSQL, " on commit delete rows")("");
    else
      text << SqlCase(ORACLE | PGSQL, " on commit preserve rows")("");
  }
  text << " as (" + SqlStatement(select).GetText() + ")";
  return SqlStatement(text);
}

Report message to a moderator

Re: PATCH/BUGFIX Oracle global temporary table [message #45236 is a reply to message #45235]

Wed, 14 October 2015 12:55

mirek
Messages: 14255
Registered: November 2005

Ultimate Member

OK, applied, but Oracle 'temporary' tables seem to be quite different semantically, so this is really ambiguos solution. It seems to me that for Oracle, temporary table support should be rather part of .sch file...

Report message to a moderator

Re: PATCH/BUGFIX Oracle global temporary table [message #45237 is a reply to message #45236]

Wed, 14 October 2015 15:45

wqcmaster
Messages: 37
Registered: March 2013

Member

Yes, that would be great

Something like that?:

sch_model.h

#ifndef SESSIONTABLE
#define SESSIONTABLE(x)                    TYPE(x)
#endif

#ifndef TRANSACTIONTABLE
#define TRANSACTIONTABLE(x)                 TYPE(x)
#endif

sch_schema.h

#define SESSIONTABLE(x)\
void TABLE_##x(SqlSchema& schema) { schema.SessionTable(#x); SCHEMA_##x(schema); schema.EndTable(); }

#define TRANSACTIONTABLE(x)\
void TABLE_##x(SqlSchema& schema) { schema.TransactionTable(#x); SCHEMA_##x(schema); schema.EndTable(); }

SqlSchema.cpp:

String SqlSchema::Expand(const char *txt, int i) const
{
...
case '<1>': r.Cat(<temp_table_attribute_variable1>); break;   //<1> -> a letter
case '<2>': r.Cat(<temp_table_attribute_variable2>); break;   //<2> -> a letter
...
}


void SqlSchema::SessionTable(const char *name) {
  FlushTable();
  table = name;
  table_suffix = Null;
  <temp_table_attribute_variable1> = "global temporary ";
  <temp_table_attribute_variable2> = "on commit preserve rows "
  Schema() << Expand("create @<1> table @t <2> (\n");  
  SchemaDrop() << Expand("drop table @t;\n");
  Attributes() << '\n';
  AttributesDrop() << '\n';
  firstcolumn = true;
}

void SqlSchema::TransactionTable(const char *name) {
  FlushTable();
  table = name;
  table_suffix = Null;
  <temp_table_attribute_variable1> = "global temporary ";
  <temp_table_attribute_variable2> = "on commit delete rows "
  Schema() << Expand("create @<1> table @t @<2> (\n");  
  SchemaDrop() << Expand("drop table @t;\n");
  Attributes() << '\n';
  AttributesDrop() << '\n';
  firstcolumn = true;
}

last but not least:

void SqlSchema::FlushColumn()
{
...
Upgrade() << Expand("create @<1> table @t @<2> ( ...

or it should be a completely different solution?

Report message to a moderator

Previous Topic:	Application crashes when connected to MySQL, what am I doing wrong?
Next Topic:	Commas into SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Sat Apr 26 15:00:11 CEST 2025

Total time taken to generate the page: 0.00561 seconds