[Mono-list] SQL Parser for System.Data

[Piers Haken]

Sure, you'll need to support providers that don't natively support
prepared statements in the backend.

PostgreSQL's JDBC driver does exactly this.

Here's the code:

#line 58 "org/postgresql/jdbc2/PreparedStatement.java"
        for (i = 0; i < sql.length(); ++i)
        {
            int c = sql.charAt(i);

            if (c == '\'')
                inQuotes = !inQuotes;
            if (c == '?' && !inQuotes)
            {
                v.addElement(sql.substring (lastParmEnd, i));
                lastParmEnd = i + 1;
            }
        }
        v.addElement(sql.substring (lastParmEnd, sql.length()));


I'm not saying you guys shouldn't embark on writing a SQL parser, I'm
just saying that there isn't really a need for it in System.Data.

Piers.

-----Original Message-----
From: Rodrigo Moya [mailto:rodrigo@ximian.com] 
Sent: Saturday, May 11, 2002 7:47 AM
To: Piers Haken
Cc: Daniel Morgan; Mono List
Subject: RE: [Mono-list] SQL Parser for System.Data


On Sat, 2002-05-11 at 15:31, Piers Haken wrote:
> But this breaks the whole idea of prepared statements: where you
> prepare a statement in the database and then bind the parameters to it

> on each call.
> 
> I don't know about other databases but I know that for MS SQL, there's
> a significant performance gain in preparing a statement and then 
> calling it multiple times, as opposed to just calling an ad-hoc 
> statement multiple times. As far as I know it's essentially creating a

> stored procedure out of the statement.
> 
yes, MS SQL does it, and thus System.Data has a lot of MSSQLisms. But
not all managed providers will support the same. That's why we'd need a
SQL parser.

> If you do the parameter replacement on the client side then you lose
> all this benefit. The whole idea of SqlParameter is that the parameter

> replacement is done by the DBMS.
> 
we are not talking about doing the parsing on the clients, but on the
managed providers, as an internal implementation detail. That is, the
whateverSQL provider can't send SQL commands with parameter placeholders
because the DB server does not support it. So, internally, the
whateverSQL provider parses the SQL command, finds placeholders,
replaces them with values, and send the clean SQL to the DB server.

In fact, that SQL parser could even be an internal class to be used only
in System.Data managed providers.

cheers