[DB-SIG] API suggestion: expose 'quote' method

[Magnus Lyckå]

At 20:20 2003-06-03 -0700, Chris Cogdon wrote:
>Clue me in here. What's Dynamic SQL, CLI, and Embedded SQL ?

Phew... I'm not so good at short explanations. :)

Among the things described in the SQL standard, are standardized
ways to call the database from within your program. You then need
to pass data from C/C++/COBOL/ADA/whatever variables to the SQL
statements and back. This is called binding.

Over the years, different binding styles have been invented, and
the SQL/92 standard identifies four different. I don't know if
any more has been added in later versions of the SQL Standards.
(Something for Java?) The four in SQL/92 are: module, embedded SQL
(ESQL), Call-Level Interface (CLI) and direct SQL.

Module is the oldest, and I don't know how much it's used outside
COBOL or PL/I. For instance, you might write...

PROCEDURE DELETE_PART
         ( SQLSTATE,
           :PNO_PARAM CHAR(6) ) ;
     DELETE FROM P WHERE P.PNO = :PNO_PARAM ;

...in the module language, and as far as I understand, you compile this
with a specific compiler, and link it with your PL/I program where you
do...

CALL DELETE_PART ( RETCODE, PNO_ARG ) ;

...to call this procedure.

I think Embedded SQL (ESQL) is what most programmers who write SQL
from C or C++ have used. You place SQL statements inside special
sections in your C code, and run a pre-processor that turns this
into RDBMS specific function calls, before you compile your program.

Depending on your backend, different things might happen here. In
DB2 your SQL statements will end up in a bind-file that you store
in the database more or less like if they were stored procedures,
and the C/C++ code that came from the ESQL pre-processor will contain
function calls that call these prodedures, and map parameters to your
local variables. It's up to the RDBMS to make your ESQL turn into
something that works.

At least ESQL comes in two "Flavours": Static and Dynamic. (I don't
know if there is dynamic SQL in module. I doubt it.)

In static SQL, your entire SQL statement is determined at compile
time, and it might look like this in the middle of your C code:

EXEC SQL DELETE FROM P WHERE P.PNO = :PNO_PARAM ;

PNO_PARAM has been declared as a C variable of suitable type inside
a special "DECLARE SECTION" before it's used. For selects etc that
might return many rows, you will need to perform an EXECUTE, and
then FETCH in a loop and check for error codes etc. It's not too
different from the Python DB-API in principle, but much more verbose
and complicated, since it can't rely on high level features like
Python's lists, tuples and exceptions.

With dynamic SQL, which isn't used as often, you can't use the
:PNO_PARAM approach above. That approach is used to bind a C variable
to the SQL call att compile time, which won't work if the SQL string
is to be built dynamically. It's not Python you know... :) Instead,
you use '?' as a placeholder in your SQL string, and pass the parameters
separately using a rather complicated approach. Let's leave that. CLI
is a much better than Dynamic SQL, but the syntax of the SQL strings are
the same, it's the surrounding junk that differs.

The Command-Level Interface (CLI) is the most recent interface and
binding style. Like Dynamic SQL, '?' is used for parameter passing.
While Embedded SQL defines a source code standard, CLI defines an
API. With CLI, we don't need to bother with Embedded SQL and
preprocessors at all. Instead, we call functions with standardized
names, passing in SQL as character arrays or whatever our programming
language supports. Binding variables is still much, much more verbose
than in Python, but at least it's possible to use the same binary to
connect to different backends if you use a least common denominator for
SQL.

Direct SQL is what you run from an interactive SQL monitor, such as
psql. That you know...

>Granted. However, the application programmer would also need to know how 
>to correctly represent particular datatypes as a string, and that's 
>something that 'string-only' APIs do right now. In those cases, it 'makes 
>sense' to be able to call exactly the same function the API uses to 
>convert those values to strings. Even now, that's driver-dependant (pg 
>uses quote, PgSQL uses _quote, and it's not available at all on PoPy nor 
>psycopg).

I agree with that.

>But... I DO understand the difficulty in exposing a function that doesn't 
>currently exist (such as the ODBC driver). And it may NOT be worth the 
>difficulty in adding that functionality as a requirement for a later API. 
>(Perhaps optional? At least if it's doable, we could get a consistent 
>interface).

Agree here too.

>I've cited examples before, but... if they're not clear enough, I think 
>I'd need to cite a complete working model. Can we take it as read that 
>there may be SOME applications where this ability would be useful ?

Do you mean that the SQL would run faster in the database,
or do you mean that it would be faster for you to code the
SQL? I don't doubt that the latter can be true.

>Yes, but the interface is non-standard, or not reachable. If it was in a 
>consistent place (as I'm suggesting), then I could swap between drivers or 
>databases at will.

I agree with that.


--
Magnus Lycka (It's really Lyckå), magnus at thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language