Mysql query fails only when I use MD5(id)

Just a guess... Could it be that you are hitting a NULL value? I think MD5(NULL) will give the same error.

Louis01 : Nope - not possible.
And SELECT md5(null) returns NULL - no error.

But thanks for the reply :-)

Mark

The line (21) that gives you the error. Is it the same one as in your code snippet?

Yes it is - it is the response.write arrVarer(0,i) that fails - this is the array that holds the md5 value.

Does changing lines 19 to 23 to the following change anything?

Response.write "<tr>"
For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>" & arrVarer(r,c) & "</td>"
		Response.write "<td>"& arrVarer(r,c) &"</td>"
	Next
Next
Response.write "</tr>"& vbCrLf

Select all Open in new window

Sorry - my code snippet has errors. Should be:

For r = LBound(arrVarer,1) TO UBound(arrVarer,1)
	Response.write "<tr>"
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>" & arrVarer(r,c) & "</td>"
	Next
	Response.write "</tr>"& vbCrLf
Next

Select all Open in new window

Hmm - the code fails in the For loop - havent seen this kind of FOR loop before (in ASP at least)
Error :
Microsoft VBScript compilation  error '800a0410'
 Invalid 'for' loop control variable
 /firebee_test.asp, line 21 For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)

sorry - didnt see your new code - will test right away

same error:

Microsoft VBScript runtime  error '800a000d'
 Type mismatch
 /firebee_test.asp, line 23

line 23 = Response.write "<td>" & arrVarer(r,c) & "</td>"

What type of field is [varenavn] and what kind of data does it fail on?
To test - try:
(Check in your DB what is in the [id] & [varenavn] fields for the record it fails on)

on error resume next
Response.write "<tr>"
For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>Row " & r & ", Col " & c & " = " & arrVarer(r,c) &"</td>"
		if err.number <> 0 Response.end
	Next
Next
Response.write "</tr>"& vbCrLf
on error goto 0
Response.write "</tr>"& vbCrLf

Select all Open in new window

Wrong again. Sorry

on error resume next
For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)
	Response.write "<tr>"
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>Row " & r & ", Col " & c & " = " & arrVarer(r,c) &"</td>"
		if err.number <> 0 Response.end
	Next
	Response.write "</tr>"& vbCrLf
Next
on error goto 0

Select all Open in new window

HI Louis
This doesnt really change anything other than it doesnt show the error

You can test the page her :
http://raskerollinger.dk/firebee_test.asp?md5=true

The querystrign md5=true - change it to false and you'll see the query without md5

the value for varenavn is string

I think maybe you get 0 rows...
(Carefull using NOT and OR together... Gets very confusing)

<%
Session.LCID = 1030
dbsetup = "Driver={MySQL ODBC 3.51 Driver}; Server=xxx; Port=3306; Option=131072; Stmt=; Database=xxx;uid=xxx; pwd=xxx;"
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open dbsetup
 
If request.querystring("md5") = "true" Then
	sql = "SELECT md5(id), varenavn FROM produkter ORDER BY id;"
Else
	sql = "SELECT id, varenavn FROM produkter ORDER BY id;"
End If
 
Set rs = dbConn.Execute( sql )
if rs.eof Then
	Response.write "0 rows returned."
else
	arrVarer = rs.getrows
	Response.write "<a href='?md5=false'>UDEN md5 (SQL = SELECT id, varenavn FROM produkter ORDER BY id;)</a><br/><a href='?md5=true'>MED md5 (SQL = SELECT md5(id), varenavn FROM produkter ORDER BY id;)</a><br/><br/>"
	Response.write "<table width='300' border='1'><tr><td>ID</td><td>Varenavn</td></tr>"
	if UBound(arrVarer,2) > 0 Then
		For i = 0 TO Ubound(arrVarer,2)
			Response.write "<tr><td>"& arrVarer(0,i) &"</td><td>"& arrVarer(1,i) &"</td></tr>"& vbCrLf
		Next
	End if
	Response.write "</table>"
End if
rs.close
%>

Select all Open in new window

?? Not sure I follow what you mean - you can see the SQL query's right there

If request.querystring("md5") = "true" Then
        sql = "SELECT md5(id), varenavn FROM produkter ORDER BY id;"
Else
        sql = "SELECT id, varenavn FROM produkter ORDER BY id;"
End If

It is the exactly same query, only difference is the md5 encoding - unless I'm mistaking ?

If (for example) there is a bug in MySQL ODBC 3.51 Driver that causes 0 rows to be returned when you use the MD5 function in a specific way, we can concentrate on that.
In my last post, I changed the code slightly to skip code if rs returned 0 rows. If we know rs returns 0 rows (no errors), we can concentrate on why the query does not behave as expected.
Maybe a simpler way would be better...
if rs.eof Then
  Response.write "0 rows returned."
else
   Response.write "More than 0 rows returned."
end if
(Next step would be to test the getRows function)

Hi Louis

I updated the page - still get's more than 0 rows. Also - it is only when I try to response.write it

Perhaps we should look at the data type in the array

It seems the value in the md5 is Byte()

Perhaps I can make it into a string ?

Well - I found a byte to char function and tested it :

if UBound(arrVarer,2) > 0 Then
    For i = 0 TO Ubound(arrVarer,2)
        Response.write "<tr><td>"& B2S( arrVarer(0,i) ) &"</td><td>"& arrVarer(1,i) &"</td></tr>"& vbCrLf
    Next
End if
Response.write "</table>"

Function B2S(Binary)
Dim I, S
For I = 1 To LenB(Binary)
S = S & Chr(AscB(MidB(Binary, I, 1)))
Next
B2S = S
End Function

Now it can print that value - but WHY :-( ?

Louis - thanks for your help - if you have anything to add please do - I'll reward you the 500 pts for the effort.

Cheers,
Mark

Nope - fails again ?!

This is for the effort more than for a solution.

Hi Mark

The problem you experience has something to do with the Character set.

It appears that the way the MD5 function behaves were changed. (The MD5(), SHA1(), and ENCRYPT() functions should return a binary string, but the result sometimes was converted to the character set of the argument. - Bug#20536)

For more info on Binary Strings (the type returned by the MD5 function) see http://dev.mysql.com/doc/refman/4.1/en/binary-varbinary.html

(Someone else experienced the same problem. The workaround they did was to use an external MD5 function. See http://forums.aspfree.com/asp-development-5/md5-hash-57661.html)

I cannot find anything on converting a binary string to a normal string (although I did not try CAST(MD5(id) as ???)

Cheers

Hi Louis

Thanks for your time - I did the same...

Function B2S(Binary)
Dim I, S
For I = 1 To LenB(Binary)
S = S & Chr(AscB(MidB(Binary, I, 1)))
Next
B2S = S
End Function


Cheers,
Mark