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

asked 15 Mar '16, 06:14

Jeavon's gravatar image

Jeavon
26112
accept rate: 0%

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.

(15 Mar '16, 06:37) RADicalSYS

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?

(16 Mar '16, 10:51) Nick Elson S...
Replies hidden

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.

(16 Mar '16, 11:14) JBSchueler

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

(16 Mar '16, 12:51) RADicalSYS

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?

permanent link

answered 16 Mar '16, 14:07

jack_'s gravatar image

jack_
11
accept rate: 0%

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

(17 Mar '16, 10:16) Nick Elson S...

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

permanent link

answered 24 Mar '16, 07:32

RADicalSYS's gravatar image

RADicalSYS
1266819
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×19
×8

question asked: 15 Mar '16, 06:14

question was seen: 516 times

last updated: 24 Mar '16, 07:32