Re: [SQL] connecting to postgres server from Access
Hi Joel,
first of all, I looked at the mailing list list at th pg web site and I
did not find a list named "pgsql-interfaces".
In pg_hba.conf I added one line at the bottom with the IP of the client:
host all 134.93.64.47 255.255.255.255 trust
In Access, after installing the pg db as system DSN source, linking to
it and selecting a table and attributes, I get:
"cannot create index for the selected field"
Then the linked table appears in the tables tab in Access. When trying
to open it, I get ("Organisation" is the table to be linked):
"ODBC error:
error while executing the query
ERROR: Organisation: Permission denied (#1)"
I have searched for Windows pg tools but I didn'*t find one.
Please help,
Markus
Joel Burton wrote:
>
> On Wed, 10 Jan 2001, Markus Wagner wrote:
>
> > Hi,
> >
> > I'm running a postgres db server on my linux machine. Someone else would like
> > to build his frontends with MS Access and use the postgres server as backend.
> > He installed the Pg/ODBC-Driver linked to at the pg web site. But when he
> > tries to link in some table he always gets an error message about wrong
> > authentication. I inserted his IP address in the pg_hba.conf file and created
> > a linux user account for him as well as a pg user account within "template1".
> > So he could log in and use "createdb" to create his own db. I tried to set
> > his password within pgaccess, but pgaccess claims about wrong input ("").
> > Leaving the password field empty doesn't help to get the connection. Then I
> > set his password with "ALTER USER" in pgsql. But there still is no connection
> > from access to pg yet.
>
> First of all, questions about interfacing to PostgreSQL are better
> directed to pgsql-interfaces list. You might not get much help posting to
> the sql list.
>
> What kind of authorization are you using in pg_hba.conf? (can you post the
> line from pg_hba.conf for him)
>
> What error message does he get in Access?
>
> Can he connect to the database from another Linux/Unix machine via
> psql? (or, from his Windows machine using a PostgreSQL-compatible command
> interface, like isql)?
>
> There's a FAQ on PostgreSQL + Access at www.scw.org/pgaccess. It may help
> as well.
>
> Good luck,
> --
> Joel Burton <[EMAIL PROTECTED]>
> Director of Information Systems, Support Center of Washington
--
Markus Wagner
Institut fuer Medizinische Statistik und Dokumentation
Johannes-Gutenberg-Universitaet
55101 Mainz
Tel.: 06131-17-5062 E-Mail: [EMAIL PROTECTED]
Re: [SQL] connecting to postgres server from Access
On Thu, 11 Jan 2001, Markus Wagner wrote:
> first of all, I looked at the mailing list list at th pg web site and I
> did not find a list named "pgsql-interfaces".
Hmmm. I see it at
http://www.postgresql.org/users-lounge/index.html
Perhaps you looked elsewhere, or you saw an old mirror? (In any event, you
can get to the pgsql-interfaces archive at
http://www.postgresql.org/mhonarc/pgsql-interfaces/
which is great place to catch up on all the ODBC/Access questions of the
past.)
> In pg_hba.conf I added one line at the bottom with the IP of the client:
>
> host all 134.93.64.47 255.255.255.255 trust
>
> In Access, after installing the pg db as system DSN source, linking to
> it and selecting a table and attributes, I get:
>
> "cannot create index for the selected field"
>
> Then the linked table appears in the tables tab in Access. When trying
> to open it, I get ("Organisation" is the table to be linked):
>
> "ODBC error:
> error while executing the query
> ERROR: Organisation: Permission denied (#1)"
Haven't seen this exact error before.
Are you sure that you found and turned off BOTH read-only options in the
ODBC driver configuration? (I'm just wondering if this "can't
create" error is a strange read-only twist.)
Also, you might try to take the error at face value: try to create an
index on the Org field using psql, and see if you run across any errors.
> I have searched for Windows pg tools but I didn'*t find one.
The nicest Windows tool is pgAdmin, a fantastic tool to administer almost
every aspect of your PG database under Windows. You can find it at
www.greatbridge.org. It can be a *bit* tricky to install, as it has a few
prerequisites, but I think you'd find it worth your time.
Slimmer but less featureful is Zeos Database Explorer, available at
http://www.zeos.dn.ua/eng/index.html
This is basically a GUI version of psql.
Either of these tools will let you try your query out from the Windows
box, and will help us figure out whether this is a PG problem, an
ODBC problem, an Access problem, etc.
My advice?
Get a Windows querying tool *other* than Access to just test the basics of
the ODBC connection, and post your results from that.
Also, you should compare your ODBC setup to the basic setup described in
the Pg+Access FAQ at www.scw.org/pgaccess, and write to the interfaces-
list with your settings, as well as a small pg_dump of your database so
that people can try to recreate your problem.
Good luck!
JB
[SQL] "SELECT" problem on 7.0.3
Hi, I use: agro=# select version(); version --- PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.3 compiled with "--enable-locales --enable-encoding --with-tcl" options on Mandrake LINUX 7.2 with 2.2.17-21mdk kernel. I have BIG problems when I want to do the following "select": agro=# SELECT * FROM conturi WHERE id ~* '^1:1:[0-9]*:$' ORDER BY id; The conditions are the following: Table "conturi" has the following structure: agro=# \d conturi Table "conturi" Attribute |Type | Modifier ---+-+-- id| varchar(32) | not null cheie | varchar(32) | denumire | varchar(60) | not null tip | char(1) | not null functie | char(1) | cc| integer | Index: conturi_id I use the unique index conturi_id on field id. agro=# SELECT * FROM conturi ORDER BY id LIMIT 20; id| cheie | denumire | tip | functie | cc --+---+--+-+-+ 1: | 1 | Capitaluri | F | P | 1:0: | 10| Capital si rezerve | F | P | 1:0:1: | 101 | Capital social | F | P | 1:0:1:1: | 1011 | Capital subscris nevarsat | O | P | 1:0:1:2: | 1012 | Capital subscris varsat | O | P | 1:0:1:7: | 1017 | Capital social reevaluat | O | P | 1:0:4: | 104 | Prime legate de capital | F | P | 1:0:4:1: | 1041 | Prime de emisiune sau de aport | O | P | 1:0:4:2: | 1042 | Prime de fuziune | O | P | 1:0:5: | 105 | Diferente din reevaluare | F | B | 1:0:5:3: | 1053 | Diferente din reevaluari cladiri | O | B | 1:0:5:4: | 1054 | Diferente din reevaluari constructii speciale | O | B | 1:0:5:6: | 1056 | Diferente din reevaluarea amortizarii la cladiri | O | B | 1:0:5:7: | 1057 | Diferente din reevaluarea amortizarii la constructii special | O | B | 1:0:6: | 106 | Rezerve | F | P | 1:0:6:1: | 1061 | Rezerve legale | O | P | 1:0:6:3: | 1063 | Rezerve statutare | O | P | 1:0:6:8: | 1068 | Alte rezerve | O | P | 1:0:7: | 107 | Rezultatul reportat | O | P | 1:0:8: | 108 | Contul intreprinzatorului individual | F | P | (20 rows) Realy I have: agro=# SELECT count(*) FROM conturi; count --- 2690 (1 row) rows in table. (VACUUM ... VACUUM ANALYZE was used before I do this example) The "id" field define a tree structure and, if I want to find all children of "1:0:", I do: agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id; id | cheie | denumire | tip | functie | cc +---+--+-+-+ (0 rows) When I drop the unique index ... agro=# DROP INDEX conturi_id; DROP agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id; id | cheie | denumire | tip | functie | cc +---+--+-+-+ 1:0:1: | 101 | Capital social | F | P | 1:0:4: | 104 | Prime legate de capital | F | P | 1:0:5: | 105 | Diferente din reevaluare | F | B | 1:0:6: | 106 | Rezerve | F | P | 1:0:7: | 107 | Rezultatul reportat | O | P | 1:0:8: | 108 | Contul intreprinzatorului individual | F | P | (6 rows) it works fine ... but if create the index again ... agro=# CREATE UNIQUE INDEX conturi_id ON conturi(id); CREATE agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id; id | cheie | denumire | tip | functie | cc +---+--+-+-+ (0 rows) nothing again. When I use the same succesion of commands on a table with the same structure but wonly 10 rows the "SELECT" works fine with or without index. On 6.5.3 (compiled exactly on same machine and conditions) I have no problems with this "SELECT", it works (the biggest structure have 10.000 rows). Any sugestions ... ??? Thanks in advance and ... sorry for my english!! George Moga, Data SYSTEMS Srl Slobozia, ROMANIA
[SQL] postmaster
Everytime I have usually start the server manually then runnig the deamons.But sometime (no many times) in 7.0.2 after killing the server with its pid ,The server process doesn't get reinitialized Has anybody faced this problem?.. What other processes might would be still be running in background? Please tell me TIA Sharmad
Re: [SQL] Bad (null) varchar() external representation
Justin Clift <[EMAIL PROTECTED]> writes: > "Bad (null) varchar() external representation" This is a known stupidity in 7.0.* and before: length(varchar) doesn't like NULLs: play=> select length(null::varchar); ERROR: Bad (null) varchar() external representation It's fixed for 7.1. If it's really bothering you in 7.0.*, find that error string in src/backend/utils/adt/varchar.c and change the code to return 0 instead of raising an error for NULL input. But, as someone else pointed out, the constraint expressions you are using are redundant anyway, given the declared column length limits. Getting rid of the constraints might be your easiest workaround for now. regards, tom lane
Re: [SQL] Possible bug? WAS :Bad (null) varchar() external representation.
Justin Clift <[EMAIL PROTECTED]> writes: > I haven't seen a mention of a maximum number of constraints of similar > applying to a table. If so, then could someone please point me to it... There is no such limit that I know of. > Yet if I remove BOTH the "staff_details_phone_three" & > "staff_details_managers_notes" constraints it works : Are you absolutely certain that that's how it went? I think the most likely story is just that you were hitting the length(varchar)-barfs- on-NULL bug, and got confused about which combinations you'd tried. regards, tom lane
Re: [SQL] "SELECT" problem on 7.0.3
What locale and encoding are you using? Evidently the LIKE index optimization is failing, but I would not have guessed that a string like 1:0: would cause a problem, especially not if all the entries in the column contain just digits and colons. You can find plenty of material about LIKE+index problems in the list archives. For 7.1, we've gone so far as to disable the optimization altogether in non-ASCII locales (and I'm sure we'll get complaints about that, too :-(). regards, tom lane
Re: [SQL] Possible bug? WAS :Bad (null) varchar() external representation.
Hi Tom, I think you are right. It does seem to barf on NULLS and length(varchar), regardless. Thanks for your assistance. BTW - How do things normally get added to the FAQ? I would like to add something about length(varchar) and NULLS not working in PostgreSQL 7.0.x Regards and best wishes, Justin Clift Database Administrator Tom Lane wrote: > > Justin Clift <[EMAIL PROTECTED]> writes: > > I haven't seen a mention of a maximum number of constraints of similar > > applying to a table. If so, then could someone please point me to it... > > There is no such limit that I know of. > > > Yet if I remove BOTH the "staff_details_phone_three" & > > "staff_details_managers_notes" constraints it works : > > Are you absolutely certain that that's how it went? I think the most > likely story is just that you were hitting the length(varchar)-barfs- > on-NULL bug, and got confused about which combinations you'd tried. > > regards, tom lane
Re: [SQL] Possible bug? WAS :Bad (null) varchar() external representation.
Justin Clift <[EMAIL PROTECTED]> writes: > BTW - How do things normally get added to the FAQ? It's not a FAQ until it's been asked, um, frequently. But you can try to talk Bruce Momjian into adding it, if you like. regards, tom lane
Re: [SQL] Possible bug? WAS :Bad (null) varchar() external representation.
Hi Tom and Stephan, Thanks for your help guys. I'm using varchar constraint definitions now that are "CHECK ((foobar ISNULL) OR (length(foobar) < 17))" The short-circuiting of OR's in 7.0.3 allow this to work without error, thereby avoiding the "Bad (null) varchar() external representation" error that I was getting before due to inserting NULL's in length(varchar) constraint checked fields. I've also extended the varchar columns to be the same size as the length checking I'm doing as Tom suggested, to ensure the constraints do work. Tom has also suggested using COALESCE instead, so I'll check this out too. Regards and best wishes, Justin Clift Database Administrator Tom Lane wrote: > > > The reason I'm using constraints in the table is to allow the database > > to recognise when oversize data is being fed to it and generate an > > error, instead of silently accepting the data and truncating it. > > OK, but have you actually stopped to check whether the combination gives > the results you expect? I believe the data will be coerced to the > destination column type --- including any implicit truncation or padding > --- before the constraint expressions are checked. (I further believe > that that's the right order of events.) > > You might need to make the declared column widths one larger than what > the constraints check for, if you want to raise an error for this. > > > I'm just about to try the same constraints with the ISNULL first, incase > > the OR's in postgreSQL are short-circuited like you mention they might > > be. > > I'd suggest > > CHECK (length(COALESCE(column,'')) < n) > > as a workaround for 7.0.*, if you don't want to hack up the source > code as I mentioned. > > regards, tom lane
[SQL] Re: automated log-messages
On Fri, Jan 12, 2001 at 07:59:26 +0100, Joern Muehlencord wrote: > I am trying to implement an automated log-message system for my > application. For example, if a new user is inserted to my table "auth", > I want to create a log message like: "user [name] added to system". Is > it possible to add this message with triggers or rules?! It can be done using a trigger procedure in Pl/pgSQL, using a "RAISE" statement; see http://postgresql.rmplc.co.uk/devel-corner/docs/postgres/plpgsql-description.htm#AEN6187 HTH, Ray -- "The proper place for a CAPSLOCK key is in a different hemisphere from you." Tom Christiansen in "Interface Zen", http://slashdot.org/article.pl?sid=99/11/30/0954216
[SQL] automated log-messages
Hi, I am trying to implement an automated log-message system for my application. For example, if a new user is inserted to my table "auth", I want to create a log message like: "user [name] added to system". Is it possible to add this message with triggers or rules?! If so, can anyone give some examples? Thanx, Joern -- Linux is like a wigwam - no Windows, no Gates and Apache inside
