MySQL Bugs: #27100: DTS Problem MSSQL Text to MySQL Text

Bug #27100	DTS Problem MSSQL Text to MySQL Text
Submitted:	13 Mar 2007 19:52	Modified:	11 Nov 12:38
Reporter:	Steve Roberts
Status:	To be fixed later
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	3.51.12,3.51.14	OS:	Linux (Ubuntu 6.06.2)
Assigned to:	Bogdan Degtyariov	Target Version:
Tags:	dts, sql server 2000, ODBC, MySQL, ms sql, text data type
Triage:	D3 (Medium) / R4 (High) / E4 (High)

Description:
I transfer data to our MySQL database (v5.0.22) from MSSQL 2000 using DTS.

Transfering: MSSQL.Text -> MySQL.Text but get "Query-based insertion or updating of BLOB
values is not supported."

I am using MySQL ODBC v3.51.12.

Before changing web hosts, this all worked.  The only difernence is that before we had
MySQL v4.1.11.

I found it worked by changing the MySQL data type to VARCHAR, but this datatype is
limited to a so many characters.

How to repeat:
Use MSSQL 2000 DTS to transfer (direct copy) a TEXT or NTEXT data type field with a
record of "abc" in it to a MySQL table with a TEXT data type field.

Run DTS package.

Suggested fix:
Unknown. :(

Tried to reproduce the problem with a very basic test:

SQL Table 'test_text_fields':
PK	auto_id	        int	4	Identity=Yes
	text_field	text	16	Allow Nulls

SQL Data
1       "hello world"

MySQL Table:
PK      auto_id         int(11)   No Null    auto_increment
        text_field      text      Yes NULL   Default NULL

No data in MySQL table.

DTS Package:
1x MS OLE DB Connector
1x MySQL ODBC 3.51 Connector
1x Transform Data Task

The Transform Data Task is doing a direct Copy Column transformation.

Under the Transformations tab, click on the 'text_field' transformation and click the
'Test' button.  Result: "Successfully completed execution of package."

If you then execute the task, you get the following error message: "Query-based insertion
or updating of BLOB values is not supported."

Hope this helps to re-create the problem.  As I said before, we did not have any problems
working with MySQL 4.1; only since using MySQL 5.0 has this become a problem.

Thanking everyone who helps in advance,

  Steve

Verified on MyODBC driver 3.51.14

Hi,

Could you please tell me what "Verified on MyODBC driver 3.51.14" means?

Does it mean that the problem is with the MyODBC driver, or that you have just verified
that the problem exists using the MyODCB 3.51.14 driver?

Could you also let me know what happens next (is there anything I can do?, what else is
there for you to do? etc)?
Also, are there any estimated timescales? - Sorry, big boss has been asking!

I have also found a small work around that has worked for most tables but not all... I
changed the MySQL TEXT data type for the text fields to VARCHAR(2000).  This has worked
for most tables, but only if there are a couple of TEXT data type fields in that table. 
One table has several TEXT data type fields... this short term solution would not work as
the table (row length I think it was!) was too big.  Not sure if this will help... but may
help others who could be experiencing the same problem.

Thanks for all your hard work,

  Steve

Steve,

"Verified on MyODBC driver 3.51.14" means that the problem still exists in the new GA
release of MyODBC driver as well as in the previous 3.51.12 (version 3.51.13 was
skipped).

Next, this problem is prioritized accordingly to the number of paying customers affected
by the current bug. Critical problems of paying customers have the highest priority as
well as crashing bugs and bugs related to general functionality. As the current bug is
not related to any support issue, it will be fixed after the problems with the higher
priority. Unfortunately, I cannot provide any time estimates for it. However, it is quite
possible that this bug is resulted by something we are already working on (for example
bug#19065, which I am sure is the culprit of many problems including the current one).

This is probably another duplicate of Bug #13776.

Steve, Bogdan, what is the exact error code received?

Please see posted comment '[19 Mar 12:36] Steve Roberts' for the test info I did in March.

I would really like to see DTS project I can load into MSSQL2005 SSIS as it appears
there's no way I can replicate this problem on new MS SW.

As stated on 19 March 12:36, the package simply contains:
1x MS OLE DB Connector
1x MySQL ODBC 3.51 Connector
1x Transform Data Task

The Transform Data Task is doing a direct Copy Column transformation.

There is no extra VB script created for any of the process.

Steve, you misunderstood me. There is no way I can do your 1-2-3 trick in VS/MSSQL 2005
using SSIS. I thought I might be able to load your DTS project...

How do I get the DTS package to you to load?

I'm also having this problem.
Local server is Windows Server 2003
Local database is MS SQL Server 2000

MyODBC is 3.51.22

Remote server is Linux (Don't know what flavor)
Remote database is MySQL 4.0.27 (Can't get them to upgrade)

Writing to a varchar works great, but unfortunately I have a local field that has to be
at least 1000 characters long, which is fine for a MSSQL varchar, but is way to long for
a MySQL varchar.

Any help or suggestions on how to solve this would be great, as at the moment I'm going
to have to resort to writing something in PHP to parse through several  CSVs when it
should be as easy as creating a stored procedure.

I'm having the same problem too!! 
myodbc 3.51.23 
MySql 5.0.27 running on windows 2003 server
Sql Server 2005

DTS does not set the cursor to SQL_CURSOR_STATIC or _DYNAMIC. It just uses the default
forward-only cursor, which does not support BLOB inserts by in ADO by design:

http://support.microsoft.com/kb/175245

This bug occurred due to ADO limitations (BLOB inserts with forward-only cursors set by
default are not supported). The bug fix should be postponed until keyset-driven cursors
are implemented in MyODBC.