On 08/15/2016 12:02 AM, [email protected] wrote:
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`
|
classPerson(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.PersonWHERE (FirstName+' '+LastName)LIKE '%ob Smi%'
|
Note that trying to use a `CONCAT` function returns the error.
|
SELECT *FROM dbo.PersonWHERE concat(FirstName+' '+LastName)LIKE '%ob Smi%'
*[S00010][195]'concat'isnota recognized built-infunctionname.**
string concatenation is available as an operator, the + sign will
compile down to the concatenation operator itself (which is usually
'||', I thought this was the case for SQL server also). assuming string
concatenation operators still work on SQL server 2012 I'd forego the
direct use of "concat"
matches = Person.query.filter(Person.FirstName + ' ' ' +
Person.LastName).like(name_substring)).all()
*
|
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
