Hi All,
I'm not sure whether I've done something wrong, hit an unsupported version
of SQL Server, or what.
I have a Person table in my SQL Server database that I'm reflecting via
`autoload`
class Person(Base):
__table__ = Table('Person', meta, autoload=True, autoload_with=engine)
The table has LastName and FirstName columns, but I want to search for
substrings in the concatenation of FirstName and LastName. The following
query works direct against the SQLServer
SELECT * FROM dbo.Person WHERE (FirstName + ' ' + LastName) LIKE '%ob Smi%'
Note that trying to use a `CONCAT` function returns the error.
SELECT * FROM dbo.Person WHERE concat(FirstName + ' ' + LastName) LIKE '%ob
Smi%'
*[S00010][195] 'concat' is not a recognized built-in function name.*
It seems that CONCAT was added in SQLServer 2012 and later:
https://msdn.microsoft.com/en-us/library/hh231515.aspx
Trying to replicate this query via SQLAlchemy, the following executes, but
generates invalid SQL including a call to CONCAT with the error below.
matches = Person.query.filter(db.func.concat(Person.FirstName, ' ',
Person.LastName).like(name_substring)).all()
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000]
[FreeTDS][SQL Server]Statement(s) could not be prepared. (8180)
(SQLExecDirectW)') [SQL: 'SELECT Person_1.[LastName] AS
[dbo_Person_LastName], Person_1.[FirstName] AS [dbo_Person_FirstName]\nFROM
dbo.Person AS Person_1 \nWHERE concat(Person_1.[FirstName], ?,
Person_1.[LastName]) LIKE ?'] [parameters: (' ', 'ob Smi')]
I don't see any mention in the documentation of how to indicate the SQL
Server version that queries should be compiled for.
Any suggestions?
Thanks,
Rob
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
