Re: [Firebird-net-provider] more commands

[<car...@te...>]

Hello:

> i think, that ExecuteSclalar is working fine , but when i use same 
> connection and transaction for two different commands there is 
> exception, that transactions in trans and conn are different... I will 
> try it with your build... Again thanx for that...

Ok, tell to me if you continue to experience this problem.

> by the way... I wan to help you improve helps, so i will send you 
> modified xml files...

Ok without problems.

> and you said that you implemented named parameters??? I was looking for 
> that in sources yesterday but i didnt find it :(

See in FbStatement.cs, this it's a really simple modification but it's 
working well in my tests ( ideas are welcome :) ), GetInSqlda was 
modified and there are a new method:

		/// <include file='xmldoc/fbstatement.xml' 
path='doc/member[@name="M:GetInSqlda"]/*'/>		
		private XSQLDA GetInSqlda()
		{
			XSQLDA		in_sqlda	= statement.InSqlda;
			Encoding	encoding	= Connection.Encoding;
			Regex		search		= new Regex("(@([a-zA-Z-$][a-zA-Z0-9_$]*).)");
			string		parameterName = String.Empty;
			
			if (in_sqlda == null)
			{
				return in_sqlda;
			}

			MatchCollection matches = search.Matches(CommandText);
			
			for (int i = 0; i < in_sqlda.sqln; i++)
			{
				try
				{
					parameterName = matches[i].Value.Trim();
				}
				catch
				{
					parameterName = parameters[i].ParameterName;
				}

				if (parameters[parameterName].Value == System.DBNull.Value)
				{
					if ((in_sqlda.sqlvar[i].sqltype & 1 ) == 0)
					{
						throw new InvalidOperationException("Input parameter value cannot 
be null.");
					}
					else
					{
						in_sqlda.sqlvar[i].sqlind	= -1;
						in_sqlda.sqlvar[i].sqldata	= null;
					}
				}
				else
				{
					long multiplier = 1;
					if (in_sqlda.sqlvar[i].sqlscale < 0)
					{
						int exp = in_sqlda.sqlvar[i].sqlscale*(-1);
						multiplier = (long)System.Math.Pow(10, exp);
					}

					switch (in_sqlda.sqlvar[i].sqltype & ~1)
					{
						case GdsCodes.SQL_TEXT:
						case GdsCodes.SQL_VARYING:							
							in_sqlda.sqlvar[i].sqldata = 
encoding.GetBytes((string)parameters[parameterName].Value);
							in_sqlda.sqlvar[i].sqllen  = 
encoding.GetByteCount((string)parameters[parameterName].Value);
							break;

						case GdsCodes.SQL_SHORT:
							if (in_sqlda.sqlvar[i].sqlscale < 0)
							{
								decimal paramValue = 
Convert.ToDecimal(parameters[parameterName].Value.ToString());
								in_sqlda.sqlvar[i].sqldata =
									Convert.ToInt16(paramValue*multiplier);
							}
							else
							{										
								in_sqlda.sqlvar[i].sqldata = 
Convert.ToInt16(parameters[parameterName].Value.ToString());
							}
							break;

						case GdsCodes.SQL_LONG:
							if (in_sqlda.sqlvar[i].sqlscale < 0)
							{
								decimal paramValue = 
Convert.ToDecimal(parameters[parameterName].Value.ToString());
								in_sqlda.sqlvar[i].sqldata =
									Convert.ToInt32(paramValue*multiplier);
							}
							else
							{
								in_sqlda.sqlvar[i].sqldata = 
Convert.ToInt32(parameters[parameterName].Value.ToString());
							}
							break;

						case GdsCodes.SQL_FLOAT:
							in_sqlda.sqlvar[i].sqldata = 
float.Parse(parameters[parameterName].Value.ToString());
							break;

						case GdsCodes.SQL_DOUBLE:
						case GdsCodes.SQL_D_FLOAT:
							in_sqlda.sqlvar[i].sqldata =
								Convert.ToDouble(parameters[parameterName].Value.ToString());
							break;

						case GdsCodes.SQL_BLOB:
							if (in_sqlda.sqlvar[i].sqlsubtype == 1)
							{
								FbClob clob = new FbClob(Connection, Transaction);
								in_sqlda.sqlvar[i].sqldata = clob.Write(
									Convert.ToString(parameters[parameterName].Value));
							}
							else
							{
								FbBlob blob = new FbBlob(Connection, Transaction);
								in_sqlda.sqlvar[i].sqldata = 
blob.Write((byte[])parameters[parameterName].Value);
							}

							break;

						case GdsCodes.SQL_ARRAY:
							// TODO: Write array data
							break;

						case GdsCodes.SQL_QUAD:
						case GdsCodes.SQL_INT64:
							if (in_sqlda.sqlvar[i].sqlscale < 0)
							{
								decimal paramValue = 
Convert.ToDecimal(parameters[parameterName].Value.ToString());
								in_sqlda.sqlvar[i].sqldata =
									Convert.ToInt64(paramValue*multiplier);
							}
							else
							{										
								in_sqlda.sqlvar[i].sqldata = 
Convert.ToInt64(parameters[parameterName].Value.ToString());
							}
							break;

						case GdsCodes.SQL_TIMESTAMP:
						case GdsCodes.SQL_TYPE_TIME:			
						case GdsCodes.SQL_TYPE_DATE:				
							in_sqlda.sqlvar[i].sqldata = 
DateTime.Parse(parameters[parameterName].Value.ToString());
							break;

						default:
							throw new NotSupportedException("Unknown data type");
					}							
				}
			}

			return in_sqlda;
		}

		/// <summary>
		/// Format the Command Text string and subst the parameters names by ?
		/// </summary>
		private string ParseCommandText(string commandText)
		{
			string		sqlText			= commandText;

			if(Parameters.Count != 0)
			{
				foreach(FbParameter parameter in parameters)
				{
					/* If ParameterName Starts with "@" replace
					 * ParameterName with ?
					 */
					if (parameter.ParameterName.StartsWith("@"))
					{
						sqlText = sqlText.Replace(parameter.ParameterName, "?");
					}
				}
			}

			return sqlText;
		}




Best regards
Carlos Guzmán Álvarez
Vigo-Spain