Re: [INTERFACES] SQLPrimaryKeys - Mailing list pgsql-general

From Stephen Davies
Subject Re: [INTERFACES] SQLPrimaryKeys
Date
Msg-id 199809220052.KAA15484@mustang.sdc.com.au
Whole thread Raw
In response to Re: [INTERFACES] SQLPrimaryKeys  (Byron Nikolaidis <byronn@insightdist.com>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Anand Surelia
Date:
Subject: Foreign Keys: check_primary_function
Next
From: Stephen Davies
Date:
Subject: Limit on multi-field indexes?