Inserting into TEXT field from Oracle

[bdrunion]

I am inserting data into a mySQL table from Oracle via an Oracle Stored Procedure. I can load data into all fields, except for TEXT fields. I receive an INVALID syntax error whenever I try to insert data into a TEXT field. I have 3 fields which will have text > 255 characters so I can not set them to VARCHAR.

 

[Bastien]

Are there quotes or other troublesome characters in the data that is causing it to crap out? What is the error?

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[bdrunion]

There error I receive (even when just trying to select from a TEXT field via ODBC) is....

[Generic Connectivity Using ODBC][unixODBC][MySQL][ODBC 3.51
Driver][mysqld-4.0.17-standard-log]You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the right syntax
to use near '"ROT_MFG_SCHEDULER" WHERE "QUOTE_LINE_ID"=251432 AND "ORDER_LIN
(SQL State: 00000; SQL Code: 1064)
----------------

2 of the 3 text columns do not have troublesome characters and I can't select from them. It works if I set up a link in MS Access, so it's something with the way either Oracle HS or the Unix ODBC is configured, but I can't figure out what it is. One thing I noticed in the log files was that the MS Access query enclosed MySQL field names in single quotes, whereas the Oracle query enclosed them in double quotes.

 

[Bastien]

if you copy and paste the string into sqlPlus or something like that, do you get the same error?

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[bdrunion]

Yes, I get the same error if I do a simple select
(SELECT SHORT_TEXT FROM ROT_MFG_SCHEDULER@mysql in SQL*Plus, T.O.A.D, or anything using Oracle's SQL*Net. However, I can retrieve the data through Access (using ODBC), or in mysql.

 

[Bastien]

post some sample data


Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[bdrunion]

I was able to resolve the issue by doing 2 things:

1) The table in MySQL needed to have a primary key
2) The MySQL database needed to be started in --ansi mode