Default NULL value for fields

[yagi]

I found something strange bevaviour. I hahe a table with column COL1 with DEFAULT 0 defined. I set OracleDataSet.OracleDictionary

 

[Marco Kalter]

The information about the default values is cached by the TOracleSession instance for the duration of the connection. It may be necessary to disconnect to get the modified default values. If this does not apply, then I can only assume that the default value is still 0.

------------------
Marco Kalter
Allround Automations

 

[yagi]

After changeing the default value for field i run the program again.
Even PL/SQL Developer show a default for fields very strange (the 'NULL| |' string appears, but for fields which has default = null given in time of creation of table nothing is shown).

 

[MRhoades]

I am having a simular problem.
I rename a table and create a new table (same name) with more fields. IE:
I have a table created as: create table TEST (f1 number not null default 0, f2 number not null default 0, f3 number not null default 0);

alter table TEST rename to TEST_OLD;

create table TEST (f1 number not null default 0, f2 number not null default 0, f3 number not null default 0, nf1 number not null default 0);

Now when I append to TEST f1, f2, and f3 have the default values set to "0", but nf1 value is [null]. If I do not update NF1 I will get an Oracle exception "NF1 value required".

Is there a way to force the cache to reset without disconnecting from the session and reconnecting?

 

[Marco Kalter]

If the table definition changes you will need to close and reopen the dataset. You do not need to disconnect the session.

 

[MRhoades]

I do create a new dataset. But the information seems to still be cached.

Here is my sample code:

// Table Test = create table TEST (f1 number not null default 0, f2 number not null default 0, f3 number not null default 0);

AnsiString Table = "TEST";

// Rename the existing table
SQLExec( frmMain->Session, "ALTER TABLE " + Owner + "." + Table + " RENAME TO " + Table + "_OLD" );

// Create the table and add the constraints
SQLExec( frmMain->Session, "create table TEST (f1 number not null default 0, f2 number not null default 0, f3 number not null default 0, nf1 number not null default 0)"

// Select from newly created table
TOracleDataSet *qryNewTable = new TOracleDataSet(Application);
DBSetup( frmMain->Session, qryNewTable );
qryNewTable->Close();
qryNewTable->SQL->Text = "SELECT " + Table + ".*, ROWID FROM " + Table;
qryNewTable->Open();

qryNewTable->Append();
// Now when I check "qryNewTable->FieldByName("NF1")->AsString" it is NULL when it should be "0"
// When I check "qryNewTable->FieldByName("F1")->AsString" it is "0"
// I have checked the structure in Oracle and everything is correct

void __fastcall DBSetup( TOracleSession *Session, TOracleDataSet *DB )
{
DB->Session = Session;
DB->CachedUpdates = false;
DB->CommitOnPost = false;
DB->CountAllRecords = false;
DB->QueryAllRecords = false;
DB->LockingMode = lmLockImmediate;
DB->ReadBuffer = 200;
DB->OracleDictionary->DefaultValues = true;
DB->OracleDictionary->DynamicDefaults = true;
}

int __fastcall SQLExec( TOracleSession *Session, AnsiString SQL )
{
bool status = true;
TOracleDataSet *q = new TOracleDataSet(Application);

try
{
DBSetup( Session, q );
q->SQL->Text = SQL;
q->ExecSQL();
q->Session->Commit();
}
catch( Exception &e )
{
ShowMessage( "SQLExec Error: " + e.Message.Trim() + "\nSQL = " + SQL );
status = false;
}
delete q;
Application->ProcessMessages();
return( status );
}