Using PgSqlDump backyp geometry columns as

Feneck91 · Post by **Feneck91** » Thu 10 Nov 2016 13:39

Using PgSqlDump generate 'geometry' columns as varchar.

PgSqlConnection conn = <my PgSqlConnection>;
conn.Open();
PgSqlDump pgSqlDump = new PgSqlDump();
pgSqlDump.Connection = conn;
pgSqlDump.IncludeDrop = false;
pgSqlDump.IncludeBlob = true;
pgSqlDump.GenerateHeader = true;
pgSqlDump.IncludeUsers = false;
pgSqlDump.Mode = Devart.Common.DumpMode.All;
pgSqlDump.Backup(_strFilePath);

The bakcup cannot be used to make database structure because the structure is not good, cannot use a varchar in place of geometry.
It generate :

CREATE TABLE public."DMChantier"
(
"ChantierID" bigint,
"Nom" varchar(5) NOT NULL,
"Type" smallint NOT NULL,
"Finalite" smallint NOT NULL,
"Etat" smallint NOT NULL,
"Emprise" varchar NOT NULL,
"BoundingBox" varchar NOT NULL,
"DateValidite" date,
"DateDebut" date,
"DateFin" date,
"NbDC" smallint NOT NULL,
"Commentaires" varchar(1024),
"CheminRacine" varchar(260) NOT NULL,
"FKPackageChantierID" bigint
);

"Emprise" and "BoundingBox" are geometry, not varchar !

Any idea ?
Thanks for your reply.

Pinturiccio · Post by **Pinturiccio** » Mon 14 Nov 2016 13:32

We could not reproduce the issue. Please provide us the following information:
1. dotConnect for PostgreSQL version;
2. PostgreSQL version;
3. DDL script of your table generated via pgAdmin.

Feneck91 · Post by **Feneck91** » Tue 15 Nov 2016 10:10

dotConnect for PostgreSQL version = 7.5.649.0
PostgreSQL version = 9.5
Script CREATE by pgAdmin =

Code: Select all

-- Table: public."DMChantier"

-- DROP TABLE public."DMChantier";

