MySQL Bugs: #31067: MS Access with VARCHAR NOT NULL columns

Bug #31067	MS Access with VARCHAR NOT NULL columns
Submitted:	17 Sep 2007 21:13	Modified:	10 Mar 2008 13:01
Reporter:	Adam Radford
Status:	Verified
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	5.1.3	OS:	Microsoft Windows (XP SP2)
Assigned to:		Target Version:
Tags:	varchar, null, odbc51_postga
Triage:	D2 (Serious)

Description:
VARCHAR columns that are set to "NOT NULL DEFAULT ''" behave differently when used via MS
Access and ODBC (either 5.0.1 or 3.51) compared to TEXT NOT NULL.

Basically, if a column is declared as VARCHAR NOT NULL, I am able to enter information
into an MS Access field bound to that table (using linked tables), but am then unable to
delete all the information from that field - I receive an error message, "You tried to
assign the null value to a variable that is not a variant data type".

How to repeat:
I have created a simple test example.

Create a table as:

CREATE TABLE `nullproblemtable` (
  `i_phone_id` int(11) NOT NULL auto_increment,
  `varchar_not_null` varchar(100) NOT NULL default '',
  `varchar_null` varchar(100) default '',
  `text_not_null` text NOT NULL default '',
  `text_null` text default '',
  `d_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`i_phone_id`)
) ENGINE=MyISAM AUTO_INCREMENT=100;

Create a new Access database (I am using Access 2002, but the problem may exist on newer
versions too). 

Go to File -> Get External Data -> Link Tables. Select the table just created.

Now go to "FORMS" and create a form based on the table using the wizard - Accept all the
defaults.

Now enter some data into all four text fields (but not the ID or timstamp) and save the
record.

Now go back to the record and try to delete the information - You should (hopefully) find
that you cannot delete the information from the VARCHAR NOT NULL field with receiving the
"You tried to assign the null value to a variable that is not a variant data type" (you
must try to delete all the information in that field). 

All other fields should allow you to delete the data within the fields though.

Suggested fix:
Not sure - TEXT fields work, even if set to NOT NULL, but they have some characteristics
that are less ideal to us than VARCHAR fields.

Equally, the database cannot easily be changed to have NULLABLE fields - This would
require us to alter a lot of the logic within other parts of our application.

Thank you for the bug report.

Hi Adam,

please try it again with MyODBC version 5.1 and send us a task file, if the error still
occurs.

Regards,

Susanne

I have tried it with version 5.1.0-alpha and the bug still occurs. It has occured in every
version I have tried including 3.51.

I'm afraid I don't know what a task file is? Could you explain please? 

I have already included a simple step by step guide to reproducing the problem when I
reported this problem, is this not enough?

Thanks,

Adam.

Sorry, changed version incorrectly in Version field. Now correct.

I wasn't able to repeat with 5.1 however with Access 2003.

I have just tried this again with a freshly installed version of Windows XP, with MySQL
5.0.45 and ODBC 5.1, using MS Access version 2003 (11.6566.8132) SP2. I have also tried
it on 4 other computers and have experienced exactly the same issue on all of them - All
I can suggest is that you didn't follow the instructions in my original bug report
correctly?

I followed the instructions exactly as specified in the original bug report and can again
confirm that I was UNABLE to delete information entered and saved into the VARCHAR NOT
NULL column, but WAS able to delete the information entered and saved into any other
column.

Please note - The record has to be SAVED (ie. use the record arrows at the bottom of the
screen to navigate to a different record before deleting the information from the problem
column).

In a comment recorded previously, I note that you requested that I sent a TASK FILE to
you. I am happy to do this if you can tell me what one of these is and how I will do
this.

Showing the bug

Attachment: ma_03.PNG (image/png, text), 20.44 KiB.

Thank you for the feedback. Now I was able to repeat (I attached a picture).

same problem with int data type
any solvations?

Table properties

Attachment: bug31067.JPG (image/jpeg, text), 47.99 KiB.

Hi all. I altered the field in question to have "test" as default value but it still
doesn't show in linked table properties window for not_null varchar field. Now, either we
do not pass everything correctly to Access or this is just the way it behaves... In any
case, there is a legitimate workaround by setting default value for field in question in
linked table designer window. I will make more tests to see what Access is asking and
what info does 5.1 provide.

It seems there is a problem in c/ODBC after all...

CREATE TABLE `nullproblemtable` (
`i_phone_id` int(11) NOT NULL auto_increment,
`varchar_not_null` varchar(100) NOT NULL default 'test',
`varchar_null` varchar(100) default ”,
`text_not_null` text NOT NULL,
`text_null` text,
`d_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`i_phone_id`));

