// Diego Caravana (diego@toth.it)
// Sebastien Pouliot (sebastien@ximian.com)
// Daniel Morgan (danielmorgan@verizon.net)
+// Gert Driesen (drieseng@users.sourceforge.net)
+// Veerapuram Varadhan (vvaradhan@novell.com)
//
// Copyright (C) 2002 Tim Coleman
// Portions (C) 2003 Motus Technologies Inc. (http://www.motus.com)
// Portions (C) 2003 Daniel Morgan
//
-
//
// Permission is hereby granted, free of charge, to any person obtaining
// a copy of this software and associated documentation files (the
namespace Mono.Data.Tds.Protocol
{
- public sealed class Tds70 : Tds
+ public class Tds70 : Tds
{
#region Fields
- public readonly static TdsVersion Version = TdsVersion.tds70;
+ //public readonly static TdsVersion Version = TdsVersion.tds70;
static readonly decimal SMALLMONEY_MIN = -214748.3648m;
static readonly decimal SMALLMONEY_MAX = 214748.3647m;
}
public Tds70 (string server, int port, int packetSize, int timeout)
- : base (server, port, packetSize, timeout, Version)
+ : base (server, port, packetSize, timeout, TdsVersion.tds70)
{
}
+ public Tds70 (string server, int port, int packetSize, int timeout, TdsVersion version)
+ : base (server, port, packetSize, timeout, version)
+ {
+ }
+
#endregion // Constructors
+ #region Properties
+
+ protected virtual byte[] ClientVersion {
+ get { return new byte[] {0x00, 0x0, 0x0, 0x70};}
+ }
+ #endregion // Properties
+
#region Methods
private string BuildExec (string sql)
int count = 0;
if (Parameters != null) {
foreach (TdsMetaParameter p in Parameters) {
+ string parameterName = p.ParameterName;
+ if (parameterName [0] == '@') {
+ parameterName = parameterName.Substring (1);
+ }
+
if (p.Direction != TdsParameterDirection.Input) {
if (count == 0)
select.Append ("select ");
else
select.Append (", ");
- select.Append (p.ParameterName);
+ select.Append ("@" + parameterName);
declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
if (p.Direction == TdsParameterDirection.InputOutput)
set.Append (String.Format ("set {0}\n", FormatParameter(p)));
else
- set.Append (String.Format ("set {0}=NULL\n", p.ParameterName));
+ set.Append (String.Format ("set @{0}=NULL\n", parameterName));
}
- count += 1;
+ count++;
}
-
if (p.Direction == TdsParameterDirection.ReturnValue)
- exec = p.ParameterName + "=";
+ exec = "@" + parameterName + "=";
}
}
exec = "exec " + exec;
Comm.Append (totalPacketSize);
//Comm.Append (empty, 3, pad);
- byte[] version = {0x00, 0x0, 0x0, 0x70};
- Comm.Append (version); // TDS Version 7
+ //byte[] version = {0x00, 0x0, 0x0, 0x71};
+ //Console.WriteLine ("Version: {0}", ClientVersion[3]);
+ Comm.Append (ClientVersion); // TDS Version 7
Comm.Append ((int)this.PacketSize); // Set the Block Size
Comm.Append (empty, 3, pad);
Comm.Append (magic);
// Set "reset-connection" bit for the next message packet
Comm.ResetConnection = true;
-
+ base.Reset ();
return true;
}
public override void ExecPrepared (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
{
Parameters = parameters;
- ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
+ if (Parameters != null && Parameters.Count > 0)
+ ExecRPC (TdsRpcProcId.ExecuteSql, commandText, parameters, timeout, wantResults);
+ else
+ ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
}
public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
ExecRPC (commandText, parameters, timeout, wantResults);
}
- protected override void ExecRPC (string rpcName, TdsMetaParameterCollection parameters,
- int timeout, bool wantResults)
+ private void WriteRpcParameterInfo (TdsMetaParameterCollection parameters)
{
- // clean up
- InitExec ();
- Comm.StartPacket (TdsPacketType.RPC);
-
- Comm.Append ( (short) rpcName.Length);
- Comm.Append (rpcName);
- Comm.Append ( (short) 0); //no meta data
if (parameters != null) {
foreach (TdsMetaParameter param in parameters) {
if (param.Direction == TdsParameterDirection.ReturnValue)
WriteParameterInfo (param);
}
}
+ }
+
+ private void WritePreparedParameterInfo (TdsMetaParameterCollection parameters)
+ {
+ if (parameters == null)
+ return;
+
+ string param = BuildPreparedParameters ();
+ Comm.Append ((byte) 0x00); // no param meta data name
+ Comm.Append ((byte) 0x00); // no status flags
+
+ // Type_info - parameter info
+ WriteParameterInfo (new TdsMetaParameter ("prep_params",
+ param.Length > 4000 ? "ntext" : "nvarchar",
+ param));
+ }
+
+ private void ExecRPC (TdsRpcProcId rpcId, string sql,
+ TdsMetaParameterCollection parameters,
+ int timeout, bool wantResults)
+ {
+ // clean up
+ InitExec ();
+ Comm.StartPacket (TdsPacketType.RPC);
+
+ Comm.Append ((ushort) 0xFFFF);
+ Comm.Append ((ushort) rpcId);
+ Comm.Append ((short) 0x02); // no meta data
+
+ Comm.Append ((byte) 0x00); // no param meta data name
+ Comm.Append ((byte) 0x00); // no status flags
+
+ // Write sql as a parameter value - UCS2
+ TdsMetaParameter param = new TdsMetaParameter ("sql",
+ sql.Length > 4000 ? "ntext":"nvarchar",
+ sql);
+ WriteParameterInfo (param);
+
+ // Write Parameter infos - name and type
+ WritePreparedParameterInfo (parameters);
+
+ // Write parameter/value info
+ WriteRpcParameterInfo (parameters);
+ Comm.SendPacket ();
+ CheckForData (timeout);
+ if (!wantResults)
+ SkipToEnd ();
+ }
+
+ protected override void ExecRPC (string rpcName, TdsMetaParameterCollection parameters,
+ int timeout, bool wantResults)
+ {
+ // clean up
+ InitExec ();
+ Comm.StartPacket (TdsPacketType.RPC);
+
+ Comm.Append ( (short) rpcName.Length);
+ Comm.Append (rpcName);
+ Comm.Append ( (short) 0); //no meta data
+ WriteRpcParameterInfo (parameters);
Comm.SendPacket ();
CheckForData (timeout);
if (!wantResults)
TdsColumnType colType = param.GetMetaType ();
param.IsNullable = false;
- Comm.Append ((byte)colType); // type
-
+ bool partLenType = false;
int size = param.Size;
- if (size == 0)
+ if (size < 1) {
+ if (size < 0)
+ partLenType = true;
size = param.GetActualSize ();
+ }
- /*
- If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
- FIXME: Need to check for other types
+ // Change colType according to the following table
+ /*
+ * Original Type Maxlen New Type
+ *
+ * NVarChar 4000 UCS2 NText
+ * BigVarChar 8000 ASCII Text
+ * BigVarBinary 8000 bytes Image
+ *
*/
- if (colType == TdsColumnType.BigNVarChar)
- size <<= 1;
+ TdsColumnType origColType = colType;
+ if (colType == TdsColumnType.BigNVarChar) {
+ // param.GetActualSize() returns len*2
+ if (size == param.Size)
+ size <<= 1;
+ if ((size >> 1) > 4000)
+ colType = TdsColumnType.NText;
+ } else if (colType == TdsColumnType.BigVarChar) {
+ if (size > 8000)
+ colType = TdsColumnType.Text;
+ } else if (colType == TdsColumnType.BigVarBinary) {
+ if (size > 8000)
+ colType = TdsColumnType.Image;
+ }
+ // Calculation of TypeInfo field
+ /*
+ * orig size value TypeInfo field
+ *
+ * >= 0 <= Maxlen origColType + content len
+ * > Maxlen NewType as per above table + content len
+ * -1 origColType + USHORTMAXLEN (0xFFFF) + content len (TDS 9)
+ *
+ */
+ // Write updated colType, iff partLenType == false
+ if (TdsVersion > TdsVersion.tds81 && partLenType) {
+ Comm.Append ((byte)origColType);
+ Comm.Append ((short)-1);
+ } else if (ServerTdsVersion > TdsVersion.tds70
+ && origColType == TdsColumnType.Decimal) {
+ Comm.Append ((byte)TdsColumnType.Numeric);
+ } else {
+ Comm.Append ((byte)colType);
+ }
+
if (IsLargeType (colType))
Comm.Append ((short)size); // Parameter size passed in SqlParameter
else if (IsBlobType (colType))
// Precision and Scale are non-zero for only decimal/numeric
if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
- Comm.Append ((param.Precision !=0 ) ? param.Precision : (byte) 28);
+ Comm.Append ((param.Precision !=0 ) ? param.Precision : (byte) 29);
Comm.Append (param.Scale);
+ // Convert the decimal value according to Scale
+ if (param.Value != null && param.Value != DBNull.Value &&
+ ((decimal)param.Value) != Decimal.MaxValue &&
+ ((decimal)param.Value) != Decimal.MinValue) {
+ decimal expo = new Decimal (System.Math.Pow (10, (double)param.Scale));
+ int pVal = (int)(((decimal)param.Value) * expo);
+ param.Value = (decimal)pVal;
+ }
+ }
+
+
+ /* VARADHAN: TDS 8 Debugging */
+ /*
+ if (Collation != null) {
+ Console.WriteLine ("Collation is not null");
+ Console.WriteLine ("Column Type: {0}", colType);
+ Console.WriteLine ("Collation bytes: {0} {1} {2} {3} {4}", Collation[0], Collation[1], Collation[2],
+ Collation[3], Collation[4]);
+ } else {
+ Console.WriteLine ("Collation is null");
}
+ */
+
+ // Tds > 7.0 uses collation
+ if (Collation != null &&
+ (colType == TdsColumnType.BigChar || colType == TdsColumnType.BigNVarChar ||
+ colType == TdsColumnType.BigVarChar || colType == TdsColumnType.NChar ||
+ colType == TdsColumnType.NVarChar || colType == TdsColumnType.Text ||
+ colType == TdsColumnType.NText))
+ Comm.Append (Collation);
+
+ // LAMESPEC: size should be 0xFFFF for any bigvarchar, bignvarchar and bigvarbinary
+ // types if param value is NULL
+ if ((colType == TdsColumnType.BigVarChar ||
+ colType == TdsColumnType.BigNVarChar ||
+ colType == TdsColumnType.BigVarBinary) &&
+ (param.Value == null || param.Value == DBNull.Value))
+ size = -1;
+ else
+ size = param.GetActualSize ();
- size = param.GetActualSize ();
if (IsLargeType (colType))
- Comm.Append ((short)size);
+ Comm.Append ((short)size);
else if (IsBlobType (colType))
- Comm.Append (size);
+ Comm.Append (size);
else
Comm.Append ((byte)size);
-
+
if (size > 0) {
switch (param.TypeName) {
case "money" : {
{
Parameters = parameters;
string sql = commandText;
- if (wantResults || (Parameters != null && Parameters.Count > 0))
- sql = BuildExec (commandText);
- ExecuteQuery (sql, timeout, wantResults);
+
+ if (Parameters != null && Parameters.Count > 0) {
+ ExecRPC (TdsRpcProcId.ExecuteSql, commandText, parameters, timeout, wantResults);
+ } else {
+ if (wantResults)
+ sql = BuildExec (commandText);
+ ExecuteQuery (sql, timeout, wantResults);
+ }
}
private string FormatParameter (TdsMetaParameter parameter)
parameterName = parameterName.Substring (1);
}
if (parameter.Direction == TdsParameterDirection.Output)
- return String.Format ("@{0}={0} output", parameterName);
+ return String.Format ("@{0}=@{0} output", parameterName);
if (parameter.Value == null || parameter.Value == DBNull.Value)
- return parameter.ParameterName + "=NULL";
+ return String.Format ("@{0}=NULL", parameterName);
string value = null;
switch (parameter.TypeName) {
//return ColumnValues [0].ToString ();
}
- protected override TdsDataColumnCollection ProcessColumnInfo ()
+ protected override void ProcessColumnInfo ()
{
- TdsDataColumnCollection result = new TdsDataColumnCollection ();
int numColumns = Comm.GetTdsShort ();
for (int i = 0; i < numColumns; i += 1) {
byte[] flagData = new byte[4];
bool autoIncrement = (flagData[2] & 0x10) > 0;
bool isIdentity = (flagData[2] & 0x10) > 0;
- TdsColumnType columnType = (TdsColumnType) (Comm.GetByte () & 0xff);
- if ((byte) columnType == 0xef)
- columnType = TdsColumnType.NChar;
+ TdsColumnType columnType = (TdsColumnType) ((Comm.GetByte () & 0xff));
byte xColumnType = 0;
if (IsLargeType (columnType)) {
if (IsBlobType (columnType)) {
columnSize = Comm.GetTdsInt ();
tableName = Comm.GetString (Comm.GetTdsShort ());
- } else if (IsFixedSizeColumn (columnType))
+ } else if (IsFixedSizeColumn (columnType)) {
columnSize = LookupBufferSize (columnType);
- else if (IsLargeType ((TdsColumnType) xColumnType))
+ } else if (IsLargeType ((TdsColumnType) xColumnType)) {
columnSize = Comm.GetTdsShort ();
- else
+ } else {
columnSize = Comm.GetByte () & 0xff;
+ }
+
+ if (IsWideType ((TdsColumnType) columnType))
+ columnSize /= 2;
byte precision = 0;
byte scale = 0;
- switch (columnType) {
- case TdsColumnType.NText:
- case TdsColumnType.NChar:
- case TdsColumnType.NVarChar:
- columnSize /= 2;
- break;
- case TdsColumnType.Decimal:
- case TdsColumnType.Numeric:
- /*
- Comm.Skip (1);
- */
+ if (columnType == TdsColumnType.Decimal || columnType == TdsColumnType.Numeric) {
precision = Comm.GetByte ();
scale = Comm.GetByte ();
- break;
+ } else {
+ precision = GetPrecision (columnType, columnSize);
+ scale = GetScale (columnType, columnSize);
}
string columnName = Comm.GetString (Comm.GetByte ());
TdsDataColumn col = new TdsDataColumn ();
- result.Add (col);
+ Columns.Add (col);
#if NET_2_0
col.ColumnType = columnType;
col.ColumnName = columnName;
col.IsReadOnly = !writable;
col.AllowDBNull = nullable;
col.BaseTableName = tableName;
+ col.DataTypeName = Enum.GetName (typeof (TdsColumnType), xColumnType);
#else
col ["ColumnType"] = columnType;
col ["ColumnName"] = columnName;
col ["IsReadOnly"] = !writable;
col ["AllowDBNull"] = nullable;
col ["BaseTableName"] = tableName;
+ col ["DataTypeName"] = Enum.GetName (typeof (TdsColumnType), xColumnType);
#endif
}
- return result;
}
public override void Unprepare (string statementId)
}
}
+ byte GetScale (TdsColumnType type, int columnSize)
+ {
+ switch (type) {
+ case TdsColumnType.DateTime:
+ return 0x03;
+ case TdsColumnType.DateTime4:
+ return 0x00;
+ case TdsColumnType.DateTimeN:
+ switch (columnSize) {
+ case 4:
+ return 0x00;
+ case 8:
+ return 0x03;
+ }
+ break;
+ default:
+ return 0xff;
+ }
+
+ throw new NotSupportedException (string.Format (
+ CultureInfo.InvariantCulture,
+ "Fixed scale not defined for column " +
+ "type '{0}' with size {1}.", type, columnSize));
+ }
+
+ byte GetPrecision (TdsColumnType type, int columnSize)
+ {
+ switch (type) {
+ case TdsColumnType.Binary:
+ return 0xff;
+ case TdsColumnType.Bit:
+ return 0xff;
+ case TdsColumnType.Char:
+ return 0xff;
+ case TdsColumnType.DateTime:
+ return 0x17;
+ case TdsColumnType.DateTime4:
+ return 0x10;
+ case TdsColumnType.DateTimeN:
+ switch (columnSize) {
+ case 4:
+ return 0x10;
+ case 8:
+ return 0x17;
+ }
+ break;
+ case TdsColumnType.Real:
+ return 0x07;
+ case TdsColumnType.Float8:
+ return 0x0f;
+ case TdsColumnType.FloatN:
+ switch (columnSize) {
+ case 4:
+ return 0x07;
+ case 8:
+ return 0x0f;
+ }
+ break;
+ case TdsColumnType.Image:
+ return 0xff;
+ case TdsColumnType.Int1:
+ return 0x03;
+ case TdsColumnType.Int2:
+ return 0x05;
+ case TdsColumnType.Int4:
+ return 0x0a;
+ case TdsColumnType.IntN:
+ switch (columnSize) {
+ case 1:
+ return 0x03;
+ case 2:
+ return 0x05;
+ case 4:
+ return 0x0a;
+ }
+ break;
+ case TdsColumnType.Void:
+ return 0x01;
+ case TdsColumnType.Text:
+ return 0xff;
+ case TdsColumnType.UniqueIdentifier:
+ return 0xff;
+ case TdsColumnType.VarBinary:
+ return 0xff;
+ case TdsColumnType.VarChar:
+ return 0xff;
+ case TdsColumnType.Money:
+ return 19;
+ case TdsColumnType.NText:
+ return 0xff;
+ case TdsColumnType.NVarChar:
+ return 0xff;
+ case TdsColumnType.BitN:
+ return 0xff;
+ case TdsColumnType.MoneyN:
+ switch (columnSize) {
+ case 4:
+ return 0x0a;
+ case 8:
+ return 0x13;
+ }
+ break;
+ case TdsColumnType.Money4:
+ return 0x0a;
+ case TdsColumnType.NChar:
+ return 0xff;
+ case TdsColumnType.BigBinary:
+ return 0xff;
+ case TdsColumnType.BigVarBinary:
+ return 0xff;
+ case TdsColumnType.BigVarChar:
+ return 0xff;
+ case TdsColumnType.BigNVarChar:
+ return 0xff;
+ case TdsColumnType.BigChar:
+ return 0xff;
+ case TdsColumnType.SmallMoney:
+ return 0x0a;
+ case TdsColumnType.Variant:
+ return 0xff;
+ case TdsColumnType.BigInt:
+ return 0xff;
+ }
+
+ throw new NotSupportedException (string.Format (
+ CultureInfo.InvariantCulture,
+ "Fixed precision not defined for column " +
+ "type '{0}' with size {1}.", type, columnSize));
+ }
+
#endregion // Methods
#if NET_2_0