1. Test Simple - POSITIVE
SQL:
SELECT COUNT(DISTINCT [t].[id])
FROM [dbo].[lime_survey_url_parameters] [t]
WHERE t.sid=:surveyid
Params:
array(1) {
[":surveyid"]=>
int(246225)
}
Result:
0
2. Test COUNT(DISTINCT)) - POSITIVE
SQL:
SELECT COUNT(DISTINCT [id])
FROM [dbo].[lime_survey_url_parameters]
WHERE sid = :surveyid
Params:
array(1) {
[":surveyid"]=>
int(246225)
}
Result:
0
3. Test original query (generating error) - traditional bind params - NEGATIVE
SQL:
SELECT COUNT(DISTINCT [t].[id])
FROM [dbo].[lime_survey_url_parameters] [t]
LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid])
WHERE t.sid=:surveyid
AND (questionl10ns.language=:language OR questionl10ns.language IS NULL)
AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
Params:
array(2) {
[":surveyid"]=>
int(246225)
[":language"]=>
string(2) "pl"
}
Error:
CDbCommand nie zdołał wykonać instrukcji SQL: SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error. The SQL statement executed was:
SELECT COUNT(DISTINCT [t].[id])
FROM [dbo].[lime_survey_url_parameters] [t]
LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid])
WHERE t.sid=:surveyid
AND (questionl10ns.language=:language OR questionl10ns.language IS NULL)
AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
. Bound with :surveyid=246225, :language='pl'
4. Test original query - method use bindVaule - NEGATIVE
SQL:
SELECT COUNT(DISTINCT [t].[id])
FROM [dbo].[lime_survey_url_parameters] [t]
LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid])
WHERE t.sid=:surveyid
AND (questionl10ns.language=:language OR questionl10ns.language IS NULL)
AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
Params:
array(2) {
[":surveyid"]=>
int(246225)
[":language"]=>
string(2) "pl"
}
Error:
CDbCommand nie zdołał wykonać instrukcji SQL: SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error. The SQL statement executed was:
SELECT COUNT(DISTINCT [t].[id])
FROM [dbo].[lime_survey_url_parameters] [t]
LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid])
WHERE t.sid=:surveyid
AND (questionl10ns.language=:language OR questionl10ns.language IS NULL)
AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
. Bound with :surveyid=246225, :language='pl'
5. Test original query - method use PDO - NEGATIVE
SQL:
SELECT COUNT(DISTINCT [t].[id])
FROM [dbo].[lime_survey_url_parameters] [t]
LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid])
LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid])
LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid])
WHERE t.sid=:surveyid
AND (questionl10ns.language=:language OR questionl10ns.language IS NULL)
AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
Params:
array(2) {
[":surveyid"]=>
int(246225)
[":language"]=>
string(2) "pl"
}
Error:
SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error
7. Test original query - without all parameters - POSITIVE
SQL:
SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE t.sid=246225 AND (questionl10ns.language='pl' OR questionl10ns.language IS NULL) AND (subquestionl10ns.language='pl' OR subquestionl10ns.language IS NULL)
Params:
array(0) {
}
Result:
0
8. Test original query - without language parameter - POSITIVE
SQL:
SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE t.sid=:surveyid AND (questionl10ns.language='pl' OR questionl10ns.language IS NULL) AND (subquestionl10ns.language='pl' OR subquestionl10ns.language IS NULL)
Params:
array(1) {
[":surveyid"]=>
int(246225)
}
Result:
0
9. Test original query - without one language parameter - POSITIVE
SQL:
SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE t.sid=:surveyid AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) AND (subquestionl10ns.language='pl' OR subquestionl10ns.language IS NULL)
Params:
array(2) {
[":surveyid"]=>
int(246225)
[":language"]=>
string(2) "pl"
}
Result:
0
10. Test original query - remove "subquestionl10ns.language" - POSITIVE
SQL:
SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE t.sid=:surveyid AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) AND (subquestionl10ns.language IS NULL)
Params:
array(2) {
[":surveyid"]=>
int(246225)
[":language"]=>
string(2) "pl"
}
Result:
0