SEO

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