Hi Inayah,
You could wrap the SRID value to ValueNode in order for the SQL
translator to generate a binding for the prepared statement.
It would look something like this if you are using TypeAwareSQLTreeProcessor:
registerValueProcessor(Wkt.class, (parent, child, i) -> {
Node geomFromText = FunctionNode.wrap(child, "GeomFromText");
geomFromText.addChild(new ValueNode(SRID, false, null));
return Optional.of(geomFromText);
}
@Andrus
We could switch all processors to the TypeAwareSQLTreeProcessor, there
shouldn't be any issues.
It's all about some free time to do it :)
On Mon, Jan 25, 2021 at 6:40 PM Inayah Max <[email protected]> wrote:
>
> Hi Andrus,
> thanks for your help. I made some progress by adding an own
> SpatialiteProcessor. But now a new challenge is coming up.
> Spatialite forces to define a SRID within a GeomFromText-Functioncall.
>
>
> The current wrapInFunction-method is implemented like this:
>
> "INSERT INTO tabname (geom) VALUES (GeomFromText (?))";
>
> To put the SRID inside the WRT-String is not working since the SQL engine is
> unaware that we intend using a SQL function.
> Consequently it will handle the corresponding argument just as a plain text
> string.
> Next an example insert query (SRID = 4236) that should work:
>
> "INSERT INTO tabname (geom) VALUES (GeomFromText (?,4236))";
> "INSERT INTO tabname (geom) VALUES (GeomFromText (?, ?),4236)"
>
>
> Is it possible to add a second numeric parameter to the prepared statement?
>
> Kind regards,
> Max
>
>
> Gesendet: Sonntag, 24. Januar 2021 um 08:17 Uhr
> Von: "Andrus Adamchik" <[email protected]>
> An: [email protected]
> Betreff: Re: Spatialite Integration in Cayenne
> Hi Inayah,
>
> As you noticed, spatial features are still new in Cayenne, and we will need
> to fill more than a few gaps. So thanks for your feedback. This will help us
> to prioritize our effort in this area.
>
> > WKT wrapper were added to the MySQLTreeProcessor and
> > PostgreSQLTreeProcessor. Both Processors extend a TypeAwareSQLTreeProcessor
> > and and add the "ST_"-Convert commands as required. In contrast the current
> > SQLiteTreeProcessor extends BaseSQLTreeProcessor.
>
> Good point.
>
> @Nikita: Is there a reason not to use TypeAwareSQLTreeProcessor as a
> superclass in all adapters? IIRC there's a minor performance hit, but looks
> like still worth it.
>
> Andrus
>
> > On Jan 23, 2021, at 11:18 PM, Inayah Max <[email protected]> wrote:
> >
> > Hi all,
> > my issue is related to the recently added support for geospatial types in
> > Cayenne. As I understand it, Mysql and Postgres spatial extensions are
> > already integrated in 4.2M2.
> > This is great but doesn't fit to my tech stack. My lightweighted
> > application has to use a filebased database (spatialite) and can't rely on
> > a server based solution.
> > Spatialite is an SQlite extention that adds spatial functionality to SQLite
> > in the same way like Postgis is doing for Postgres.
> > My current progress is that Cayenne can use connect to a spatialite
> > database via JDBC (using jdbcUrl:
> > jdbc:sqlite:file.db?enable_load_extension=true and
> > SQLSelect.dataRowQuery("SELECT
> > load_extension('mod_spatialite');").select(context);). But all queries fail
> > since the ST-Functions are not implemented yet.
> >
> > When looking at Cayenne spatial implementation, WKT wrapper were added to
> > the MySQLTreeProcessor and PostgreSQLTreeProcessor.
> > Both Processors extend a TypeAwareSQLTreeProcessor and and add the
> > "ST_"-Convert commands as required.
> > In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor.
> > Having no registerColumnProcess, the WKT convert can't take place in the
> > same way like before.
> > Let me know if someone has an idea how to overcome this problem.
> > Thanks.
>
--
Best regards,
Nikita Timofeev
