Call Microsoft SQL Server static methods with double colons

[Sebastian Eckweiler]

Hi there,

I'm trying to do with Microsoft SQL Server what this snippet:

https://docs.sqlalchemy.org/en/13/core/custom_types.html#types-sql-value-processing

does for PostGIS.

Unfortunately in MSSQL "ST_GeomFromText" does not exist in the default function namespace, but is a static method of the geography type and is expected to be called as

geography::STGeomFromText

(see: https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stgeomfromtext-geography-data-type?view=sql-server-ver15)

I tried several things in "bind_expression" but didn't manage to get sqlalchemy to compile anything into "geography::STGeomFromText".

When using "func.geography.STGeomFromText" this is ends up in SQL with a plain "." as a separation.

All attempts at injecting the double colons literally failed because the prefix is then wrapped in quotes.

Am I missing something here or do I have to (e.g.) use a custom dialect for that?

Thanks & cheers

Sebastian

[Mike Bayer]

the double colon does not appear to be necessarily a "separator" but various forum posts and whatnot seem to suggest it is some kind of namespace qualifier, it seems to be referred towards heavily in SQL Server 2000 and was somehow changed in 2005, but for this particular extension their current documentation is still referring towards it.     

in the official docs for 2019 we see it called the "scope resolution operator":

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/scope-resolution-operator-transact-sql?view=sql-server-ver15

it's not really clear how this should be implemented in SQLAlchemy, as I'm not really sure "double colon" outright replaces the dot for function namespace qualifiers.    

If SQL Server can tolerate whitespace between the :: and the name, this recipe will work for now:

from sqlalchemy.sql.expression import UnaryExpression

from sqlalchemy.sql import operators

from sqlalchemy import func

def geometry(fn):

    return UnaryExpression(fn, operator=operators.custom_op("geometry::"))

expr = geometry(func.STGeomFromText())

print(expr)

generates:

    geometry:: STGeomFromText()

if not then we need to build some custom @compiles for that right now.

longer term I think we either need a new namespace added to the SQL Server dialect or we need additional options on the func. namespace to simulate this effect.

Thanks & cheers

Sebastian

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/37eb37ce-92e8-4eb4-b880-75aa86dfdbb7%40googlegroups.com.

[Sebastian Eckweiler]

Hi

thanks for the quick reply - this works just fine. The whitespace appears to be okay.

It also helped solving the next issue of calling functions with method-like dot-notation like in:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stastext-geography-data-type?view=sql-server-ver15

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.