SqlAnywhere v12 (SyBase v10) ASP.NET stored procedure named parameters being ignored

[Former Member]

Hello,

We are trying to use named parameters to execute stored procedures but regardless of what we construct, they seem to be ignored and the order of the parameters is observed instead.

As an example below, the iclientid is the 32nd parameter but we are only passing in 5 parameters, so iclientid is going into the 5th parameter which happens to be Add1, obviously not what we want.

var customerParams = new[]
{
    new SAParameter("@icustomerid", SADbType.Char)
    {
        Value = customerId
    },
    new SAParameter("@isite", SADbType.Char)
    {
        Value = siteId
    },
    new SAParameter("@isurname", SADbType.Char)
    {
        Value = surname
    },
    new SAParameter("@iforenames", SADbType.Char)
    {
        Value = firstName
    },
    new SAParameter("@ititle", SADbType.Char)
    {
        Value = title               
    },
    new SAParameter("@iclientid", SADbType.Char)
    {
        Value = trackerId
    }
};

Are we doing something wrong, any help most appreciated!

Jeavon

[RADical_Systems]

Hi All, I am involved in this as well at the database end. When we call the procedure in the database, (ISQL), passing just a few parameters i.e. the required ones and the iclientid the result is just fine and the clientid field is updated not address1.

We can only presume the .NET call is being made slightly differently but that is beyond my area of knowledge.

[Former Member]

An array of parameters does not tell us much about what is going on with the rest of the code. It might help if you include the Execute method, SQL statement and how you are associating that array to the collection and statment.

You could get the positional behaviour if the names don't match up exactly [tip: with SQL Anywhere you do not require an '@' prefix to your parameter names, so you could try removing that from the equation identifying parameter in the SQL statement with a ':' decorator].

This would also be expected to fail if the connection was not made using our Ado.Net data provider but is (instead) ~bridging~ to the ODBC or OleDB drivers. Otherwise, named parameter support was added back in version 10.

And finally, does 'Sybase10' indicate anything important here?

[jack_schueler]

The parameter name is "param" in the following example.

SELECT * FROM Customers WHERE ID = :param

The "parameter" name is essentially the host variable name.

So, as Nick said, more info such as the statement being executed would help.

[RADical_Systems]

Nick, I believe that they are using the Sybase 12 PHP drivers but the database being connected to is a Sybase 10 database.

[RADical_Systems]

Hi All,

Thanks for your help here. The web guys are off to do some changes and try to make calls directly without using the steps of the embedded processes. Hopefully that will solve the problem.

Have a great Easter.

Alasdair

[Former Member]

Hi, to follow on from what Jeavon said..

We pass those parameters to the following method which executes a stored procedure

public string ExecuteScalar(string storeProcedureName, SAParameter[] parameters)
{
    string returnValue = String.Empty;

    using (var database = new SAConnection(_connectionString))
    {
        database.Open();

        using (var command = database.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storeProcedureName;
            command.Parameters.AddRange(parameters);

            var response = command.ExecuteScalar();

            if (response != null)
                returnValue = response.ToString();
        }
    }

    return returnValue;
}

It seems to ignore the name of the parameters and just put them in the order provided. We are updating a record and only really want to provide the parameters to update and not the whole range.

I've removed the '@' symbols as suggested above and changed our connection string so that it is in the following format (after a bit of digging in the docs)

HOST=222.222.222.2:222;DBN=dbname;UID=username;PWD=password;

This is connecting fine. Will this make it use the ADO.NET provider rather than go through the OLE DB? Or is there another step to it?

[Former Member]

I've converted your answer to a comment since it sounds like you may still be having an issue.

Going by your use of "new SAConnection(_connectionString)", it seems you have been connecting with our V12 data provider all along already.

You might want to verify the rest of your code by changing your

command.CommandText = storeProcedureName;

to an explicit "call procedure( )". The use of just a bare procedure name is Transact-SQL shorthand for EXEC[ute] <proc-name> and that may not parse or operate correctly with our provider.

Also the use of named input parameters to identify the procedure parameters is a usage I have not investigated much yet myself. Most of the issues I've been involved with such named parameters has been with parameterized SQL statements. As such you might want to test with something more like this line to see if that works:

command.CommandText =

"@icustomerid =:customerId, @isite=:siteId, @isurname=:surname, @iforenames=:firstName, @ititle=:title, @iclientid= ..."

and if it does that could prove that the parameter array and 'add' collection range operations are correct and it may just be down to mapping named parameters to procedure defined input parameters.

You will have to map that into valid VB.Net corrected syntax for me ... (more c/cpp/c#/java focussed myself)

Good luck