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();
}
}
/**
* 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;
}
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 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;
}
/**
* 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;
}
/**
* 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;
}
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;
}
/**
* 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 void InsertRow(int id)
{
object i, si, d, r, n, year, month, day, dt, tm, dtm, str, bin;
if (id == 0)
{
i = si = d = r = n = year = month = day = dt = tm = dtm = str = bin = DBNull.Value;
}
else
{
i = id + 1000;
si = (short)(id + 2000);
d = id * 1000.0001;
r = (float)(id * 100.001);
n = (decimal)id * 10000001;
year = 1990 + id;
month = (id - 1) % 12 + 1;
day = (id - 1) % DateTime.DaysInMonth((int)year, (int)month) + 1;
dt = new DateTime((int)year, (int)month, (int)day);
tm = new TimeSpan(id % 24, id % 60, id % 60);
dtm = new DateTime((int)year, (int)month, (int)day, id % 24, id % 60, id % 60);
int length = id % 128;
char[] chars = new char[length];
byte[] bytes = new byte[length];
for (int count = 0; count < length; count++)
{
chars[count] = (char)('a' + (id + count) % 26 - 1);
bytes[count] = (byte)(id + count);
}
str = new String(chars);
bin = bytes;
}
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText =
"insert into foo "
+ "(id, i, si, d, r, n, dt, tm, dtm, c, vc, lvc, nc, nvc, lnvc, b, vb, lvb) "
+ "values "
+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
VirtuosoParameterCollection parameters = insert.Parameters;
VirtuosoParameter idParam = insert.CreateParameter();
idParam.ParameterName = "id";
idParam.DbType = DbType.Int32;
idParam.Value = id;
parameters.Add(idParam);
VirtuosoParameter iParam = insert.CreateParameter();
iParam.ParameterName = "i";
iParam.DbType = DbType.Int32;
iParam.Value = i;
parameters.Add(iParam);
VirtuosoParameter siParam = insert.CreateParameter();
siParam.ParameterName = "si";
siParam.DbType = DbType.Int16;
siParam.Value = si;
parameters.Add(siParam);
VirtuosoParameter dParam = insert.CreateParameter();
dParam.ParameterName = "d";
dParam.DbType = DbType.Double;
dParam.Value = d;
parameters.Add(dParam);
VirtuosoParameter rParam = insert.CreateParameter();
rParam.ParameterName = "r";
rParam.DbType = DbType.Single;
rParam.Value = r;
parameters.Add(rParam);
VirtuosoParameter nParam = insert.CreateParameter();
nParam.ParameterName = "n";
nParam.DbType = DbType.Decimal;
nParam.Value = n;
parameters.Add(nParam);
VirtuosoParameter dtParam = insert.CreateParameter();
dtParam.ParameterName = "dt";
dtParam.DbType = DbType.Date;
dtParam.Value = dt;
parameters.Add(dtParam);
VirtuosoParameter tmParam = insert.CreateParameter();
tmParam.ParameterName = "tm";
tmParam.DbType = DbType.Time;
tmParam.Value = tm;
parameters.Add(tmParam);
VirtuosoParameter dtmParam = insert.CreateParameter();
dtmParam.ParameterName = "dtm";
dtmParam.DbType = DbType.DateTime;
dtmParam.Value = dtm;
parameters.Add(dtmParam);
VirtuosoParameter cParam = insert.CreateParameter();
cParam.ParameterName = "c";
cParam.DbType = DbType.AnsiStringFixedLength;
cParam.Value = str;
parameters.Add(cParam);
VirtuosoParameter vcParam = insert.CreateParameter();
vcParam.ParameterName = "vc";
vcParam.DbType = DbType.AnsiString;
vcParam.Value = str;
parameters.Add(vcParam);
VirtuosoParameter lvcParam = insert.CreateParameter();
lvcParam.ParameterName = "lvc";
lvcParam.DbType = DbType.AnsiString;
lvcParam.Value = str;
parameters.Add(lvcParam);
VirtuosoParameter ncParam = insert.CreateParameter();
ncParam.ParameterName = "nc";
ncParam.DbType = DbType.StringFixedLength;
ncParam.Value = str;
parameters.Add(ncParam);
VirtuosoParameter nvcParam = insert.CreateParameter();
nvcParam.ParameterName = "nvc";
nvcParam.DbType = DbType.String;
nvcParam.Value = str;
parameters.Add(nvcParam);
VirtuosoParameter lnvcParam = insert.CreateParameter();
lnvcParam.ParameterName = "lnvc";
lnvcParam.DbType = DbType.String;
lnvcParam.Value = str;
parameters.Add(lnvcParam);
VirtuosoParameter bParam = insert.CreateParameter();
bParam.ParameterName = "b";
bParam.DbType = DbType.Binary;
bParam.Value = bin;
parameters.Add(bParam);
VirtuosoParameter vbParam = insert.CreateParameter();
vbParam.ParameterName = "vb";
vbParam.DbType = DbType.Binary;
vbParam.Value = bin;
parameters.Add(vbParam);
VirtuosoParameter lvbParam = insert.CreateParameter();
lvbParam.ParameterName = "lvb";
lvbParam.DbType = DbType.Binary;
lvbParam.Value = bin;
parameters.Add(lvbParam);
try
{
insert.ExecuteNonQuery();
}
finally
{
insert.Dispose();
insert = null;
}
DataRow row = checkTable.NewRow();
row["id"] = id;
row["i"] = i;
row["si"] = si;
row["d"] = d;
row["r"] = r;
row["n"] = n;
row["dt"] = dt;
row["tm"] = tm;
row["dtm"] = dtm;
row["c"] = str;
row["vc"] = str;
row["lvc"] = str;
row["nc"] = str;
row["nvc"] = str;
row["lnvc"] = str;
row["b"] = bin;
row["vb"] = bin;
row["lvb"] = bin;
checkTable.Rows.Add(row);
}
public void ResultSetAndOutputParameters(TestCaseResult result)
{
DropProcedure();
DropProcedure();
ExecuteNonQuery(
"create procedure bar (out x integer)\n" +
"{\n" +
" declare i int;\n" +
" result_names (i);\n" +
" result (1);\n" +
" result (2);\n" +
" x := 3;\n" +
" return 4;\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.Output;
x.VirtDbType = VirtDbType.Integer;
command.Parameters.Add(x);
VirtuosoDataReader reader = null;
bool closed = false;
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());
reader.Close();
closed = true;
result.FailIfNotEqual("Out Parameter", 3, x.Value);
result.FailIfNotEqual("Return Value", 4, returnValue.Value);
}
finally
{
if (reader != null && !closed)
{
reader.Close();
}
command.Dispose();
}
}
public static void DeriveParameters(VirtuosoCommand command)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (command.CommandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException("DeriveParameters supports only stored procedures.");
}
VirtuosoConnection connection = (VirtuosoConnection)command.Connection;
if (connection == null)
{
throw new InvalidOperationException("The Connection property is not set.");
}
if (connection.State == ConnectionState.Closed)
{
throw new InvalidOperationException("The connection is closed.");
}
IInnerCommand innerCommand = null;
VirtuosoDataReader reader = null;
try
{
innerCommand = connection.innerConnection.CreateInnerCommand(null);
innerCommand.GetProcedureColumns(command.CommandText);
reader = new VirtuosoDataReader(connection, innerCommand, null, CommandBehavior.Default, false);
command.Parameters.Clear();
while (reader.Read())
{
CLI.InOutType iotype = (CLI.InOutType)reader.GetInt16(reader.GetOrdinal("COLUMN_TYPE"));
ParameterDirection direction;
switch (iotype)
{
case CLI.InOutType.SQL_PARAM_INPUT:
direction = ParameterDirection.Input;
break;
case CLI.InOutType.SQL_PARAM_OUTPUT:
direction = ParameterDirection.Output;
break;
case CLI.InOutType.SQL_PARAM_INPUT_OUTPUT:
direction = ParameterDirection.InputOutput;
break;
case CLI.InOutType.SQL_PARAM_RETURN_VALUE:
direction = ParameterDirection.ReturnValue;
break;
default:
#if MONO
direction = ParameterDirection.Input;
#endif
continue;
}
string name = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
if (name == "" && iotype == CLI.InOutType.SQL_PARAM_RETURN_VALUE)
{
name = "ReturnValue";
}
CLI.SqlType sqlType = (CLI.SqlType)reader.GetInt16(reader.GetOrdinal("DATA_TYPE"));
DataType type = DataTypeInfo.MapSqlType(sqlType);
if (type == null)
{
throw new SystemException("Unknown data type");
}
int sizeOrdinal = reader.GetOrdinal("COLUMN_SIZE");
if (sizeOrdinal < 0)
{
sizeOrdinal = reader.GetOrdinal("PRECISION");
}
int size = reader.IsDBNull(sizeOrdinal) ? 0 : reader.GetInt32(sizeOrdinal);
int scaleOrdinal = reader.GetOrdinal("DECIMAL_DIGITS");
if (scaleOrdinal < 0)
{
scaleOrdinal = reader.GetOrdinal("SCALE");
}
short scale = reader.IsDBNull(scaleOrdinal) ? (short)0 : reader.GetInt16(scaleOrdinal);
int nullableOrdinal = reader.GetOrdinal("NULLABLE");
CLI.Nullable nullable = (CLI.Nullable)reader.GetInt16(nullableOrdinal);
bool isNullable = (nullable != CLI.Nullable.SQL_NO_NULLS);
VirtuosoParameter parameter = (VirtuosoParameter)command.CreateParameter();
parameter.ParameterName = name;
parameter.Direction = direction;
parameter.VirtDbType = type.vdbType;
parameter.Size = type.GetFieldSize(size);
parameter.Precision = type.GetPrecision(size);
parameter.Scale = (byte)scale;
parameter.IsNullable = isNullable;
command.Parameters.Add(parameter);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
if (innerCommand != null)
{
innerCommand.Dispose();
}
}
}
private void InsertRow(int id)
{
object c, nc, b;
if (id == 0)
{
c = nc = b = DBNull.Value;
}
else
{
int length = 1 << (id - 1);
char[] chars = new char[length];
byte[] bytes = new byte[length];
for (int i = 0; i < length; i++)
{
chars[i] = (char)(' ' + i % (127 - ' '));
bytes[i] = (byte)(i % 256);
}
c = nc = new String(chars);
b = bytes;
}
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText =
"insert into foo "
+ "(id, c, nc, b) "
+ "values "
+ "(?, ?, ?, ?)";
VirtuosoParameterCollection parameters = insert.Parameters;
VirtuosoParameter idParam = insert.CreateParameter();
idParam.ParameterName = "id";
idParam.DbType = DbType.Int32;
idParam.Value = id;
parameters.Add(idParam);
VirtuosoParameter cParam = insert.CreateParameter();
cParam.ParameterName = "c";
cParam.DbType = DbType.AnsiString;
cParam.Value = c;
parameters.Add(cParam);
VirtuosoParameter ncParam = insert.CreateParameter();
ncParam.ParameterName = "nc";
ncParam.DbType = DbType.String;
ncParam.Value = nc;
parameters.Add(ncParam);
VirtuosoParameter bParam = insert.CreateParameter();
bParam.ParameterName = "b";
bParam.DbType = DbType.Binary;
bParam.Value = b;
parameters.Add(bParam);
try
{
insert.ExecuteNonQuery();
}
finally
{
insert.Dispose();
insert = null;
}
DataRow row = checkTable.NewRow();
row["id"] = id;
row["c"] = c;
row["nc"] = nc;
row["b"] = b;
checkTable.Rows.Add(row);
}
