How to stop Npqsql casting parameter values

Question

I am using Npgsql to issue parameterized PostGIS queries on a Postgres database. The problem is that Npgsql casts all parameterized variables using a longhand notation, and PostGIS doesn't understand cast variables in some cases.

For example, suppose the original query starts with this:

ST_GeometryFromText('POLYGON((:x :y,...

Npgsql turns it into this:

ST_GeometryFromText('POLYGON((((1278594)::int4) ((1206979)::int4),...

That doesn't work. It would work if the casts could be left out, like this:

ST_GeometryFromText('POLYGON((1278594 1206979,...

There is apparently a UseCast attribute of a parameter, but it is not settable per NpgsqlParameter.cs.

Do I have any alternative besides dynamically constructing my queries?

try dropping the parentheses around your constants, or using the SQL syntax. — didierc, Dec 09 '12 at 10:47
No problem. Your comment prompted me to improve the question. :-) — Aren Cambre, Dec 09 '12 at 19:07

Mike T · Answer 1 · 2012-12-10T02:55:53.707

0

ST_GeometryFromText uses well-known text, so just use a string variable to represent the WKT. Since WKT has nothing to do with PostgreSQL, it cannot mix with SQL and cannot be parameterised in any way. The string would need to be formatted separately from Npgsql, using standard approaches.

If you are dynamically generating your own geometries, you can use some geometry constructors instead of attempting to piece together WKT strings. If you post the type of geometry your are attempting to dynamically generate, I can pass a few ideas on how to parametrise.

edited Dec 10 '12 at 02:55

answered Dec 09 '12 at 22:19

Mike T

41,085
18
152
203

Thanks. I am using a parameterized query to avoid the pitfalls of dynamic queries. I updated my question to be more clear on that. As a workaround, I am replacing the `:parameters` dynamically with string replacement before executing the query, but I was hoping for a more durable solution. – Aren Cambre Dec 10 '12 at 02:29
updated: you are attempting to parameterise WKT, but that cannot be done since it isn't SQL. The durable solution is with [geometry constructors](http://postgis.refractions.net/docs/reference.html#Geometry_Constructors), and there are many tricks available. – Mike T Dec 10 '12 at 02:58
Parameters should work with WKT. The problem is that Npgsql doesn't allow you to disable casting in the parameters. The Npgsql devs have acknowledged that this is a problem they need to work on. – Aren Cambre Dec 12 '12 at 15:50

Aren Cambre · Accepted Answer · 2012-12-12T15:51:36.740

0

Per Francisco Figueiredo Jr. and Josh Cooley, two key developers of Npgsql, there is currently no good way to force Npgsql not to cast parameterized values.

While it is possible to set the parameter to type DbType.Object, which may avoid casting, that has issues with ambiguous function calls and possibly inducing bugs.

For values that need to go inside WKT, I am using a workaround of string replacement on the query before parameters are processed.

edited Dec 12 '12 at 15:51

answered Dec 10 '12 at 14:59

Aren Cambre

6,540
9
30
36

I would expect parameters for SQL to work *only* with SQL, not with anything else (XML, JSON, etc.) Why is standard string replacement considered a "workaround" for string objects? – Mike T Dec 12 '12 at 22:44
I'm not aware of any rule that says that parameters _have_ to work only with SQL statements and never with ancillary code. – Aren Cambre Dec 13 '12 at 02:34

How to stop Npqsql casting parameter values

2 Answers2