tomazos has asked for the wisdom of the Perl Monks concerning the following question:

I'm using DBI to talk to a MySQL server.

When preparing SQL statements to send to the server, DBI circumvents the problem of different SQL syntaxes across all the different SQL servers (Oracle, Sybase, MySQL etc) by simply taking the statement as a raw string and letting the SQL server tell you whether the syntax is valid or not.

The problem with this is that you don't find out until deep into runtime whether there is a problem with your SQL - even something simple such as a typo.

I appreciate that it would be really hard to write a layer over SQL that was general across all servers, but I'm really only interested in MySQL's specific syntax to get my job done.

With that as a given, I think it should be possible to write something (perhaps like a CGI.pm style interface to HTML) that will allow you to compile-time check MySQL specific SQL syntax, and perhaps even check if you are refering to real databases and tables.

I was thinking something like: 

$sth->prepare(
        table{users}->select(
          all,
          where(
            like{name}->quote('%john%')
          )
        )
      )

[download]

would be equivilant to: 

$sth->prepare( << 'EOS' );
  select * from users where name like '%john%'
EOS

[download]

Obviously for this simple example the extra layer is not worth the overhead for clarity's sake - but for a more complex real world situation the compile time syntax checking would be worth it.

Has anyone got any thoughts further to this?

Replies are listed 'Best First'.
Re: SQL/DBI
by Zaxo (Archbishop) on Oct 20, 2001 at 10:43 UTC
    When preparing SQL statements to send to the server, DBI circumvents the problem of different SQL syntaxes across all the different SQL servers (Oracle, Sybase, MySQL etc) by simply taking the statement as a raw string and letting the SQL server tell you whether the syntax is valid or not.

    When you &DBI::connect, give it RaiseError=>1 in the \%attr slot. Testing should then catch unimplemented SQL.

    DBI is the cross-driver interface to SQL. stick to vanilla DBI.pm and most things will work.

    Your example suggests that your application is pretty tightly bound to some data structures you haven't explained.

    After Compline,
    Zaxo

[reply]
Re: SQL/DBI
by lhoward (Vicar) on Oct 20, 2001 at 14:55 UTC
    You should check out the SQL::Statement module. It will validate generic SQL with no database connection required. Of course, w/o a DB it won't be able to validate table and col names, but it will make sure you're syntactically valid.
[reply]
Re: SQL/DBI
by jepri (Parson) on Oct 20, 2001 at 10:51 UTC
    Since most useful databases support SQL I don't understand how the syntax changes between them. mSQL, mySQL, PostGreSQL, Oracle all support SQL 92. Some of them omit commands, like leaving out rollback if they don't support transactions, but that isn't a different syntax, just a cut down command set.

    Do you actually have an example of a 'syntax change' that you could show us? In your example that query will run on all the databases that I mention above.

    The delayed syntax checking is annoying but nothing stops you from logging into the database from a command shell and running your queries directly to check. There are large parts of Perl that only explode when you execute them, so this is at least consistent.

    Finally there is a module that does what you want, it's called Tie::DBI. It presents tables as hash variables and you can write to or read from them.

    You could conceivably write a module that checks your SQL at compile time, which would be kind of convenient.

    ____________________
    Jeremy
    I didn't believe in evil until I dated it.

[reply]

      If only that were so. There are as many 'dialects' of SQL as there are DMBS's pratically. Without even getting into extensions such as P/L SQL, T-SQL, PgSQL there are many differences. SQL92 is like the HTML/CSS specifications, Oracle and MSSQL being the Netscape 4.x and IE of the DBMS world with Postgres, mySQL, mSQL, etc. somewhere in the mozilla, Opera, etc. area ;)

      While the main DML statements are generally interoperable, various keywords and clauses are specific to packages. For example:

      SELECT TOP 10 *
FROM table

SELECT *
FROM table
LIMIT 10
OFFSET 1

SELECT *
FROM table
LIMIT 10[, 1]

      [download]
      [ ] denotes an optional piece

      The first works in MS SQL the later forms work in Postgres and MySQL (though Pg also supports the short form). I might even be confusing myself as I have to talk Informix (akin to MS in this regard I believe), Oracle, or DB2 on occasion as well. Now it's not just big bad MS doing this, I only chose the example because it was easy syntax. Pratically every DBMS has 'standard' command, in the sense they're used often, that are subtly (or not so subtly) incompatible with their peers.

      DDL is a whole different story, it's far far worse for compatibility as it's more directly tied to the internals of the DMBS you're working with (a quick example is datatype names).

      Now skipping to the original question

      As you've already stated, portability isn't an issue to you -- or you wouldn't ask for MySQL specific. And variable interpolation also isn't an issue as you can't check dynamic statements except on runtime (I'm not talking data binding with ? here). So why not just skip the whole prepare section? Store the SQL in the database itself as a stored procedure or view. That way the execution plan is already worked out; a grand total of once for the entire application. You can then just call it via do without any penalties.

      Note: I don't use MySQL much as it lacks the features I need, so I'm not sure it has views or something similar. It didn't have SPs the last time I used it.
[reply]
[d/l]
[select]
        I wasn't really thinking about extensions - I was talking about core SQL. Obviously if you use DBMS specific features they aren't going to be portable. However most DBMS that I have used (at least the four I list) make more than a passing attempt to be SQL compatible. I've never used MS SQL (and with luck it may never happen) so I don't know if it accepts LIMIT clauses. Does it? Then your second example would run on MSSQL and everyone would be happy.

        As you say DDL is a huge problem but I usually do the schema separately to the program and yes, they have to be ported for each DBMS.

        I do agree with you that views are a good idea, and again most good DBMSs should support them. I see most of the problem to be unsupported language features, rather than fundamental incompatibilities. T

        ____________________
        Jeremy
        I didn't believe in evil until I dated it.

[reply]

Back to Seekers of Perl Wisdom