One Star

How to execute a "ALTER TABLE" ?

How can I execute a "ALTER TABLE" ?
I am using Talend v2.0, java version, and my database is Oracle.
I tried with a tOracleInput, and I wrote my query "ALTER TABLE ..." in the field "Query" in the "Properties" tab. It runs ! , but in the "Run" tab, I have an exception :
Exception in component tOracleInput_3
java.sql.SQLException: ORA-01003: no statement parsed
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Odscrarr.receive(T4C8Odscrarr.java:203)
at oracle.jdbc.driver.T4CStatement.do_describe(T4CStatement.java:295)
at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:3181)
at oracle.jdbc.driver.OracleResultSetMetaData.<init>(OracleResultSetMetaData.java:63)
at oracle.jdbc.driver.OracleResultSetImpl.getMetaData(OracleResultSetImpl.java:133)

Is this a good solution or is there a better ?

It is a problem because even if it runs (the "ALTER TABLE"), the job is on error ! so, I can't use a "run if ok" and "run if error" correctly !

I tried to use a tOracleRow, but the query "ALTER TABLE" doesn't run ! There is no error when I execute the job, but the "ALTER TABLE" is not executed in the database !
6 REPLIES
Employee

Re: How to execute a "ALTER TABLE" ?

Hello
The correct component to use is the tOracleRow.
But all row components needs an input row to work.
So you have to insert a "fake" component like a tJava with a row main to the tOracleRow.

Best Regards,
Michaël
One Star

Re: How to execute a "ALTER TABLE" ?

Thank you very much... It runs ! ;-)
One Star

Re: How to execute a "ALTER TABLE" ?

I've tried something similar with a tMysqlRow with a request on Talend 2.0.0 - java version
My tMySQLRow contains many request :
"delete from XXXX; Insert into XXX (....) VALUES (...); ...'
Just like the missjustme, it doesn't run when it is alone. You said it needed an input...
So I plugged in a tJava control just like you said with a main row but it gives me the following error :
"If the component has ouput, there must be an input link to propagate the data."
So I tried to plug a rowGenerator with 1 row generated (I also set the propertie of my tMysqlRow to commit after 1 row).
And it still doesn't work!!!
Any idea?
Thx!
Employee

Re: How to execute a "ALTER TABLE" ?

Hello sologlobe,
I think your problem is that you cant' put many request in a single tMySQLRow.
Best Regards,
Michaël.
One Star

Re: How to execute a "ALTER TABLE" ?

tHDFSInput ------> tHiveRow
i am running above talend job , but i am getting below error anybody help me ?
: DataNucleus.Datastore - Error thrown executing ALTER TABLE VERSION ADD VERSION_COMMENT VARCHAR(255) NOT NULL : In an ALTER TABLE statement, the column 'VERSION_COMMENT' has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL.
anybody suggest me rectifying error, i am using Talend open studio 5.4, i dont have any metadata node in repository tree,
my requirement is i have to load data from hdfs to hive meta store or hive db, any other components required in my job? any one tell me the detailed process how to achieve my task.
Five Stars

Re: How to execute a "ALTER TABLE" ?

you are adding additional column with not null constraint to hive table which has some rows, that means this new column should be created with null values for existing rows. But constraints stopping it being null. either you can add default value along with alter statement or remove the not null constraint then update this column with values then again alter for not null.