Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-odbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group