let sqlTest = `
SELECT *
FROM User u
WHERE state = 'LIVE'
AND NOT EXISTS(SELECT 1 FROM UserBlock ub1 WHERE ub1.blockedUserId = u.userId AND ub1.blockerUserId = UNHEX(:currentUserId))
AND NOT EXISTS(SELECT 1 FROM UserBlock ub2 WHERE ub2.blockerUserId = u.userId AND ub2.blockedUserId = UNHEX(:currentUserId))
LIMIT 4;
`;
let usersDbResponse = await dataApiClient.query(sqlTest, { currentUserId: currentUserId });
This does not work:
let sqlTest = `
SET @loggedUserId = :currentUserId;
SELECT *
FROM User u
WHERE state = 'LIVE'
AND NOT EXISTS(SELECT 1 FROM UserBlock ub1 WHERE ub1.blockedUserId = u.userId AND ub1.blockerUserId = UNHEX(@loggedUserId))
AND NOT EXISTS(SELECT 1 FROM UserBlock ub2 WHERE ub2.blockerUserId = u.userId AND ub2.blockedUserId = UNHEX(@loggedUserId))
LIMIT 4;
`;
let usersDbResponse = await dataApiClient.query(sqlTest, { currentUserId: currentUserId });
The error message in CloudWatch (long line):
BadRequestException: Database error code: 1064. Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM User u WHERE state = 'LIVE' AND NOT EXISTS(SELECT 1 FROM Use' at line 2 | 2021-06-09T20:14:13.298Z 4cb8dae9-bc14-4a8d-8e51-85ef14baca21 ERROR Error retrieving users for game: BadRequestException: Database error code: 1064. Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM User u WHERE state = 'LIVE' AND NOT EXISTS(SELECT 1 FROM Use' at line 2
-- | --
How should I pass a variable to a SQL variable (SET)?
The reason I need this is because my SQL statement is a bit complex and long and very often I use the AWS query editor and it is better having to change only one thing rather than a lot.
Thanks a lot for suggestions!
EDIT:
I just realised this does not work (same error message). So, the issue is not passing the variable, but using SET.
Do you know why I cannot use SET?
Thanks!
let sqlTest = `
SET @loggedUserId = '3D5F858CF29F4A8289DD9D034F2E943C';
SELECT *
FROM User u
WHERE state = 'LIVE'
AND NOT EXISTS(SELECT 1 FROM UserBlock ub1 WHERE ub1.blockedUserId = u.userId AND ub1.blockerUserId = UNHEX(@loggedUserId))
AND NOT EXISTS(SELECT 1 FROM UserBlock ub2 WHERE ub2.blockerUserId = u.userId AND ub2.blockedUserId = UNHEX(@loggedUserId))
LIMIT 4;
`;
let usersDbResponse = await dataApiClient.query(sqlTest);
Hello,
This works:
This does not work:
The error message in CloudWatch (long line):
How should I pass a variable to a SQL variable (SET)? The reason I need this is because my SQL statement is a bit complex and long and very often I use the AWS query editor and it is better having to change only one thing rather than a lot. Thanks a lot for suggestions!
EDIT:
I just realised this does not work (same error message). So, the issue is not passing the variable, but using SET. Do you know why I cannot use SET? Thanks!