Bug #31067 MS Access with VARCHAR NOT NULL columns
Submitted: 17 Sep 2007 19:13 Modified: 25 Jan 2012 23:19
Reporter: Adam Radford Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1.3 OS:Microsoft Windows (XP SP2)
Assigned to: Lawrenty Novitsky CPU Architecture:Any
Tags: null, odbc51_postga, varchar

[17 Sep 2007 19:13] Adam Radford
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.
[18 Sep 2007 23:39] MySQL Verification Team
Thank you for the bug report.
[8 Oct 2007 16:08] Susanne Ebrecht
Hi Adam,

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

Regards,

Susanne
[8 Oct 2007 17:50] Adam Radford
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.
[8 Oct 2007 17:51] Adam Radford
Sorry, changed version incorrectly in Version field. Now correct.
[10 Oct 2007 16:07] MySQL Verification Team
I wasn't able to repeat with 5.1 however with Access 2003.
[15 Oct 2007 14:05] Adam Radford
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.
[15 Oct 2007 14:40] MySQL Verification Team
Showing the bug

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

[15 Oct 2007 14:46] MySQL Verification Team
Thank you for the feedback. Now I was able to repeat (I attached a picture).
[18 Jan 2008 16:43] Jdih Aar
same problem with int data type
any solvations?
[4 Feb 2008 13:06] Tonci Grgin
Table properties

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

[4 Feb 2008 13:15] Tonci Grgin
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.
[4 Feb 2008 18:46] Tonci Grgin
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.
[8 Mar 2008 0:01] Joel Rea
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).
[8 Mar 2008 0:08] Joel Rea
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!
[9 Mar 2008 15:23] Tonci Grgin
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.
[10 Mar 2008 12:01] Adam Radford
Changed severity following discussion about bug.
[11 Mar 2010 8:47] Adam Radford
Bug still seems to exist, nothing done to fix it, so upping priority to S1, in case it gets this fixed sooner.

Think this is justified as there is no workaround and this bug severely limits ability to use MS Access with MySQL.
[1 Jun 2010 15:28] Lawrenty Novitsky
I pushed the patch to working_tree branch, rev#899. By popular marketing technology(and what is becoming a tradition for me) that is "3in1" patch. It fixes the bug itself, then the bug spotted by tonci - no default value if catalog is specified, and then it contains whole new i_s version of the SQLColumns(which was pain to write).
To fix the original bug I made the driver not to lie to Access, but just not to tell it the whole truth we know about nullability of a particular column.
"The value returned for this column differs from the value returned for the IS_NULLABLE column. The NULLABLE column indicates with certainty that a column can accept NULLs, but cannot indicate with certainty that a column does not accept NULLs. The IS_NULLABLE column indicates with certainty that a column cannot accept NULLs, but cannot indicate with certainty that a column accepts NULLs." So I made it return to Access for "not null" fields  SQL_NULLABLE_UNKNOWN in NULLABLE, and YES in IS_NULLABLE columns. I didn't/couldn't ran into any problem caused by that change in Access .

For 2nd problem in the non-I_S version of the SQLColumns i made it to change working directory and then change it back. That also made everything a lot simpler.
No testcases were added, however i've been just thinking that i could add testcase for the default value problem. Will do that.
[2 Jun 2010 8:54] Tonci Grgin
Bug#54171 should be functional after this is fixed so please test that too.
[8 Jun 2010 19:23] Lawrenty Novitsky
the patch has been uncommitted from the branch. the fixed patch will have different revno
[7 Dec 2010 7:48] Bogdan Degtyariov
Verified with 5.1.8
[25 Jul 2011 9:47] Lawrenty Novitsky
Patch diff against rev#976

Attachment: bug31067.diff (application/octet-stream, text), 11.74 KiB.

[29 Jul 2011 15:33] Bogdan Degtyariov
Checked the patch with MS Access 2007, worked fine.
NOTE: it failed with non-patched version 5.1.8.
[2 Aug 2011 19:24] Lawrenty Novitsky
the patch has been pushed as rev#978
[25 Jan 2012 23:19] Philip Olson
This has already been documented, as:

MS Access fields with <literal>VARCHAR NOT NULL</literal>
columns could not be altered.

So, closing.