Thread: ODBC call syntax returns 1 column instead of n columns

Greetings everybody,

 I am currently writing an application in C++ that uses ODBC to connect to a PGSQL database, and have run into an interesting issue regarding the escape sequence "{call <func>([params])}". Here's a bit of information about my current configuration. I'm using Ubuntu Edgy Eft using the following packages:
odbc-postgresql (1:8.01.0200-2)
postgresql-8.1 (8.1.9-ubuntu0.6.10)
libodbc++ 0.2.3+0.2.4pre3-1
iodbc 3.52.4-3

My problem is this. Suppose the following SQL code was executed:

<pre>
-- Create test source
CREATE TABLE "odbc_test_table" (
  "testid" serial NOT NULL PRIMARY KEY,
  "start" timestamp NOT NULL DEFAULT localtimestamp,
  "end" timestamp
);

-- Populate it with some data
INSERT INTO "odbc_test_table"("end") VALUES('2007/07/23 00:00:00');
INSERT INTO "odbc_test_table"("end") VALUES(NULL);

-- Create return type
CREATE TYPE odbc_test_fun_t AS (
 "testid" int,
 "ttl"    interval
);

-- Create test function
CREATE FUNCTION odbc_test_fun () RETURNS odbc_test_fun_t AS
'SELECT "testid", age("end",localtimestamp) AS ttl FROM odbc_test_table;'
LANGUAGE 'sql';
</pre>

Let's also use this code for the test case:
<pre>
#include <odbc++/drivermanager.h>
#include <odbc++/connection.h>
#include <odbc++/preparedstatement.h>
#include <odbc++/resultset.h>
#include <odbc++/resultsetmetadata.h>
#include <iostream>
#include <string>

using namespace odbc;
using namespace std;

void call_ps(PreparedStatement* s) {
 ResultSet* r = s->executeQuery();
 ResultSetMetaData *rm = r->getMetaData();

 switch (rm->getColumnCount()) {
  case 2:
    while(r->next()) {
     string ttl = r->getString(2);
     if (r->wasNull())
    ttl = "<NULL>";
     cout << "Two Cols: ID=" << r->getInt(1) << ", TTL=" <<
    ttl << endl;     
    }
    break;
  case 1:
    while(r->next()) {
     cout << "One col: " << r->getString(1) << endl;
    }
    break;
  default:
    cout << "Uh...." << endl;
 }
}

int main() {
 //
 Connection *c = DriverManager::getConnection("DSN=msafe_psql");
 PreparedStatement *select = c->prepareStatement(
    "select * from odbc_test_fun()");
 PreparedStatement *call = c->prepareStatement(
    "{call odbc_test_fun()}");

 call_ps(select);
 call_ps(call);

 DriverManager::shutdown();

 return 0;
}
</pre>

If I call "SELECT * FROM odbc_test_fun()" from ODBC, there is no problem, and it returns a result set with three columns as one would expect. However, by preparing a statement "{exec odbc_test_fun()}", then executing it, I instead get a single column with all of the fields joined in CSV format like '(1,"1 mon 1 day 07:01:46.744928")'). I am curious if anybody else has experienced this sort of problem with the ODBC driver, or if perhaps ODBC++ is broken or my understanding of the {call} escape sequence is misguided. Thanks in advance!

-- P. Kilgore