SQL syntax problems when upgrading from MySQL 4 to MySQL5

[Jakob Hellquist]

Hi,

I am running an e-learning site with MySQL4 where we recently upgraded a test version to MySQL5 in order to handle chinese characters in utf-8.

The page encoding is set to utf-8 and I manage to update tables with chinese characters alright. The result is correct also when browsing with SQLYog.

BUT: The online drills don't work anymore. The code basically stores user input values in arrays, compares the input with the correct answers in database tables and displays the results on the web pages using javascript saying "correct", "incorrect", total number of correct answers and alert boxes with teacher comments.

Since the upgrade I cannot make these online drills work, it seems that there is nothing stored in the arrays and no values are compared with the correct answers.

I have browsed several forums and understand that there are e.g. differences in JOIN syntax between version 4 and 5. Can anyone spot the general problem in the code below?

Regards,
Jakob H.

++++++++++++++++++++++++++++++++++++++++++++
'MYSQL DATABASE CONNECTION
set conntemp=server.createobject("adodb.connection")
conntemp.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=MYSQLSERVER;DATABASE=MYDATABASE;UID=MYUSERID;PWD=MYPWD;stmt=SET NAMES

'utf8';"


' Denna fil (formuläret hänvisar till sig själv)
sThisFile = Request.ServerVariables("SCRIPT_NAME")

' Tom tvådimensionell array som kommer att fyllas på vid rättning
Dim arUserAnswers()

' Tom sträng som kommer innehålla sammanfattningen av antal rätt
sAnswers = ""

' Om vi ska rätta

If Not IsEmpty(Request.Form) then

ReDim arExerciseItemId(50)
intIndex = 1

' Ifall vi ska rätta select ELLER text-formulär
If Request.Form("checkthis") = "text" OR Request.Form("checkthis") = "select" then

' Loopa igenom Request.Form collection
For Each varItem in Request.Form
If varItem <> "submit" AND varItem <> "exercisesubid" AND varItem <> "checkthis" then
arExerciseItemId(intIndex) = trim(Request.Form(varItem))
intIndex = intIndex + 1
End If
Next

' Ifall vi ska rätta ett radio-formulär
ElseIf Request.Form("checkthis") = "radio" then

ReDim arTemp(10,1)

iItems = int(Request.Form("RadioItems"))
' Loopa igenom Request.Form collection
For Each varItem in Request.Form
if varItem <> "submit" AND varItem <> "exercisesubid" AND varItem <> "RadioItems" AND varItem <>

"checkthis" then

' Om kontrollen inte har fler värden spara detta värde i arrayen
arTemp(intIndex,0) = varItem
arTemp(intIndex,1) = trim(Request.Form(varItem))
intIndex = intIndex + 1
End if
Next

For i = 1 To iItems
For i2 = 1 To 10
if CStr(i) = arTemp(i2,0) then
arExerciseItemId(i) = arTemp(i2,1)
Exit For
else
arExerciseItemId(i) = ""
end if
Next
Next

End if


' Hämtar rätta svaren
Set rsAnswers = Server.CreateObject("ADODB.Recordset")
sql="SELECT CorrectAnswerId, CourseID, ExerciseID, ExerciseSubID, CorrectAnswer, ExerciseItemId FROM CorrectAnswers

WHERE ExerciseId="& iExerciseId &" AND ExerciseSubId ="& Request.Form("exercisesubid") &" ORDER BY ExerciseItemId"

rsAnswers.Open sql, conntemp, 3, 3

' Sätt ToComment Recordset
Set rsComments = Server.CreateObject("ADODB.Recordset")

Dim iAnswers, iRightAnswers

' iAnswers = int(rsAnswers.RecordCount)
'Eftersom .RecordCount inte stöds av MySQL-drivrutinen

Set rsAnswersCount = Server.CreateObject("ADODB.Recordset")
sql="SELECT COUNT(0) FROM CorrectAnswers WHERE ExerciseId="& iExerciseId &" AND ExerciseSubId ="& Request.Form

("exercisesubid") &" ORDER BY ExerciseItemId"

rsAnswersCount.Open sql, conntemp, 3, 3
iAnswers = rsAnswersCount.Fields(0)

' Räknar antal rätt
iRightAnswers = 0

' Redim för att skapa dynamisk array
Redim arUserAnswers(iAnswers, 1)

' Array med lärarkommentar m.m som vi använder för att skriva ut en array i Javascript
Redim arJscript(iAnswers, 2)

