-1

I have asked this question on Github, issue #641, since it seems like a deficiency or an enhancement question.

Most databases (e.g. Oracle, SQL*Server, Sybase, ... ) send back a description of the resultset, even if there are zero rows. I cannot seem to find this in pqxx, nor can I find a way to describe SQL of any kind prior to execution. The only way I have been able to get the SQL metadata is by collection upon the first row retrieved, but this is limited to an actual row being retrieved. If there are zero rows, I have no way to determine the metadata.

Looking at the v15.x code base for libpq (standard C library), I see that psql has a "workaround" for this very problem. In the file src/bin/psql/common.c there is a function called static bool DescribeQuery(const char *query, double *elapsed_msec) (on or about line #1248).

This function has a rather ingenious solution for this very problem. They create an "unnamed" prepared statement, which they do not execute, instead they use the results of that call, which provides the field info, and subsequently query against the pg_catalog for the metadata descriptions (source below).

Unfortunately, the pqxx library doesn't provide enough (that I can tell) to duplicate this functionality. Does anyone have a solution for this problem?

nvanwyen
  • 81
  • 1
  • 4
  • You can always ask the catalogs. – πάντα ῥεῖ Feb 16 '23 at 21:43
  • Do you mean result? https://libpqxx.readthedocs.io/en/stable/a01132.html#details – Frank Heikens Feb 16 '23 at 21:49
  • The result set object would be fine if it were returned on a zero rows query, but it is not. Again, I'm looking to describe SQL (like a query) on zero rows. – nvanwyen Feb 16 '23 at 22:24
  • @nvanwyen you can always ask the catalogs, and build prepare) your SQL statements properly based on that information. – πάντα ῥεῖ Feb 16 '23 at 22:29
  • Maybe this will help. In Oracle OCI, if I submit a query to the DB I can use the "OCI_DESCRIBE_ONLY" flag to retrieve the SELECT column list, including position, name, type, length, etc... I can achieve the same thing in Oracle, if I call OCIStmtExecute() but it doesn't return any data. This question is how to achieve this in pqxx (or an alternative). – nvanwyen Feb 16 '23 at 22:33
  • @ πάντα ῥεῖ . Thank you for responding but your assuming I have control over the query to begin with, but these are user input, much like the psql. If I know I was going to get ```select * from mytable```, then yes the catalog helps. But I don't know the query upfront, and I cannot parse it because it may be complex like a WITH() statement. Do you see my issue? The catalog works only if a get a list of column OID at a minimum. – nvanwyen Feb 16 '23 at 22:42
  • I have asked the developer of libpqxx to consider, what seems to be, a simple change in the ``pqxx::connection::prepare()``` call ... https://github.com/jtv/libpqxx/issues/641#issuecomment-1433950910 – nvanwyen Feb 17 '23 at 01:08

1 Answers1

0

Credit for resolution goes to Jeroen Vermeulen, on the Github issue #641. He suggested that the predicate AND false be added to the query to force a prototype result. By doing this an empty resultset is returned, with the fields available for metadata collection.

nvanwyen
  • 81
  • 1
  • 4