Thread: 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'.
Re: [INTERFACES] postgresODBC and Symantec dbAnywhere/Visual Cafe, broken?
From
Matthew Hagerty
Date:
>> 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. > David, That worked... I'm not sure how usefull it will be though. Now when I select the "plus" next to the contacts table, it lists the fields like this: 4 INTEGER 15 -blank- 0 0 0 -blank- -blank- -blank- -blank- When I select one of these items, all its properties are ghosted out. Then again, I'm not sure how it would look with say, Oracle or some other database. Do these RAD tools really increase a programmers development time, or just change what he/she spends most of his/her time working on? Visual Cafe and JBuilder seem like a large amount of money to only be able to paint the GUI, or are biased to large RDBMSes that cost mega bucks and only run on UN*X systems that cost just as much. What are you using for RAD Java apps? Matthew
Matthew Hagerty wrote: > David, > > That worked... I'm not sure how usefull it will be though. Now when I > select the "plus" next to the contacts table, it lists the fields like this: > > 4 > INTEGER > 15 > -blank- > 0 > 0 > 0 > -blank- > -blank- > -blank- > -blank- > This may be a bug in the driver. Every once and a while when we throw a new application a the driver we find some imperfections in its implementation. This is our first attempt at dbAnywhere. I am sure we can straighten this out, but we are backed up right now. > When I select one of these items, all its properties are ghosted out. Then > again, I'm not sure how it would look with say, Oracle or some other > database. Do these RAD tools really increase a programmers development > time, or just change what he/she spends most of his/her time working on? > Visual Cafe and JBuilder seem like a large amount of money to only be able > to paint the GUI, or are biased to large RDBMSes that cost mega bucks and > only run on UN*X systems that cost just as much. What are you using for > RAD Java apps? Much of what you are experiencing is one time start-up cost. For you this is a new database and a new IDE. For us, this is the first time with dbAnywhere. These problems will work themselves out; future dbAnywhere users will hopefully benefit from your experience. To your question "What are you using for RAD Java apps?" VB5 :-)