Bind variable options

Question

Is it possible to denote a bind variable in SQL Server using the same notation as Oracle i.e. :0, :1 instead of using ?.

I have searched but not found anything conclusive on this. At the moment my solution uses bind variables to introduce values to statements that I run on the database which works great in Oracle but I also need to do the same in SQL Server and PostGIS to name a few. I dont want to have to say for example:

switch(dialect)
{
    case "Oracle":
    {
        oleDataBaseConnection.AddParameter(":1", coordsys);
        break;
    }

    case "SQLServer":
    { 
        oleDataBaseConnection.AddParameter("?", coordsys);
        break;
    }
}

*AddParameter() is a function from my wrapper class that add the values to a list to be added to the command object when calling the execute function I have written.

I would like my code to be as clean as possible and not contain hardcoded stuff as shown above. I would like a solution that fits all. (yes I know this is probably wishful thinking!)

I know I could use a string replace to do this but it is just not what I am after. I dont want to use a workaround like that really. Also my project manager requested that I try to find a solution using bind variables.

Any ideas?

score 1 · Accepted Answer · answered Feb 21 '12 at 16:37

1

The short answer is no, bind variables are implemented differently in different databases. So you'll need the messy compatibility logic somewhere.

That said, I personally have solved this problem in the past using string substitution to put in the right syntax for a bind parameter. Thus you could embed :coordsys in your SQL statement, and then oleDataBaseConnection.AddParameter("coordsys", coordsys); in your code. Your prepare statement would then search the SQL, find :coordsys and replace it by whatever you need (for instance ?), and also build up the list of parameters by name for your later execute. When you go to execute, you can on the fly build up the right list of bind parameters to use.

Implementing the behind the scenes bit is a bit tricky, but I've personally found that it leads to clean SQL, with the benefits of bind parameters (like database performance, security from SQL injection attacks).

answered Feb 21 '12 at 16:37

btilly

43,296
3
59
88

I think I get what you mean. So basically the character substitution would be handled by my wrapper class when I add the parameters to the command. So I would simpy detect the dialect needed as I do now and put in the correct syntax be that a ? or a :. One issue is that one of my databases may not support bind variables at all. I guess in this case I would have to do a string replace in my code but I would have to know before hand that it doesnt support a bind variable. It is quite a tricky task really. – CSharpened Feb 21 '12 at 16:43
@CSharpened exactly. However at this point I'd expect all decent database drivers to support bind variables in some way, because that is so important for avoiding SQL injection attacks. – btilly Feb 21 '12 at 16:56
Ok thanks for the info. I will see if I can implement your suggestion successfully. It seems like what I need and had thought of but I was hoping there may be a slightly more elegant solution. Thanks. – CSharpened Feb 21 '12 at 16:59

Bind variable options

1 Answers1