Byron Nikolaidis wrote:
>
> Stephen Davies wrote:
>
> > Hello again.
> >
> > Does the SQLPrimaryKeys facility work with 6.3.2 and ODBC 6.30.0249?
> >
> > If so, could someone please send me the declaration and a sample of usage.
> >
> > If not, how else can I determine which columns comprise a unique key of a
> > PostgreSQL table via ODBC?
> >
> > (I can successfully access other functions such as SQLColumns. It is just
> > SQLPrimaryKeys that is causing me grief at the moment.)
> >
>
> It should work. I have used it and I tested it with applications such as Visio. Did you get an
> error or something?
>
> Here is a test usage to get the primary keys of the "t1" table:
>
> result = SQLAllocStmt( self, &hstmt1);
>
> result = SQLPrimaryKeys(hstmt1, NULL, 0, NULL, 0, "t1", SQL_NTS);
>
> result = SQLBindCol(hstmt1, 3, SQL_C_CHAR, pktab, sizeof(pktab), &pktab_len);
> result = SQLBindCol(hstmt1, 4, SQL_C_CHAR, pkcol, sizeof(pkcol), &pkcol_len);
> result = SQLBindCol(hstmt1, 5, SQL_C_SHORT, &seq, 0, NULL);
>
> result = SQLFetch(hstmt1);
> while (result != SQL_NO_DATA_FOUND) {
>
> qlog("fetch on stmt1: result = %d, pktab='%s', pkcol='%s', seq=%d\n",
> result, pktab, pkcol, seq);
>
> result = SQLFetch(hstmt1);
> }
>
>
G'day Byron.
I don't get an error, I get "No primary keys for this table" and when I look
in the log I see:
select distinct on attnum a2.attname, a2.attnum from pg_attribute a1,
pg_attribute a2, pg_class c, pg_index i where c.relname='??_pkey' and
c.oid=i.indexrelid and a1.attrelid=c.oid and a2.attrelid=c.oid and
(i.indkey[0]=a1.attnum or ....) order by a2.attnum
The key bit is "where c.relname='??_pkey'
Every time I execute the SQLPrimaryKeys function, the log shows different
rubbish where I have shown ?? (I say rubbish because it is sometimes readable
and sometimes control characters but never means anything real.)
It turns out that the "official" VB5 declaration for SQLPrimaryKeys is wrong.
I changed it to make all of the table attrib strings into ByVal params and the
?? became the correct table name string.
Obviously your code is assuming that any primary key index on table foo will
be named foo_pkey. I obviously missed this on the list.
Cheers and thanks,
Stephen.
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 61-8-82728863
Computing & Network solutions. Fax: 61-8-82741015