iJscriptCount = 0

' Här läggs det rätta svaret (för att jämföras med user input), iCount är räknare
Dim arCorrectAnswers, iCount, iFoundIt

rsAnswers.MoveFirst

Dim storedCourseID, storedExerciseID, storedExerciseSubID
storedCourseID = rsAnswers.Fields("CourseID").Value
storedExerciseID = rsAnswers.Fields("ExerciseID").Value
storedExerciseSubID = rsAnswers.Fields("ExerciseSubID").Value

for i=1 to iAnswers

if Len(arExerciseItemId(i)) > 0 AND arExerciseItemId(i) <> arInitValues(Request.Form("exercisesubid"), i) then

' Splitta rätta svaren
arCorrectAnswers = Split(rsAnswers.Fields("CorrectAnswer").Value, "|", -1, 1)

' Kolla efter rätt svar i arrayen
For iCount = 0 to Ubound(arCorrectAnswers)
if arExerciseItemId(i) = arCorrectAnswers(iCount) then
iFoundIt = iFoundIt+1
Exit For
Else
iFoundIt = 0
End if
Next

if iFoundIt > 0 then

arUserAnswers(i,0) = "Rätt!"
arUserAnswers(i,1) = ""
iRightAnswers = iRightAnswers+1

'Annars loopa över mistakes med samma ExerciseItemId
else

if Request.Form("checkthis") = "text" then

' Hämtar CommonMistakes
Set rsMistakes = Server.CreateObject("ADODB.Recordset")
sql="SELECT CommonMistakeId, CommonMistake, TeachersComment FROM CommonMistakes WHERE

CorrectAnswerId="& rsAnswers.Fields("CorrectAnswerId").Value &" ORDER BY CorrectAnswerId"
rsMistakes.Open sql, Conntemp, 3, 3

iToComment = 0

' Finns ett common mistake?
Set rsCommonMistakesCount = Server.CreateObject("ADODB.Recordset")
sql="SELECT COUNT(0) FROM CommonMistakes WHERE CorrectAnswerId="& rsAnswers.Fields("CorrectAnswerId").Value &" ORDER BY

CorrectAnswerId"

rsCommonMistakesCount.Open sql, conntemp, 3, 3
iCommonMistakes = rsCommonMistakesCount.Fields(0)
if iCommonMistakes > 0 then


rsMistakes.MoveFirst
Do While Not rsMistakes.EOF
if arExerciseItemId(i) = rsMistakes.Fields("CommonMistake").Value then
arUserAnswers(i,0) = ""
arUserAnswers(i,1) = printJscript(rsMistakes.Fields("CommonMistakeId").Value)
arJscript(iJscriptCount,0) = rsMistakes.Fields("CommonMistakeId").Value
arJscript(iJscriptCount,1) = rsMistakes.Fields("TeachersComment").Value
'arJscript(iJscriptCount,2) = Replace(rsAnswers.Fields

("CorrectAnswer").Value, "|", " eller ")
arJscript(iJscriptCount,2) = arCorrectAnswers(0)
iJscriptCount = iJscriptCount + 1
iToComment = 0
Exit Do
else
' Det finns records i CommonMistake men inget som mathcar detta fel
iToComment = iToComment+1

'arUserAnswers(i,0) = "Fel!"
arUserAnswers(i,0) = ""

' Användaren ska bara få ett svar som är rätt /2000-12-08
arUserAnswers(i,1) = "Rätt svar är: "&arCorrectAnswers(0)

' arUserAnswers(i,1) = "Rätt svar är: "&Replace(rsAnswers.Fields

("CorrectAnswer").Value, "|", " eller ")
rsMistakes.MoveNext
end if
loop

IF session("fullname") <> "demo" THEN

if iToComment > 0 then
' Uppdatera ToComment tabellen
sql="INSERT INTO ToComment (CorrectAnswerId, SubmittedAnswer, StudentQueryName)

VALUES ("& rsAnswers.Fields("CorrectAnswerId").Value &", '"& arExerciseItemId(i) &"', '"& myStudent &"')"
rsComments.Open sql, Conntemp, 3, 3
End if

END IF


else
' Det finns inget record i CommonMistakes för detta ExItemId
' Uppdatera ToComment tabellen

sql="INSERT INTO ToComment (CorrectAnswerID, SubmittedAnswer, StudentQueryName) VALUES ("&

