Mike Hillyer is a Technical Writer for MySQL AB and lives in Alberta, Canada.
By Mike Hillyer
Visual Basic can be very loose with it's data types. If you want to avoid worrying about which type of data is coming from your database, you can simply not specify a datatype when you declare a variable, which defaults the datatype to variant. The variant should be avoided whenever possible, as it can be very CPU and memory intensive. Below is a table of the different MySQL and VB6 datatypes and how they correlate. One warning: often the VB6 datatype can hold a larger value than the MySQL equivalent (marked with a * in the notes section), in these situations you need to take care that the VB6 value is not to large to fit in the MySQL column. While you will not necesscarily get an error (you get a warning in the mysql client), data corruption will occur.
MySQL Datatype | Length | VB6 Datatype | Length | Notes |
---|---|---|---|---|
TINYINT | -128 to 127 | integer | -32,768 to 32,767 | * |
TINYINT UNSIGNED | 0 to 255 | byte | 0 to 255 | |
SMALLINT | -32,768 to 32,767 | integer | -32,768 to 32,767 | |
SMALLINT UNSIGNED | 0 to 65,535 | long | -2,147,483,647 to 2,147,483,647 | * |
MEDIUMINT | -8,388,608 to 8,388,607 | long | -2,147,483,647 to 2,147,483,647 | * |
MEDIUMINT UNSIGNED | 0 to 16,777,215 | long | -2,147,483,647 to 2,147,483,647 | * |
INT | -2,147,483,647 to 2,147,483,647 | long | -2,147,483,647 to 2,147,483,647 | |
INT UNSIGNED | 0 to 4,294,967,295 | double | 64 Bit | 1* |
BIGINT | 64 Bit | N/A | N/A | 2 |
FLOAT | 32 Bit Floating Point | single | 32 Bit Floating Point | |
DOUBLE | 64 Bit Floating Point | double | 64 Bit Floating Point | |
DECIMAL | Variable Floating Point | double | 64 Bit Floating Point | 3* |
CHAR | 1 to 255 Characters | string | 1 to Approx. 2,000,000,000 Characters | * |
VARCHAR | 1 to 255 Characters | string | 1 to Approx. 2,000,000,000 Characters | * |
TINYTEXT | 1 to 255 Characters | string | 1 to Approx. 2,000,000,000 Characters | * |
TEXT | 1 to 65535 Characters | string | 1 to Approx. 2,000,000,000 Characters | 4* |
MEDIUMTEXT | 1 to 16,777,215 Characters | string | 1 to Approx. 2,000,000,000 Characters | 4* |
LONGTEXT | 1 to 4,294,967,295 Characters | N/A | N/A | 5 |
all BLOB types | 1 to 4,294,967,295 Bytes | Variant | Varies | 6 |
DATE | Date without Time | date | Date and Time value | * |
DATETIME | Date and Time | date | Date and Time value | |
TIMESTAMP | Date and Time | date | Date and Time value | |
TIME | Time | date | Date and Time value | * |
YEAR | Year | integer | -32,768 to 32,767 | * |
ENUM | Enumeration of Value Set | string | 1 to Approx. 2,000,000,000 Characters | * |
SET | Set of Values | string | 1 to Approx. 2,000,000,000 Characters | * |
Notes:
1) There is no such thing as a 32 bit unsigned integer in
VB, so data must be stored in a double, which is actually a floating-point
datatype, therefore, be careful to make sure that any value that you will be
passing to MySQL is rounded off before you post it. The use of an UNSIGNED INT
with VB6 is not reccomended, but you may have some luck with the larger
integers of VB.NET.
2) BIGINT
cannot be used with ADO, as it will cause errors,
you can use the MyODBC option of 16384 (convert BIGINT
to INT
) to make them
funnel down, but from what I understand your BIGINT
is then limited to the size
of an INT
anyway. If a BIGINT
is brought into a long, you will be ok up to the
limit if the long datatype, after which your data will be corrupted.
3) DECIMAL
can be passed to different VB6 Datatypes
depending on how you define the DECIMAL
column when creating the table.
I personally would recommend staying away from the DECIMAL
type, as it
takes 1 byte per digit for storage, you are better off using the appropriate
numeric type.
4) If you have trouble loading TEXT
and MEDIUMTEXT
values from a recordset, make sure your max_allowed_packet
parameter in MySQL is set appropriately high. I have been able to load text from a MEDIUMTEXT
object up to the max_allowed_packet
limit set in my server.
5) LONGTEXT
data cannot be held in a string, and would
need to be treated as a BLOB
and written directly to a file using the
BLOB handling methods. If using the stream object, change the stream
type to adTypeText
instead of adTypeBinary
.
6) All blob data must be passed using either the getchunk
/appendchunk
method, or the ADO stream object. The
variant in this case becomes a byte array, and cannot be handled directly
by VB6.