Re: problem with update data sets from front ends 8.4, Ubuntu 10.04 - Mailing list pgsql-sql

Here is the sql statements. We don't pass update, I guess base or the relevant 
programmes does that. Some transactions that are based on forms seem to 
update, but sometimes they don't, and it would seem that certain constraints 
are not met, causing holes in the database. This didn't happen prior to 
upgrade - it would tell you that you couldn't delete the record, upholding 
constraints.

SELECT "HOMINVOLV"."PROJREF", "HOMNLPOST"."LANGREF", "HOMINVOLV"."HISTORYREC", 
"HOMINVOLV"."RESTRICTEDACCESS", "HOMADDRESS"."ADDRESSSTOP", 
"HOMNLPOST"."SENDNL", "HOMINVOLV"."TITLEREF", "HOMINVOLV"."INITIALS", 
"HOMNLPOST"."PERSNAME", "HOMCONT"."LASTNAME", "HOMCONCERN"."POSTHELD", 
"HOMCONCERN"."CONCERN", "HOMCONCERN"."BRANCHOFCONC", "HOMADDRESS"."ADD1", 
"HOMADDRESS"."ADD2", "HOMADDRESS"."ADD3", "HOMADDRESS"."ADD4", 
"HOMADDRESS"."ADD5", "HOMADDRESS"."ADD6", "HOMNLPOST"."ENVELOPE", 
"HOMINVOLV"."INVREF", "HOMNLPOST"."AFRIGREET" FROM { OJ "public"."HOMCONCERN" 
AS "HOMCONCERN" RIGHT OUTER JOIN "public"."HOMCONT" AS "HOMCONT" RIGHT OUTER 
JOIN "public"."HOMINVOLV" AS "HOMINVOLV" RIGHT OUTER JOIN 
"public"."HOMADDRESS" AS "HOMADDRESS" LEFT OUTER JOIN "public"."HOMNLPOST" AS 
"HOMNLPOST" ON "HOMADDRESS"."ADDREF" = "HOMNLPOST"."ADDREF" ON 
"HOMINVOLV"."INVREF" = "HOMNLPOST"."INVREF" ON "HOMCONT"."CONTREF" = 
"HOMADDRESS"."CONTREF" ON "HOMCONCERN"."CONCREF" = "HOMADDRESS"."CONCREF" } 
WHERE "HOMINVOLV"."PROJREF" = :proj AND "HOMNLPOST"."LANGREF" = :lang AND 
"HOMINVOLV"."HISTORYREC" = '1' AND "HOMINVOLV"."RESTRICTEDACCESS" = '0' AND 
"HOMADDRESS"."ADDRESSSTOP" = '0' AND "HOMNLPOST"."SENDNL" = '1' ORDER BY 
"HOMCONT"."LASTNAME" ASC

This query which would have run before in prior setting is no longer working. 

Error code: 1