rsAnswers.Fields("CorrectAnswerId").Value &", '"& arExerciseItemId(i) &"', '"& myStudent &"')"
rsComments.Open sql, Conntemp, 3, 3

' arUserAnswers(i,0) = "Fel!"
arUserAnswers(i,0) = ""
' Användaren ska bara få ett svar som är rätt /2000-12-08
arUserAnswers(i,1) = "Rätt svar är: "&arCorrectAnswers(0)
' arUserAnswers(i,1) = "Rätt svar är: "&Replace(rsAnswers.Fields("CorrectAnswer").Value, "|", "

eller ")

end if


else
arUserAnswers(i,0) = "Fel!"
arUserAnswers(i,1) = ""
end if
end if

else
' Användaren har inte skrivit något svar
arUserAnswers(i,0) = "Ej besvarad"
arUserAnswers(i,1) = ""
end if
rsAnswers.MoveNext
next

' Lagra användarens svarsprocent
Dim percentRight
percentRight = iRightAnswers / iAnswers * 100
Set rsUserValues = Server.CreateObject("ADODB.Recordset")
sql="INSERT INTO LearningProfile (CourseID, ExerciseID, ExerciseSubID, StudentQueryName, CorrectAnswers) VALUES

("&storedCourseID&","&storedExerciseID&","&storedExerciseSubID&",'"&myStudent&"','"&percentRight&"')"
rsUserValues.Open sql, Conntemp, 3, 3

end if

'//////////////////////////////////////////////////////////////////////////////////////////////////////////
' Funktioner
'//////////////////////////////////////////////////////////////////////////////////////////////////////////

Function printAnswers(iWhichExercise, iWhichRecord)
if int(Request.Form("exercisesubid")) = iWhichExercise then
if Not IsEmpty(Request.Form) then
printAnswers = arUserAnswers(iWhichRecord,0)&" "&arUserAnswers(iWhichRecord,1)
else
printAnswers = "&nbsp;"
end if
else
printAnswers = "&nbsp;"
end if
End Function

Function printSummary(iWhichExercise)
if int(Request.Form("exercisesubid")) = iWhichExercise then
if Not IsEmpty(Request.Form) then
printSummary = "<B>Du hade "& iRightAnswers &" rätt av "& iAnswers &" möjliga</B>"
else
printSummary = "&nbsp;"
end if
else
printSummary = "&nbsp;"
end if
End Function


' Skriver ut initvärden samt det användaren skrivit i rutan
Function printInitValues(iWhichExercise, iWhichRecord)
if int(Request.Form("exercisesubid")) = iWhichExercise then
if Not IsEmpty(Request.Form) then
printInitValues = arExerciseItemId(iWhichRecord)
else
printInitValues = arInitValues(iWhichExercise, iWhichRecord)
end if
else
printInitValues = arInitValues(iWhichExercise, iWhichRecord)
end if
End Function

' Kommer ihåg det alternativ användaren valt
Function radioSelect(iWhichExercise, iWhichRecord, sWhatToLookFor)
if int(Request.Form("exercisesubid")) = iWhichExercise then
if arExerciseItemId(iWhichRecord) = sWhatToLookFor then
radioSelect = "CHECKED"
else
radioSelect = ""
end if
else
radioSelect = ""
end if
End Function

' Kommer ihåg det alternativ användaren valt
Function selectSelect(iWhichExercise, iWhichRecord, sWhatToLookFor)
if int(Request.Form("exercisesubid")) = iWhichExercise then
if arExerciseItemId(iWhichRecord) = sWhatToLookFor then
selectSelect = "SELECTED"
else
selectSelect = ""
end if
else
selectSelect = ""
end if
End Function

Function printJscript(CommonMistakeId)
printJscript = "<a href='#' onMouseOver='alertBox("&CommonMistakeId&")'>"&sCommentLink&"</a>"
End Function
'+++++++++++++++++++++++++++++
' CLOSE DATABASE CONNECTIONS!
'+++++++++++++++++++++++++++++
rsAnswersCount.Close
Set rsAnswersCount=nothing

rsMistakes.Close
Set rsMistakes=nothing

rsCommonMistakesCount.Close
Set rsCommonMistakesCount=nothing

rsAnswers.Close
Set rsAnswers=nothing

rsUserValues.close
set rsUserValues=nothing

rsComments.close
set rsComments=nothing

conntemp.close
set conntemp=nothing
'//////////////////////////////////////////////////////////////////////////////////////////////////////////
' Slut funktioner
'//////////////
%>