Switching my project from MySQL to MS SQL Server and have been having problems...
I'm using Slick and when I try to execute a query I'm getting the error
[SQLServerException: Incorrect syntax near '='.]
This is super descriptive except I'm not seeing what slick is trying to execute. So after some debugging I've narrowed it down to the filters that cause this exception.
//my join
var q = for {
license <- License
category <- Category if license.category === category.id
keyContact <- Contact if license.keyContact === keyContact.id
supplierContact <- Contact if license.supplierContact === supplierContact.id
pricing <- Pricing if license.pricing === pricing.id
location <- Location if license.location === location.id
} yield (license,category,keyContact,supplierContact,pricing,location)
}
//THESE are the lines that don't work!
if(!approved.isDefined) q=q.filter(!_._1.approved)
if(!pendings.isDefined) q=q.filter(_._1.approved)
if(!completes.isDefined) q=q.filter(x => (
x._5.price === -1 ||
x._5.serverCost === -1 ||
x._5.depreciation === -1 ||
((x._5.maintCost === -1 || x._5.maintCost.isNull) &&
x._1.maintAgreement === true)
))
if(!incompletes.isDefined) q=q.filter(x => (
x._5.price =!= -1 &&
x._5.serverCost =!= -1 &&
x._5.depreciation =!= -1 &&
(x._1.maintAgreement === false || x._5.maintCost =!= -1)
))
//The join works if I don't have these filters. I have other filters I left out that do work for simplicity and readability
The values that have isDefined called on them are just Options I pass into the function as filters. This code above works fine with MySQL but MSSQL doesn't seem to like it. Any thoughts?
What does it take for q=q.filter(!_._1.approved) to work at least? I've tried _._1 === false as well.
EDIT The debug info for the Query...
[debug] s.s.s.BaseSession - Preparing statement: select top 100 percent s25."id", s25."category", s25."tool", s25."supplier", s25."manufacturer", s25."licenseType", s25."version_edition", s25."location", s25."keyContact", s25."supplierContact", s25."supportedBy", s25."licenseEnd", s25."maintAgreement", s25."maintEnd",s25."count", s25."pricing", s25."compatibility_notes", s25."other_notes", s25."approved", s26."id", s26."name", s27."id", s27."nameFirst", s27."nameLast", s27."email", s27."phone", s28."id", s28."nameFirst", s28."nameLast", s28."email", s28."phone", s29."id", s29."price", s29."licenseOneTime", s29."depreciation", s29."maintCost", s29."serverCost", s29."purchCostSaved", s29."maintCostSaved", s30."id", s30."name", s30."city", s30."state", s30."country", s30."contact" from "licenses" s25, "categories" s26, "contacts" s27, "contacts" s28, "pricing" s29, "locations" s30 where (((((s25."category" = s26."id") and (s25."keyContact" = s27."id")) and (s25."supplierContact" = s28."id")) and (s25."pricing" = s29."id")) and (s25."location" = s30."id")) and (not s25."approved") order by s26."name", s25."tool", s25."manufacturer", s30."name", s25."supplier"
The useful part is the very end. Note that this is only querying when approved is not defined.
Solution What I did was change my projections to do a type conversion during the apply methods.
def * = id ~ category ~ tool ~ supplier ~ manufacturer ~ licenseType ~ version_edition ~
location ~ keyContact ~ supplierContact ~ supportedBy.? ~ licenseEnd.? ~ maintAgreement ~
maintEnd.? ~ count ~ pricing ~ compatibility_notes.? ~ other_notes.? ~ approved <> (
{l => License(l._1,l._2,l._3,l._4,l._5,l._6,l._7,l._8,l._9,l._10,l._11,l._12,
(if(l._13 == 1) true else false),l._14,l._15,l._16,l._17,l._18,(if(l._19 == 1) true else false))},
{(l:License) => Some(l.id,l.category,l.tool,l.supplier,l.manufacturer,l.licenseType,l.version_edition,
l.location,l.keyContact,l.supplierContact,l.supportedBy,l.licenseEnd,(if(l.maintAgreement) 1 else 0),
l.maintEnd,l.count,l.pricing,l.compatibility_notes,l.other_notes,(if(l.approved) 1 else 0))})
It's not the ideal solution, but it works and is better than changing my case class to Int =].
