Home > mailing lists

Thread: Pb with linked tables on PG8

Pb with linked tables on PG8

From

"Ets ROLLAND"

Date:

09 June 2005, 11:20:19

Hello !

I have an Access database using linked tables on PG 8.0.3 (on Windows 2003 server).
I use pgODBC 8.00.01.01.

Some tables appear like deleted !
In Access I see :

Categ : Table
vt_cat vt_libcat

#Supprimé #Supprimé

(Supprmé means deleted in French)

I try to delete the link and recreate it, same problem !

I try a VACUUM FULL ANALYSE on the database, same problem !

How can I solve this problem ? Why ?

Many thanks for all your explainations.

Regards.

Luc

Re: Pb with linked tables on PG8

From

"Zlatko Matic"

Date:

09 June 2005, 16:52:29

Hello.

I have experoenced the same problem. It seems to be common problem with Access connectiong to ODBC data source. It seems that Access has some problems to determine primary key...

You should not use textual fields as primary key. Insted, add some bigserial (integer autoincrement field) into your tables and make such field primary key. Then relink your tables. This will solve your problem. Also, it is good to add timestamp field into tables.

Also, be aware that your tables names should not be too long, because if they are long you will have problems with relinking. Access would not relink correctly (preassuming that you will use DSN-less and relinking on each startup).

Bye.

Zlatko

For your information, this is explanation from MSDN:

ACC: "#Deleted" Errors with Linked ODBC Tables

View products that this article applies to.

Article ID	:	128809
Last Review	:	May 6, 2003
Revision	:	1.0

This article was previously published under Q128809

SYMPTOMS

When you retrieve, insert, or update records in a linked ODBC table, each field in a record contains the "#Deleted" error message. When you retrieve, insert, or update records using code, you receive the error message "Record is deleted."

Back to the top

CAUSE

The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on key values (in the case of a linked ODBC table, the unique index of a table).

After Microsoft Access performs an insert or an update of a linked ODBC table, it uses a Where criteria to select the record again to verify the insert or update. The Where criteria is based on the unique index. Although numerous factors can cause the select not to return any records, most often the cause is that the key value Microsoft Access has cached is not the same as the actual key value on the ODBC table. Other possible causes are as follows:

	Having an update or insert trigger on the table, modifying the key value.
	Basing the unique index on a float value.
	Using a fixed-length text field that may be padded on the server with the correct amount of spaces.
	Having a linked ODBC table containing Null values in any of the fields making up the unique index.

These factors do not directly cause the "#Deleted" error message. Instead, they cause Microsoft Access to go to the next step in maintaining the key values, which is to select the record again, this time with the criteria based on all the other fields in the record. If this step returns more than one record, Microsoft Access returns the "#Deleted" message because it does not have a reliable key value to work with. If you close and re-open the table or choose Show All Records from the Records menu, the "#Deleted" errors are removed.

Microsoft Access uses a similar process to retrieve records from an linked ODBC table. First, it retrieves the key values and then the rest of the fields that match the key values. If Microsoft Access is not able to find that value again when it tries to find the rest of the record, it assumes that the record is deleted.

Back to the top

RESOLUTION

The following are some strategies that you can use to avoid this behavior:

	Avoid entering records that are exactly the same except for the unique index.
	Avoid an update that triggers updates of both the unique index and another field.
	Do not use a Float field as a unique index or as part of a unique index because of the inherent rounding problems of this data type.
	Do all the updates and inserts by using SQL pass-through queries so that you know exactly what is sent to the ODBC data source.
	Retrieve records with an SQL pass-through query. An SQL pass-through query is not updateable, and therefore does not cause "#Delete" errors.
	Avoid storing Null values within any field making up the unique index of your linked ODBC table.

Back to the top

MORE INFORMATION

Note: In Microsoft Access 2.0, linked tables were called attached tables.

Steps to Reproduce Behavior

