public void CheckTable(VirtuosoConnection connection, TestCaseResult result)
{
VirtuosoCommand select = connection.CreateCommand();
select.CommandText = "select * from foo";
VirtuosoDataAdapter adapter = new VirtuosoDataAdapter();
adapter.SelectCommand = (VirtuosoCommand)select;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable table = dataset.Tables["Table"];
result.FailIfNotEqual(checkTable.Rows.Count, table.Rows.Count);
result.FailIfNotEqual(checkTable.Columns.Count, table.Columns.Count);
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
DataRow checkRow = checkTable.Rows[i];
for (int j = 0; j < table.Columns.Count; j++)
{
string name = table.Columns[j].ColumnName;
result.FailIfNotEqual(checkRow[name], row[name]);
}
}
}
public override IInnerCommand CreateInnerCommand(VirtuosoCommand outerCommand)
{
IntPtr hstmt;
CLI.ReturnCode rc = (CLI.ReturnCode)CLI.SQLAllocHandle(
(short)CLI.HandleType.SQL_HANDLE_STMT,
hdbc,
out hstmt);
if (rc != CLI.ReturnCode.SQL_SUCCESS)
{
HandleConnectionErrors(rc);
}
try
{
//statementList.Add (hstmt, outerCommand);
Add(hstmt, outerCommand);
}
catch (Exception)
{
CLI.SQLFreeHandle((short)CLI.HandleType.SQL_HANDLE_STMT, hstmt);
throw;
}
GC.KeepAlive(this);
return(new OdbcCommand(hstmt, outerCommand));
}
private void CreateTable()
{
VirtuosoCommand cmd = new VirtuosoCommand();
cmd.Connection = connection;
try
{
cmd.CommandText = "drop table foo";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
cmd.CommandText = "create table foo (id int primary key, c long varchar, nc long nvarchar, b long varbinary)";
cmd.ExecuteNonQuery();
cmd.Dispose();
checkTable = new DataTable();
checkTable.Columns.Add("id", typeof(int));
checkTable.Columns.Add("c", typeof(string));
checkTable.Columns.Add("nc", typeof(string));
checkTable.Columns.Add("b", typeof(byte[]));
}
public static int add_data (int nrows)
{
Object port = AppDomain.CurrentDomain.GetData("OpenLink.Virtuoso.InProcessPort");
if (port == null)
throw new Exception ("not running as a hosted module");
VirtuosoConnection c = new VirtuosoConnection ("HOST=:in-process:" + port.ToString()
+ ";UID=dummy;PWD=dummy");
int i;
c.Open();
new VirtuosoCommand ("exec_result_names (vector ('x'))", c).ExecuteNonQuery ();
VirtuosoCommand cmd = new VirtuosoCommand ("result (?)", c);
cmd.Parameters.Add (":1", SqlDbType.Int);
for (i = 0; i < nrows; i++)
{
cmd.Parameters[":1"].Value = i;
cmd.ExecuteNonQuery ();
}
cmd = null;
c.Close();
return 0;
}
public static int add_data(int nrows)
{
Object port = AppDomain.CurrentDomain.GetData("OpenLink.Virtuoso.InProcessPort");
if (port == null)
{
throw new Exception("not running as a hosted module");
}
VirtuosoConnection c = new VirtuosoConnection("HOST=:in-process:" + port.ToString()
+ ";UID=dummy;PWD=dummy");
int i;
c.Open();
new VirtuosoCommand("exec_result_names (vector ('x'))", c).ExecuteNonQuery();
VirtuosoCommand cmd = new VirtuosoCommand("result (?)", c);
cmd.Parameters.Add(":1", SqlDbType.Int);
for (i = 0; i < nrows; i++)
{
cmd.Parameters[":1"].Value = i;
cmd.ExecuteNonQuery();
}
cmd = null;
c.Close();
return(0);
}
public String set_var_data(String var, String part, String query, Object val, String vars, String xmlnss)
{
c.Open();
VirtuosoCommand cmd = new VirtuosoCommand("select BPEL.BPEL.set_var_to_dump (?, ?, ?, ?, ?, ?)", c);
cmd.Parameters.Add(":1", SqlDbType.VarChar);
cmd.Parameters.Add(":2", SqlDbType.VarChar);
cmd.Parameters.Add(":3", SqlDbType.VarChar);
cmd.Parameters.Add(":4", SqlDbType.VarChar);
cmd.Parameters.Add(":5", SqlDbType.VarChar);
cmd.Parameters.Add(":6", SqlDbType.VarChar);
cmd.Parameters[":1"].Value = var;
cmd.Parameters[":2"].Value = part;
cmd.Parameters[":3"].Value = query;
cmd.Parameters[":4"].Value = val;
cmd.Parameters[":5"].Value = vars;
cmd.Parameters[":6"].Value = xmlnss;
String result = (String)cmd.ExecuteScalar();
c.Close();
return(result);
}
public void InitBuffer()
{
// размер буфера
int bufferportion = 1000;
// размер порции для внедрения данных
int portion = 40;
b_entities = new BufferredProcessing <string>(bufferportion, flow =>
{
var query = flow.Select((ent, i) => new { e = ent, i = i }).GroupBy(ei => ei.i / portion, ei => ei);
VirtuosoCommand trcommand = engine.RunStart();
foreach (var q in query)
{
string data = q.Select(ei => ei.e + " . ")
.Aggregate((sum, s) => sum + " " + s);
//bool found = q.Any(ei => ei.e.s == "Gury_Marchuk");
trcommand.CommandText = "SPARQL INSERT INTO GRAPH <" + graph + "> {" + data + "}\n";
try
{
trcommand.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
engine.RunStop(trcommand);
});
}
public void RefreshSchema()
{
tableName = null;
columns = null;
if (deleteCommand != null)
{
if (adapter != null && adapter.DeleteCommand == deleteCommand)
{
adapter.DeleteCommand = null;
}
deleteCommand.Dispose();
deleteCommand = null;
}
if (insertCommand != null)
{
if (adapter != null && adapter.InsertCommand == insertCommand)
{
adapter.InsertCommand = null;
}
insertCommand.Dispose();
insertCommand = null;
}
if (updateCommand != null)
{
if (adapter != null && adapter.UpdateCommand == updateCommand)
{
adapter.UpdateCommand = null;
}
updateCommand.Dispose();
updateCommand = null;
}
}
private void CheckTable(TestCaseResult result)
{
VirtuosoCommand select = connection.CreateCommand();
select.CommandText = "select * from foo";
VirtuosoDataAdapter adapter = new VirtuosoDataAdapter();
adapter.SelectCommand = (VirtuosoCommand)select;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable table = dataset.Tables["table"];
result.FailIfNotEqual(checkTable.Rows.Count, table.Rows.Count);
result.FailIfNotEqual(checkTable.Columns.Count, table.Columns.Count);
for (int i = 0; i < table.Rows.Count; i++)
{
Debug.WriteLine("row #" + i);
DataRow row = table.Rows[i];
DataRow checkRow = checkTable.Rows[i];
for (int j = 0; j < table.Columns.Count; j++)
{
string name = table.Columns[j].ColumnName;
result.FailIfNotEqual(this, "Comparison failed for column " + name + ": ", checkRow[name], row[name]);
}
}
}
internal Sleeper(string name, VirtuosoCommand command, int delay, int timeout)
{
this.name = name;
this.command = command;
this.delay = delay;
this.timeout = timeout;
}
public void ThreeCommands(TestCaseResult result)
{
/*
* VirtuosoCommand cmd1 = connection.CreateCommand ();
* Sleeper sleeper1 = new Sleeper ("first", cmd1, 30, 60);
* Thread thread1 = new Thread (new ThreadStart (sleeper1.Sleep));
*/
VirtuosoCommand cmd2 = connection.CreateCommand();
Sleeper sleeper2 = new Sleeper("second", cmd2, 20, 40);
Thread thread2 = new Thread(new ThreadStart(sleeper2.Sleep));
VirtuosoCommand cmd3 = connection.CreateCommand();
Sleeper sleeper3 = new Sleeper("third", cmd3, 10, 20);
Thread thread3 = new Thread(new ThreadStart(sleeper3.Sleep));
try
{
//thread1.Start ();
//Thread.Sleep (2000);
thread2.Start();
Thread.Sleep(2000);
thread3.Start();
}
finally
{
//thread1.Join ();
thread2.Join();
thread3.Join();
//cmd1.Dispose ();
cmd2.Dispose();
cmd3.Dispose();
}
}
private VirtuosoCommand GetInsertCommand(DataTableMapping mapping, DataRow row)
{
Debug.WriteLineIf(CLI.FnTrace.Enabled, "VirtuosoCommandBuilder.GetInsertCommand()");
VirtuosoCommand selectCommand = CollectInfo();
VirtuosoCommand command = insertCommand;
if (command == null)
{
command = CreateCommand(selectCommand);
}
command.Parameters.Clear();
StringBuilder text = new StringBuilder("insert into ");
text.Append(tableName);
text.Append(" (");
int n = columns.Length;
for (int i = 0; i < n; i++)
{
ColumnData c = columns[i];
if (c.IsAutoIncrement || c.IsRowVersion || c.IsHidden || c.IsExpression)
{
continue;
}
if (i != 0)
{
text.Append(", ");
}
text.Append(c.baseColumnName);
}
text.Append(") values (");
for (int i = 0; i < n; i++)
{
ColumnData c = columns[i];
if (c.IsAutoIncrement || c.IsRowVersion || c.IsHidden || c.IsExpression)
{
continue;
}
if (i != 0)
{
text.Append(", ");
}
text.Append("?");
AddParameter(command, c, DataRowVersion.Current, mapping, row);
}
text.Append(")");
command.CommandText = text.ToString();
command.CommandType = CommandType.Text;
command.UpdatedRowSource = UpdateRowSource.None;
insertCommand = command;
return(command);
}
public void OutputParameters(TestCaseResult result)
{
DropProcedure();
ExecuteNonQuery(
"create procedure bar (in x integer, out y integer, inout z integer)\n" +
"{\n" +
" y := x * 2;\n" +
" z := z * 2;\n" +
" return y + z;\n" +
"}\n"
);
VirtuosoCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "bar";
VirtuosoParameter returnValue = command.CreateParameter();
returnValue.ParameterName = "ReturnValue";
returnValue.Direction = ParameterDirection.ReturnValue;
returnValue.VirtDbType = VirtDbType.Integer;
command.Parameters.Add(returnValue);
VirtuosoParameter x = command.CreateParameter();
x.ParameterName = "x";
x.Direction = ParameterDirection.Input;
x.VirtDbType = VirtDbType.Integer;
x.Value = 2;
command.Parameters.Add(x);
VirtuosoParameter y = command.CreateParameter();
y.ParameterName = "y";
y.Direction = ParameterDirection.Output;
y.VirtDbType = VirtDbType.Integer;
command.Parameters.Add(y);
VirtuosoParameter z = command.CreateParameter();
z.ParameterName = "z";
z.Direction = ParameterDirection.InputOutput;
z.VirtDbType = VirtDbType.Integer;
z.Value = 3;
command.Parameters.Add(z);
try
{
command.ExecuteNonQuery();
result.FailIfNotEqual(this, "Return Value", 10, returnValue.Value);
result.FailIfNotEqual(this, "Out Parameter", 4, y.Value);
result.FailIfNotEqual(this, "InOut Parameter", 6, z.Value);
}
finally
{
command.Dispose();
}
}
private void CreateTable()
{
VirtuosoCommand create = connection.CreateCommand();
create.CommandText = "create table xmlt (id int primary key, data long xml)";
create.ExecuteNonQuery();
create.Dispose();
}
private void InsertRowText()
{
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText = "insert into xmlt (id, data) values (1, ?)";
insert.Parameters.Add(new VirtuosoParameter(":0", TheXml));
insert.ExecuteNonQuery();
insert.Dispose();
}
internal bool Remove(IntPtr hstmt, VirtuosoCommand command)
{
Debug.WriteLineIf(CLI.FnTrace.Enabled, "StatementList.Remove (" + hstmt + ")");
#if !WIN32_FINALIZERS
lock (list)
{
for (int i = 0; i < list.Count; i++)
{
Item item = (Item)list[i];
if (item.hstmt == hstmt)
{
list.RemoveAt(i);
CLI.SQLFreeHandle((short)CLI.HandleType.SQL_HANDLE_STMT, hstmt);
}
}
CloseFinalizedCommands();
}
#else
if (commands != null)
{
lock (commands)
{
int i = 0;
while (i < commands.Count)
{
WeakReference weak = (WeakReference)commands[i];
VirtuosoCommand target = (VirtuosoCommand)weak.Target;
if (target == null || target == command)
{
commands.RemoveAt(i);
}
else
{
i++;
}
}
}
}
Lock();
int eltcount = Marshal.ReadInt32(head, 8);
IntPtr elts = Marshal.ReadIntPtr(head, 12);
for (int i = 0; i < eltcount; i++)
{
IntPtr h = Marshal.ReadIntPtr(elts, i * ELT_SIZE);
if (h == hstmt)
{
Marshal.WriteIntPtr(elts, i * ELT_SIZE, IntPtr.Zero);
Unlock();
CLI.SQLFreeHandle((short)CLI.HandleType.SQL_HANDLE_STMT, hstmt);
return(true);
}
}
Unlock();
#endif
return(false);
}
private VirtuosoCommand CreateCommand(VirtuosoCommand selectCommand)
{
VirtuosoCommand command = new VirtuosoCommand();
command.CommandTimeout = selectCommand.CommandTimeout;
command.Connection = selectCommand.Connection;
command.Transaction = selectCommand.Transaction;
return(command);
}
public void MultipleResultSets(TestCaseResult result)
{
DropProcedure();
ExecuteNonQuery(
"create procedure bar ()\n" +
"{\n" +
" declare i int;\n" +
" declare c char;\n" +
" result_names (i);\n" +
" result (1);\n" +
" result (2);\n" +
" end_result ();\n" +
" result_names (c);\n" +
" result ('a');\n" +
" result ('b');\n" +
" return 0;\n" +
"}\n"
);
VirtuosoCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "bar";
VirtuosoDataReader reader = null;
try
{
reader = command.ExecuteReader();
result.FailIfNotEqual(1, reader.FieldCount);
result.FailIfNotEqual("i", reader.GetName(0).ToLower());
result.FailIfNotEqual(typeof(int), reader.GetFieldType(0));
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual(1, reader["i"]);
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual(2, reader["i"]);
result.FailIfNotEqual(false, reader.Read());
result.FailIfNotEqual(true, reader.NextResult());
result.FailIfNotEqual(1, reader.FieldCount);
result.FailIfNotEqual("c", reader.GetName(0).ToLower());
result.FailIfNotEqual(typeof(string), reader.GetFieldType(0));
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual("a", reader["c"]);
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual("b", reader["c"]);
result.FailIfNotEqual(false, reader.NextResult());
}
finally
{
if (reader != null)
{
reader.Close();
}
command.Dispose();
}
}
private VirtuosoCommand CollectInfo()
{
if (adapter == null)
{
throw new InvalidOperationException("The DataAdapter property is not set.");
}
VirtuosoCommand selectCommand = (VirtuosoCommand)adapter.SelectCommand;
if (selectCommand == null)
{
throw new InvalidOperationException("The SelectCommand property is not set.");
}
VirtuosoConnection connection = (VirtuosoConnection)selectCommand.Connection;
if (connection == null)
{
throw new InvalidOperationException("The Connection property is not set.");
}
if (columns != null)
{
return(selectCommand);
}
bool close = false;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
close = true;
}
VirtuosoDataReader reader = null;
try
{
reader = (VirtuosoDataReader)selectCommand.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
tableName = GetTableName(reader.Columns);
columns = reader.Columns;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (close)
{
connection.Close();
}
}
return(selectCommand);
}
public override IInnerCommand CreateInnerCommand(VirtuosoCommand outerCommand)
{
if (commands == null)
{
commands = new ArrayList();
}
commands.Add(new WeakReference(outerCommand));
return(new ManagedCommand(this));
}
public DataTable ExecuteQuery(string queryString, ITransaction transaction = null)
{
DataTable result = new DataTable();
VirtuosoDataAdapter adapter = null;
VirtuosoCommand command = null;
try
{
command = Connection.CreateCommand();
command.CommandText = queryString;
if (transaction != null && transaction is VirtuosoTransaction)
{
command.Transaction = (transaction as VirtuosoTransaction).Transaction;
}
result.Columns.CollectionChanged += OnColumnsCollectionChanged;
adapter = new VirtuosoDataAdapter(command);
adapter.Fill(result);
result.Columns.CollectionChanged -= OnColumnsCollectionChanged;
}
catch (InvalidOperationException ex)
{
string msg = string.Format("Error: Caught {0} exception.", ex.GetType());
Debug.WriteLine(msg);
} /* This seems to be different in 7.x version of Openlink.Virtuoso.dll
* catch (VirtuosoException e)
* {
*
* if (e.ErrorCode == 40001)
* throw new ResourceLockedException(e);
* else
*
* throw;
* } */
finally
{
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return(result);
}
private void AddParameter(
VirtuosoCommand command,
ColumnData column,
DataRowVersion version,
DataTableMapping mapping,
DataRow row)
{
Debug.WriteLineIf(CLI.FnTrace.Enabled, "VirtuosoCommandBuilder.AddParameter()");
VirtuosoParameter parameter = new VirtuosoParameter();
string name = String.Format("p{0}", command.Parameters.Count);
object value = null;
if (row != null)
{
Debug.WriteLineIf(Switch.Enabled, " row != null");
//string datasetColumn = column.columnName;
if (mapping != null)
{
Debug.WriteLineIf(Switch.Enabled, " mapping != null");
DataColumnMapping columnMapping = mapping.GetColumnMappingBySchemaAction(
column.columnName,
adapter.MissingMappingAction);
if (columnMapping != null)
{
Debug.WriteLineIf(Switch.Enabled, " columnMapping != null");
DataColumn dataColumn = columnMapping.GetDataColumnBySchemaAction(
row.Table,
column.columnType.bufferType.type,
adapter.MissingSchemaAction);
if (dataColumn != null)
{
Debug.WriteLineIf(Switch.Enabled, " dataColumn != null");
value = row[dataColumn, version];
}
}
}
}
Debug.WriteLineIf(Switch.Enabled, " value: " + value);
parameter.ParameterName = name;
parameter.VirtDbType = column.columnType.vdbType;
parameter.Precision = (byte)column.precision;
parameter.Scale = (byte)column.scale;
parameter.Size = 0;
parameter.IsNullable = column.IsNullable;
parameter.Direction = ParameterDirection.Input;
parameter.SourceColumn = column.columnName;
parameter.SourceVersion = version;
parameter.Value = value;
command.Parameters.Add(parameter);
}
private VirtuosoCommand GetDeleteCommand(DataTableMapping mapping, DataRow row)
{
Debug.WriteLineIf(CLI.FnTrace.Enabled, "VirtuosoCommandBuilder.GetDeleteCommand()");
VirtuosoCommand selectCommand = CollectInfo();
VirtuosoCommand command = deleteCommand;
if (command == null)
{
command = CreateCommand(selectCommand);
}
command.Parameters.Clear();
StringBuilder text = new StringBuilder("delete from ");
text.Append(tableName);
text.Append(" where ");
bool first = true;
int n = columns.Length;
for (int i = 0; i < n; i++)
{
ColumnData c = columns[i];
if (c.IsLong || c.IsHidden || c.IsExpression)
{
continue;
}
if (first)
{
first = false;
}
else
{
text.Append(" and ");
}
text.Append("((? is null and ");
text.Append(c.baseColumnName);
text.Append(" is null) or (");
text.Append(c.baseColumnName);
text.Append(" = ?))");
AddParameter(command, c, DataRowVersion.Original, mapping, row);
AddParameter(command, c, DataRowVersion.Original, mapping, row);
}
command.CommandText = text.ToString();
command.CommandType = CommandType.Text;
command.UpdatedRowSource = UpdateRowSource.None;
deleteCommand = command;
return(command);
}
private void ExecuteNonQuery(string text)
{
VirtuosoCommand command = connection.CreateCommand();
command.CommandText = text;
try
{
command.ExecuteNonQuery();
}
finally
{
command.Dispose();
}
}
public void DelayLessThanTimeout(TestCaseResult result)
{
VirtuosoCommand command = connection.CreateCommand();
try
{
command.CommandTimeout = 50;
command.CommandText = "delay(5)";
command.ExecuteNonQuery();
}
finally
{
command.Dispose();
}
}
// Используется для организации запросов внутри транзакции
public IEnumerable <object[]> RunQuery(string sql, VirtuosoCommand runcommand)
{
runcommand.CommandText = sql;
var reader = runcommand.ExecuteReader();
int ncols = reader.FieldCount;
object[] data = new object[ncols];
while (reader.Read())
{
reader.GetValues(data);
yield return(data);
}
reader.Close();
}
// Начальная и конечная "скобки" транзакции. В серединке должны использоваться команды ТОЛЬКО на основе команды runcommand
public VirtuosoCommand RunStart()
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
connection.Open();
VirtuosoCommand runcommand = connection.CreateCommand();
runcommand.CommandType = System.Data.CommandType.Text;
var transaction = connection.BeginTransaction();
runcommand.Transaction = transaction;
return(runcommand);
}
public void CreateTable(VirtuosoConnection connection)
{
VirtuosoCommand create = connection.CreateCommand();
create.CommandText = "create table foo (id int primary key, txt varchar(100))";
create.ExecuteNonQuery();
create.Dispose();
checkTable = new DataTable();
checkTable.Columns.Add("id", typeof(int));
checkTable.Columns.Add("txt", typeof(string));
InsertRow(connection, 1);
InsertRow(connection, 2);
}
public void TestGetString(TestCaseResult result)
{
InsertRowText();
VirtuosoCommand cmd = connection.CreateCommand();
cmd.CommandText = "select data from xmlt";
VirtuosoDataReader rdr = cmd.ExecuteReader();
rdr.Read();
String x = rdr.GetString(0);
FailIfXmlNotEqual(result, x, TheXml);
}
public void DeriveParamters(TestCaseResult result)
{
DropProcedure();
ExecuteNonQuery(
"create function BAR (in X integer, out Y integer, inout Z integer, in V varchar(20), in W nvarchar(20), in D numeric(20, 5)) returns real\n" +
"{\n" +
" return 0.0;\n" +
"}\n"
);
VirtuosoCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "BAR";
try
{
VirtuosoCommandBuilder.DeriveParameters(command);
result.FailIfNotEqual("Parameter Count", 7, command.Parameters.Count);
CheckParameter(result, command.Parameters[0],
"ReturnValue", ParameterDirection.ReturnValue, VirtDbType.Real, DbType.Single,
4, 0, 0); // FIXME: The precision should be 7.
CheckParameter(result, command.Parameters[1],
"X", ParameterDirection.Input, VirtDbType.Integer, DbType.Int32,
4, 10, 0);
CheckParameter(result, command.Parameters[2],
"Y", ParameterDirection.Output, VirtDbType.Integer, DbType.Int32,
4, 10, 0);
CheckParameter(result, command.Parameters[3],
"Z", ParameterDirection.InputOutput, VirtDbType.Integer, DbType.Int32,
4, 10, 0);
CheckParameter(result, command.Parameters[4],
"V", ParameterDirection.Input, VirtDbType.VarChar, DbType.AnsiString,
20, 0, 0);
CheckParameter(result, command.Parameters[5],
"W", ParameterDirection.Input, VirtDbType.NVarChar, DbType.String,
20, 0, 0);
CheckParameter(result, command.Parameters[6],
"D", ParameterDirection.Input, VirtDbType.Decimal, DbType.Decimal,
19, 20, 5);
}
finally
{
command.Dispose();
}
}
private void ExecuteDropCommand(string text)
{
VirtuosoCommand command = connection.CreateCommand();;
command.CommandText = text;
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
command.Dispose();
}
}
public Object get_var_data (String var, String part, String query, String vars, String xmlnss)
{
// Console.WriteLine ("get_var_data:0");
try
{
c.Open();
}
catch (Exception e)
{
// Console.WriteLine("{0} Exception caught.", e);
throw;
}
// Console.WriteLine ("get_var_data:1");
VirtuosoCommand cmd = new VirtuosoCommand ("select BPEL.BPEL.get_var_from_dump (?, ?, ?, ?, ?)", c);
// Console.WriteLine ("get_var_data:2");
cmd.Parameters.Add (":1", SqlDbType.VarChar);
cmd.Parameters.Add (":2", SqlDbType.VarChar);
cmd.Parameters.Add (":3", SqlDbType.VarChar);
cmd.Parameters.Add (":4", SqlDbType.VarChar);
cmd.Parameters.Add (":5", SqlDbType.VarChar);
// Console.WriteLine ("get_var_data:3");
cmd.Parameters[":1"].Value = var;
cmd.Parameters[":2"].Value = part;
cmd.Parameters[":3"].Value = query;
cmd.Parameters[":4"].Value = vars;
cmd.Parameters[":5"].Value = xmlnss;
// Console.WriteLine ("get_var_data:4");
String result = (String) cmd.ExecuteScalar();
// Console.WriteLine (result);
// Console.WriteLine ("get_var_data:7");
c.Close();
// Console.WriteLine ("get_var_data:8");
return result;
}
public String set_var_data (String var, String part, String query, Object val, String vars, String xmlnss)
{
c.Open();
VirtuosoCommand cmd = new VirtuosoCommand ("select BPEL.BPEL.set_var_to_dump (?, ?, ?, ?, ?, ?)", c);
cmd.Parameters.Add (":1", SqlDbType.VarChar);
cmd.Parameters.Add (":2", SqlDbType.VarChar);
cmd.Parameters.Add (":3", SqlDbType.VarChar);
cmd.Parameters.Add (":4", SqlDbType.VarChar);
cmd.Parameters.Add (":5", SqlDbType.VarChar);
cmd.Parameters.Add (":6", SqlDbType.VarChar);
cmd.Parameters[":1"].Value = var;
cmd.Parameters[":2"].Value = part;
cmd.Parameters[":3"].Value = query;
cmd.Parameters[":4"].Value = val;
cmd.Parameters[":5"].Value = vars;
cmd.Parameters[":6"].Value = xmlnss;
String result = (String) cmd.ExecuteScalar();
c.Close();
return result;
}
/**
* Gets a description of table columns available in
* the specified catalog.
*
* <P>Only column descriptions matching the catalog, schema, table
* and column name criteria are returned. They are ordered by
* TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
*
* <P>Each column description has the following columns:
* <OL>
* <LI><B>TABLE_CAT</B> String => table catalog (may be null)
* <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
* <LI><B>TABLE_NAME</B> String => table name
* <LI><B>COLUMN_NAME</B> String => column name
* <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
* <LI><B>TYPE_NAME</B> String => Data source dependent type name,
* for a UDT the type name is fully qualified
* <LI><B>COLUMN_SIZE</B> int => column size. For char or date
* types this is the maximum number of characters, for numeric or
* decimal types this is precision.
* <LI><B>BUFFER_LENGTH</B> is not used.
* <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
* <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
* <LI><B>NULLABLE</B> int => is NULL allowed?
* <UL>
* <LI> columnNoNulls - might not allow NULL values
* <LI> columnNullable - definitely allows NULL values
* <LI> columnNullableUnknown - nullability unknown
* </UL>
* <LI><B>REMARKS</B> String => comment describing column (may be null)
* <LI><B>COLUMN_DEF</B> String => default value (may be null)
* <LI><B>SQL_DATA_TYPE</B> int => unused
* <LI><B>SQL_DATETIME_SUB</B> int => unused
* <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
* maximum number of bytes in the column
* <LI><B>ORDINAL_POSITION</B> int => index of column in table
* (starting at 1)
* <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
* does not allow NULL values; "YES" means the column might
* allow NULL values. An empty string means nobody knows.
* </OL>
*
* @param catalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* @param schemaPattern a schema name pattern; "" retrieves those
* without a schema
* @param tableNamePattern a table name pattern
* @param columnNamePattern a column name pattern
* @return ResultSet - each row is a column description
* @exception virtuoso.jdbc2.VirtuosoException if a database access error
* occurs
* @see #getSearchStringEscape
*/
private DataTable GetSchemaColumns(string catalog, string schema, string table, string column)
{
DataTable dt = new DataTable("Columns");
// Following schema mirrors System.Data.Odbc provider.
// System.Data.SqlClient provider schema differs significantly.
dt.Columns.Add("TABLE_CAT", typeof(string));
dt.Columns.Add("TABLE_SCHEM", typeof(string));
dt.Columns.Add("TABLE_NAME", typeof(string));
dt.Columns.Add("COLUMN_NAME", typeof(string));
dt.Columns.Add("DATA_TYPE", typeof(short));
dt.Columns.Add("TYPE_NAME", typeof(string));
dt.Columns.Add("COLUMN_SIZE", typeof(int));
dt.Columns.Add("BUFFER_LENGTH", typeof(int));
dt.Columns.Add("DECIMAL_DIGITS", typeof(short));
dt.Columns.Add("NUM_PREC_RADIX", typeof(short));
dt.Columns.Add("NULLABLE", typeof(short));
dt.Columns.Add("REMARKS", typeof(string));
dt.Columns.Add("COLUMN_DEF", typeof(string));
dt.Columns.Add("SQL_DATA_TYPE", typeof(short));
dt.Columns.Add("SQL_DATETIME_SUB", typeof(short));
dt.Columns.Add("CHAR_OCTET_LENGTH", typeof(int));
dt.Columns.Add("ORDINAL_POSITION", typeof(int));
dt.Columns.Add("IS_NULLABLE", typeof(string));
if(catalog == null)
catalog = "";
if(schema == null)
schema = "";
if(table == null)
table = "";
if(column == null)
column = "";
catalog = (catalog.Length == 0) ? "%" : catalog;
schema = (schema.Length == 0) ? "%" : schema;
table = (table.Length == 0) ? "%" : table;
column = (column.Length == 0) ? "%" : column;
DataTable dtColumns = new DataTable("Columns");
String cmdText;
cmdText = (innerConnection.IdentCase ==
CLI.IdentCase.SQL_IC_MIXED) ? getWideColumnsText_case2 :
getWideColumnsText_case0;
VirtuosoCommand cmd = new VirtuosoCommand(cmdText ,this);
VirtuosoParameter p1 = (VirtuosoParameter) cmd.CreateParameter();
p1.Value = catalog;
p1.ParameterName = ("@catalog");
cmd.Parameters.Add (p1);
VirtuosoParameter p2 = (VirtuosoParameter) cmd.CreateParameter();
p2.Value = schema;
p2.ParameterName = ("@schema");
cmd.Parameters.Add (p2);
VirtuosoParameter p3 = (VirtuosoParameter) cmd.CreateParameter();
p3.Value = table;
p3.ParameterName = ("@table");
cmd.Parameters.Add (p3);
VirtuosoParameter p4 = (VirtuosoParameter) cmd.CreateParameter();
p4.Value = column;
p4.ParameterName = ("@column");
cmd.Parameters.Add (p4);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dtColumns.Load(reader);
return dt;
}
private DataTable GetSchemaColumnPrivileges(string catalog, string schema, string table, string column)
{
DataTable dt = new DataTable("ColumnPrivileges");
StringBuilder cmdText = new StringBuilder ("select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES where ");
if (catalog != null && catalog.Length != 0)
cmdText.Append ("TABLE_CATALOG like '" + catalog + "' AND ");
if (schema != null && schema.Length != 0)
cmdText.Append ("TABLE_SCHEMA like '" + schema + "' AND ");
if (table != null && table.Length != 0)
cmdText.Append ("TABLE_NAME like '" + table + "' AND ");
if (column != null && column.Length != 0)
cmdText.Append ("COLUMN_NAME like '" + column + "' AND ");
cmdText.Append ("0 = 0");
VirtuosoCommand cmd = new VirtuosoCommand (cmdText.ToString() ,this);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
return dt;
}
internal Item (IntPtr hstmt, VirtuosoCommand command)
{
this.hstmt = hstmt;
this.weakCommand = command == null ? null : new WeakReference (command);
}
public VirtuosoCommand CreateCommand ()
#endif
{
// Return a new instance of a command object.
Debug.WriteLineIf (CLI.FnTrace.Enabled, "VirtuosoConnection.CreateCommand()");
VirtuosoCommand command = new VirtuosoCommand ();
command.Connection = this;
return command;
}
private DataTable GetSchemaViews(string catalog, string schema, string view)
{
DataTable dt = new DataTable("Views");
StringBuilder cmdText = new StringBuilder ("select TABLE_CATALOG as TABLE_CAT, TABLE_SCHEMA as TABLE_SCHEM, TABLE_NAME, 'VIEW' as TABLE_TYPE from INFORMATION_SCHEMA.VIEWS where ");
if (catalog != null && catalog.Length != 0)
cmdText.Append ("TABLE_CATALOG like '" + catalog + "' AND ");
if (schema != null && schema.Length != 0)
cmdText.Append ("TABLE_SCHEMA like '" + schema + "' AND ");
if (view != null && view.Length != 0)
cmdText.Append ("TABLE_NAME like '" + view + "' AND ");
cmdText.Append ("0 = 0");
VirtuosoCommand cmd = new VirtuosoCommand (cmdText.ToString() ,this);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
return dt;
}
private DataTable GetSchemaProcedureParameters(string catalog, string schema, string procedure, string column)
{
DataTable dt = new DataTable("ProcedureParameters");
// Following schema mirrors System.Data.Odbc provider.
dt.Columns.Add("PROCEDURE_CAT", typeof(string));
dt.Columns.Add("PROCEDURE_SCHEM", typeof(string));
dt.Columns.Add("PROCEDURE_NAME", typeof(string));
dt.Columns.Add("COLUMN_NAME", typeof(string));
dt.Columns.Add("COLUMN_TYPE", typeof(short));
dt.Columns.Add("DATA_TYPE", typeof(short));
dt.Columns.Add("TYPE_NAME", typeof(string));
dt.Columns.Add("COLUMN_SIZE", typeof(int));
dt.Columns.Add("BUFFER_LENGTH", typeof(int));
dt.Columns.Add("DECIMAL_DIGITS", typeof(short));
dt.Columns.Add("NUM_PREC_RADIX", typeof(short));
dt.Columns.Add("NULLABLE", typeof(short));
dt.Columns.Add("REMARKS", typeof(string));
dt.Columns.Add("COLUMN_DEF", typeof(string));
dt.Columns.Add("SQL_DATA_TYPE", typeof(short));
dt.Columns.Add("SQL_DATETIME_SUB", typeof(short));
dt.Columns.Add("CHAR_OCTET_LENGTH", typeof(int));
dt.Columns.Add("ORDINAL_POSITION", typeof(int));
dt.Columns.Add("IS_NULLABLE", typeof(string));
if(catalog == null)
catalog = "%";
if(schema == null)
schema = "%";
if(procedure == null)
procedure = "%";
if(column == null)
column = "%";
string cmdText = "DB.DBA.SQL_PROCEDURE_COLUMNSW (?, ?, ?, ?, ?, ?)";
VirtuosoCommand cmd = new VirtuosoCommand(cmdText ,this);
// Arguments to SQL_PROCEDURE_COLUMNSW must be narrow so change
// the parameter type to AnsiString
VirtuosoParameter p1 = (VirtuosoParameter) cmd.CreateParameter();
p1.Value = catalog;
p1.ParameterName = ("@catalog");
p1.DbType = DbType.AnsiString;
cmd.Parameters.Add (p1);
VirtuosoParameter p2 = (VirtuosoParameter) cmd.CreateParameter();
p2.Value = schema;
p2.ParameterName = ("@schema");
p2.DbType = DbType.AnsiString;
cmd.Parameters.Add (p2);
VirtuosoParameter p3 = (VirtuosoParameter) cmd.CreateParameter();
p3.Value = procedure;
p3.ParameterName = ("@procedure");
p3.DbType = DbType.AnsiString;
cmd.Parameters.Add (p3);
VirtuosoParameter p4 = (VirtuosoParameter) cmd.CreateParameter();
p4.Value = column;
p4.ParameterName = ("@column");
p4.DbType = DbType.AnsiString;
cmd.Parameters.Add (p4);
VirtuosoParameter p5 = (VirtuosoParameter) cmd.CreateParameter();
if (innerConnection.IdentCase == CLI.IdentCase.SQL_IC_MIXED)
p5.Value = 2;
else if (innerConnection.IdentCase == CLI.IdentCase.SQL_IC_UPPER)
p5.Value = 1;
else
p5.Value = 0;
p5.ParameterName = ("@case");
cmd.Parameters.Add (p5);
VirtuosoParameter p6 = (VirtuosoParameter) cmd.CreateParameter();
p6.Value = 1;
p6.ParameterName = ("@isODBC3");
cmd.Parameters.Add (p6);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
// The MS Odbc provider supports both ProcedureColumns and
// ProcedureParameters metadata collections. This provider
// does likewise. Filter the output on COLUMN_TYPE to
// differentiate these two metadata collections.
foreach (DataRow dr in dt.Rows)
{
if (dr[4] != null)
{
short colType = Int16.Parse(dr[4].ToString());
if (colType == (short)CLI.InOutType.SQL_RESULT_COL)
dr.Delete();
}
}
dt.AcceptChanges();
return dt;
}
/**
* Gets a description of the stored procedures available in a
* catalog.
*
* <P>Only procedure descriptions matching the schema and
* procedure name criteria are returned. They are ordered by
* PROCEDURE_SCHEM, and PROCEDURE_NAME.
*
* <P>Each procedure description has the following columns:
* <OL>
* <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
* <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
* <LI><B>PROCEDURE_NAME</B> String => procedure name
* <LI> reserved for future use
* <LI> reserved for future use
* <LI> reserved for future use
* <LI><B>REMARKS</B> String => explanatory comment on the procedure
* <LI><B>PROCEDURE_TYPE</B> short => kind of procedure:
* <UL>
* <LI> procedureResultUnknown - May return a result
* <LI> procedureNoResult - Does not return a result
* <LI> procedureReturnsResult - Returns a result
* </UL>
* </OL>
*
* @param catalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* @param schemaPattern a schema name pattern; "" retrieves those
* without a schema
* @param procedureNamePattern a procedure name pattern
* @return ResultSet - each row is a procedure description
* @exception virtuoso.jdbc2.VirtuosoException if a database access error
* occurs
* @see #getSearchStringEscape
*/
private DataTable GetSchemaProcedures(string catalog, string schema, string procedure, string type)
{
CLI.ProcedureType procType = CLI.ProcedureType.SQL_PT_UNKNOWN;
if (type != null && type.Trim().Length > 0)
{
try
{
procType = (CLI.ProcedureType)Int32.Parse(type);
}
catch
{
throw new ArgumentOutOfRangeException("The string received for a PROCEDURE_TYPE restriction must " +
"parse to one of the following integer values: SQL_PT_UNKNOWN(0), SQL_PT_PROCEDURE(1) or " +
"SQL_PT_FUNCTION(2)");
}
}
if(catalog == null)
catalog = "%";
if(schema == null)
schema = "%";
if(procedure == null)
procedure = "%";
DataTable dt = new DataTable("Procedures");
String cmdText;
cmdText = innerConnection.IdentCase == CLI.IdentCase.SQL_IC_MIXED ?
getWideProceduresCaseMode2 :
getWideProceduresCaseMode0;
VirtuosoCommand cmd = new VirtuosoCommand(cmdText ,this);
VirtuosoParameter p1 = (VirtuosoParameter) cmd.CreateParameter();
p1.Value = catalog;
p1.ParameterName = ("@catalog");
cmd.Parameters.Add (p1);
VirtuosoParameter p2 = (VirtuosoParameter) cmd.CreateParameter();
p2.Value = schema;
p2.ParameterName = ("@schema");
cmd.Parameters.Add (p2);
VirtuosoParameter p3 = (VirtuosoParameter) cmd.CreateParameter();
p3.Value = procedure;
p3.ParameterName = ("@procedure");
cmd.Parameters.Add (p3);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
// Filter on the 'type' restriction here.
// The underlying SQLProcedures call doesn't support this.
if (procType != CLI.ProcedureType.SQL_PT_UNKNOWN)
{
foreach (DataRow dr in dt.Rows)
{
if (dr[7] != null)
{
int iProcType = Int32.Parse(dr[7].ToString());
if (iProcType != (int)CLI.ProcedureType.SQL_PT_UNKNOWN &&
iProcType != (int)procType)
dt.Rows.Remove (dr);
}
}
}
dt.AcceptChanges();
return dt;
}
object ICloneable.Clone ()
{
VirtuosoCommand command = new VirtuosoCommand ();
command.Connection = this.Connection;
command.CommandText = this.CommandText;
command.CommandType = this.CommandType;
command.CommandTimeout = this.CommandTimeout;
#if (!MONO || ADONET2)
command.DesignTimeVisible = this.DesignTimeVisible;
#endif
command.Transaction = this.Transaction;
command.UpdatedRowSource = this.UpdatedRowSource;
command.executeSecondaryStmt = this.executeSecondaryStmt;
command.secondaryStmt = this.secondaryStmt;
foreach (VirtuosoParameter p in this.Parameters)
command.Parameters.Add (((ICloneable) p).Clone());
return command;
}
public void Close ()
#endif
{
Debug.WriteLineIf (CLI.FnTrace.Enabled, "VirtuosoDataReader.Close()");
if (open)
{
if (command != null)
{
command.CloseDataReader ();
command = null;
}
if (connection != null && (commandBehavior & CommandBehavior.CloseConnection) != 0)
{
connection.Close ();
connection = null;
}
columns = null;
open = false;
}
}
/*
* Because the user should not be able to directly create a
* DataReader object, the constructors are
* marked as internal.
*/
internal VirtuosoDataReader (
VirtuosoConnection connection,
IInnerCommand innerCommand,
VirtuosoCommand command,
CommandBehavior commandBehavior,
bool schemaOnly)
{
Debug.WriteLineIf (CLI.FnTrace.Enabled, "VirtuosoDataReader.ctor()");
Debug.Assert (connection != null);
Debug.Assert (innerCommand != null);
this.connection = connection;
this.command = command;
this.innerCommand = innerCommand;
this.commandBehavior = commandBehavior;
InitializeResultInfo (schemaOnly);
if (schemaOnly)
{
over = true;
last = true;
}
}
internal OdbcCommand (IntPtr hstmt, VirtuosoCommand outerCommand)
{
this.hstmt = hstmt;
this.outerCommand = outerCommand;
}
internal void Add (IntPtr hstmt, VirtuosoCommand command)
{
Debug.WriteLineIf (CLI.FnTrace.Enabled, "StatementList.Add (" + hstmt + ")");
#if !WIN32_FINALIZERS
lock (list)
{
list.Add (new Item (hstmt, command));
CloseFinalizedCommands ();
}
#else
if (command != null)
{
lock (commands)
{
commands.Add (new WeakReference (command));
}
}
Lock ();
int eltcount = Marshal.ReadInt32 (head, 8);
IntPtr elts = Marshal.ReadIntPtr (head, 12);
for (int i = 0; i < eltcount; i++)
{
IntPtr h = Marshal.ReadIntPtr (elts, i * ELT_SIZE);
if (h == IntPtr.Zero)
{
Marshal.WriteIntPtr (elts, i * ELT_SIZE, hstmt);
Unlock ();
return;
}
}
int neweltcount = eltcount == 0 ? INITIAL_ELT_COUNT : eltcount * 2;
IntPtr newelts = Marshal.ReAllocCoTaskMem (elts, neweltcount * ELT_SIZE);
for (int i = eltcount + 1; i < neweltcount; i++)
Marshal.WriteIntPtr (newelts, i * ELT_SIZE, IntPtr.Zero);
Marshal.WriteIntPtr (newelts, eltcount * ELT_SIZE, hstmt);
Marshal.WriteInt32 (head, 8, neweltcount);
Marshal.WriteIntPtr (head, 12, newelts);
Unlock ();
#endif
}
private DataTable GetSchemaSpecialColumns(
string identifierType,
string catalog,
string schema,
string table,
string scope,
string nullable
)
{
if(catalog == null)
catalog = "";
if(schema == null)
schema = "";
if(table == null)
table = "";
CLI.IdentifierType _identifierType;
CLI.Scope _scope;
CLI.Nullable _nullable;
// Convert non-string restrictions from supplied string
if (String.Compare(identifierType,
SpecialColumnsRestrictions.IDENTIFIER_TYPE_BEST_ROWID) == 0)
_identifierType = CLI.IdentifierType.SQL_BEST_ROWID;
else if (String.Compare(identifierType,
SpecialColumnsRestrictions.IDENTIFIER_TYPE_ROWVER) == 0)
_identifierType = CLI.IdentifierType.SQL_ROWVER;
else
throw new ArgumentOutOfRangeException(
#if MONO
"SpecialColumnsRestrictions",
"IDENTIFIER_TYPE restriction out of range");
#else
"IDENTIFIER_TYPE restriction out of range",
(Exception) null);
#endif
if (String.Compare(scope,
SpecialColumnsRestrictions.SCOPE_CURROW) == 0)
_scope = CLI.Scope.SQL_SCOPE_CURROW;
else if (String.Compare(scope,
SpecialColumnsRestrictions.SCOPE_SESSION) == 0)
_scope = CLI.Scope.SQL_SCOPE_SESSION;
else if (String.Compare(scope,
SpecialColumnsRestrictions.SCOPE_TRANSACTION) == 0)
_scope = CLI.Scope.SQL_SCOPE_TRANSACTION;
else
throw new ArgumentOutOfRangeException(
#if MONO
"SpecialColumnsRestrictions",
"SCOPE restriction out of range");
#else
"SCOPE restriction out of range",
(Exception) null);
#endif
if (String.Compare(nullable,
SpecialColumnsRestrictions.NO_NULLS) == 0)
_nullable = CLI.Nullable.SQL_NO_NULLS;
else if (String.Compare(nullable,
SpecialColumnsRestrictions.NULLABLE) == 0)
_nullable = CLI.Nullable.SQL_NULLABLE;
else
throw new ArgumentOutOfRangeException(
#if MONO
"SpecialColumnsRestrictions",
"NULLABLE restriction out of range");
#else
"NULLABLE restriction out of range",
(Exception) null);
#endif
if (table == null || table.Trim().Length == 0)
throw new ArgumentOutOfRangeException(
#if MONO
"table",
"TABLE_NAME restriction cannot be null or empty");
#else
"TABLE_NAME restriction cannot be null or empty",
(Exception) null);
#endif
DataTable dt = new DataTable("SpecialColumns");
/*
* Comment from virtodbc implementation for SQLSpecialColumns
*/
/* fColType must be one of the following values:
SQL_BEST_ROWID: Returns the optimal column or set of columns that,
by retrieving values from the column or columns, allows any row in
the specified table to be uniquely identified. A column can be either
a pseudocolumn specifically designed for this purpose
(as in Oracle ROWID or Ingres TID) or the column or columns of any
unique index for the table.
Well, we implement this later better. Now just choose all the columns
of the primary key.
(0 = SQL_SCOPE_CURROW) Let's use the most narrow scope as I am not
really sure about this. fScope argument is
ignored anyway.
(1 = SQL_SCOPE_TRANSACTION)
(2 = SQL_SCOPE_SESSION)
*/
String cmdText;
if (_identifierType != CLI.IdentifierType.SQL_ROWVER)
{
cmdText = (innerConnection.IdentCase ==
CLI.IdentCase.SQL_IC_MIXED) ?
sql_special_columnsw1_casemode_2:
sql_special_columnsw1_casemode_0;
}
else
{
/*
fColType is SQL_ROWVER: Returns the column or columns in the
specified table, if any, that are automatically updated by the
data source when any value in the row is updated by any transaction
as in SQLBase ROWID or Sybase (and KUBL!) TIMESTAMP (= COL_DTP 128).
*/
cmdText = (innerConnection.IdentCase ==
CLI.IdentCase.SQL_IC_MIXED) ?
sql_special_columnsw2_casemode_2:
sql_special_columnsw2_casemode_0;
/* With COL_ID returns columns in the same order as they were defined
with create table. Without it they would be in alphabetical order. */
}
VirtuosoCommand cmd = new VirtuosoCommand(cmdText ,this);
VirtuosoParameter p1 = (VirtuosoParameter) cmd.CreateParameter();
p1.Value = catalog;
p1.ParameterName = ("@catalog");
cmd.Parameters.Add (p1);
VirtuosoParameter p2 = (VirtuosoParameter) cmd.CreateParameter();
p2.Value = schema;
p2.ParameterName = ("@schema");
cmd.Parameters.Add (p2);
VirtuosoParameter p3 = (VirtuosoParameter) cmd.CreateParameter();
p3.Value = table;
p3.ParameterName = ("@table");
cmd.Parameters.Add (p3);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
return dt;
}
public abstract void RemoveCommand (VirtuosoCommand outerCommand);
/**
* Gets a description of a table's indices and statistics. They are
* ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
*
* <P>Each index column description has the following columns:
* <OL>
* <LI><B>TABLE_CAT</B> String => table catalog (may be null)
* <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
* <LI><B>TABLE_NAME</B> String => table name
* <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
* false when TYPE is tableIndexStatistic
* <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
* null when TYPE is tableIndexStatistic
* <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
* tableIndexStatistic
* <LI><B>TYPE</B> short => index type:
* <UL>
* <LI> tableIndexStatistic - this identifies table statistics that
* are
* returned in conjunction with a table's index descriptions
* <LI> tableIndexClustered - this is a clustered index
* <LI> tableIndexHashed - this is a hashed index
* <LI> tableIndexOther - this is some other style of index
* </UL>
* <LI><B>ORDINAL_POSITION</B> short => column sequence number
* within index; zero when TYPE is tableIndexStatistic
* <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
* tableIndexStatistic
* <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" =>
* ascending,
* "D" => descending, may be null if sort sequence is not supported;
* null when TYPE is tableIndexStatistic
* <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
* this is the number of rows in the table; otherwise, it is the
* number of unique values in the index.
* <LI><B>PAGES</B> int => When TYPE is tableIndexStatistic then
* this is the number of pages used for the table, otherwise it
* is the number of pages used for the current index.
* <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
* (may be null)
* </OL>
*
* @param catalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* @param schema a schema name; "" retrieves those without a schema
* @param table a table name
* @param unique when true, return only indices for unique values;
* when false, return indices regardless of whether unique or not
* @param approximate when true, result is allowed to reflect approximate
* or out of data values; when false, results are requested to be
* accurate
* @return ResultSet - each row is an index column description
* @exception virtuoso.jdbc2.VirtuosoException if a database access error
* occurs
*/
private DataTable GetSchemaIndexes(string catalog, string schema, string table, string index)
{
DataTable dt = new DataTable("Indexes");
if(catalog == null)
catalog = "";
if(schema == null)
schema = "";
if(table == null)
table = "";
catalog = catalog == "" ? "%" : catalog;
schema = schema == "" ? "%" : schema;
table = table == "" ? "%" : table;
String cmdText;
cmdText = (innerConnection.IdentCase ==
CLI.IdentCase.SQL_IC_MIXED) ?
sql_statistics_textw_casemode_2:
sql_statistics_textw_casemode_0;
VirtuosoCommand cmd = new VirtuosoCommand(cmdText ,this);
VirtuosoParameter p1 = (VirtuosoParameter) cmd.CreateParameter();
p1.Value = catalog;
p1.ParameterName = ("@catalog");
cmd.Parameters.Add (p1);
VirtuosoParameter p2 = (VirtuosoParameter) cmd.CreateParameter();
p2.Value = schema;
p2.ParameterName = ("@schema");
cmd.Parameters.Add (p2);
VirtuosoParameter p3 = (VirtuosoParameter) cmd.CreateParameter();
p3.Value = table;
p3.ParameterName = ("@table");
cmd.Parameters.Add (p3);
VirtuosoParameter p4 = (VirtuosoParameter) cmd.CreateParameter();
p4.Value = CLI.IndexType.SQL_INDEX_ALL;
p4.ParameterName = ("@unique");
cmd.Parameters.Add (p4);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
DataTable dtSqlStats = new DataTable("SQLStatistics");
dtSqlStats.Load(reader);
// Filter on any index restriction and exclude SQL_TABLE_STAT rows
string indexFilter = "";
if (index != null && index.Trim().Length > 0)
indexFilter = " AND INDEX_NAME LIKE '" + index + "'";
DataView dv = dtSqlStats.DefaultView;
dv.RowFilter = "TYPE <> 0" + indexFilter;
dt = dv.ToTable();
return dt;
}
public abstract IInnerCommand CreateInnerCommand (VirtuosoCommand outerCommand);
/**
* Gets a description of a table's primary key columns. They
* are ordered by COLUMN_NAME.
*
* <P>Each primary key column description has the following columns:
* <OL>
* <LI><B>TABLE_CAT</B> String => table catalog (may be null)
* <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
* <LI><B>TABLE_NAME</B> String => table name
* <LI><B>COLUMN_NAME</B> String => column name
* <LI><B>KEY_SEQ</B> short => sequence number within primary key
* <LI><B>PK_NAME</B> String => primary key name (may be null)
* </OL>
*
* @param catalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* @param schema a schema name; "" retrieves those
* without a schema
* @param table a table name
* @return ResultSet - each row is a primary key column description
* @exception virtuoso.jdbc2.VirtuosoException if a database access error
* occurs
*/
private DataTable GetSchemaPrimaryKeys(string catalog, string schema, string table)
{
DataTable dt = new DataTable("PrimaryKeys");
if(catalog == null)
catalog = "";
if(schema == null)
schema = "";
if(table == null)
table = "";
catalog = catalog == "" ? "%" : catalog;
schema = schema == "" ? "%" : schema;
table = table == "" ? "%" : table;
String cmdText;
cmdText = innerConnection.IdentCase == CLI.IdentCase.SQL_IC_MIXED ?
get_wide_pk_case2 :
get_wide_pk_case0;
VirtuosoCommand cmd = new VirtuosoCommand(cmdText ,this);
VirtuosoParameter p1 = (VirtuosoParameter) cmd.CreateParameter();
p1.Value = catalog;
p1.ParameterName = ("@catalog");
cmd.Parameters.Add (p1);
VirtuosoParameter p2 = (VirtuosoParameter) cmd.CreateParameter();
p2.Value = schema;
p2.ParameterName = ("@schema");
cmd.Parameters.Add (p2);
VirtuosoParameter p3 = (VirtuosoParameter) cmd.CreateParameter();
p3.Value = table;
p3.ParameterName = ("@table");
cmd.Parameters.Add (p3);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
return dt;
}
internal VirtuosoParameterCollection (VirtuosoCommand command)
{
this.command = command;
this.items = new ArrayList ();
}
private DataTable GetSchemaTables(string catalog, string schema, string table, string types)
{
DataTable dt = new DataTable("Tables");
StringBuilder cmdText = new StringBuilder ("select TABLE_CATALOG as TABLE_CAT, TABLE_SCHEMA as TABLE_SCHEM, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES where ");
if (catalog != null && catalog.Length != 0)
cmdText.Append ("TABLE_CATALOG like '" + catalog + "' AND ");
if (schema != null && schema.Length != 0)
cmdText.Append ("TABLE_SCHEMA like '" + schema + "' AND ");
if (table != null && table.Length != 0)
cmdText.Append ("TABLE_NAME like '" + table + "' AND ");
if (types != null && types.Length != 0)
cmdText.Append ("TABLE_TYPE like '**" + types + "' AND ");
cmdText.Append ("0 = 0");
VirtuosoCommand cmd = new VirtuosoCommand (cmdText.ToString() ,this);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
return dt;
}
public override void RemoveCommand (VirtuosoCommand outerCommand)
{
if (commands == null)
return;
int i = 0;
while (i < commands.Count)
{
WeakReference weak = (WeakReference) commands[i];
VirtuosoCommand target = (VirtuosoCommand) weak.Target;
if (target == null || target == outerCommand)
commands.RemoveAt(i);
else
i++;
}
}
/**
* Gets a description of the foreign key columns in the foreign key
* table that reference the primary key columns of the primary key
* table (describe how one table imports another's key.) This
* should normally return a single foreign key/primary key pair
* (most tables only import a foreign key from a table once.) They
* are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
* KEY_SEQ.
*
* <P>Each foreign key column description has the following columns:
* <OL>
* <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be
* null)
* <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be
* null)
* <LI><B>PKTABLE_NAME</B> String => primary key table name
* <LI><B>PKCOLUMN_NAME</B> String => primary key column name
* <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be
* null)
* being exported (may be null)
* <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
* null)
* being exported (may be null)
* <LI><B>FKTABLE_NAME</B> String => foreign key table name
* being exported
* <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
* being exported
* <LI><B>KEY_SEQ</B> short => sequence number within foreign key
* <LI><B>UPDATE_RULE</B> short => What happens to
* foreign key when primary is updated:
* <UL>
* <LI> importedNoAction - do not allow update of primary
* key if it has been imported
* <LI> importedKeyCascade - change imported key to agree
* with primary key update
* <LI> importedKeySetNull - change imported key to NULL if
* its primary key has been updated
* <LI> importedKeySetDefault - change imported key to default values
* if its primary key has been updated
* <LI> importedKeyRestrict - same as importedKeyNoAction
* (for ODBC 2.x compatibility)
* </UL>
* <LI><B>DELETE_RULE</B> short => What happens to
* the foreign key when primary is deleted.
* <UL>
* <LI> importedKeyNoAction - do not allow delete of primary
* key if it has been imported
* <LI> importedKeyCascade - delete rows that import a deleted key
* <LI> importedKeySetNull - change imported key to NULL if
* its primary key has been deleted
* <LI> importedKeyRestrict - same as importedKeyNoAction
* (for ODBC 2.x compatibility)
* <LI> importedKeySetDefault - change imported key to default if
* its primary key has been deleted
* </UL>
* <LI><B>FK_NAME</B> String => foreign key name (may be null)
* <LI><B>PK_NAME</B> String => primary key name (may be null)
* <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
* constraints be deferred until commit
* <UL>
* <LI> importedKeyInitiallyDeferred - see SQL92 for definition
* <LI> importedKeyInitiallyImmediate - see SQL92 for definition
* <LI> importedKeyNotDeferrable - see SQL92 for definition
* </UL>
* </OL>
*
* @param primaryCatalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* @param primarySchema a schema name; "" retrieves those
* without a schema
* @param primaryTable the table name that exports the key
* @param foreignCatalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* @param foreignSchema a schema name; "" retrieves those
* without a schema
* @param foreignTable the table name that imports the key
* @return ResultSet - each row is a foreign key column description
* @exception virtuoso.jdbc2.VirtuosoException if a database access error
* occurs
* @see #getImportedKeys
*/
private DataTable GetSchemaForeignKeys(
string pkCatalog, string pkSchema, string pkTable,
string fkCatalog, string fkSchema, string fkTable)
{
DataTable dt = new DataTable("ForeignKeys");
if(pkCatalog == null)
pkCatalog = "";
if(pkSchema == null)
pkSchema = "";
if(pkTable == null)
pkTable = "";
if(fkCatalog == null)
fkCatalog = "";
if(fkSchema == null)
fkSchema = "";
if(fkTable == null)
fkTable = "";
pkCatalog = pkCatalog == "" ? "%" : pkCatalog;
pkSchema = pkSchema == "" ? "%" : pkSchema;
pkTable = pkTable == "" ? "%" : pkTable;
fkCatalog = fkCatalog == "" ? "%" : fkCatalog;
fkSchema = fkSchema == "" ? "%" : fkSchema;
fkTable = fkTable == "" ? "%" : fkTable;
String cmdText;
cmdText = innerConnection.IdentCase == CLI.IdentCase.SQL_IC_MIXED ?
fk_textw_casemode_2 :
fk_textw_casemode_0;
VirtuosoCommand cmd = new VirtuosoCommand(cmdText ,this);
VirtuosoParameter p1 = (VirtuosoParameter) cmd.CreateParameter();
p1.Value = pkCatalog;
p1.ParameterName = ("@pkCatalog");
cmd.Parameters.Add (p1);
VirtuosoParameter p2 = (VirtuosoParameter) cmd.CreateParameter();
p2.Value = pkSchema;
p2.ParameterName = ("@pkSchema");
cmd.Parameters.Add (p2);
VirtuosoParameter p3 = (VirtuosoParameter) cmd.CreateParameter();
p3.Value = pkTable;
p3.ParameterName = ("@pkTable");
cmd.Parameters.Add (p3);
VirtuosoParameter p4 = (VirtuosoParameter) cmd.CreateParameter();
p4.Value = fkCatalog;
p4.ParameterName = ("@fkCatalog");
cmd.Parameters.Add (p4);
VirtuosoParameter p5 = (VirtuosoParameter) cmd.CreateParameter();
p5.Value = fkSchema;
p5.ParameterName = ("@fkSchema");
cmd.Parameters.Add (p5);
VirtuosoParameter p6 = (VirtuosoParameter) cmd.CreateParameter();
p6.Value = fkTable;
p6.ParameterName = ("@fkTable");
cmd.Parameters.Add (p6);
VirtuosoDataReader reader = (VirtuosoDataReader)cmd.ExecuteReader();
dt.Load(reader);
return dt;
}
public override IInnerCommand CreateInnerCommand (VirtuosoCommand outerCommand)
{
if (commands == null)
commands = new ArrayList();
commands.Add (new WeakReference (outerCommand));
return new ManagedCommand(this);
}
public override IInnerCommand CreateInnerCommand (VirtuosoCommand outerCommand)
{
IntPtr hstmt;
CLI.ReturnCode rc = (CLI.ReturnCode) CLI.SQLAllocHandle (
(short) CLI.HandleType.SQL_HANDLE_STMT,
hdbc,
out hstmt);
if (rc != CLI.ReturnCode.SQL_SUCCESS)
HandleConnectionErrors (rc);
try
{
//statementList.Add (hstmt, outerCommand);
Add (hstmt, outerCommand);
}
catch (Exception)
{
CLI.SQLFreeHandle ((short) CLI.HandleType.SQL_HANDLE_STMT, hstmt);
throw;
}
GC.KeepAlive (this);
return new OdbcCommand (hstmt, outerCommand);
}
internal bool Remove (IntPtr hstmt, VirtuosoCommand command)
{
Debug.WriteLineIf (CLI.FnTrace.Enabled, "StatementList.Remove (" + hstmt + ")");
#if !WIN32_FINALIZERS
lock (list)
{
for (int i = 0; i < list.Count; i++)
{
Item item = (Item) list[i];
if (item.hstmt == hstmt)
{
list.RemoveAt (i);
CLI.SQLFreeHandle ((short) CLI.HandleType.SQL_HANDLE_STMT, hstmt);
}
}
CloseFinalizedCommands ();
}
#else
if (commands != null)
{
lock (commands)
{
int i = 0;
while (i < commands.Count)
{
WeakReference weak = (WeakReference) commands[i];
VirtuosoCommand target = (VirtuosoCommand) weak.Target;
if (target == null || target == command)
commands.RemoveAt (i);
else
i++;
}
}
}
Lock ();
int eltcount = Marshal.ReadInt32 (head, 8);
IntPtr elts = Marshal.ReadIntPtr (head, 12);
for (int i = 0; i < eltcount; i++)
{
IntPtr h = Marshal.ReadIntPtr (elts, i * ELT_SIZE);
if (h == hstmt)
{
Marshal.WriteIntPtr (elts, i * ELT_SIZE, IntPtr.Zero);
Unlock ();
CLI.SQLFreeHandle ((short) CLI.HandleType.SQL_HANDLE_STMT, hstmt);
return true;
}
}
Unlock ();
#endif
return false;
}
public VirtuosoDataAdapter (VirtuosoCommand selectCommand) : this ()
{
SelectCommand = selectCommand;
}
public void DeleteInnerCommand (OdbcCommand odbcCommand, VirtuosoCommand outerCommand)
{
//statementList.Remove (odbcCommand.hstmt, outerCommand);
Remove (odbcCommand.hstmt, outerCommand);
}
