Bug #41418 php and asp return different results, show create table query

• Submitted: 12 Dec 2008 6:26
• Modified: 16 Dec 2008 5:44
• Reporter: Stuart Palmer
• Status: Duplicate
• Category: Connector / ODBC
• Severity: S2 (Serious)
• Version: 3.51
• OS: Any
• CPU Architecture: Any
• Tags: ?????, ASP, php, question marks, SHOW CREATE TABLE

[12 Dec 2008 6:26] Stuart Palmer

Description:
The same query produces a different result if I code with PHP or ASP.

In PHP the result returned is fine, however, with asp using "MySQL ODBC 3.51 Driver" the result of a 'show create table' returns many ?????????????? question marks.

How to repeat:
PHP:

<?php

$table = "_TABLENAME_";

$servername = "_SERVERNAME_";
$username = "_USERNAME_";
$password = "_PASSWORD_"; 

$con = mysql_connect($servername,$username,$password);

if (!$con)
{
	die('Could not connect: ' . mysql_error());
}
else
{
	mysql_select_db("_SCHEMA_", $con);
	$result = mysql_query("Show Create Table " . $table);

	while($row = mysql_fetch_array($result))
  	{
  		//echo $row[0] . " " . $row[1];
		echo $row[1];

  	}
}

mysql_close($con);
?>

ASP CODE:

<%

strTable = "_TABLENAME_"

strDriver = "MySQL ODBC 3.51 Driver"
strServername = "_SERVERNAME_"
strDatabase = "_SCHEMA_"
strUsername = "_USERNAME_"
strPassword = "_PASSWORD_"

strConnectionString = "DRIVER={" & strDriver & "};Server=" & strServername & ";Database=" & strDatabase & ";UID=" & strUsername & ";PWD=" & strPassword & ";"
	
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = strConnectionString
	
objConn.open

Set objRS = objConn.execute("Show Create Table " & strTable )

If NOT objRS.EOF Then
	Response.Write(objRS(1))
End If

objRS.close
Set objRS = Nothing

objConn.close
Set objConn= Nothing

%>

[12 Dec 2008 8:27] Tonci Grgin

Hi Stuart and thanks for your report.

This is actually duplicate of Bug#10491, see the discussion there. I will just quote part of my own hidden comment:
"This problem represents grave issue for all connectors. They can't work around it, at least not reliably. Ad hoc user queries like this do not allow connector to distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary."

Now, every connector is free to choose how to deal with this problem. PHP chooses to ignore and c/ODBC devs decided to add special flag "Always handle binary function result as character data" which you need to check in DSN or add to connection string in order for your sample to work.

Closing the report now.

[16 Dec 2008 5:44] Stuart Palmer

I have searched online on how to set the driver to "Always handle binary function result as character data" but I cannot find any information on this that works.

In my case I am using a connectionstring rather than DNS. How do I add this functions to my connection string in order to get valid results?

I have found references to 'a check box' but I am unsure where this box is to check to enable this, so I am a little in the dark as to how to fix this.

Many thanks
Stuart Palmer