ODBC call syntax returns 1 column instead of n columns

From: "Phillip Kilgore" <organicnightmare(at)gmail(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: ODBC call syntax returns 1 column instead of n columns
Date: 2007-06-21 22:01:21
Message-ID: f5a52d450706211501u6b9c964bx631b8db27c75c687@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

Browse pgsql-odbc by date

  From Date Subject
Next Message Mark Cave-Ayland 2007-06-22 08:29:44 Re: Proposal for new pgsqlODBC feature - hiding tables inaccessible to the current user
Previous Message Hiroshi Inoue 2007-06-20 14:47:48 Re: Proposal for new pgsqlODBC feature - hiding tables inaccessible to the current user