oledb diregarding parameters on sqlanywhere-db

Question

I have a SqlContext class which is implemented for SQL Server, MySql, Odbc and Oledb. It delivers basic functions like insert, update and delete. It worked all fine until I had to connect to a SqlAnywhere database. When I call update, I get an error which says the column is not found.

Here is my Odbc implementation for the update function:

public override int Update(string tableName, Action<ISqlParameterizer> actionSqlParameterizerUpdate, Action<ISqlParameterizer> actionSqlParameterizerWhere)
{
    var commandText = $"update {tableName} set @update where @where";

    var whereBuilder = new StringBuilder();
    var updateBuilder = new StringBuilder();
    var whereParameterizer = SqlParameterizer.CreateFrom(this);
    var updateParameterizer = SqlParameterizer.CreateFrom(this);

    actionSqlParameterizerWhere?.Invoke(whereParameterizer);
    actionSqlParameterizerUpdate?.Invoke(updateParameterizer);

    List<IDbDataParameter> parameterList = new List<IDbDataParameter>();

    foreach (var whereParameter in whereParameterizer.GetParameters())
    {
        whereBuilder.Append($" and {whereParameter.ParameterName} = @{whereParameter.ParameterName}");
        parameterList.Add(whereParameter);
    }

    foreach (var updateParameter in updateParameterizer.GetParameters())
    {
        updateBuilder.Append($", {updateParameter.ParameterName} = @{updateParameter.ParameterName}");
        parameterList.Add(updateParameter);
    }

    commandText = commandText.Replace("@where", whereBuilder.ToString().Substring(4));
    commandText = commandText.Replace("@update", updateBuilder.ToString().Substring(1));

    return base.ExecuteNonQuery(commandText, parameterList.ToArray());
}

I call it like this:

base.Source.Update("SOME_TABLE", set => 
{
    set.Add("ANWENDER_ID", anwender_id); //Store some FK in here
},where =>
{
    where.Add("user_name", user_name); //For a specific user (user_name is pk)
});

The exception message:

ERROR [42S22] [SAP][ODBC Driver][SQL Anywhere]Spalte '@user_name' nicht gefunden

It says "column '@user_name' not found".

The commandText looks like this:

update SOME_TABLE set ANWENDER_ID = @ANWENDER_ID where user_name = @user_name

The parameters of my SqlCommand are filled correctly. Why does it say that the column is not found? The column exists and has the right name. When I copy the commandText and fill in the parameters, I can execute the query. Can someone explain this?

Answer 1

The @parameter in your CommandText is not valid syntax for SQLAnywhere.

Placeholders for parameters in SQL statements are indicated by the ? character. For any INSERT, UPDATE, or DELETE, each ? is referenced according to its ordinal position in the command's parameters collection. For example, the first ? is referred to as 0, and the second as 1.

So your CommandText needs to look like this:

update SOME_TABLE set ANWENDER_ID = ? where user_name = ?