pq_driver: [PGRES_FATAL_ERROR]ERROR:  syntax error at or near "{"
LINE 1: ...HOMINVOLV"."INVREF", "HOMNLPOST"."AFRIGREET" FROM { OJ "publ...
             ^(caused by statement 'SELECT "HOMINVOLV"."PROJREF", "HOMNLPOST"."LANGREF", 
 
"HOMINVOLV"."HISTORYREC", "HOMINVOLV"."RESTRICTEDACCESS", 
"HOMADDRESS"."ADDRESSSTOP", "HOMNLPOST"."SENDNL", "HOMINVOLV"."TITLEREF", 
"HOMINVOLV"."INITIALS", "HOMNLPOST"."PERSNAME", "HOMCONT"."LASTNAME", 
"HOMCONCERN"."POSTHELD", "HOMCONCERN"."CONCERN", "HOMCONCERN"."BRANCHOFCONC", 
"HOMADDRESS"."ADD1", "HOMADDRESS"."ADD2", "HOMADDRESS"."ADD3", 
"HOMADDRESS"."ADD4", "HOMADDRESS"."ADD5", "HOMADDRESS"."ADD6", 
"HOMNLPOST"."ENVELOPE", "HOMINVOLV"."INVREF", "HOMNLPOST"."AFRIGREET" FROM { 
OJ "public"."HOMCONCERN" AS "HOMCONCERN" RIGHT OUTER JOIN "public"."HOMCONT" 
AS "HOMCONT" RIGHT OUTER JOIN "public"."HOMINVOLV" AS "HOMINVOLV" RIGHT OUTER 
JOIN "public"."HOMADDRESS" AS "HOMADDRESS" LEFT OUTER JOIN 
"public"."HOMNLPOST" AS "HOMNLPOST" ON "HOMADDRESS"."ADDREF" = 
"HOMNLPOST"."ADDREF" ON "HOMINVOLV"."INVREF" = "HOMNLPOST"."INVREF" ON 
"HOMCONT"."CONTREF" = "HOMADDRESS"."CONTREF" ON "HOMCONCERN"."CONCREF" = 
"HOMADDRESS"."CONCREF" } WHERE "HOMINVOLV"."PROJREF" = '1017' AND 
"HOMNLPOST"."LANGREF" = 'English' AND "HOMINVOLV"."HISTORYREC" = '1' AND 
"HOMINVOLV"."RESTRICTEDACCESS" = '0' AND "HOMADDRESS"."ADDRESSSTOP" = '0' AND 
"HOMNLPOST"."SENDNL" = '1' ORDER BY "HOMCONT"."LASTNAME" ASC')


SELECT "HOMNLEMAIL"."LANGUAGE", "HOMINVOLV"."PROJREF", "HOMNLEMAIL"."SEND", 
"HOMINVOLV"."RESTRICTEDACCESS", "HOMINVOLV"."HISTORYREC", 
"HOMINVOLV"."TITLEREF", "HOMINVOLV"."INITIALS", "HOMCONT"."LASTNAME", 
"HOMCONCERN"."POSTHELD", "HOMCONCERN"."CONCERN", "HOMCONCERN"."BRANCHOFCONC", 
"HOMINVOLV"."INVREF", "HOMEMAIL"."emailadd", "HOMEMAIL"."emailstop", 
"HOMNLEMAIL"."PERSNAMES", "HOMNLEMAIL"."AFRIGREET" FROM "HOMCONT" AS "HOMCONT" 
RIGHT OUTER JOIN "HOMCONCERN" AS "HOMCONCERN" RIGHT OUTER JOIN "HOMINVOLV" AS 
"HOMINVOLV" LEFT OUTER JOIN "HOMNLEMAIL" AS "HOMNLEMAIL" ON 
"HOMINVOLV"."INVREF" = "HOMNLEMAIL"."INVREF" LEFT OUTER JOIN "HOMEMAIL" AS 
"HOMEMAIL" ON "HOMNLEMAIL"."EMAILREF" = "HOMEMAIL"."EMAILREF" ON 
"HOMCONCERN"."CONCREF" = "HOMINVOLV"."CONCREF" ON "HOMCONT"."CONTREF" = 
"HOMINVOLV"."CONTREF" WHERE "HOMNLEMAIL"."LANGUAGE" = 'English' AND 
"HOMINVOLV"."PROJREF" = 1017 AND "HOMNLEMAIL"."SEND" = '1' AND 
"HOMINVOLV"."RESTRICTEDACCESS" = '0' AND "HOMINVOLV"."HISTORYREC" = '1' AND 
"HOMEMAIL"."emailstop" = '0' ORDER BY "HOMCONT"."LASTNAME" ASC

Not potential to edit this data


On Monday 23 May 2011 20:01:16 you wrote:
> On Mon, May 23, 2011 at 9:53 AM, Dean le Roux <projserve@gmail.com> wrote:
> > postgres 8.3 to 8.4  Open office base 3.2 Ubuntu 9.04 to 10.04 (new
> > install)
> > 
> > I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other
> > updates have been effected. I used webmin to restore the databases.
> > 
> > Since migrating to Linux around 2006 we were successful in converting our
> > mdb
> > file to postgresql - only one glitch was a difference in -1 as YES. We
> > successfully used the systems with open office as a front end for the
> > last few
> > years. Until now - after the upgrade I can view data but not update data.
> > 
> > Experience with sql is very limited as I have always used sql query
> > builders
> > over the years. The system has worked well with multiple tables, and in
> > the past there was no problems with sending sql statements to
> > postgresql.
> > 
> > My problem in OOO 3.2 base (other front ends also) is that there is
> > continually a problem with not allowing queries to update data back to
> > postgresql.
> > 
> > I believe something has changed perhaps in sql from 8.3 to 8.4, or I have
> > missed something with restoring the files.
> 
> At minimum, we need to see the update statement that you are attempting to
> execute, preferably with any error message you receive.