Description:
When you create a view that contains a fixed integer number as a field, calling GetSchema() will yield the DATA_TYPE of this field as an Int (32-bit) field. However, when you use a data adapter to retrieve data from this field, the data adapter returns the integer as a BIGINT (64-bit).
How to repeat:
This issue and behavior can easily be replicated. First, create a new MySql database. Then create a new table called Artists:
|------------------------------------------------------------|
| Field Name | Data Type | Comments |
|------------|-------------|---------------------------------|
| ArtistId | INT | Primary Key / Auto-Increment |
| FirstName | VARCHAR(45) | |
| LastName | VARCHAR(45) | |
|------------------------------------------------------------|
Now insert at least one row into this table:
INSERT INTO Artists (FirstName, LastName) VALUES ('Bill', 'Clinton');
Now create a new view in MySql called MyView containing this SQL SELECT statement:
SELECT
`FirstName` AS `FirstName`,
`LastName` AS `LastName`,
9 AS `Nine`
FROM
`Artist`
Finally, open Visual Studio and create a new C# console application and add a reference to MySql.Data.Dll and type in the following program (change the connection string, of course):
using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace ConsoleApplication5
{
class Program
{
static void Main(string[] args)
{
const string connectionString = "user id=userid;password=password;database=TestDb;server=mysql-server;convertzerodatetime=True;port=3306";
string schemaDataType = "";
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
using (DataTable table = connection.GetSchema("Columns"))
{
foreach (DataRow row in table.Rows)
{
if (!((string) row["COLUMN_NAME"]).Equals("Nine")) continue;
schemaDataType = (string)row["DATA_TYPE"];
break;
}
}
connection.Close();
}
// schemaDataType == "int"
Console.WriteLine(schemaDataType);
DataTable tbl;
using (var connection = new MySqlConnection(connectionString))
{
using (var command = new MySqlCommand("SELECT * FROM MyView", connection))
{
connection.Open();
using (var adapter = new MySqlDataAdapter())
{
using (var ds = new DataSet())
{
adapter.SelectCommand = command;
adapter.Fill(ds);
tbl = ds.Tables[0];
}
}
connection.Close();
}
}
// adapterDataType == "Int64"
// schemaDataType != adapterDataType // WHY?
string adapterDataType = (tbl.Rows[0]["Nine"]).GetType().ToString();
Console.WriteLine(adapterDataType);
}
}
}
Running this program will first print "int", which is what GetSchema() is reporting as the data type. According to the MySql Guide, an INT is 4 bytes of storage (32-bits).
Below that, using a MySqlDataAdapter, the program prints "Int64", which is obviously 64-bits.
Suggested fix:
My "hack" to temporarily get around this issue is to create a function in MySql that converts a 64-bit number into a 32-bit number:
CREATE FUNCTION BigToInt (n BIGINT) RETURNS INTEGER RETURN n;
Then I modify my view, like this:
SELECT
`FirstName` AS `FirstName`,
`LastName` AS `LastName`,
BigToInt(9) AS `Nine`
FROM
`Artist`
Description: When you create a view that contains a fixed integer number as a field, calling GetSchema() will yield the DATA_TYPE of this field as an Int (32-bit) field. However, when you use a data adapter to retrieve data from this field, the data adapter returns the integer as a BIGINT (64-bit). How to repeat: This issue and behavior can easily be replicated. First, create a new MySql database. Then create a new table called Artists: |------------------------------------------------------------| | Field Name | Data Type | Comments | |------------|-------------|---------------------------------| | ArtistId | INT | Primary Key / Auto-Increment | | FirstName | VARCHAR(45) | | | LastName | VARCHAR(45) | | |------------------------------------------------------------| Now insert at least one row into this table: INSERT INTO Artists (FirstName, LastName) VALUES ('Bill', 'Clinton'); Now create a new view in MySql called MyView containing this SQL SELECT statement: SELECT `FirstName` AS `FirstName`, `LastName` AS `LastName`, 9 AS `Nine` FROM `Artist` Finally, open Visual Studio and create a new C# console application and add a reference to MySql.Data.Dll and type in the following program (change the connection string, of course): using System; using System.Data; using MySql.Data.MySqlClient; namespace ConsoleApplication5 { class Program { static void Main(string[] args) { const string connectionString = "user id=userid;password=password;database=TestDb;server=mysql-server;convertzerodatetime=True;port=3306"; string schemaDataType = ""; using (var connection = new MySqlConnection(connectionString)) { connection.Open(); using (DataTable table = connection.GetSchema("Columns")) { foreach (DataRow row in table.Rows) { if (!((string) row["COLUMN_NAME"]).Equals("Nine")) continue; schemaDataType = (string)row["DATA_TYPE"]; break; } } connection.Close(); } // schemaDataType == "int" Console.WriteLine(schemaDataType); DataTable tbl; using (var connection = new MySqlConnection(connectionString)) { using (var command = new MySqlCommand("SELECT * FROM MyView", connection)) { connection.Open(); using (var adapter = new MySqlDataAdapter()) { using (var ds = new DataSet()) { adapter.SelectCommand = command; adapter.Fill(ds); tbl = ds.Tables[0]; } } connection.Close(); } } // adapterDataType == "Int64" // schemaDataType != adapterDataType // WHY? string adapterDataType = (tbl.Rows[0]["Nine"]).GetType().ToString(); Console.WriteLine(adapterDataType); } } } Running this program will first print "int", which is what GetSchema() is reporting as the data type. According to the MySql Guide, an INT is 4 bytes of storage (32-bits). Below that, using a MySqlDataAdapter, the program prints "Int64", which is obviously 64-bits. Suggested fix: My "hack" to temporarily get around this issue is to create a function in MySql that converts a 64-bit number into a 32-bit number: CREATE FUNCTION BigToInt (n BIGINT) RETURNS INTEGER RETURN n; Then I modify my view, like this: SELECT `FirstName` AS `FirstName`, `LastName` AS `LastName`, BigToInt(9) AS `Nine` FROM `Artist`