Re: [INTERFACES] postgresODBC and Symantec dbAnywhere/Visual Cafe, broken?

Matthew Hagerty wrote:

> I made the index and got a new error. I don't know what to do. Both
> parties (your company and Symantec) are pointing the finger in the other
> direction. I will do everything in my power to provide all the information
> I can. I'm kind of stuck in the middle. So, as a last ditch effort, I
> made three tests and have sent the results to both parties.
>
> I know I sent most of this before, but this will put it all in one place
> for you.
>

...

> Basic test goes like this:
>
> 1. Clear all log files.
> There are 3 files, one made by the postgres driver, one made by
> dbanywhere, and one made by the Windows ODBC administrator (trace tab).
> The files are named psqlodbc.log, dbanywhere.log, and odbc.log. I have
> appended a number 1, 2, or 3 to the end, indicating which test they were
> made from.
>
> 2. Click the "start tracing now" button in the Windows ODBC administrator.
> 3. Start dbAnywhere.
> 4. Start Visual Cafe.
> 5. In Visual Cafe select view->dbNAVIGATOR
> A navigation window pops up that displays items like Windows Explorer.
> 6. Click the "plus" next to "evaluation" (the name of the dbAnywhere server).
> A list of data sources is shown.
> 7. Click the "plus" next to "PostgreSQL"
> A window pops up asking for a username and password.
> 8. Enter a username and password.
> A list of tables for the database "test" indicated in the ODBC set-up of
> the postgresql driver.
> 9. Click the "plus" next to the "contacts" table.
> An error message window pops up.
> 10. Click "OK" in the error window.
> 11. Shutdown Visual Cafe.
> 12. Shutdown dbAnywhere.
> 13. Click the "stop tracing now" button in the Windows ODBC administrator.
> 14. Rename and copy the logs to a temp directory.
>
> Test 1:
> The first test was done with only the contacts and person tables in the
> test database.
>

Same as before...

> Test 2:
> The second test was done after I created an index called contacts_pkey by
> hand on the server with the following command:
>
> CREATE UNIQUE INDEX contacts_pkey ON contacts(id);
>
> I found the dbAnywhere error message *very* interesting for this test.
>

Indeed. Notice the query on dd_fkey. This is cause by dbAnywhere's request for
foreign keys of the table contact. PostgreSQL does not support foreign keys.
However, we implemented a work around (hack by any other name) so that we (at
Insight) could use Visio and any other application that made such ODBC API
requests. Surprisingly, his is the first application outside of our company
that has raised this issue.

Ok, here's what you need to do:

CREATE TABLE dd_fkey (
PRIMARY KEY (relname, attnames),
relname name, -- base table name
attnames varchar(128), -- list of foreign attributes concatenated by '+'
(i.e. 'att1+att2' or just 'att1' )
frelname NOT NULL -- foreign table name
);

-- Example
INSERT INTO dd_fkey (relname, attnames, frelname) VALUES ('contact',
'att1+att2...', 'persons');

This assumes you have a foreign key on contact. If not, just create the empty
table. Lets see how far this gets us.

BTW, in version 6.4 we plan to use Vadim's triggers used to implement foreign keys
as the source for this information.

> Test 3:
> The third test was done with an index with an arbitrary name idx1 with the
> following command:
>
> DROP INDEX contacts_pkey;
> CREATE UNIQUE INDEX idx1 ON contacts(id);
>

It looks like dbAnyware is doing a specific request for primacy key. And in
PostgreSQL primacy keys have the convention of having an index of '*_pkey'.