CREATE TABLE public."DMChantier"
(
  "ChantierID" bigint NOT NULL DEFAULT nextval('"DMChantier_ChantierID_seq"'::regclass),
  "Nom" character varying(5) NOT NULL,
  "Type" smallint NOT NULL,
  "Finalite" smallint NOT NULL,
  "Etat" smallint NOT NULL,
  "Emprise" geometry NOT NULL,
  "BoundingBox" geometry NOT NULL,
  "DateValidite" date,
  "DateDebut" date,
  "DateFin" date,
  "NbDC" smallint NOT NULL,
  "Commentaires" character varying(1024),
  "CheminRacine" character varying(260) NOT NULL,
  "FKPackageChantierID" bigint,
  CONSTRAINT "DMChantier_pkey" PRIMARY KEY ("ChantierID"),
  CONSTRAINT "FK_DMChantier_DMChantier_FKPackageChantierID" FOREIGN KEY ("FKPackageChantierID")
      REFERENCES public."DMChantier" ("ChantierID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public."DMChantier"
  OWNER TO dmrole;

-- Index: public."IX_DMChantier_FKPackageChantierID"

-- DROP INDEX public."IX_DMChantier_FKPackageChantierID";

CREATE INDEX "IX_DMChantier_FKPackageChantierID"
  ON public."DMChantier"
  USING btree
  ("FKPackageChantierID");


-- Trigger: dmchantier_create_update_delete_trigger on public."DMChantier"

-- DROP TRIGGER dmchantier_create_update_delete_trigger ON public."DMChantier";

CREATE TRIGGER dmchantier_create_update_delete_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON public."DMChantier"
  FOR EACH ROW
  EXECUTE PROCEDURE public.dmchantier_create_update_delete_notify();

Is it normal or the format should be different?
In normal way, the geometry columns format should be preserved?
May be an option to set into providers?

Pinturiccio · Post by **Pinturiccio** » Wed 16 Nov 2016 13:19

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

Feneck91 · Post by **Feneck91** » Wed 16 Nov 2016 14:11

Great, waiting reply...
Sure, I'm not the first one to try to backup a spatial Postgres database using PgSqlDump, isn't it ?

Pinturiccio · Post by **Pinturiccio** » Thu 17 Nov 2016 11:38

The issue is only related to the PostgGIS datatypes. The standard PostgreSQL spatial types like point, box, circle and others work correctly. We will post here when the issue is fixed.

Feneck91 · Post by **Feneck91** » Wed 23 Nov 2016 13:10

Do you have an idea of delay to get a workaroud or but correction for this feature?
Best regards.

Shalex · Post by **Shalex** » Thu 24 Nov 2016 13:03

The issue is fixed. A timeframe for the new public build is several weeks.

Pinturiccio · Post by **Pinturiccio** » Mon 12 Dec 2016 10:13

New version of dotConnect for PostgreSQL 7.7 is released!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34690

Feneck91 · Post by **Feneck91** » Wed 04 Jan 2017 09:16

Did you test to backup / restore this kind of fields ?
Ok, now, the geometry fields are correctly written like :

Code: Select all

CREATE TABLE public."DMChantier"
(
  "ChantierID" bigint,
  "Nom" varchar(5) NOT NULL,
  "Type" smallint NOT NULL,
  "Finalite" smallint NOT NULL,
  "Etat" smallint NOT NULL,
  "Emprise" geometry NOT NULL,
  "BoundingBox" geometry NOT NULL,
  "DateValidite" date,
  "DateDebut" date,
  "DateFin" date,
  "NbDC" smallint NOT NULL,
  "Commentaires" varchar(1024),
  "CheminRacine" varchar(260) NOT NULL,
  "FKPackageChantierID" bigint
);

But, this Table backup generate a line like :

Code: Select all

INSERT INTO public."DMChantier" VALUES (1, E'NALT4', 0, 4, 1, E'\\001\\003\\000\\000 \\346\\020\\000\\000\\001\\000\\000\\000\\037\\000\\000\\000\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0006@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000(@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000*@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000,@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000.@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0000@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0001@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0001@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0002@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0003@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0006@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0003@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0002@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0001@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0000@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000.@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000.@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000,@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000,@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000*@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000(@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000(@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0006@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0006@', E'\\001\\003\\000\\000\\000\\001\\000\\000\\000\\005\\000\\000\\000\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0008@', '2010-01-01 AD', '2015-01-01 AD', '2018-12-31 AD', 32, E'', E'Z:\\NALT4', NULL);

Try to execute this request with PgSqlDump or with pgAdmin : it will not work :
For pgAdmin :

ERREUR: parse error - invalid geometry
LINE 1: ...public."DMChantier" VALUES (1, E'NALT4', 0, 4, 1, E'\\001\\0...
^
HINT: "\0" <-- parse error at position 2 within geometry
********** Erreur **********

ERREUR: parse error - invalid geometry
État SQL :XX000
Astuce : "\0" <-- parse error at position 2 within geometry
Caractère : 63

For PgSqlDump, an exception is raised :

Filename : lwgeom_pg.c
"\"\\0\" <-- parse error at position 2 within geometry"
"pg_parser_errhint"

error = parse error - invalid geometry

.

It seems that the backup work well for table CREATION, not for the data of the table.
A way to make it work well ?

In postgresql documentation
The geometry are not saved / backup into blob

Pinturiccio · Post by **Pinturiccio** » Thu 05 Jan 2017 15:57

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

Pinturiccio · Post by **Pinturiccio** » Fri 13 Jan 2017 14:30

We have fixed the bug with backing up values of PostGIS datatypes via the PgSqlDump class. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

Pinturiccio · Post by **Pinturiccio** » Mon 16 Jan 2017 16:02

New build of dotConnect for PostgreSQL 7.7.819 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34832

Devart Forums

Using PgSqlDump backyp geometry columns as

Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as

Re: Using PgSqlDump backyp geometry columns as