//
-// System.Data.SqlClient.SqlCommand.cs
+// Mono.Data.PostgreSqlClient.PgSqlCommand.cs
//
// Author:
// Rodrigo Moya (rodrigo@ximian.com)
// Daniel Morgan (danmorg@sc.rr.com)
+// Tim Coleman (tim@timcoleman.com)
//
// (C) Ximian, Inc 2002 http://www.ximian.com/
// (C) Daniel Morgan, 2002
+// (C) Copyright 2002 Tim Coleman
//
// Credits:
// SQL and concepts were used from libgda 0.8.190 (GNOME Data Access)\r
// Gonzalo Paniagua Javier <gonzalo@gnome-db.org>
//
+//
+// Permission is hereby granted, free of charge, to any person obtaining
+// a copy of this software and associated documentation files (the
+// "Software"), to deal in the Software without restriction, including
+// without limitation the rights to use, copy, modify, merge, publish,
+// distribute, sublicense, and/or sell copies of the Software, and to
+// permit persons to whom the Software is furnished to do so, subject to
+// the following conditions:
+//
+// The above copyright notice and this permission notice shall be
+// included in all copies or substantial portions of the Software.
+//
+// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
+// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
+// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
+// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
+// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
+//
+
// use #define DEBUG_SqlCommand if you want to spew debug messages
// #define DEBUG_SqlCommand
using System.Text;
using System.Xml;
-namespace System.Data.SqlClient {
+namespace Mono.Data.PostgreSqlClient {
/// <summary>
/// Represents a SQL statement that is executed
/// while connected to a SQL database.
/// </summary>
- // public sealed class SqlCommand : Component, IDbCommand, ICloneable
- public sealed class SqlCommand : IDbCommand {
+ // public sealed class PgSqlCommand : Component, IDbCommand, ICloneable
+ public sealed class PgSqlCommand : IDbCommand {
#region Fields
// default is 30 seconds
// for command execution
- private SqlConnection conn = null;
- private SqlTransaction trans = null;
+ private PgSqlConnection conn = null;
+ private PgSqlTransaction trans = null;
private CommandType cmdType = CommandType.Text;
private bool designTime = false;
- private SqlParameterCollection parmCollection = new
- SqlParameterCollection();
+ private PgSqlParameterCollection parmCollection = new
+ PgSqlParameterCollection();
- // SqlDataReader state data for ExecuteReader()
- private SqlDataReader dataReader = null;
+ // PgSqlDataReader state data for ExecuteReader()
+ private PgSqlDataReader dataReader = null;
private string[] queries = null;
- private int currentQuery;
+ private int currentQuery = -1;
private CommandBehavior cmdBehavior = CommandBehavior.Default;
private ParmUtil parmUtil = null;
#region Constructors
- public SqlCommand() {
+ public PgSqlCommand() {
sql = "";
}
- public SqlCommand (string cmdText) {
+ public PgSqlCommand (string cmdText) {
sql = cmdText;
}
- public SqlCommand (string cmdText, SqlConnection connection) {
+ public PgSqlCommand (string cmdText, PgSqlConnection connection) {
sql = cmdText;
conn = connection;
}
- public SqlCommand (string cmdText, SqlConnection connection,
- SqlTransaction transaction) {
+ public PgSqlCommand (string cmdText, PgSqlConnection connection,
+ PgSqlTransaction transaction) {
sql = cmdText;
conn = connection;
trans = transaction;
}
[MonoTODO]
- public SqlParameter CreateParameter () {
- return new SqlParameter ();
+ public PgSqlParameter CreateParameter () {
+ return new PgSqlParameter ();
+ }
+
+ public uint EscapeString (string to, string from, uint length) {
+ uint result = PostgresLibrary.PQescapeString (out to, from, length);
+ return result;
+ }
+
+ public byte[] EscapeByteArray (byte[] bintext, uint binlen,
+ uint bytealen) {
+
+ byte[] result;
+ result = PostgresLibrary.PQescapeBytea (bintext,
+ binlen, bytealen);
+
+ return result;
}
public int ExecuteNonQuery () {
execStatus = PostgresLibrary.
PQresultStatus (pgResult);
- if(execStatus == ExecStatusType.PGRES_COMMAND_OK) {
+ if(execStatus == ExecStatusType.PGRES_COMMAND_OK ||
+ execStatus == ExecStatusType.PGRES_TUPLES_OK ) {
+
rowsAffectedString = PostgresLibrary.
PQcmdTuples (pgResult);
PostgresLibrary.PQclear (pgResult);
pgResult = IntPtr.Zero;
\r
- throw new SqlException(0, 0,
+ throw new PgSqlException(0, 0,
errorMessage, 0, "",
conn.DataSource, "SqlCommand", 0);\r
}
}
[MonoTODO]
- public SqlDataReader ExecuteReader () {
+ public PgSqlDataReader ExecuteReader () {
return ExecuteReader(CommandBehavior.Default);
}
}
[MonoTODO]
- public SqlDataReader ExecuteReader (CommandBehavior behavior)
+ public PgSqlDataReader ExecuteReader (CommandBehavior behavior)
{
if(conn.State != ConnectionState.Open)
throw new InvalidOperationException(
queries = null;
currentQuery = -1;
- dataReader = new SqlDataReader(this);
+ dataReader = new PgSqlDataReader(this);
- if((behavior & CommandBehavior.SingleResult) == CommandBehavior.SingleResult) {
- queries = new String[1];
- queries[0] = sql;
- }
- else {
- queries = sql.Split(new Char[] {';'});
- }
+ queries = sql.Split(new Char[] {';'});
dataReader.NextResult();
return dataReader;
}
- internal SqlResult NextResult()
+ internal PgSqlResult NextResult()
{
- SqlResult res = new SqlResult();
+ PgSqlResult res = new PgSqlResult();
res.Connection = this.Connection;
+ res.Behavior = cmdBehavior;
string statement;
currentQuery++;
+ res.CurrentQuery = currentQuery;
+
if(currentQuery < queries.Length && queries[currentQuery].Equals("") == false) {
+ res.SQL = queries[currentQuery];
statement = TweakQuery(queries[currentQuery], cmdType);
ExecuteQuery(statement, res);
res.ResultReturned = true;
private string TweakQuery(string query, CommandType commandType) {
string statement = "";
- StringBuilder td;
-
-#if DEBUG_SqlCommand
- Console.WriteLine("---------[][] TweakQuery() [][]--------");
- Console.WriteLine("CommandType: " + commandType + " CommandBehavior: " + cmdBehavior);
- Console.WriteLine("SQL before command type: " + query);
-#endif
+
// finish building SQL based on CommandType
switch(commandType) {
case CommandType.Text:
- statement = query;
+ // TODO: this parameters utility
+ // currently only support input variables
+ // need todo output, input/output, and return.
+ parmUtil = new ParmUtil(query, parmCollection);
+ statement = parmUtil.ReplaceWithParms();
break;
case CommandType.StoredProcedure:
- statement =
- "SELECT " + query + "()";
+ string sParmList = GetStoredProcParmList ();
+ statement = "SELECT " + query + "(" + sParmList + ")";
break;
- case CommandType.TableDirect:
- // NOTE: this is for the PostgreSQL provider
- // and for OleDb, according to the docs,
- // an exception is thrown if you try to use
- // this with SqlCommand
- string[] directTables = query.Split(
- new Char[] {','});
-
- td = new StringBuilder("SELECT * FROM ");
-
- for(int tab = 0; tab < directTables.Length; tab++) {
- if(tab > 0)
- td.Append(',');
- td.Append(directTables[tab]);
- // FIXME: if multipe tables, how do we
- // join? based on Primary/Foreign Keys?
- // Otherwise, a Cartesian Product happens
- }
- statement = td.ToString();
- break;
- default:
- // FIXME: throw an exception?
- statement = query;
+ case CommandType.TableDirect:
+ statement = "SELECT * FROM " + query;
break;
}
-#if DEBUG_SqlCommand
- Console.WriteLine("SQL after command type: " + statement);
-#endif
- // TODO: this parameters utility
- // currently only support input variables
- // need todo output, input/output, and return.
-#if DEBUG_SqlCommand
- Console.WriteLine("using ParmUtil in TweakQuery()...");
-#endif
- parmUtil = new ParmUtil(statement, parmCollection);
-#if DEBUG_SqlCommand
- Console.WriteLine("ReplaceWithParms...");
-#endif
- statement = parmUtil.ReplaceWithParms();
-
-#if DEBUG_SqlCommand
- Console.WriteLine("SQL after ParmUtil: " + statement);
-#endif
return statement;
}
- private void ExecuteQuery (string query, SqlResult res)
+ string GetStoredProcParmList () {
+ StringBuilder s = new StringBuilder();
+
+ int addedCount = 0;
+ for(int p = 0; p < parmCollection.Count; p++) {
+ PgSqlParameter prm = parmCollection[p];
+ if(prm.Direction == ParameterDirection.Input) {\r
+ string strObj = PostgresHelper.\r
+ ObjectToString(prm.DbType, \r
+ prm.Value);\r
+ if(addedCount > 0)\r
+ s.Append(",");\r
+ s.Append(strObj);\r
+ addedCount++;\r
+ }
+ }
+ return s.ToString();
+ }
+
+ private void ExecuteQuery (string query, PgSqlResult res)
{
IntPtr pgResult;
execStatus = PostgresLibrary.
PQresultStatus (pgResult);
- if(execStatus == ExecStatusType.PGRES_TUPLES_OK) {
+ res.ExecStatus = execStatus;
+
+ if(execStatus == ExecStatusType.PGRES_TUPLES_OK ||
+ execStatus == ExecStatusType.PGRES_COMMAND_OK) {
+
res.BuildTableSchema(pgResult);
}
else {
PostgresLibrary.PQclear (pgResult);
pgResult = IntPtr.Zero;
\r
- throw new SqlException(0, 0,
+ throw new PgSqlException(0, 0,
errorMessage, 0, "",
conn.DataSource, "SqlCommand", 0);\r
}
// those resources. Also, need to allow this SqlCommand
// and this SqlConnection to do things again.
internal void CloseReader() {
- conn.OpenReader = false;
dataReader = null;
queries = null;
+
+ if((cmdBehavior & CommandBehavior.CloseConnection) == CommandBehavior.CloseConnection) {
+ conn.CloseReader(true);
+ }
+ else {
+ conn.CloseReader(false);
+ }
+ }
+
+ // only meant to be used between SqlConnectioin,
+ // SqlCommand, and SqlDataReader
+ internal void OpenReader(PgSqlDataReader reader) {
+ conn.OpenReader(reader);
}
/// <summary>\r
execStatus = PostgresLibrary.
PQresultStatus (pgResult);
-
- if(execStatus == ExecStatusType.PGRES_TUPLES_OK) {
+ if(execStatus == ExecStatusType.PGRES_COMMAND_OK) {
+ // result was a SQL Command
+
+ // close result set
+ PostgresLibrary.PQclear (pgResult);
+ pgResult = IntPtr.Zero;
+
+ return null; // return null reference
+ }
+ else if(execStatus == ExecStatusType.PGRES_TUPLES_OK) {
+ // result was a SQL Query
+
nRows = PostgresLibrary.
PQntuples(pgResult);
PostgresLibrary.PQclear (pgResult);
pgResult = IntPtr.Zero;
\r
- throw new SqlException(0, 0,
+ throw new PgSqlException(0, 0,
errorMessage, 0, "",
conn.DataSource, "SqlCommand", 0);\r
}
}
[MonoTODO]
- public SqlCommand Clone () {
+ public PgSqlCommand Clone () {
throw new NotImplementedException ();
}
// transaction in progress
// csc
- Connection = (SqlConnection) value;
+ Connection = (PgSqlConnection) value;
// mcs
// Connection = value;
}
}
- public SqlConnection Connection {
+ public PgSqlConnection Connection {
get {
// conn defaults to null
return conn;
}
}
- public SqlParameterCollection Parameters {
+ public PgSqlParameterCollection Parameters {
get {
return parmCollection;
}
// has already begun
// csc
- Transaction = (SqlTransaction) value;
+ Transaction = (PgSqlTransaction) value;
// mcs
// Transaction = value;
}
}
- public SqlTransaction Transaction {
+ public PgSqlTransaction Transaction {
get {
return trans;
}
}
[MonoTODO]
- ~SqlCommand() {
+ ~PgSqlCommand() {
// FIXME: need proper way to release resources
// Dispose(false);
}
// SqlResult is used for passing Result Set data
// from SqlCommand to SqlDataReader
- internal class SqlResult {
+ internal class PgSqlResult {
- private DataTable dataTableSchema; // only will contain the schema
- private IntPtr pg_result; // native PostgreSQL PGresult
- private int rowCount;
- private int fieldCount;
- private string[] pgtypes; // PostgreSQL types (typname)
+ private DataTable dataTableSchema = null; // only will contain the schema
+ private IntPtr pg_result = IntPtr.Zero; // native PostgreSQL PGresult
+ private int rowCount = 0;
+ private int fieldCount = 0;
+ private string[] pgtypes = null; // PostgreSQL types (typname)
private bool resultReturned = false;
- private SqlConnection con;
+ private PgSqlConnection con = null;
+ private int rowsAffected = -1;
+ private ExecStatusType execStatus = ExecStatusType.PGRES_FATAL_ERROR;
+ private int currentQuery = -1;
+ private string sql = "";
+ private CommandBehavior cmdBehavior = CommandBehavior.Default;
+
+ internal CommandBehavior Behavior {
+ get {
+ return cmdBehavior;
+ }
+ set {
+ cmdBehavior = value;
+ }
+ }
+
+ internal string SQL {
+ get {
+ return sql;
+ }
+ set {
+ sql = value;
+ }
+ }
+
+ internal ExecStatusType ExecStatus {
+ get {
+ return execStatus;
+ }
+ set {
+ execStatus = value;
+ }
+ }
+
+ internal int CurrentQuery {
+ get {
+ return currentQuery;
+ }
+
+ set {
+ currentQuery = value;
+ }
+
+ }
+
+ internal PgSqlConnection Connection {
+ get {
+ return con;
+ }
- internal SqlConnection Connection {
set {
con = value;
}
}
+ internal int RecordsAffected {
+ get {
+ return rowsAffected;
+ }
+ }
+
internal bool ResultReturned {
get {
return resultReturned;
internal void BuildTableSchema (IntPtr pgResult) {
pg_result = pgResult;
-
- int nCol;
-
- dataTableSchema = new DataTable();
-
- rowCount = PostgresLibrary.
- PQntuples(pgResult);
-
- fieldCount = PostgresLibrary.
- PQnfields(pgResult);
- int oid;
- pgtypes = new string[fieldCount];
+ // need to set IDataReader.RecordsAffected property
+ string rowsAffectedString;
+ rowsAffectedString = PostgresLibrary.
+ PQcmdTuples (pgResult);
+ if(rowsAffectedString != null)
+ if(rowsAffectedString.Equals("") == false)
+ rowsAffected = int.Parse(rowsAffectedString);
- for(nCol = 0; nCol < fieldCount; nCol++) {
-
- DbType dbType;
-
- // get column name
- String fieldName;
- fieldName = PostgresLibrary.
- PQfname(pgResult, nCol);
+ // Only Results from SQL SELECT Queries
+ // get a DataTable for schema of the result
+ // otherwise, DataTable is null reference
+ if(execStatus == ExecStatusType.PGRES_TUPLES_OK) {
- // get PostgreSQL data type (OID)
- oid = PostgresLibrary.
- PQftype(pgResult, nCol);
- pgtypes[nCol] = PostgresHelper.
- OidToTypname (oid, con.Types);
+ dataTableSchema = new DataTable ();
+ dataTableSchema.Columns.Add ("ColumnName", typeof (string));
+ dataTableSchema.Columns.Add ("ColumnOrdinal", typeof (int));
+ dataTableSchema.Columns.Add ("ColumnSize", typeof (int));
+ dataTableSchema.Columns.Add ("NumericPrecision", typeof (int));
+ dataTableSchema.Columns.Add ("NumericScale", typeof (int));
+ dataTableSchema.Columns.Add ("IsUnique", typeof (bool));
+ dataTableSchema.Columns.Add ("IsKey", typeof (bool));
+ DataColumn dc = dataTableSchema.Columns["IsKey"];
+ dc.AllowDBNull = true; // IsKey can have a DBNull
+ dataTableSchema.Columns.Add ("BaseCatalogName", typeof (string));
+ dataTableSchema.Columns.Add ("BaseColumnName", typeof (string));
+ dataTableSchema.Columns.Add ("BaseSchemaName", typeof (string));
+ dataTableSchema.Columns.Add ("BaseTableName", typeof (string));
+ dataTableSchema.Columns.Add ("DataType", typeof(Type));
+ dataTableSchema.Columns.Add ("AllowDBNull", typeof (bool));
+ dataTableSchema.Columns.Add ("ProviderType", typeof (int));
+ dataTableSchema.Columns.Add ("IsAliased", typeof (bool));
+ dataTableSchema.Columns.Add ("IsExpression", typeof (bool));
+ dataTableSchema.Columns.Add ("IsIdentity", typeof (bool));
+ dataTableSchema.Columns.Add ("IsAutoIncrement", typeof (bool));
+ dataTableSchema.Columns.Add ("IsRowVersion", typeof (bool));
+ dataTableSchema.Columns.Add ("IsHidden", typeof (bool));
+ dataTableSchema.Columns.Add ("IsLong", typeof (bool));
+ dataTableSchema.Columns.Add ("IsReadOnly", typeof (bool));
+
+ fieldCount = PostgresLibrary.PQnfields (pgResult);
+ rowCount = PostgresLibrary.PQntuples(pgResult);
+ pgtypes = new string[fieldCount];\r
+\r
+ // TODO: for CommandBehavior.SingleRow\r
+ // use IRow, otherwise, IRowset\r
+ if(fieldCount > 0)\r
+ if((cmdBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow)\r
+ fieldCount = 1;\r
+
+ // TODO: for CommandBehavior.SchemaInfo
+ if((cmdBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
+ fieldCount = 0;
+
+ // TODO: for CommandBehavior.SingleResult
+ if((cmdBehavior & CommandBehavior.SingleResult) == CommandBehavior.SingleResult)
+ if(currentQuery > 0)
+ fieldCount = 0;
+
+ // TODO: for CommandBehavior.SequentialAccess - used for reading Large OBjects
+ //if((cmdBehavior & CommandBehavior.SequentialAccess) == CommandBehavior.SequentialAccess) {
+ //}
+
+ DataRow schemaRow;
+ int oid;
+ DbType dbType;
+ Type typ;
+
+ for (int i = 0; i < fieldCount; i += 1 ) {
+ schemaRow = dataTableSchema.NewRow ();
+
+ string columnName = PostgresLibrary.PQfname (pgResult, i);
+
+ schemaRow["ColumnName"] = columnName;
+ schemaRow["ColumnOrdinal"] = i+1;
+ schemaRow["ColumnSize"] = PostgresLibrary.PQfsize (pgResult, i);
+ schemaRow["NumericPrecision"] = 0;
+ schemaRow["NumericScale"] = 0;
+ // TODO: need to get KeyInfo
+ if((cmdBehavior & CommandBehavior.KeyInfo) == CommandBehavior.KeyInfo) {
+ bool IsUnique, IsKey;
+ GetKeyInfo(columnName, out IsUnique, out IsKey);
+ }
+ else {
+ schemaRow["IsUnique"] = false;
+ schemaRow["IsKey"] = DBNull.Value;
+ }
+ schemaRow["BaseCatalogName"] = "";
+ schemaRow["BaseColumnName"] = columnName;
+ schemaRow["BaseSchemaName"] = "";
+ schemaRow["BaseTableName"] = "";
- int definedSize;
- // get defined size of column
- definedSize = PostgresLibrary.
- PQfsize(pgResult, nCol);
-
- // build the data column and add it the table
- DataColumn dc = new DataColumn(fieldName);
-
- dbType = PostgresHelper.
- TypnameToSqlDbType(pgtypes[nCol]);
- dc.DataType = PostgresHelper.
- DbTypeToSystemType(dbType);
- dc.MaxLength = definedSize;
- dc.SetTable(dataTableSchema);
+ // PostgreSQL type to .NET type stuff
+ oid = PostgresLibrary.PQftype (pgResult, i);
+ pgtypes[i] = PostgresHelper.OidToTypname (oid, con.Types); \r
+ dbType = PostgresHelper.TypnameToSqlDbType (pgtypes[i]);\r
+ \r
+ typ = PostgresHelper.DbTypeToSystemType (dbType);\r
+ string st = typ.ToString();\r
+ schemaRow["DataType"] = typ;\r
+
+ schemaRow["AllowDBNull"] = false;
+ schemaRow["ProviderType"] = oid;
+ schemaRow["IsAliased"] = false;
+ schemaRow["IsExpression"] = false;
+ schemaRow["IsIdentity"] = false;
+ schemaRow["IsAutoIncrement"] = false;
+ schemaRow["IsRowVersion"] = false;
+ schemaRow["IsHidden"] = false;
+ schemaRow["IsLong"] = false;
+ schemaRow["IsReadOnly"] = false;
+ schemaRow.AcceptChanges();
+ dataTableSchema.Rows.Add (schemaRow);
+ }
- dataTableSchema.Columns.Add(dc);
+#if DEBUG_SqlCommand
+ Console.WriteLine("********** DEBUG Table Schema BEGIN ************");
+ foreach (DataRow myRow in dataTableSchema.Rows) {\r
+ foreach (DataColumn myCol in dataTableSchema.Columns)\r
+ Console.WriteLine(myCol.ColumnName + " = " + myRow[myCol]);\r
+ Console.WriteLine();\r
+ }
+ Console.WriteLine("********** DEBUG Table Schema END ************");
+#endif // DEBUG_SqlCommand
+
}
}
+
+ // TODO: how do we get the key info if
+ // we don't have the tableName?
+ private void GetKeyInfo(string columnName, out bool isUnique, out bool isKey) {
+ isUnique = false;
+ isKey = false;
+/*
+ string sql;
+
+ sql =
+ "SELECT i.indkey, i.indisprimary, i.indisunique " +
+ "FROM pg_class c, pg_class c2, pg_index i " +
+ "WHERE c.relname = ':tableName' AND c.oid = i.indrelid " +
+ "AND i.indexrelid = c2.oid ";
+*/
+ }
}
}
projects / mono.git / blobdiff