MySQL Bugs: #25947: CreateFormat/CreateParameters Column of DataTypes collection incorrect for CHAR

Bug #25947	CreateFormat/CreateParameters Column of DataTypes collection incorrect for CHAR
Submitted:	30 Jan 2007 15:27	Modified:	2 Apr 2007 7:09
Reporter:	Sönke Schau	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.0.5	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
The CreateFormat Column of the DataTypes collection should contain a
"Format string that represents how to add this column to a data definition statement, such as CREATE TABLE. Each element in the CreateParameter array should be represented by a “parameter marker” in the format string. 
For example, the SQL data type DECIMAL needs a precision and a scale. In this case, the format string would be “DECIMAL({0},{1})”."

In the case of CHAR and VARCHAR the CreateFormat should be "CHAR({0})" resp. "VARCHAR({0})" and the CreateParameters Column should contain "Length".

The IsFixedLength Column should be "false" not "true"

I don't think there is a satisfactional solution for the SET or ENUM datatypes.

How to repeat:
const int SampleMaxLength = 100;
const int SampleMaxPrecision = 10;
const int SampleMaxScale = 3;

DbProviderFactory TargetDbProviderFactory = DbProviderFactories.GetFactory(
"MySql.Data.MySqlClient" );
using ( DbConnection TargetDbConnection =
TargetDbProviderFactory.CreateConnection() )
{
  TargetDbConnection.ConnectionString = "Server=localhost;User
Id=user;Password=secret;Database=PortalLogs";
  TargetDbConnection.Open();
  DataTable dtdt = DbConnection.GetSchema( "DataTypes", new string[] { } );

  foreach ( DataRow scdr in dtdt.Rows )
  {
    bool IsFixedPrecisionScale = Convert.ToBoolean( scdr["IsFixedPrecisionScale"] );
    bool IsFixedLength = Convert.ToBoolean( scdr["IsFixedLength"] );
    object[] Parameters = new object[( IsFixedLength ? 1 : 0 ) + ( IsFixedPrecisionScale ? 0 : 2 )];
    int index = 0;
    if ( IsFixedLength )
       Parameters[index++] = SampleMaxLength;
    if ( !IsFixedPrecisionScale )
    {
      Parameters[index++] = SampleMaxPrecision;
      Parameters[index++] = SampleMaxScale;
    }
    Console.WriteLine( scdr["CreateFormat"].ToString(), Parameters );
  }
}

Suggested fix:
Correct the values as stated in the description

Hi Sönke and thanks for your report. 
I can agree with you on the basis of common sense but still I would like you to point me to part of MS docs describing what should be in "CreateFormat" for VARCHAR columns as I'm unable to locate it looking in http://msdn2.microsoft.com/en-us/library/hyx0dh68.aspx.
Also, I can't run your example with c/NET 5.0.3, only with 5.0.5. Are you sure about the version?

Hello Tonci,
please look at http://msdn2.microsoft.com/en-us/library/ms254501.aspx; search for "CreateFormat" and see how the field is meant to be used by MS.
I used a version of 5.0.3 which I patched for my reported problems 25906 and 25907.
Maybe this is why you can't run the sample with 5.0.3.
Best regards
Sönke

Hi Sönke and thanks for clarification. Indeed it seems we're not following specs found in http://msdn2.microsoft.com/en-us/library/ms254501.aspx.

Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23063

Fixed in 5.0.7

A note has been added to the 5.0.7 changelog.