0

I'm doing a MySQL query in an aws-lambda function.

I'd like to know if it's possible to have a js function inside an SQL query where it would use a selected item as it's parameter. like this

Left join users user
WHERE ( student.id = user.id AND student.age = '${Process_minimum_Age('user.dateOfBirth')}')

or a more realistic example

WHERE ( currentLocation = user.location AND currentTime = '${ moment().tz('user.timezone')}' )

I'm not sure how to refer back to SQL inside the parameter, when I try it I get the following error.

"errorMessage": "ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '='"

UPDATE:

Thanks to @H. Figueiredo who kindly and promptly helped me to figure what the problem and gave me the right answer. Since I was using template literals and the fact that user.timezone parameter was returning a string I had to wrap it with the following escape quote method .. '${moment().tz(`\'us.timezone\'`)
(it's a bit of a challenge escaping backticks here too :)

Dheeraj Kumar
  • 410
  • 6
  • 16
adimona
  • 109
  • 2
  • 15
  • Seems to me that you've created the table and fields with a certain collation (utf8mb4_general_ci) and are trying to insert data with a different collation. Check out this link: https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql – H. Figueiredo Mar 12 '19 at 09:51
  • Thank, I know the error says it's a problem with collation but when I remove the parameters inside wrapped single quotes , it won't give me that error but obviously the function wouldn't work. – adimona Mar 12 '19 at 10:01
  • Ok, so what does the var user.timezone contain? Have you tried to debug it before the query? – H. Figueiredo Mar 12 '19 at 10:11
  • user.timezone returns a string like 'America/Los_Angeles',. When I try to instead use the moment().tz('result.timeone')}' ouside of the the query and let's say inside the result.map it works fine. – adimona Mar 12 '19 at 10:27
  • Just a guess, but could this be your problem? https://stackoverflow.com/questions/26262648/phpmyadmin-1267-illegal-mix-of-collations-for-operation Not using backticks might make MySQL think you're invoking a var (that doesn't exist) – H. Figueiredo Mar 12 '19 at 10:30
  • Oh! Also in the ${ moment().tz('city') function. The city must be inside quotes! So in your case should be : '${ moment().tz(\"'user.timezone'\")}' – H. Figueiredo Mar 12 '19 at 10:32
  • ok let me try \".."\, BTW, when you said backtick you made me think .. cause I'm using https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals – adimona Mar 12 '19 at 10:37
  • Hum... Are your template literals wrapping the whole query? That might be the problem, can you try with using them? If you want more help lets move this into chat! – H. Figueiredo Mar 12 '19 at 10:39
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189874/discussion-between-h-figueiredo-and-adimona). – H. Figueiredo Mar 12 '19 at 10:40

0 Answers0