hi.
we've been developing client reporting solutions using cognos reportnet
(1.1mr2) working nicely with informix (jenzabar/cars implementations) via
both the informix odbc driver and the native driver (even more stable with
the native driver, of course).
we thought we'd put together a sample database on postgres for
demonstrating to prospects, and we've hit a snag--
we downloaded the odbc (for version psqlodbc-07_03_0200.zip) from
http://www.postgresql.org/ftp/odbc/versions/msi just yesterday.
$ psql -V
psql (PostgreSQL) 7.4.7
with a mere two-table select, the reportnet error is:
QE-DEF-0177 An error occurred while performing operation
'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ERROR: syntax error at or near "." at character 108
once we set "log_min_error_statement = error" in
/etc/postgresql/postgresql.conf and restarted the postmaster, we found the
syntax snag:
STATEMENT: select "Donor_ID_Fields"."fullname", "Donor_Fields"."id" from
"public"."id_view" "Donor_ID_Fields""public"."donor_rec" "Donor_Fields" on
"Donor_ID_Fields"."id" = "Donor_Fields"."id" order by
"Donor_Fields"."id" asc
with indentation, that's:
select
"Donor_ID_Fields"."fullname",
"Donor_Fields"."id"
from
"public"."id_view" "Donor_ID_Fields"
"public"."donor_rec" "Donor_Fields"
on
"Donor_ID_Fields"."id" = "Donor_Fields"."id"
order by
"Donor_Fields"."id" asc
there's no join clause between the tables in the 'from' clause. somewhere
between reportnet and postgres (i.e. odbc) there's a clause that goes
missing.
here's the result with a three-table join:
select
"Gift_Fields"."desg",
"Designation_Lookup"."txt",
sum("Gift_Fields"."amt")
from
"public"."gift_rec" "Gift_Fields" LEFT OUTER JOIN
"public"."desg_table" "Designation_Lookup"
[missing comma/join clause]
"public"."desg_rec" "Designation_Fields"
on "Designation_Lookup"."desg" = "Designation_Fields"."desg"
on "Gift_Fields"."desg" = "Designation_Fields"."desg" and
"Gift_Fields"."camp" = "Designation_Fields"."camp"
group by
"Gift_Fields"."desg", "Designation_Lookup"."txt"
we'll download other versions of the driver tomorrow and see where the
chips fall then. thought you'd like to know, in case you didn't already...
--
Liberty > Security
