Support for parameters in multiple statements

[honewatson]

Hi thanks for the great work.

Can we please have support for parameters in multiple statements?

There seems to be some work complete on this for https://github.com/tulayang/asyncmysql so would be good if the Postgres version also supported this.

[cheatfate]

Could you please explain more precisely? I'm not sure i can understand what are you trying to achieve...

[cheatfate]

Are you are talking about this limitation?

import asyncdispatch, asyncpg, strutils

proc multipleStatements(conn: apgConnection) {.async.} =
  # Execute multiple SELECT operations
  # Only text SQL queries are supported, you cannot separate
  # parameters from query, e.g.
  # var r = await conn.exec("SELECT $1; SELECT $2; SELECT $3", 1, 2, 3)
  var r = await conn.exec("SELECT 1; SELECT 2; SELECT 3")
  # Get number of results
  var resultsCount = len(r)
  echo "Results count = " & $resultsCount

  # Echo first result
  echo getValue(r[0])
  # Echo second result
  echo getValue(r[1])
  # Echo third result
  echo getValue(r[2])

  # Close results
  close(r)

var connStr = "host=localhost port=5432 dbname=travis_ci_test user=postgres"
var conn = waitFor connect(connStr)

waitFor conn.multipleStatements()
conn.close()

[honewatson]

Yes that it is correct. I see you have a line commented out. Would this be a difficult enhancement?

[cheatfate]

This is problemmatic, just because i'm using libpq postgresql native library api functions which has such limitations:

The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping.

Unlike PQexec, PQexecParams allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.) This is a limitation of the underlying protocol, but has some usefulness as an extra defense against SQL-injection attacks.

https://www.postgresql.org/docs/current/static/libpq-exec.html#LIBPQ-PQEXECPARAMS

[cheatfate]

To implement it by myself i need to have SQL parser with support of all PostgreSQL extensions, and this is pretty tough work.

[honewatson]

Thanks for the update. Sounds complex. I wonder if it might be easier creating a PL/Nim Library by wrapping PL/C with some kind of easy deployment mechanism of the generated dynamic libraries.