Mysql – Not able to update MySQL table using Oracle: Unknown column in ‘where clause’ {42S22,NativeErr = 1054}

MySQLoracleplsqlstored-procedures

I have the following tables:

MySQL table: jti_member_interact

CREATE TABLE `jti_member_interact` (
  `INT_MEMBER_ID` int(11) NOT NULL,
  `INT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `INT_SOURCE` varchar(1) NOT NULL,
  `INT_DATE` datetime NOT NULL,
  `INT_TYPE` varchar(255) NOT NULL,
  `COPY_TO_STG` varchar(12) DEFAULT 'NO',
  `NEW_STG_SEQ` int(11) DEFAULT NULL,
  `COPY_TO_STG_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INT_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=43670 DEFAULT CHARSET=utf8$$

Oracle table: MEMBER_INTERACT_MYSQL_STG

  CREATE TABLE "JTI_HTP"."MEMBER_INTERACT_MYSQL_STG" 
   (    "INT_MEMBER_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "INT_ID" NUMBER(10,0), 
    "INT_SOURCE" NVARCHAR2(1) NOT NULL ENABLE, 
    "INT_DATE" DATE, 
    "INT_TYPE" NVARCHAR2(30) NOT NULL ENABLE, 
    "INSERTING_DATE" DATE, 
    "MYSQL_ID" NUMBER(12,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

Basically, I needed to copy jti_member_interact into member_interact_mysql_stg with adding new columns as follow:

  1. MEMBER_INTERACT_MYSQL_STG.STG_INT_ID = NEW_SEQ
  2. MEMBER_INTERACT_MYSQL_STG.MYSQL_INT_ID = MEMBER_INTERACT.INT_ID
  3. MEMBER_INTERACT.COPY_TO_STG = 'YES', once the copy operation is completed.
  4. MEMBER_INTERACT.NEW_STG_SEQ = MEMBER_INTERACT_MYSQL_STG.STG_INT_ID

I have created the following procedure:

CREATE OR REPLACE PROCEDURE COPY_MYSQL_WEB_INT_TO_STG(
P_BATCH_NO IN NUMBER)
IS
BEGIN
    INSERT INTO MEMBER_INTERACT_MYSQL_STG
    SELECT "INT_MEMBER_ID",
      STG_SEQ.NEXTVAL,
      "INT_SOURCE",
      "INT_DATE",
      "INT_TYPE",
      CURRENT_DATE,
      "INT_ID"
    FROM "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE" des
    WHERE "NEW_STG_SEQ" IS NULL;
    COMMIT;
    FOR REC IN (SELECT  STG_INT_ID, INSERTING_DATE, MYSQL_INT_ID FROM MEMBER_INTERACT_MYSQL_STG) 
    LOOP
        UPDATE "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE" SRC
        SET "COPY_TO_STG"     = 'YES',
            "NEW_STG_SEQ"     = REC.STG_INT_ID,
            "COPY_TO_STG_DATE" = REC.INSERTING_DATE
        WHERE SRC.INT_ID = REC.MYSQL_INT_ID;
    END LOOP;
--    COMMIT;
END;

The procedure compiles successfully. However, when I run the procedure, I get the following error:

[MySQL][ODBC 5.1 Driver][mysqld-5.5.31-30.3-log]Unknown column 'jtipartn_mydb.jti_member_interact.INT_ID' in 'where clause' {42S22,NativeErr = 1054}
ORA-02063: preceding 2 lines from JTIPARTN_WEBSITE

Apparently, the error I get has to do with MySQL table jti_member_interact specially, the column INT_ID. I double checked the name, it is correct! I tried to change it to something else but I got the same error.

I search this error and I found it quite common in some MySQL versions (version 5). I could not find a way to fix my problem (I do not use any trigger here or USING statement). In addition, my knowledge in MySQL is very limited. More interesting is that I ran the same procedure on another MySQL table that has identical structure and column names and it worked perfectly!

When I query the column in normal query such as:

SELECT INT_ID
FROM "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE";

I get NO errors on both DB's (oracle & MySQL)! Only UPDATE statement cause errors.
In addition, I tried instead of SRC.INT_ID in where clause to use "INT_ID". I got the same error.
Using without double quote has the same the problem.
I have created another identical MySQL table and tried to update it and I got the same error.
The only difference between the table that I can update successfully and the the table I cannot, is that they belong to different MySQL databases (in different locations). But I do not know how to solve this problem.

Best Answer

by experience sometimes creating oracle tables by using ODBC produce errors.

so please try to do the following:

  1. create the table manually on oracle db by using sql plus.
  2. try to use capital letters for table and column names without double quotes (example: use TABLE_NAME not table_name or Table_Name)
  3. try to run your application to check if any errors appears.

also in where clause you have to check it. try:

WHERE SRC."INT_ID" = REC.MYSQL_INT_ID;

does it works?