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
