[squeak-dev] PostgresV3 with parameter binding

Pierce Ng pierce at samadhiweb.com
Tue Dec 25 03:23:59 UTC 2018

On Sun, Dec 23, 2018 at 01:50:45PM +0100, Levente Uzonyi wrote:
> I didn't finish the implementation of the extended query protocol

Turns out the modifications required to support parameter binding are

> it didn't really give us any benefit. I expected better parsing performance
> from binary encoded column values, but that only applies to certain types,

For me the first consideration is security. I avoid libraries that do
SQL string construction.

> I have added you as developer.

Thank you. I've committed my changes to PostgresV3-Core and added
PostgresV3-DBAPI, a very simple client API. Tested select, insert and
delete successfully. 

In PostgresV3-DBAPI, examples are in PG3ExampleClient class-side. Here's

    self run: 'insert into actor (first_name, last_name) values ($1, $2)'
        parameters: #('JACKIE' 'CHAN').
    (self run: 'select actor_id, first_name, last_name from actor where last_name = $1' 
        parameters: #('CHAN')) 
      first inspect.
    self run: 'delete from actor where first_name = $1 and last_name = $2' parameters: #('JACKIE' 'CHAN').
   Expected result: 
   <actor_id> JACKIE CHAN

These examples require a running PostgreSQL server with the Sakila database
loaded and appropriate permissions for the user 'testuser'. Setting that up
is left as an exercise for the reader. :-)

All existing tests pass, and the simple query protocol should continue to
work as seen in PG3ExampleClient class>>selectSakilaSimple.

Some points about my changes:

- The states DescribeCompleted and GotRowDescription overlap. I imagine one
  of them is redundant, although I didn't look at the former very hard.
  I've gone for the latter.

- For binding string parameters, printing Smalltalk strings literally as
  done by String>>pg3PrintAsLiteralOn: doesn't work, because the
  parameter that is transmitted is then a string in single quotes which
  is usually not what is wanted. I've left the method alone and modified
  PG3BinaryWriteStream>>writeObject:formatCode: instead.

- Only tested with string and integer parameters.

- The DB API does query-based parse-bind-execute. It doesn't currently
  do repeatable portal-based execution. Something to add if needed.

Season's greetings.


More information about the Squeak-dev mailing list