?
Solved

Mysql query fails only when I use MD5(id)

Posted on 2008-11-10
23
Medium Priority
?
552 Views
Last Modified: 2012-05-05
Hi.
Just moved a site to a new hosting provider. Suddenly all pages that use md5(id) in the query fails with the following error :

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

Please see my attached script - I can't really make a more simple example.
The script only fails in the MD5 scenario.

Hosting is a Windows 2003, fully patched. IIS6.

Any ideas, hints, tests, well almost anything goes. Please help me out here :-(

Cheers,
Mark


<%
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 not (rs.bof OR rs.eof) Then
	arrVarer = rs.getrows
End if
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>"
%>

Open in new window

0
Comment
Question by:trg_dk
  • 13
  • 10
23 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 22920453
Just a guess... Could it be that you are hitting a NULL value? I think MD5(NULL) will give the same error.
0
 
LVL 2

Author Comment

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

But thanks for the reply :-)

Mark
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22920493
The line (21) that gives you the error. Is it the same one as in your code snippet?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:trg_dk
ID: 22920501
Yes it is - it is the response.write arrVarer(0,i) that fails - this is the array that holds the md5 value.
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22920546
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

Open in new window

0
 
LVL 11

Expert Comment

by:Louis01
ID: 22920556
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

Open in new window

0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920560
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)

0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920561
sorry - didnt see your new code - will test right away
0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920565
same error:

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

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

Expert Comment

by:Louis01
ID: 22920587
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

Open in new window

0
 
LVL 11

Expert Comment

by:Louis01
ID: 22920596
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

Open in new window

0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920621
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
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22920650
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
%>

Open in new window

0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920668
?? 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 ?
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22920765
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)
0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920865
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
0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920894
It seems the value in the md5 is Byte()

Perhaps I can make it into a string ?
0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920918
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
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 1500 total points
ID: 22920937
MD5 function returns a binary string. It would seem that using the UPPER function might work.
SELECT UPPER(md5(id)), varenavn FROM produkter ORDER BY id;
0
 
LVL 2

Author Comment

by:trg_dk
ID: 22920946
Nope - fails again ?!
0
 
LVL 2

Author Closing Comment

by:trg_dk
ID: 31514983
This is for the effort more than for a solution.
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22921146
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
0
 
LVL 2

Author Comment

by:trg_dk
ID: 22921176
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question