1.	Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access 2.0)
2.	Use the Upsizing Tools to upsize the Shippers table. NOTE: This table contains an AutoNumber field (or Counter field in Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools into a trigger that emulates a counter.
3.	Open the linked Shippers table and enter a new record. Make sure that the record you enter has the same data in the Company Name field as the previous record.
4.	Press TAB to move to a new record. Note that the "#Deleted" error fills the record you entered.
5.	Close and re-open the table. Note that the record is correct.

----- Original Message -----
From: Ets ROLLAND
To: pgsql-general@postgresql.org
Sent: Thursday, June 09, 2005 1:19 PM
Subject: [GENERAL] Pb with linked tables on PG8

Hello !

I have an Access database using linked tables on PG 8.0.3 (on Windows 2003 server).
I use pgODBC 8.00.01.01.
Some tables appear like deleted !
In Access I see :
Categ : Table
vt_cat            vt_libcat
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
(Supprmé means deleted in French)
I try to delete the link and recreate it, same problem !
I try a VACUUM FULL ANALYSE on the database, same problem !
How can I solve this problem ? Why ?
Many thanks for all your explainations.
Regards.

Luc

Attachment

Re: SPAM Re: Pb with linked tables on PG8

From

"Ets ROLLAND"

Date:

09 June 2005, 20:42:12

Many thanks for this very usefull information.

Luc

----- Original Message -----
From: Zlatko Matic
To: Ets ROLLAND ; pgsql-general@postgresql.org
Sent: Thursday, June 09, 2005 6:52 PM
Subject: **SPAM** Re: [GENERAL] Pb with linked tables on PG8

Hello.

I have experoenced the same problem. It seems to be common problem with Access connectiong to ODBC data source. It seems that Access has some problems to determine primary key...
You should not use textual fields as primary key. Insted, add some bigserial (integer autoincrement field) into your tables and make such field primary key. Then relink your tables. This will solve your problem. Also, it is good to add timestamp field into tables.
Also, be aware that your tables names should not be too long, because if they are long you will have problems with relinking. Access would not relink correctly (preassuming that you will use DSN-less and relinking on each startup).

Bye.

Zlatko

For your information, this is explanation from MSDN:
"
ACC: "#Deleted" Errors with Linked ODBC Tables
View products that this article applies to.
Article ID : 128809
Last Review : May 6, 2003
Revision : 1.0
This article was previously published under Q128809
On this page
SYMPTOMS
CAUSE
RESOLUTION
MORE INFORMATION
Steps to Reproduce Behavior
APPLIES TO
SYMPTOMS
When you retrieve, insert, or update records in a linked ODBC table, each field in a record contains the "#Deleted" error message. When you retrieve, insert, or update records using code, you receive the error message "Record is deleted."
Back to the top
CAUSE
The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on key values (in the case of a linked ODBC table, the unique index of a table).

After Microsoft Access performs an insert or an update of a linked ODBC table, it uses a Where criteria to select the record again to verify the insert or update. The Where criteria is based on the unique index. Although numerous factors can cause the select not to return any records, most often the cause is that the key value Microsoft Access has cached is not the same as the actual key value on the ODBC table. Other possible causes are as follows:
Having an update or insert trigger on the table, modifying the key value.
Basing the unique index on a float value.
Using a fixed-length text field that may be padded on the server with the correct amount of spaces.
Having a linked ODBC table containing Null values in any of the fields making up the unique index.
These factors do not directly cause the "#Deleted" error message. Instead, they cause Microsoft Access to go to the next step in maintaining the key values, which is to select the record again, this time with the criteria based on all the other fields in the record. If this step returns more than one record, Microsoft Access returns the "#Deleted" message because it does not have a reliable key value to work with. If you close and re-open the table or choose Show All Records from the Records menu, the "#Deleted" errors are removed.

Microsoft Access uses a similar process to retrieve records from an linked ODBC table. First, it retrieves the key values and then the rest of the fields that match the key values. If Microsoft Access is not able to find that value again when it tries to find the rest of the record, it assumes that the record is deleted.
Back to the top
RESOLUTION
The following are some strategies that you can use to avoid this behavior:
Avoid entering records that are exactly the same except for the unique index.
Avoid an update that triggers updates of both the unique index and another field.
Do not use a Float field as a unique index or as part of a unique index because of the inherent rounding problems of this data type.
Do all the updates and inserts by using SQL pass-through queries so that you know exactly what is sent to the ODBC data source.
Retrieve records with an SQL pass-through query. An SQL pass-through query is not updateable, and therefore does not cause "#Delete" errors.
Avoid storing Null values within any field making up the unique index of your linked ODBC table.
Back to the top
MORE INFORMATION
Note: In Microsoft Access 2.0, linked tables were called attached tables.
Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access 2.0)
2. Use the Upsizing Tools to upsize the Shippers table.

