Devart Forums

Posted: **Tue 10 Nov 2020 13:55**

Hi,

I'm trying to populate a table in Postgres containing a Postgis geometry datatype, essentially this is the code:

using System.Data.Entity.Spatial;

public class Table {
	public DbGeometry ProjectedGeom2D
	{
		get;
		set;
	}
} 

public void WriteToTable() {
	Table table = new Table()
	{
		ProjectedGeom2D = DbGeometry.FromText("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))", 4326)
	};

	var conn = new PgSqlConnection("user id=postgres;Password=postgres;host=localhost;database=database;");
	
	if (conn.State == ConnectionState.Closed)
		conn.Open();

	PgSqlLoader modelLoader = new PgSqlLoader();	
	modelLoader.Connection = conn;	
	modelLoader.TableName = "Models";	
	modelLoader.CreateColumns();	
	modelLoader.Open();
	modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
	modelLoader.Close();
}

This will produce the following exception:

Error = {ERROR: XX000: parse error - invalid geometry}

COPY Models, line 1, column projected_geom2_d: "\001\003\000\000\000\001\000\000\000\005\000\000\000\000\000\000\000\000\000>@\000\000\000\000\000\0..."

"\0" <-- parse error at position 2 within geometry

Do you guys have any idea how to solve this?

Kinds regards Gajus.

Posted: **Wed 11 Nov 2020 19:58**

Please add this line to your code:

Code: Select all

        modelLoader.Mode = PgSqlLoaderMode.Binary;

Refer to https://www.devart.com/dotconnect/postg ... ~Mode.html.

Posted: **Thu 12 Nov 2020 08:50**

Hi Shalex, thanks for your reply.

Your solution seems to get me a step further. Although I think it has some ramifications for other properties I'm trying set.

In short my table contains the following datatypes:

guids
strings
doubles
jsonb
geometry

When setting the I receive the following error on my JSONB column: {ERROR: XX000: unsupported jsonb version number 91}

More completely, my code looks something like this:

Code: Select all

using System.Data.Entity.Spatial;
public class Table
{
	public DbGeometry ProjectedGeom2D { get; set; }

	[Column(TypeName = "jsonb")]
	public string JsonbType { get; set; }

	public Guid Id { get; set; }

	public string StringType { get; set; }

	public double DoubleType { get; set; }
}

public void WriteToTable()
{
	Table table = new Table()
	{
		ProjectedGeom2D = DbGeometry.FromText("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))", 4326),
		DoubleType = 1.0,
		JsonbType = "[{\"type\":\"Cylinder\",\"radius\": 0.346,\"startPoint\": {\"x\": 762893.708,\"y\": 3352038.011,\"z\": 23.902},\"endPoint\": {\"x\": 762893.544,\"y\": 3352038.335,\"z\": 23.901},\"center\": {\"x\": 762893.626,\"y\": 3352038.173,\"z\": 23.901}}]",
		StringType = "a string",
		Id = Guid.NewGuid()                
	};

	var conn = new PgSqlConnection("user id=postgres;Password=postgres;host=localhost;database=database;");

	if (conn.State == ConnectionState.Closed)
		conn.Open();

	PgSqlLoader modelLoader = new PgSqlLoader();
	modelLoader.Mode = PgSqlLoaderMode.Binary;
	modelLoader.Connection = conn;
	modelLoader.TableName = "Models";
	modelLoader.CreateColumns();
	modelLoader.Open();
	
	// In reality i have a substantial for loop
	for (int i = 0; i < 10; i++) {
		modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
		modelLoader.SetValue("double_type", table.DoubleType);
		modelLoader.SetValue("string_type", table.StringType);
		modelLoader.SetValue("id", table.Id);
		modelLoader.SetValue("jsonb_type", table.JsonbType);
		modelLoader.NextRow();
	}
	modelLoader.Close();
}

I am using:
Devart.Data runtime version: v2.0.50727
Devart.Data.PostgreSql runtime version: v2.0.50727
Devart.Data.PostgreSql.Entity.EF6 runtime version: v4.0.30319
Devart.Data.PostgreSql.Entity.Spatials.EF6 runtime version: v4.0.30319

I have tried toggling the modelLoader.Mode in the for loop between text and binary before setting the jsonb and geometry respectively. This doesnt seem to help. Do you have any suggestions on how to get both a geometry and jsonb persisted in the same loop?

Thanks in advance, Gajus

Posted: **Fri 13 Nov 2020 10:32**

We have reproduced the issue with jsonb in the PgSqlLoaderMode.Binary mode:

Code: Select all

modelLoader.Mode = PgSqlLoaderMode.Binary;
...
modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
modelLoader.SetValue("jsonb_type", table.JsonbType);
modelLoader.Close();

->

Devart.Data.PostgreSql.PgSqlException
  Message=unsupported jsonb version number 91

We will investigate it and notify you about the result.

As a workaround, please use PgSqlLoaderMode.Text:

Code: Select all

//modelLoader.Mode = PgSqlLoaderMode.Binary;
modelLoader.Mode = PgSqlLoaderMode.Text;
...
//modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsText());
modelLoader.SetValue("jsonb_type", table.JsonbType);
modelLoader.Close();

Posted: **Fri 04 Dec 2020 18:38**

The bug with loading data into the JSONB column via PgSqlLoader in the PgSqlLoaderMode.Binary mode is fixed in v7.20.1782: viewtopic.php?f=3&t=44279.

Devart Forums

Using PgSqlLoader to populate a Postgis geometry

Using PgSqlLoader to populate a Postgis geometry

Re: Using PgSqlLoader to populate a Postgis geometry

Re: Using PgSqlLoader to populate a Postgis geometry

Re: Using PgSqlLoader to populate a Postgis geometry

Re: Using PgSqlLoader to populate a Postgis geometry