Oracle to MySQL Community edition SQL error

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
epsidude
Premium Member
Premium Member
Posts: 17
Joined: Fri Feb 27, 2009 10:14 am

Oracle to MySQL Community edition SQL error

Post by epsidude »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Try putting single quotes around the parameter marker. (I am assuming that short_name is a string data type of some kind.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
epsidude
Premium Member
Premium Member
Posts: 17
Joined: Fri Feb 27, 2009 10:14 am

Post by epsidude »

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.
roy
Participant
Posts: 2598
Joined: Wed Jul 30, 2003 2:05 am
Location: Israel

Post by roy »

Hi,
if you try to auto-generate the sql statement what happens?
Roy R.
Time is money but when you don't have money time is all you can afford.

Search before posting:)

Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Image
epsidude
Premium Member
Premium Member
Posts: 17
Joined: Fri Feb 27, 2009 10:14 am

Post by epsidude »

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.
Post Reply