Answer from database empty list, list with tuple

[Константин Комков]

I want to have answer which contain id or None, but now in my query I have answer as empty list or list with tuple.

It's my query (version 1):

row = db_xml.executesql("SELECT FIRST 1 CASE WHEN V_CODE={0} THEN ID ELSE NULL END AS ID FROM ABIT_VALIDATION_CODES WHERE A_EMAIL='{1}' AND SESSION_DATE>DATEADD(-2 MINUTE TO CURRENT_TIMESTAMP) ORDER BY SESSION_DATE DESC".format(db._adapter.dialect.quote(request.vars.code),db._adapter.dialect.quote(request.vars.email.strip())))

It's my query (version 2):

cutoff_time = datetime.datetime.now() - datetime.timedelta(minutes=2)

condition = db_xml.abit_validation_codes.V_CODE==request.vars.code

typeOfAnswer = condition.case(db_xml.abit_validation_codes.id,None)

row = db_xml((db_xml.abit_validation_codes.A_EMAIL==request.vars.email.strip()) & (db_xml.abit_validation_codes.SESSION_DATE>cutoff_time)).select(typeOfAnswer,orderby=~db_xml.abit_validation_codes.SESSION_DATE).first()

Both of them do not work correct.

[Константин Комков]

Version 2 can return

<Row {'_extra': {"CASE WHEN (abit_validation_codes.V_CODE = '3099323') THEN abit_validation_codes.id ELSE NULL END": None}}>

[villas]

1. Note that this will fail if no email var is passed.
request.vars.email.strip()

2. For the second query,  print the SQL to the console using ._select()

Are you getting the SQL you expected?