Parameterized queries to different data providers

Hi,

I would like to build my app so it can connect to SQL Server, Oracle
or mySQL and query data.
Data queries should be parameterized to reduce impact of SQL
Injection.

SQLServer Dataprovider uses named parameters (i.e. @name)
OracleClient Dataprovider uses named parameters (i.e. :name)
mySQL ODBC Dataprovider uses question marks (i.e. ?) on the place of a
parameter (so order of question marks is important)

How do I write my queries easily so they will work with all 3 of these
data providers?

For example how do I write this to work with all tree:

SELECT a1, a2 FROM table1 WHERE a1 = 'blah' AND a2 = 'blahblah'

thank you
_dino_

Miha
Tue Nov 29 14:48:16 CST 2005

Hi Dino,

I don't think that there is an easy solution other than doing naming based
on provider manually.
Perhaps you might look into ORM products that do that part for you?

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Dino Buljubasic" <dino@noplacelikehome.com> wrote in message
news:faepo1t9vt5nqnp3bu3joc11ku0gal646u@4ax.com...
> Hi,
>
> I would like to build my app so it can connect to SQL Server, Oracle
> or mySQL and query data.
> Data queries should be parameterized to reduce impact of SQL
> Injection.
>
> SQLServer Dataprovider uses named parameters (i.e. @name)
> OracleClient Dataprovider uses named parameters (i.e. :name)
> mySQL ODBC Dataprovider uses question marks (i.e. ?) on the place of a
> parameter (so order of question marks is important)
>
> How do I write my queries easily so they will work with all 3 of these
> data providers?
>
> For example how do I write this to work with all tree:
>
> SELECT a1, a2 FROM table1 WHERE a1 = 'blah' AND a2 = 'blahblah'
>
> thank you
> _dino_

Marina
Tue Nov 29 15:29:52 CST 2005

This is why it is virtually impossible to write one code base that works for
multiple database engines. There are just too many differences among them,
and invariable there has to be provider specific code.

"Dino Buljubasic" <dino@noplacelikehome.com> wrote in message
news:faepo1t9vt5nqnp3bu3joc11ku0gal646u@4ax.com...
> Hi,
>
> I would like to build my app so it can connect to SQL Server, Oracle
> or mySQL and query data.
> Data queries should be parameterized to reduce impact of SQL
> Injection.
>
> SQLServer Dataprovider uses named parameters (i.e. @name)
> OracleClient Dataprovider uses named parameters (i.e. :name)
> mySQL ODBC Dataprovider uses question marks (i.e. ?) on the place of a
> parameter (so order of question marks is important)
>
> How do I write my queries easily so they will work with all 3 of these
> data providers?
>
> For example how do I write this to work with all tree:
>
> SELECT a1, a2 FROM table1 WHERE a1 = 'blah' AND a2 = 'blahblah'
>
> thank you
> _dino_

Martin
Tue Nov 29 16:31:42 CST 2005

I have written a utility function that corrects parameter names based upon
the xxxCommand object being used; my data access layer then pre-processes
each command object before executing it.

For info: SqlServer requires "@name" - correct; Oracle requires "name" or
":name" - the colon being optional and Odbc ignores parameter names instead
relying on the parameter order being correct so providing that you always
order your parameters in the sequence that they are used, you can then
simply use names and then pre-process to add "@" for SqlServer (I found
regular expressions were the easiest way for this).


"Dino Buljubasic" <dino@noplacelikehome.com> wrote in message
news:faepo1t9vt5nqnp3bu3joc11ku0gal646u@4ax.com...
> Hi,
>
> I would like to build my app so it can connect to SQL Server, Oracle
> or mySQL and query data.
> Data queries should be parameterized to reduce impact of SQL
> Injection.
>
> SQLServer Dataprovider uses named parameters (i.e. @name)
> OracleClient Dataprovider uses named parameters (i.e. :name)
> mySQL ODBC Dataprovider uses question marks (i.e. ?) on the place of a
> parameter (so order of question marks is important)
>
> How do I write my queries easily so they will work with all 3 of these
> data providers?
>
> For example how do I write this to work with all tree:
>
> SELECT a1, a2 FROM table1 WHERE a1 = 'blah' AND a2 = 'blahblah'
>
> thank you
> _dino_

IUnknown
Wed Nov 30 15:08:07 CST 2005

You may need OleDBPro for dotNet at http://www.udaparts.com/oledbpronet.htm

For sure, it will work for all of databases using one copy of source code
only!



"Dino Buljubasic" <dino@noplacelikehome.com> wrote in message
news:faepo1t9vt5nqnp3bu3joc11ku0gal646u@4ax.com...
> Hi,
>
> I would like to build my app so it can connect to SQL Server, Oracle
> or mySQL and query data.
> Data queries should be parameterized to reduce impact of SQL
> Injection.
>
> SQLServer Dataprovider uses named parameters (i.e. @name)
> OracleClient Dataprovider uses named parameters (i.e. :name)
> mySQL ODBC Dataprovider uses question marks (i.e. ?) on the place of a
> parameter (so order of question marks is important)
>
> How do I write my queries easily so they will work with all 3 of these
> data providers?
>
> For example how do I write this to work with all tree:
>
> SELECT a1, a2 FROM table1 WHERE a1 = 'blah' AND a2 = 'blahblah'
>
> thank you
> _dino_