SQLColumns("test", "test", "nullproblemtable", "varchar_not_null" ...) yields wrong
result, ie. COLUMN_DEF == <Null>:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
"COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE",
"REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH",
"ORDINAL_POSITION", "IS_NULLABLE"
"test", <Null>, "nullproblemtable", "varchar_not_null", 12, "varchar", 100, 100, <Null>,
<Null>, 0, "", <Null>, 12, <Null>, 100, 1, "NO"

Using SQLColumns(<empty_string>, <null_string>, "nullproblemtable", "varchar_not_null"
...) gives correct result:
Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
"COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE",
"REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH",
"ORDINAL_POSITION", "IS_NULLABLE"
"test", <Null>, "nullproblemtable", "varchar_not_null", 12, "varchar", 100, 100, <Null>,
<Null>, 0, "", "'test'", 12, <Null>, 100, 1, "NO"

Alas, Access uses <empty_string>, <empty_string> ... thus c/ODBC provides no default
field values:
MSACCESS        1ae8-1f68	ENTER SQLColumnsW 
		HSTMT               10771EB8
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 
		WCHAR *             0x0013A154 [      -3] "nullproblemtable\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 

Verified as described with 5.0.54BK on WinXP Pro SP2 localhost using latest c/ODBC
snapshot.

Tonci Grgn: “In any case, there is a legitimate workaround by setting default value for
field in question in linked table designer window.”

That doesn’t work. I just tried it. I set EVERY Required (NOT NULL) Memo (VARCHAR or
TEXT) field to Default of “""” (empty string), and every Required Numeric (INT or any
other numeric type) to Default of “0”, and every Required DateTime to Default of
“Now()” (VBA function available in Access).

The same error message still occurs, when attempting to paste records copied from the
very same table in as new records.

Access put the records into a “Paste Errors” Table for me which is a native Jet
database table. I then attempted to make an Insert Query to insert all of the records
from it back into the original table, with modifications pre-made as needed, the Primary
Key field omitted (auto_increment), etc.

The SQL of said query:

“INSERT INTO zen_product_type_layout ( product_type_id, configuration_title,
configuration_key, configuration_value, configuration_description, sort_order,
use_function, set_function )
SELECT 6 AS WineTypeID, configuration_title, configuration_key, configuration_value,
configuration_description, sort_order, use_function, set_function
FROM [Paste Errors];”

Same error. Both before AND AFTER trying your “legitimate workaround.”

With this in mind, and considering how important this functionality is for proper ODBC
use by any ODBC client, I recommend that the Severity rating be upgraded well beyond
“S3 (Non-critical)”.

MySQL 5.0.24a, MyODBC 3.51.23 (unable to use 5.1 because of problems with BIGINT fields
that SERIOUSLY mess up Access).

Update: in checking back to the Access Design View of the MyODBC Linked Table in question,
I found that it did NOT save my Default Value settings.

And for good reason: “This property cannot be modified in linked tables.” displays in
bright red text for the “Default Value” property. So, apparently, the problem does NOT
happen with your legitimate workaround. It’s simply FLATLY IMPOSSIBLE TO IMPLEMENT your
“legitimate workaround.”

I suppose I could set the Defaults on the actual MySQL tables as opposed to their MyODBC
links, but as this is a ZenCart database (as was probably obvious before), I really
don’t want to mess with the actual MySQL schema any more than absolutely necessary,
lest I break something.

Any ideas? This is CRITICAL for our business and those of our clients, and we NEED it
ASAP!! Next week is too late!

Joel, if you need anything done asap you should get support contract covering such
situations. We can't skip our schedule based on each report or personal sense of urgency.
I think all of you witnessed great improvement in both c/ODBC over last year or so and we
are truly trying to do our best.

Now for your questions:

> Same error. Both before AND AFTER trying your “legitimate workaround.”

I'm not all-knowing & unmistakable. This bears no impact on reported problem however, I
just tried to help.

> With this in mind, and considering how important this functionality is for proper ODBC
use by any ODBC client, I recommend that the Severity rating be upgraded well beyond
“S3(Non-critical)”.

I am not allowed to change severity, only original reporter is. Adam? From my side, I
marked this report *Serious / With NO workaround / With Widespread impact*. Can't really
do much more until it's picked up by devs.

> MySQL 5.0.24a, MyODBC 3.51.23 (unable to use 5.1 because of problems with BIGINT fields
that SERIOUSLY mess up Access).

I am aware Access, as well as any other MS tool, has problems supporting anything that
comes outside Microsoft, like BIGINT fields. But I think there's a patch committed
regarding this so you might want to search BugsDB.

Changed severity following discussion about bug.