MySQL Bugs: #38210: 5.2.2 .net adapter has some strange datatypes generated in teh dataset objects

Bug #38210	5.2.2 .net adapter has some strange datatypes generated in teh dataset objects
Submitted:	17 Jul 2008 18:03	Modified:	10 Apr 2009 11:59
Reporter:	Matthew Hill	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.2.2	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Decimal and Tinyint(1) dattypes in the database.

When you create a tableadapter in a dataset and generate some query methods (all done autmatically in Visual Studio)

Decimal values...

autmoted insert query generation requires a "string" value to be passed to the insert method..it enters the value into the database correctly..but for some reason the "insert query" demands a string not a decimal variable.

HOWEVER the autogenerated UPDATE query in the tabledapater wants a decimal variable to be passed into it...its not difficult to work around the issue but the inconsistency is kind of obnoxious.

ALSO.

Tinint(1) accepts True False boolian variables passed to it..however as with the decimal it demands a Byte variable for an insert and requires a bool variable for the update (once again these are the autogenerated query methods created inside the dataset tableadapter objects. As with the decimal variable this isnt a deal breaker but requires me to write handlers to convert my variables for the insert methods...sort of a pain.

MY SQL version is 5.0.51b  its the same in Visual Studio 2005 and VS 2008 

How to repeat:
Have a table in the sql database with a decimal and a tinyint(1) datacolumn.

INside Visual studio create a dataset and in the dataset create a tableadapter to the table. IN the table adapter add an insert query....the update query should be autogenerated in the background.

Create a class file object and in the class instantiate the table adapter.

create a method to handle the tableadapters insert method and create a method to handle the table adpaters update method.  

You should notice that the insert requires a string and byte for the decimal and tiney(int) datacolumns whiel the update accept decimal and bool variables for those same objects.

Suggested fix:
no suggested fix other than to request that the insert methods that are generated accept the correct datatypes so that conversions arent required.

Delete cmd params

Attachment: Bug38210-Del.JPG (image/jpeg, text), 35.54 KiB.

Insert cmd params

Attachment: Bug38210-Ins.JPG (image/jpeg, text), 32.90 KiB.

Update cmd params

Attachment: Bug38210-Upd.JPG (image/jpeg, text), 35.93 KiB.

Hi Matthew and thanks for your report.

Since info provided is not enough I used following environment:
  - MySQL server 5.0.68pb running on WinXP Pro SP2 localhost
  - c/NET 5.2.2 from our tags folder in source repo with VS2005Pro
  - Table structure and data as follows:
mysql> create table bug38210 (Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KE
Y,
    -> FldTiny TINYINT(1),
    -> FldDec DECIMAL(12,2));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into bug38210 values(NULL,1,12.50);
Query OK, 1 row affected (0.03 sec)

mysql> select * from bug38210;
+----+---------+--------+
| Id | FldTiny | FldDec |
+----+---------+--------+
|  1 |       1 |  12.50 |
+----+---------+--------+
1 row in set (0.00 sec)
  - Connection string as follows: server=localhost;user id=root;Password=*****;persist security info=True;allow zero datetime=True;ignore prepare=False;database=test;logging=True;port=5068;pooling=False

As you can see from attached images, I found no problem (not only with Decimal field, all fields check out OK) so if you do not have any more/new info to provide me with I'll close the report as CRp.

Intellisense of the insert command for decimal.

Attachment: decimal_string_insert.GIF (image/gif, text), 4.96 KiB.

intellisense of insert command for bool

Attachment: bool_byte_insert.GIF (image/gif, text), 3.07 KiB.

I dont know what it is you are doing but I doubt you are duplicating what I am.

I will reiterate this is a Visual Studio Dataset.xsd object with a table adapter to a table in a mysql 5.0.51b database.

the datatype for the column is a decimal  and a tineyint(1) for the second example.

In a class file I instantiate the table adapter using ths sample code 

private tblfertilizingeventTableAdapter _tblfertilizingeventAdapter = null;
        internal tblfertilizingeventTableAdapter FertilizingEventAdapter
        {
            get
            {
                if (_tblfertilizingeventAdapter == null)
                    _tblfertilizingeventAdapter = new tblfertilizingeventTableAdapter();
                return _tblfertilizingeventAdapter;
            }
        }

I then access teh insert method of the table adapter using this bit of code.

        public long AddFertilizingEvent(long? vineventid, int? fertid, int? apptypeid, long? measid, decimal? quant)
        {
            string s_quant = quant.ToString();

            long fertevid = Convert.ToInt64(FertilizingEventAdapter.InsertQuery(vineventid.Value, fertid.Value, s_quant, apptypeid.Value, measid.Value));

            return fertevid;
        }

notice that I have to convert my decimal to a string to insert it.  Notice the intellisense image I uploaded of this insert command..it is clearly requireing a string value for this decimal...and now look at the outgenerated code of the designer.cs file for the Dataset.xsd  I wont ost the whole code because it would be too long..only the key segment which CLEARLY is asking for a string value...for the quantused column

public virtual int Insert(global::System.Nullable<long> VineyardEventID, global::System.Nullable<int> FertlizerID, string QuantityUsed, global::System.Nullable<int> ApplicationTypeID, global::System.Nullable<long> MeasurementID) {
            if ((VineyardEventID.HasValue == true))

            if ((QuantityUsed == null)) {
                this.Adapter.InsertCommand.Parameters[2].Value = global::System.DBNull.Value;
            }
            else {
                this.Adapter.InsertCommand.Parameters[2].Value = ((string)(QuantityUsed));
            }

and now look at this bit of code FROM THE SAME Autogenerated code in teh designer.cs file

this.columnQuantityUsed = new global::System.Data.DataColumn("QuantityUsed", typeof(decimal), null, global::System.Data.MappingType.Element);
                base.Columns.Add(this.columnQuantityUsed);

here it clearly identifies the value of the column type as a decimal...and yet when the insert command is generated it is requiring a string value...

Now maybe that is by design and not a bug..if so its frankly a stupid design because it requires me to convert my decimal to a string value in order to use the insert method.  as I stated not the end of the world..but obnoxious.

If you review the other intellisense you can clearly see it is requiring a byte value for my tinyint(1) column..thus not allowing me to use a bool variable in teh insert causing me to have to run another conversion on teh varible before passing it into the insert method.

Here is similar code snippets from the autgened designer.cs file

        
        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
        [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Insert, true)]
        public virtual int Insert(string Pest, byte Active)

clearly requiring a byte not a bool variable. and yet here in teh autgened code it is clearly identifiedd as a bool column

                this.columnActive = new global::System.Data.DataColumn("Active", typeof(bool), null, global::System.Data.MappingType.Element);
                base.Columns.Add(this.columnActive);

I hope tihs give a clearer picture of what I am talking about.

Matthew, "I dont know what it is you are doing but I doubt you are duplicating what I am." is the essence of bug reporting... I probably am doing something different. But there are several other factors to include in equation:
 - I always use newer server
 - I almost always use c/NET latest sources
so it might just be that the bug is fixed but I need to find out when and why before making my ruling. Analyzing again.

Sorry Matthew I completely forgot about this. Is this still a problem for you using new versions of c/NET?

I checked the logs but nothing to be found.