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:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[30 Jan 2007 15:27] Sönke Schau
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
[12 Mar 2007 10:31] Tonci Grgin
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?
[12 Mar 2007 17:02] Sönke Schau
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
[12 Mar 2007 17:57] Tonci Grgin
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.
[27 Mar 2007 18:41] Bugs System
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
[27 Mar 2007 18:41] Reggie Burnett
Fixed in 5.0.7
[2 Apr 2007 7:09] MC Brown
A note has been added to the 5.0.7 changelog.