Обсуждение: ODBC / MS-Access... linking a ms-access DB to a view...
After spending some hours talking with the kind folks in #postgresql, I was unable to find someone who was able to pointme in the right direction towards solving a data access problem between Postgresql, MS-Access 2000, and the PostgreSQL30ODBC driver. Here's my setup: - Win2k Professional - MS-Access 2000 - Postgresql-7.4.2 running on Gentoo Linux After a considerable amount of netsleuthing, I finally able to find someone who had devised a way to implement Row LevelSecurity with Postgres. (As seen on: http://www.varlena.com/varlena/GeneralBits/77.php * scroll down to the "Proofof Concept" section...) The basic idea is to create an additional userid field in your source table, then create a view of the source table whichexcludes the userid and apply rules for select, update, view and insert. Here's a describe of a test view (which can be linked without problem in MSAccess), and the view that I created for my table(just in the testing phases now) http://rafb.net/paste/results/gmYOkn43.html This all seems to work without any problem whatsoever when I change the user and perform the select from the psql utility. You can see how I have performed a test of this functionality here: http://rafb.net/paste/results/rQHqmC51.html Now, once I get to MS-Access, here is where stuff breaks: 1. In MS-Access 2000 * select File, Get External Data, Link Tables... 2. Select Files of Type ODBC Databases() 3. Select the postgresql datasource previously defined 4. Enter the database name, server name, port, username (in this case 01BLUESHIELD) & password 5. The database connects fine (which indicates to me that there are no problems with the connection and/or permissions onthe user account) and it shows all of the system schemas and 3 public tables. The FIRST table is public.tblparticipant *> this is the view that I have created which, when selected, should only returnthe records (about 250 records) which are associated with the login (01BLUESHIELD) as per the rules mentioned earlier. When selected, it returns an empty recordset, which makes NO sense, considering this code works fine from psql. The SECOND table is the public.tblparticipants *> selecting from this table = access denied. This makes sense given theprivileges I established on the account. The THIRD table is public.test *> this table (as shown in the link above) is a view of * in tblparticipants. When I selectthis table, all 2406 records return normally, which indicates to me that the ODBC driver doesn't have any problem "handling"views as opposed to tables. Does anyone have some insight as to what's going wrong or how to fix it? I'd be greatly indebted.. maybe could even paypal someone a few bucks if they have some idea! Kind regards, ./Peter T. Bense Peter T. Bense (ptbense@gwm.sc.edu) - 803-777-9476 Database Administrator/Webmaster Prevention Research Center University of South Carolina
Hello Peter, I don't know what your exact problem is, but here is a list of points I would test if I were you: 1) Test with another driver version. The old installer installs version 7.03.02.00, and there is snapshot with version 7.03.02.08. Make sure to use the secondversion, or the driver snapshot that has just been released for PGSQL 8. This is version 8.00.00.02. It works finefor me, with a 7.5.4 database as well. If you use the 7.xx driver, you have three drivers actually: use "Postgresql", and not the Unicode or Legacy drivers. Here is the link: http://gborg.postgresql.org/project/psqlodbc/genpage.php?downloads 2) Test with another MS Office installation. Weird installation? 3) Test with the postgres/pgsql user. Permission problem? 4) Use a pass-through query instead of a linked view. Primary key problem? Regards, Philippe Lang -----Message d'origine----- De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Peter Bense Envoyé : vendredi, 29. octobre 2004 01:09 À : pgsql-interfaces@postgresql.org; pgsql-odbc@postgresql.org Objet : [ODBC] ODBC / MS-Access... linking a ms-access DB to a view... After spending some hours talking with the kind folks in #postgresql, I was unable to find someone who was able to pointme in the right direction towards solving a data access problem between Postgresql, MS-Access 2000, and the PostgreSQL30ODBC driver. Here's my setup: - Win2k Professional - MS-Access 2000 - Postgresql-7.4.2 running on Gentoo Linux After a considerable amount of netsleuthing, I finally able to find someone who had devised a way to implement Row LevelSecurity with Postgres. (As seen on: http://www.varlena.com/varlena/GeneralBits/77.php * scroll down to the "Proofof Concept" section...) The basic idea is to create an additional userid field in your source table, then create a view of the source table whichexcludes the userid and apply rules for select, update, view and insert. Here's a describe of a test view (which can be linked without problem in MSAccess), and the view that I created for my table(just in the testing phases now) http://rafb.net/paste/results/gmYOkn43.html This all seems to work without any problem whatsoever when I change the user and perform the select from the psql utility. You can see how I have performed a test of this functionality here: http://rafb.net/paste/results/rQHqmC51.html Now, once I get to MS-Access, here is where stuff breaks: 1. In MS-Access 2000 * select File, Get External Data, Link Tables... 2. Select Files of Type ODBC Databases() 3. Select the postgresql datasource previously defined 4. Enter the database name,server name, port, username (in this case 01BLUESHIELD) & password 5. The database connects fine (which indicates tome that there are no problems with the connection and/or permissions on the user account) and it shows all of the systemschemas and 3 public tables. The FIRST table is public.tblparticipant *> this is the view that I have created which, when selected, should only returnthe records (about 250 records) which are associated with the login (01BLUESHIELD) as per the rules mentioned earlier. When selected, it returns an empty recordset, which makes NO sense, considering this code works fine from psql. The SECOND table is the public.tblparticipants *> selecting from this table = access denied. This makes sense given theprivileges I established on the account. The THIRD table is public.test *> this table (as shown in the link above) is a view of * in tblparticipants. When I selectthis table, all 2406 records return normally, which indicates to me that the ODBC driver doesn't have any problem "handling"views as opposed to tables. Does anyone have some insight as to what's going wrong or how to fix it? I'd be greatly indebted.. maybe could even paypal someone a few bucks if they have some idea! Kind regards, ./Peter T. Bense Peter T. Bense (ptbense@gwm.sc.edu) - 803-777-9476 Database Administrator/Webmaster Prevention Research Center Universityof South Carolina ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Make sure that logging is turned on for ODBC (i.e. locally: check the "mylog" option in the DSN setting) and on the server, with settings to ensure that SQL statements sent to the backend are logged (it's been a while since I messed with this, so better read the documentation on how to do this). That way, you will see the exact queries that are being sent from the client, and exactly what is being sent to the backend. Most mysterious ODBC problems become clear that way. --- Peter Bense <Ptbense@gwm.sc.edu> wrote: > After spending some hours talking with the kind > folks in #postgresql, I was unable to find someone > who was able to point me in the right direction > towards solving a data access problem between > Postgresql, MS-Access 2000, and the PostgreSQL30 > ODBC driver. > > Here's my setup: > - Win2k Professional > - MS-Access 2000 > - Postgresql-7.4.2 running on Gentoo Linux > > After a considerable amount of netsleuthing, I > finally able to find someone who had devised a way > to implement Row Level Security with Postgres. (As > seen on: > http://www.varlena.com/varlena/GeneralBits/77.php * > scroll down to the "Proof of Concept" section...) > > The basic idea is to create an additional userid > field in your source table, then create a view of > the source table which excludes the userid and apply > rules for select, update, view and insert. > > Here's a describe of a test view (which can be > linked without problem in MSAccess), and the view > that I created for my table (just in the testing > phases now) > http://rafb.net/paste/results/gmYOkn43.html > > This all seems to work without any problem > whatsoever when I change the user and perform the > select from the psql utility. You can see how I > have performed a test of this functionality here: > http://rafb.net/paste/results/rQHqmC51.html > > Now, once I get to MS-Access, here is where stuff > breaks: > > 1. In MS-Access 2000 * select File, Get External > Data, Link Tables... > 2. Select Files of Type ODBC Databases() > 3. Select the postgresql datasource previously > defined > 4. Enter the database name, server name, port, > username (in this case 01BLUESHIELD) & password > 5. The database connects fine (which indicates to me > that there are no problems with the connection > and/or permissions on the user account) and it shows > all of the system schemas and 3 public tables. > > The FIRST table is public.tblparticipant *> this is > the view that I have created which, when selected, > should only return the records (about 250 records) > which are associated with the login (01BLUESHIELD) > as per the rules mentioned earlier. When selected, > it returns an empty recordset, which makes NO sense, > considering this code works fine from psql. > > The SECOND table is the public.tblparticipants *> > selecting from this table = access denied. This > makes sense given the privileges I established on > the account. > > The THIRD table is public.test *> this table (as > shown in the link above) is a view of * in > tblparticipants. When I select this table, all 2406 > records return normally, which indicates to me that > the ODBC driver doesn't have any problem "handling" > views as opposed to tables. > > Does anyone have some insight as to what's going > wrong or how to fix it? > > I'd be greatly indebted.. maybe could even paypal > someone a few bucks if they have some idea! > > > Kind regards, > > ./Peter T. Bense > > Peter T. Bense (ptbense@gwm.sc.edu) - 803-777-9476 > Database Administrator/Webmaster > Prevention Research Center > University of South Carolina > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail
HI all, i'm trying to insert images in a PostgreSQL database from a VB application using ODBC. So, i found there is a way involving an oid datatype column using lo_import/export functions. Can i use this with ODBC, how? Another way i found is with a bytea datatype column but a i need an equivalent of the PQescapeBytea function that comes with libpq. There is such a thing in ODBC? thanx in advance, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com
Hi Guys, I am using psqlodbc 07.03.0200 with postgresql 7.4.6 and I am having problems when making queries with text fields. plsqlodbc is giving the errors below: 29/10/2004 08:24:49 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : PLEITE - COMMENT: MATA410TCPIP] 29/10/2004 09:21:11 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : MCARVALHAES - COMMENT: MATA440TCPIP] 29/10/2004 09:49:49 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : PLEITE - COMMENT: MATA410TCPIP] 29/10/2004 09:50:37 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : LOLIVEIRA - COMMENT: MATA410TCPIP] 29/10/2004 10:10:01 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : MCARVALHAES - COMMENT: MATA010TCPIP] 29/10/2004 10:14:54 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : LOLIVEIRA - COMMENT: MATA010TCPIP] 29/10/2004 10:16:40 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : LOLIVEIRA - COMMENT: MATA410TCPIP] 29/10/2004 10:31:27 : ERROR : 14 - RECEIVED AN UNSUPPORTED TYPE FROM POSTGRES.[ USER NAME : LOLIVEIRA - COMMENT: MATA410TCPIP] All this erros are on queries with text fields... Any tip? I searched a lot but without any concrete answer... I already tryed unixodbc and the problem it´s the same... Any help will be really appreciated as this is a "production server". Regards, Rodrigo Carvalhaes
Hi all, I want to know if there is someone working on the odbc, and what are the plans to improve it if any. regards, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com
Jaime Casanova wrote: > I want to know if there is someone working on the > odbc, and what are the plans to improve it if any. There is currently no one doing any active development on the ODBC driver. A few people are helping out with minimal maintenance, but there will be no major new developments unless someone else steps up. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> Jaime Casanova wrote: >> I want to know if there is someone working on the >> odbc, and what are the plans to improve it if any. > > There is currently no one doing any active development on the ODBC > driver. A few people are helping out with minimal maintenance, but > there will be no major new developments unless someone else steps up. Wow! I must not have been paying attention. Who is managing the project? > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
markw@mohawksoft.com wrote: > Wow! I must not have been paying attention. > Who is managing the project? Mostly Dave Page, although he will probably claim that he isn't. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/