NOTE: This table contains an AutoNumber field (or Counter field in Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools into a trigger that emulates a counter.
3. Open the linked Shippers table and enter a new record. Make sure that the record you enter has the same data in the Company Name field as the previous record.
4. Press TAB to move to a new record. Note that the "#Deleted" error fills the record you entered.
5. Close and re-open the table. Note that the record is correct.
"
----- Original Message -----
From: Ets ROLLAND
To: pgsql-general@postgresql.org
Sent: Thursday, June 09, 2005 1:19 PM
Subject: [GENERAL] Pb with linked tables on PG8

Hello !

I have an Access database using linked tables on PG 8.0.3 (on Windows 2003 server).
I use pgODBC 8.00.01.01.
Some tables appear like deleted !
In Access I see :
Categ : Table
vt_cat            vt_libcat
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
#Supprimé    #Supprimé
(Supprmé means deleted in French)
I try to delete the link and recreate it, same problem !
I try a VACUUM FULL ANALYSE on the database, same problem !
How can I solve this problem ? Why ?
Many thanks for all your explainations.
Regards.

Luc

Attachment

how to return a result set from a stored procedure

From

Hugo

Date:

11 June 2005, 12:34:10

Hi everybody

I am trying to write a stored procedure that returns a result set but it is not working
this is the function:
///
CREATE OR REPLACE FUNCTION remisiones.fn_get_total_remitidoxprovision1("numeric")
RETURNS SETOF record AS
$BODY$
begin
  select rm.provision as provision,
           drm.producto as producto,
           sum(drm.cantidad) as cantidad
FROM remisiones.remisiones rm, remisiones.detalles_remision drm
WHERE rm.remision = drm.remision and rm.provision = $1
GROUP BY rm.provision, drm.producto
ORDER BY rm.provision, drm.producto;
end;$BODY$

///
If I call this function from the interactive sql of pgadminIII I get this result:
   select * from fn_gert_total_remitidosxprovision(1)
---------------------------------------------------------------------------
   row    refcursor
   1       <unnamed porta1>

is there a way to display the value of the rows returned, i need it becouse I need to use it in a Datawindow definition in an Powerbuilder app.

thanks in advance

Hugo

Re: how to return a result set from a stored procedure

From

Petar Jovanovic

Date:

11 June 2005, 13:20:21

Hugo wrote:

> Hi everybody
>
> I am trying to write a stored procedure that returns a result set but
> it is not working
> this is the function:
> ///
> CREATE OR REPLACE FUNCTION
> remisiones.fn_get_total_remitidoxprovision1("numeric")
> RETURNS SETOF record AS
> $BODY$
> begin
>   select rm.provision as provision,
>            drm.producto as producto,
>            sum(drm.cantidad) as cantidad
>  FROM remisiones.remisiones rm, remisiones.detalles_remision drm
>  WHERE rm.remision = drm.remision and rm.provision = $1
> GROUP BY rm.provision, drm.producto
> ORDER BY rm.provision, drm.producto;
> end;$BODY$
>
> ///
> If  I call this function from the interactive sql of pgadminIII I get
> this result:
>    select * from fn_gert_total_remitidosxprovision(1)
> ---------------------------------------------------------------------------
>    row    refcursor
>    1       <unnamed porta1>
>
>
> is there a way to display the value of the rows returned, i need it
> becouse I need to use it in a Datawindow definition in an Powerbuilder
> app.
>
> thanks in advance
>
> Hugo
>
>
In PL/pgSQL you have to specify structure or records that function will
return. For example :
SELECT * FROM fn_get_total_remitidoxprovision1(1) AS (provision integer,
producto integer,cantidad float);

Re: how to return a result set from a stored procedure

From

Hugo

Date:

11 June 2005, 17:05:01

hi Petar, thanks for your answer, I tried it and got this message:

ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "fn_get_total_remitidoxprovisio

n1" line 5 at SQL statement

could you advice me on this ??

thanks

Hugo

On 11/06/05, Petar Jovanovic <jpetar@pinsoft.com> wrote:

Hugo wrote:

> Hi everybody
>
> I am trying to write a stored procedure that returns a result set but
> it is not working
> this is the function:
> ///
> CREATE OR REPLACE FUNCTION
> remisiones.fn_get_total_remitidoxprovision1("numeric")
> RETURNS SETOF record AS
> $BODY$
> begin
>   select rm.provision as provision,
>            drm.producto as producto,
>            sum(drm.cantidad) as cantidad
>  FROM remisiones.remisiones rm, remisiones.detalles_remision drm
>  WHERE rm.remision = drm.remision and rm.provision = $1
> GROUP BY rm.provision, drm.producto
> ORDER BY rm.provision, drm.producto;
> end;$BODY$
>
> ///
> If  I call this function from the interactive sql of pgadminIII I get
> this result:
>    select * from fn_gert_total_remitidosxprovision(1)
> ---------------------------------------------------------------------------
>    row    refcursor
>    1       <unnamed porta1>
>
>
> is there a way to display the value of the rows returned, i need it
> becouse I need to use it in a Datawindow definition in an Powerbuilder
> app.
>
> thanks in advance
>
> Hugo
>
>
In PL/pgSQL you have to specify structure or records that function will
return. For example :
SELECT * FROM fn_get_total_remitidoxprovision1(1) AS (provision integer,
producto integer,cantidad float);

Re: Pb with linked tables on PG8

From

"jeremy `"

Date:

12 June 2005, 23:24:04

i had the exact same thing using the same scenario but with pg 8.0.3
installed on the same win XP pro machine.
The strange thing is when I closed the table and opened it again, all the
fields containing #deleted then showed up with the values I actually input.
So i'm thinking theres some bug in refreshing the table each time you enter
a new record.

>From: "Ets ROLLAND" <ets@rolland-fr.com>
>Reply-To: "Ets ROLLAND" <ets@rolland-fr.com>
>To: <pgsql-general@postgresql.org>
>Subject: [GENERAL] Pb with linked tables on PG8
>Date: Thu, 9 Jun 2005 13:19:57 +0200
>
>Hello !
>
>I have an Access database using linked tables on PG 8.0.3 (on Windows 2003
>server).
>I use pgODBC 8.00.01.01.
>Some tables appear like deleted !
>In Access I see :
>Categ : Table
>vt_cat            vt_libcat
>#Supprim�    #Supprim�
>#Supprim�    #Supprim�
>#Supprim�    #Supprim�
>#Supprim�    #Supprim�
>#Supprim�    #Supprim�
>#Supprim�    #Supprim�
>#Supprim�    #Supprim�
>#Supprim�    #Supprim�
>(Supprm� means deleted in French)
>I try to delete the link and recreate it, same problem !
>I try a VACUUM FULL ANALYSE on the database, same problem !
>How can I solve this problem ? Why ?
>Many thanks for all your explainations.
>Regards.
>
>Luc

_________________________________________________________________
Shop �til you drop at XtraMSN Shopping http://shopping.xtramsn.co.nz/home/

Re: Pb with linked tables on PG8

From

Zlatko Matić

Date:

13 June 2005, 22:34:04

Hi.
I'm forwarding you what I answered to Rolland about it. This "#deleted"
phenomena is something often, but can be easily solved.
It seems to be common problem with Access connectiong to ODBC data source.
Access has some problems to determine primary key...
You should not use textual fields as primary key. Insted, add some bigserial
(integer autoincrement field) into your tables and make such field primary
key. Then relink your tables. This will solve your problem. Also, it is good
to add timestamp field into tables.
Also, be aware that your tables names should not be too long, because if
they are long you will have problems with relinking. Access would not relink
correctly (preassuming that you will use DSN-less and relinking on each
startup).

Bye.

Zlatko

For your information, this is explanation from MSDN:
"
ACC: "#Deleted" Errors with Linked ODBC Tables
View products that this article applies to.
      Article ID : 128809
      Last Review : May 6, 2003
      Revision : 1.0

This article was previously published under Q128809
On this page
     SYMPTOMS
     CAUSE
     RESOLUTION
     MORE INFORMATION
         Steps to Reproduce Behavior
     APPLIES TO

SYMPTOMS
When you retrieve, insert, or update records in a linked ODBC table, each
field in a record contains the "#Deleted" error message. When you retrieve,
insert, or update records using code, you receive the error message "Record
is deleted."
     Back to the top

CAUSE
The Microsoft Jet database engine is designed around a keyset-driven model.
This means that data is retrieved, inserted, and updated based on key values
(in the case of a linked ODBC table, the unique index of a table).

After Microsoft Access performs an insert or an update of a linked ODBC
table, it uses a Where criteria to select the record again to verify the
insert or update. The Where criteria is based on the unique index. Although
numerous factors can cause the select not to return any records, most often
the cause is that the key value Microsoft Access has cached is not the same
as the actual key value on the ODBC table. Other possible causes are as
follows:
      . Having an update or insert trigger on the table, modifying the key
value.
      . Basing the unique index on a float value.
      . Using a fixed-length text field that may be padded on the server
with the correct amount of spaces.
      . Having a linked ODBC table containing Null values in any of the
fields making up the unique index.
These factors do not directly cause the "#Deleted" error message. Instead,
they cause Microsoft Access to go to the next step in maintaining the key
values, which is to select the record again, this time with the criteria
based on all the other fields in the record. If this step returns more than
one record, Microsoft Access returns the "#Deleted" message because it does
not have a reliable key value to work with. If you close and re-open the
table or choose Show All Records from the Records menu, the "#Deleted"
errors are removed.

Microsoft Access uses a similar process to retrieve records from an linked
ODBC table. First, it retrieves the key values and then the rest of the
fields that match the key values. If Microsoft Access is not able to find
that value again when it tries to find the rest of the record, it assumes
that the record is deleted.
     Back to the top

RESOLUTION
The following are some strategies that you can use to avoid this behavior:
      . Avoid entering records that are exactly the same except for the
unique index.
      . Avoid an update that triggers updates of both the unique index and
another field.
      . Do not use a Float field as a unique index or as part of a unique
index because of the inherent rounding problems of this data type.
      . Do all the updates and inserts by using SQL pass-through queries so
that you know exactly what is sent to the ODBC data source.
      . Retrieve records with an SQL pass-through query. An SQL pass-through
query is not updateable, and therefore does not cause "#Delete" errors.
      . Avoid storing Null values within any field making up the unique
index of your linked ODBC table.

     Back to the top

MORE INFORMATION
Note: In Microsoft Access 2.0, linked tables were called attached tables.
Steps to Reproduce Behavior

      1. Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft
Access 2.0)
      2. Use the Upsizing Tools to upsize the Shippers table.

      NOTE: This table contains an AutoNumber field (or Counter field in
Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools
into a trigger that emulates a counter.
      3. Open the linked Shippers table and enter a new record. Make sure
that the record you enter has the same data in the Company Name field as the
previous record.
      4. Press TAB to move to a new record. Note that the "#Deleted" error
fills the record you entered.
      5. Close and re-open the table. Note that the record is correct.
"
----- Original Message -----
From: "jeremy `" <norman1nz@hotmail.com>
To: <ets@rolland-fr.com>; <pgsql-general@postgresql.org>
Sent: Monday, June 13, 2005 1:23 AM
Subject: Re: [GENERAL] Pb with linked tables on PG8


>i had the exact same thing using the same scenario but with pg 8.0.3
>installed on the same win XP pro machine.
> The strange thing is when I closed the table and opened it again, all the
> fields containing #deleted then showed up with the values I actually
> input.
> So i'm thinking theres some bug in refreshing the table each time you
> enter a new record.
>
>>From: "Ets ROLLAND" <ets@rolland-fr.com>
>>Reply-To: "Ets ROLLAND" <ets@rolland-fr.com>
>>To: <pgsql-general@postgresql.org>
>>Subject: [GENERAL] Pb with linked tables on PG8
>>Date: Thu, 9 Jun 2005 13:19:57 +0200
>>
>>Hello !
>>
>>I have an Access database using linked tables on PG 8.0.3 (on Windows 2003
>>server).
>>I use pgODBC 8.00.01.01.
>>Some tables appear like deleted !
>>In Access I see :
>>Categ : Table
>>vt_cat            vt_libcat
>>#Supprimé    #Supprimé
>>#Supprimé    #Supprimé
>>#Supprimé    #Supprimé
>>#Supprimé    #Supprimé
>>#Supprimé    #Supprimé
>>#Supprimé    #Supprimé
>>#Supprimé    #Supprimé
>>#Supprimé    #Supprimé
>>(Supprmé means deleted in French)
>>I try to delete the link and recreate it, same problem !
>>I try a VACUUM FULL ANALYSE on the database, same problem !
>>How can I solve this problem ? Why ?
>>Many thanks for all your explainations.
>>Regards.
>>
>>Luc
>
> _________________________________________________________________
> Shop til you drop at XtraMSN Shopping http://shopping.xtramsn.co.nz/home/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>