Re: [SQL] Q:Postgres 7.0 & Access'97?
Hello Emils, Once, Tuesday, June 13, 2000, 8:42:05 PM, you wrote: EK> I am using Postgres 7.0 on SuSE Linux 6.2 and Postdrv.exe EK> 6.50. (as downloaded from /latest/ dir of PG FTP site EK> yesterday). EK> Now, I have experienced 4 issues immediately with Postgres via EK> ODBC from Access and I was wondering whether you could have EK> any idea what could possibly be done about them: EK> 1. Every time a query runs from Access, I get an error on the server EK> coneole: EK> pq_recvbuf: EOF the client closed the connection unexpectedly EK> The query seems to run fine though. I am getting the same messages, looks like cause somewhere in Access because I never got such messages when using other programs working with postgresql via odbc. And this messages appearing not after any queries. Since it doesn't disturb to work I didn't look into this. EK> 2. The tables on the SQL server were exported from Access tables, EK> using Access' File | Export -> ODBC connection. We used Pgupt for this: http://dspace.dial.pipex.com/boylesa/pgupt/pgupt.shtml and some hand works. Also look at the http://www.sevainc.com/Access/index.html EK> Now I can only access them from psql monitor if I enclose both the EK> table and field names in double quotes, like "Field1". The EK> tables/fields do have alphanumeric chars in them only (A-z,0-9). If I EK> try to access table "Test" like: EK> \d Test, I get: can't find relation 'test'. EK> (Note the caps both in table name & error msg) EK> \d "Test" works. When you create table with name in double quote (create table "Table" ...) then you must to use the same characters case (Table) in queries. It's written somewhere in docs or in FAQs. EK> 3. The connection seems to be QUITE slow (approximately 1-2 EK> seconds to show a form in Access), considering that the total of 19 EK> tables in the dbase contain a maximum of a couple of thousand of EK> rows (I think even less). The server is a HP Netserver PIII650 and EK> does not at the moment run anything else. The client computer is EK> not very up-to-date, yet the same Access db with local tables runs EK> practically instantly. I think 1-2 seconds is not a demonstrative time. Try to run a complex queries on a large tables in order to estimate speed. EK> 4. This is almost definitely an Access problem, but even if so: are EK> there any workarounds available? EK> A WHERE clause that compares a boolean value with a boolean EK> constant gives error: can't compare bool and int, use explicit cast. EK> Ie. in Access trying to use a SELECT WHERE a=True , yields EK> the above error, considering that a is a boolean field. EK> Same query works from psql monitor. You need to create operator "=" for int4 and bool. I used the following (you need to have a plpgsl language installed in postgresql): drop operator = (bool,int4); drop function MsAccessBool(bool,int4); create function MsAccessBool(bool,int4) returns bool as ' begin if $1 is NULL then return NULL; end if; if $1 is TRUE then if $2 <> 0 then return TRUE; end if; else if $2 = 0 then return TRUE; end if; end if; return FALSE; end; ' language 'plpgsql'; create operator = ( leftarg=bool, rightarg=int4, procedure=MsAccessBool, commutator='=', negator='<>', restrict=eqsel, join=eqjoinsel ); Also uncheck "bool as char" option in odbc driver properties. EK> Thanks a LOT in advance for any comments. EK> Emils, trying to make Access frontend work with Postgres EK> backend. Yury, successfuly maded Access frontend work with Postgres backend :-) -- Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED]
[SQL] attribute error
Hi, Im rather new to postgres so please exuse my ignorance. I have a table that I would like to drop, only problem is it wont let me. I keep getting the error message: Cannot find attribute 5 of relation tablename. Can anyone explain to me what this means and how I can resolve this issue. Fred Lecul IT Department Paspaley Pearling Company P/L Darwin, NT 0800 Australia Ph: 61 8 8982 5535 Fax: 61 8 8982 5501
[SQL] Re: Simple search question
Alex <[EMAIL PROTECTED]> writes: > Hi, > after running a script which performs an insert, a new tuple is > created, and a serial number is generated for it. I want to write the > new tuple data back to the screen, including the new serial number. > My question is, do I have to do a search for the tuple just inserted in > order to get the data back again? I am thinking there must be a faster, > more efficient way. > Thanks, > Alex I had the same problem but was using Java, not PHP (I guess that whatever I can do in JDBC, you can do in PHP ;-)). SELECT last_value FROM ; worked fine to retrieve the last-used serial number. I could have used SELECT i.* FROM i, s WHERE i.serial=s.last_value; to retrieve the tuple, but I didn't need to do this. > This leads to another question. If someone adds another row during this, > what will happen? In JDBC I would turn off auto-commit mode, then commit after the SELECT. This should ensure that the sequence doesn't get incremented between INSERT and SELECT. > > Thanks, > Alex Hope this helps, Peter
[SQL] Outputting the Tables of a database
Hello all, I am trying to out put the tables of a database. I have psql -d mydb -c "\d" This works but I am looking at turning on the html out put which it will not do. If I am able to get just the table titles that would be most ideal.
Re: [SQL] Outputting the Tables of a database
You could possibly use grep and awk to filter the output. See "man grep" and "man awk" for details. "Brian C. Doyle" wrote: > Hello all, > > I am trying to out put the tables of a database. I have > > psql -d mydb -c "\d" > > This works but I am looking at turning on the html out put which it will > not do. If I am able to get just the table titles that would be most ideal.
[SQL] Outputting the Tables of a database
Hello all, What would the query line be to mimic mydb=>\d tablename ? I have been able to run a query to mimic #psql -l and to mimic mydb=>\d thanks for the help Brian
[SQL] inheritance and primary key
Hi, Is primary key similar to 'not null columns with indices'? The reason I am asking because I found that when you inherit table B from table A, the primary key in A won't be inherited to B, but only the columns. I was wondering how do you inherit a primary key or, even, a foreign key? If it's not possible, what are the ways going around it? (not null index or...???) Thanks for your help! - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
[SQL] Some problem with inet type on PostgreSQL-7.0
Hi All, I have some problem with inet type on PostgreSQL-7.0 (FreeBSD 3.4-STABLE) Table "ipaddresses" Attribute | Type | Modifier --+-+ sysname | text| not null index| integer | not null ip_addr | inet| not null Indices: ipaddresses_ip_addr, ipaddresses_pkey (sysname, ip_addr) - PRIMARY KEY View "ipaddresses_view" Attribute | Type | Modifier --+-+-- sysname | text| index| integer | ip_addr | inet| ip_netmask | inet| View definition: SELECT ipaddresses.sysname, ipaddresses."index", ipv4_host(ipaddresses.ip_addr) AS ip_addr, ipv4_netmask(ipaddresses.ip_addr) AS ip_netmask FROM ipaddresses; ipv4_host and ipv4_netmask like original host and netmask but return inet type ( need for ORDER ) CREATE FUNCTION ipv4_host(inet) RETURNS inet AS ' BEGIN RETURN host($1); END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION ipv4_netmask(inet) RETURNS inet AS ' BEGIN RETURN netmask($1); END; ' LANGUAGE 'plpgsql'; Problem select * from ipaddresses where sysname = 'switch01.tor'; sysname| index | ip_addr --+---+-- switch01.tor | 1 | 127.0/8 switch01.tor | 2 | 127.0/8 switch01.tor | 3 | 209.250.155.8/27 (2 rows) but (sysname, ip_addr) - PRIMARY KEY 127.0/8 - it's not correct output ( real 127.0.0.2/8 and 127.0.0.3/8) select * from ipaddresses_view where sysname = 'switch01.tor'; sysname| index |ip_addr| ip_netmask --+---+---+- switch01.tor | 1 | 127.0.0.2 | 255.0.0.0 switch01.tor | 2 | 127.0.0.3 | 255.0.0.0 switch01.tor | 3 | 209.250.155.8 | 255.255.255.224 (2 rows) 127.0.0.2 | 255.0.0.0 and 127.0.0.3 | 255.0.0.0 - it's correct output And of course after pg_dump and restore correct value 127.0.0.2/8 and 127.0.0.3/8 will lose and will have problem with PRIMARY KEY - (sysname, ip_addr). -- Passynkov Vadim, Axxent Inc.
