SelectScalar problem

Aug 20, 2010 at 1:42 PM
Edited Aug 20, 2010 at 2:29 PM

Is there a way to get something like this to work:

 

var ssv = ServiceLocator.Get<ISimpleSavantU>();
var pUsername = new CommandParameter("username", Username);
var pPassword = new CommandParameter("password", Password);
result = (Guid?) ssv.SelectScalar<Guid?>("SELECT itemName() FROM users WHERE Username = @username AND Password = @password", pUsername, pPassword);

This select seems to cause two different types of problems:
  1. It somehow expects T to always be a mapped SimpleSavant Entity/Domain class
  2. None of the CommandParameters work because I always end up with the error: Unable to add command parameter with attribute name of 'username'. Command has no properties mapped to that attribute name. As if SimpleSavant would get confused by the itemName() in the query.

 

Coordinator
Aug 20, 2010 at 3:05 PM

Yes, command parameters must always be mapped to a domain class property for formatting purposes. You would need to do something like this:

 

result = (Guid?) ssv.SelectScalar<User>("select...", pUsername, pPassword);

 

This may seem like overkill for simple string parameters but it ensures that your parameter values are always formatted exactly like the domain attributes they're being compared with--even when using custom types or custom formatting of standard types.

If you're only using string parameters it may be easier to use string.Format() like this:

string.Format("SELECT itemName() FROM users WHERE Username = '{0}' AND Password = '{1}'", Username, Password);
(Don't forget to escape single quotes embedded in your strings by replacing ' with '' if you follow this method.)

If you absolutely must run these queries without any domain class you should check out Typeless Operations.

Aug 20, 2010 at 3:09 PM

Thanks Ashley. In the end I've reverted that part of the code to the original AWSSDK based version using string.format. I would have liked it though to be able to use SimpleSavant's Command Paramenter substitution without the need to work with an Entity but I think this may be just an edge use case :)

Coordinator
Aug 20, 2010 at 3:18 PM
Edited Aug 20, 2010 at 3:19 PM

Understandable.

But keep in mind there are also SimpleDB edge cases you may need to handle explicitly by going this route. It's possible for any select query (not just those returning many items) to timeout and return a nexttoken. Savant handles this for you transparently, even on SelectScalar queries.

Aug 20, 2010 at 3:32 PM

Yes I know about the cool NextToken support in SS. But in this case it was a nobrainer to revert to the native SDK because we are talking about exactly one value.