NETProvider icon indicating copy to clipboard operation
NETProvider copied to clipboard

Is InsertUsingStoredProcedure, UpdateUsingStoredProcedure, DeleteUsingStoredProcedure in model builder supported?

Open Camel-RD opened this issue 2 years ago • 2 comments

When i configure model with InsertUsingStoredProcedure and try to save changes, i get an error: SQL error code = -104 Token unknown - line 1, column 1 CALL

Camel-RD avatar Feb 12 '24 12:02 Camel-RD

Can we fix this issue with this code in FbUpdateSqlGenerator.cs?

	public override ResultSetMapping AppendStoredProcedureCall(
		StringBuilder commandStringBuilder,
		IReadOnlyModificationCommand command,
		int commandPosition,
		out bool requiresTransaction)
	{
		var storedProcedure = command.StoreStoredProcedure;
		var resultSetMapping = ResultSetMapping.NoResults;

		foreach (var resultColumn in storedProcedure.ResultColumns)
		{
			resultSetMapping = ResultSetMapping.LastInResultSet;
			if (resultColumn == command.RowsAffectedColumn)
			{
				resultSetMapping |= ResultSetMapping.ResultSetWithRowsAffectedOnly;
			}
			else
			{
				resultSetMapping = ResultSetMapping.LastInResultSet;
				break;
			}
		}

		commandStringBuilder.Append("EXECUTE PROCEDURE ");
		SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, storedProcedure.Name);

		if (storedProcedure.Parameters.Any())
		{
			commandStringBuilder.AppendLine();
			var first = true;

			for (var i = 0; i < command.ColumnModifications.Count; i++)
			{
				var columnModification = command.ColumnModifications[i];
				if (columnModification.Column is not IStoreStoredProcedureParameter parameter)
				{
					continue;
				}

				if (parameter.Direction.HasFlag(ParameterDirection.Output))
				{
					continue;
				}

				if (first)
				{
					first = false;
				}
				else
				{
					commandStringBuilder.Append(", ");
				}
				SqlGenerationHelper.GenerateParameterNamePlaceholder(
					commandStringBuilder, columnModification.UseOriginalValueParameter
						? columnModification.OriginalParameterName!
						: columnModification.ParameterName!);
			}
		}

		commandStringBuilder.AppendLine(SqlGenerationHelper.StatementTerminator);
		requiresTransaction = true;
		return resultSetMapping;
	}

Camel-RD avatar Jul 10 '24 16:07 Camel-RD

Can you create PR?

cincuranet avatar Jul 15 '24 08:07 cincuranet