DSXchange

Hi,

I have a requirement in which I have to pull data out of 1 oracle table and load into 1 MySQL Community edition table.

MySQl Community is unsupported I have read, however I found an article here on how to setup the ODBC driver. This step is complete. I can connect via ./example program and was able to select, insert, update, delete, truncate.

Datastage the client is also able to connect. I was able to create a table, and do not get connection errors. However here is the issue.

Running SQL! I have tried everything I can think of and all I get is SQL errors.

The job is an ODBC stage reading from Oracle into an ODBC stage writing to MySQL Community.

This is the error I receive:
-------------------
CopyOffJ_Stage_LATTE_ENROLLMENT_SQL_NO_TRF..ODBC_71.IDENT1.Source_Load: DSD.BCIPut call to function SQLExecute failed.
SQL statement:INSERT INTO `moodle2_enrol`.`latte_enrollment`(`short_name`) VALUES (?)
SQLSTATE=37000, DBMS.CODE=1064
[DataStage][SQL Client][ODBC][MySQL][ODBC 5.3(w) Driver][mysqld-5.5.50-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 '?)' at line 1

short_name = 151AMST-98B-1
-------------------
I have the ODBC Update setup to be "insert rows without clearing".


If I choose user defined SQL and hard code bind value it will perform the insert, and insert this value for the number of rows that come from the source.
`moodle2_enrol`.`latte_enrollment`(`short_name`) VALUES ("DATA")


Does anyone have any thoughts on what this issue could be?

I have tried:

1) the DRS stage and no matter which option I choose it says invalid number of columns which happens with user created SQL. I get this error even with "Insert rows without Clearing".

2) The DRS connector, same error as DRS Stage

3) The ODBC connector same error as ODBC stage

4) I have tried creating a file of insert statements and in the DRS Stage chose user defined SQL file.

Thank you

Try putting single quotes around the parameter marker. (I am assuming that short_name is a string data type of some kind.)

Thank you for the Suggestion Ray. I gave it a try and it inserted "?" in as values. So still searching for solution, right now I am trying building a 26mb SQL insert statement and will try reading that in as a parameter.

Hi,
if you try to auto-generate the sql statement what happens?

Auto-Generate give the Syntax error listed above saying issue near the ?. This is from the ODBC stage. When I use the DRS stage, Datastage says not enough columns, even when the SQL is auto-generated.