/// <summary>
/// Create the temp table for the list if needed
/// </summary>
/// <param name="schema"></param>
/// <param name="name"></param>
public static bool CreateListTableIfNeeded(
string dsName,
string listName)
{
DbCommandMx cmd = null;
bool created = true;
string fullTableName;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
if (Lists.ContainsKey(fullTableName))
{
return(false);
}
string sql = @"
create global temporary table <schema.name> (
rowPos integer,
intKey integer,
stringKey varchar2(256))
on commit preserve rows" ;
sql = sql.Replace("<schema.name>", fullTableName);
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql);
cmd.ExecuteNonReader();
created = true;
int tDelta = (int)TimeOfDay.Delta(t0);
//DebugLog.Message("Create Table time: " + tDelta);
}
catch (Exception ex)
{
if (!ex.Message.Contains("ORA-xxx"))
{
throw ex; // if other than already exists then throw exception
}
created = false; // already exists
}
finally { if (cmd != null)
{
cmd.Dispose();
}
}
TempDbList list = new TempDbList();
list.DataSource = ds;
list.Name = listName;
Lists[fullTableName] = list;
return(created);
}
/// <summary>
/// Get the data source info and full name for the temp table
/// </summary>
/// <param name="dsName"></param>
/// <param name="listName"></param>
/// <param name="fullTableName"></param>
/// <returns></returns>
static DataSourceMx GetDataSourceInfo(
ref string dsName,
ref string listName,
out string fullTableName)
{
dsName = dsName.ToUpper();
listName = listName.ToUpper();
DataSourceMx ds = DataSourceMx.DataSources[dsName];
fullTableName = ds.UserName.ToUpper() + "." + listName;
return(ds);
}
public string DataSourceName = ""; // datasource name
/// <summary>
/// Get the DataSourceMx associated with a schema name
/// </summary>
/// <param name="schemaName"></param>
/// <returns></returns>
public static DataSourceMx GetDataSourceForSchemaName(string schemaName)
{
DbSchemaMx schema = GetSchemaInfoFromName(schemaName);
string dsName = schema?.DataSourceName;
if (Lex.IsUndefined(dsName))
{
return(null);
}
if (!DataSourceMx.DataSources.ContainsKey(dsName))
{
return(null); // unknown datasource
}
DataSourceMx ds = DataSourceMx.DataSources[dsName];
return(ds);
}
/// <summary>
/// Be sure the table is truncated
/// </summary>
/// <param name="schema"></param>
/// <param name="listName"></param>
public static void TruncateListTable(
string dsName,
string listName)
{
DbCommandMx cmd = null;
string fullTableName;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
string sql = "truncate table " + fullTableName;
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql);
cmd.ExecuteNonReader();
int tDelta = (int)TimeOfDay.Delta(t0);
//DebugLog.Message("Truncate Table time: " + tDelta);
}
catch (Exception ex) // if truncate failed see if need to create
{
if (!ex.Message.Contains("ORA-00942"))
{
throw ex; // if other than already exists then throw exception
}
bool created = CreateListTableIfNeeded(dsName, listName);
}
finally { if (cmd != null)
{
cmd.Dispose();
}
}
return;
}
public bool CanCreateTempTables = false; // true if the associated account can create temp tables
/// <summary>
/// Load metadata describing connections and associated schemas
/// </summary>
/// <param name="dsFileName"></param>
/// <returns></returns>
public static int LoadMetadata()
{
XmlAttributeCollection atts;
// Get some inifile parameters first
if (ServicesIniFile.IniFile == null)
{
throw new Exception("ServicesIniFile.IniFile is null");
}
SessionConnection.Pooling = ServicesIniFile.ReadBool("Pooling", true);
SessionConnection.MinPoolSize = ServicesIniFile.ReadInt("MinPoolSize", 1);
SessionConnection.IncrPoolSize = ServicesIniFile.ReadInt("IncrPoolSize", 5);
SessionConnection.MaxPoolSize = ServicesIniFile.ReadInt("MaxPoolSize", 100);
SessionConnection.DecrPoolSize = ServicesIniFile.ReadInt("DecrPoolSize", 1);
SessionConnection.ConnectionLifetime = ServicesIniFile.ReadInt("ConnectionLifetime", 0);
string tok = ServicesIniFile.Read("KeyListPredType", "Parameterized");
int enumInt = EnumUtil.Parse(typeof(KeyListPredTypeEnum), tok);
if (enumInt >= 0)
{
DbCommandMx.DefaultKeyListPredType = (KeyListPredTypeEnum)enumInt;
}
DbCommandMx.DbFetchRowCount = ServicesIniFile.ReadInt("DbFetchRowCount", DbCommandMx.DbFetchRowCount);
DbCommandMx.DbFetchSizeMax = ServicesIniFile.ReadInt("DbFetchSizeMax", DbCommandMx.DbFetchSizeMax);
DataSources = new Dictionary <string, DataSourceMx>(StringComparer.OrdinalIgnoreCase);
Schemas = new Dictionary <string, DbSchemaMx>(StringComparer.OrdinalIgnoreCase);
string dsFileName = ServicesDirs.MetaDataDir + @"\" + "DataSources.xml";
StreamReader sr = new StreamReader(dsFileName);
XmlDocument doc = new XmlDocument();
doc.Load(sr);
XmlNode node = doc.FirstChild;
while (node != null)
{
if (node.NodeType == XmlNodeType.Element)
{
break;
}
node = node.NextSibling;
if (node == null)
{
throw new Exception("No initial element found");
}
}
if (!Lex.Eq(node.Name, "DataSources"))
{
throw new Exception("Expected DataSources node: " + node.Name);
}
atts = node.Attributes; // datasources attributes
for (int i = 0; i < atts.Count; i++)
{
XmlNode att = atts.Item(i);
if (Lex.Eq(att.Name, "DblinkConnName"))
{
} // obsolete key word
else
{
throw new Exception
("Unexpected DataSources attribute: " + att.Name);
}
}
node = node.FirstChild;
while (node != null)
{
if (node.NodeType != XmlNodeType.Element)
{
; // ignore non-elements
}
else if (Lex.Eq(node.Name, "DataSource") || // connection element
Lex.Eq(node.Name, "Connection"))
{
DataSourceMx cd = new DataSourceMx();
atts = node.Attributes;
for (int i = 0; i < atts.Count; i++)
{
XmlNode att = atts.Item(i);
if (Lex.Eq(att.Name, "DatabaseType") || Lex.Eq(att.Name, "DbType"))
{
if (!Enum.TryParse <DatabaseType>(att.Value, true, out cd.DbType))
{
throw new Exception("Invalid Database type: " + att.Value);
}
}
else if (Lex.Eq(att.Name, "Name"))
{
cd.DataSourceName = att.Value.ToUpper().Trim();
}
else if (Lex.Eq(att.Name, "DatabaseName") || Lex.Eq(att.Name, "OracleName"))
{
cd.DatabaseLocator = att.Value.ToUpper().Trim();
}
else if (Lex.Eq(att.Name, "ConnectionString")) // keep case if connection string
{
cd.DatabaseLocator = att.Value.Trim();
}
else if (Lex.Eq(att.Name, "UserName"))
{
cd.UserName = att.Value.Trim(); // keep case as is (Oracle 11g)
}
else if (Lex.Eq(att.Name, "Password"))
{
cd.Password = att.Value.Trim(); // keep case as is (Oracle 11g)
}
else if (Lex.Eq(att.Name, "MdlInit")) // mdl initialization
{
cd.InitCommand = "select cdcaux.ctenvinit('" + att.Value.Trim() + "') from dual";
}
else if (Lex.Eq(att.Name, "InitCommand"))
{
cd.InitCommand = att.Value.Trim();
}
else if (Lex.Eq(att.Name, "KeyDataSource"))
{
bool.TryParse(att.Value.Trim(), out cd.IsKeyDataSource);
}
else if (Lex.Eq(att.Name, "CanCreateTempTables"))
{
bool.TryParse(att.Value.Trim(), out cd.CanCreateTempTables);
}
else if (Lex.Eq(att.Name, "AlwaysUseDbLink"))
{
} // obsolete
else
{
throw new Exception
("Unexpected Connection attribute: " + att.Name);
}
}
if (cd.DataSourceName == "")
{
DebugLog.Message("Connection is missing name: " + cd.DatabaseLocator);
}
else if (DataSourceMx.DataSources.ContainsKey(cd.DataSourceName))
{
DebugLog.Message("Data source defined twice: " + cd.DataSourceName);
}
else
{
DataSourceMx.DataSources.Add(cd.DataSourceName, cd);
}
}
else if (Lex.Eq(node.Name, "SchemaToDataSource") || // schema map element
Lex.Eq(node.Name, "SchemaToConnection"))
{
DbSchemaMx schema = new DbSchemaMx();
atts = node.Attributes;
for (int i = 0; i < atts.Count; i++)
{
XmlNode att = atts.Item(i);
if (Lex.Eq(att.Name, "Schema"))
{
schema.Name = att.Value.ToUpper().Trim();
}
else if (Lex.Eq(att.Name, "AliasFor"))
{
schema.AliasFor = att.Value.ToUpper().Trim();
}
else if (Lex.Eq(att.Name, "Connection"))
{
schema.DataSourceName = att.Value.ToUpper().Trim();
}
else if (Lex.Eq(att.Name, "Label") || Lex.Eq(att.Name, "L"))
{
schema.Label = att.Value.Trim();
}
else
{
throw new Exception
("Unexpected Connection attribute: " + att.Name);
}
}
if (schema.Name == "")
{
throw new Exception("Missing schema name");
}
if (schema.DataSourceName == "")
{
throw new Exception("Missing data source/connection name");
}
Schemas[schema.Name] = schema;
}
else
{
throw new Exception("Expected Connection or SchemaToDataSource element but saw " +
node.Name);
}
node = node.NextSibling;
}
sr.Close();
return(DataSources.Count);
}
/// <summary>
/// Write temporary list
/// </summary>
/// <param name="dsName">Name of datasource</param>
/// <param name="listName">Name of list</param>
/// <param name="listItems">The list of items to write</param>
/// <param name="numeric">Store list items in numeric column if true, text otherwise</param>
public static string Write(
string dsName,
string listName,
List <string> listItems,
int firstKeyIdx,
int keyCount,
bool numeric)
{
DbCommandMx cmd = null;
Oracle.DataAccess.Client.OracleDbType parmType;
string fullTableName, sKey;
int iKey, i1;
object o;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
TruncateListTable(dsName, listName);
if (keyCount == 0)
{
return(null); // nothing to write
}
string sql = "insert into <schema.name> (<cols>) values(:0)";
sql = sql.Replace("<schema.name>", fullTableName);
if (numeric)
{
sql = sql.Replace("<cols>", "intKey");
}
else
{
sql = sql.Replace("<cols>", "stringKey");
}
object[][] pva = DbCommandMx.NewObjectArrayArray(1, keyCount); // alloc insert row array
if (numeric)
{
for (i1 = 0; i1 < keyCount; i1++)
{
if (int.TryParse(listItems[firstKeyIdx + i1], out iKey))
{
pva[0][i1] = iKey;
}
else
{
pva[0][i1] = DBNull.Value;
}
}
parmType = Oracle.DataAccess.Client.OracleDbType.Int32;
}
else // string keys
{
for (i1 = 0; i1 < keyCount; i1++)
{
sKey = listItems[firstKeyIdx + i1];
if (!Lex.IsNullOrEmpty(sKey))
{
pva[0][i1] = sKey;
}
else
{
pva[0][i1] = DBNull.Value;
}
}
parmType = Oracle.DataAccess.Client.OracleDbType.Varchar2;
}
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql, parmType);
int insCount = cmd.ExecuteArrayNonReader(pva, ref keyCount);
int tDelta = (int)TimeOfDay.Delta(t0);
if (Debug)
{
DebugLog.Message("Write TempDbList for KeyCount: " + insCount + ", Time: " + tDelta);
}
}
catch (Exception ex)
{
throw ex;
}
finally { cmd.Dispose(); }
return(fullTableName);
}
